$16B health dept managed finances with single Excel spreadsheet. It hasn’t gone well The body that runs New Zealand’s public health system uses a single Excel spreadsheet as the primary source of data to consolidate and manage its finances, which aren’t in great shape perhaps due to the sheet’s shortcomings. The spreadsheet-using agency is Health New Zealand (HNZ) which was established in 2022 to replace 20 …
I've seen that too many times. Here is new system. Can it do a, b an c? Sure. Then they find out it can't really do c and nobody thought to ask for d, e and f. Then there is the inevitable infighting where you try to alter a process to accommodate the new system that can take months to plan out and implement.
Reminds me of a story my old Computing lecturer used to tell from a time she was a Systems Analyst.
She was part of a team that, during the 80s, implemented a huge new computer system for Southwark Council. I *think* it was for Housing, but don't quote me on that. Her team spent months designing the system, and further months implementing and installing it.
A year or so after the go live date, she was passing and popped into one of the offices to see how the system was going. She didn't see a sign of the terminal they had installed so the staff could access the new system, so she asked the staff about it. Apparently, the terminal was being used. As a door stop.
They will rue the day they maligned Excel. Just needs a lots of VBA to verify/audit and automate/front-end everything and they are good to go. It will however never appease auditors like Deloitte and they are in part to blame for the fore-gone disastrous conclusion the head-first dive into Oracle or SAP will be.
I mean, obviously Excel is the incorrect tool for the job - but most of the shortcomings that were flagged in the article are avoidable by building the spreadsheet correctly in the first place.
Also VBA is cheating. Everything they need to do can be done with raw formulae. That way you don't need to open up the pain of Macro-enabled workbooks.
Can't we use Python now? You can, in OO, and it has the advantage that it's a pain in the butt to edit or test, as well as being more obscure.
Where Python shines vs VBA is with multi-dimensional analysis. So if they decide they want to expand their organization into the 5th dimension, python in OO will be the way to go.
I don't believe that Excel is the right tool for a $NZ 28 bn consolidation and reporting - there are dedicated, well-established applications for that purpose. But it might also not be completely off, if done correctly - not sure about VBA though. The tool is rarely the problem and a fool remains a fool, as we all know.
Spreadsheet cells showing values, but hiding mathematical expressions that reference other spreadsheet cells showing....
VBA macros referencing spreadsheet cells through relative row-column addresses...
Refactor this application?
Document it?
Provide a suite of unit tests?
Holiday in Cambodia, anyone?
I'm not maligning Excel, or spreadsheets in general.
If the only tool you think you know how to use is a spreadsheet, then that's what you'll use. For everything. Presentations. Databases. Word processing. Image manipulation.
ERP systems frequently are overkill, and too-complex to bring more benefit than the time and money they waste, because they dictate and contort peoples's workflows.
Does anyone still use accounting software to do ... accounting?
Okay, so digging into this further at https://www.tewhatuora.govt.nz/assets/Uploads/HNZ-Financial-Review-Report.pdf they actually had a web of manually managed excel spreadsheets, and this central one got updated by people calling each other and going 'yeah my J345 value is...' so it took two weeks every single time they tried to consolidate, with all the errors that go along with that manual process and nobody involved knowing what they're doing.
This is pretty much what I expect from a government agency, because at least a giant evil corporation wants to make money, but for a government agency all this ramshackle gum and duct tape just creates more jobs.
No surprise there, but it does put the process ~35 years out of date -- that's when I was fixing this problem in paper spreadsheets by migrating to automatic consolidation.
I wonder if their immediate problem exists because everybody who understands spreadsheet automation has reached end-of-career and has retired?
Indeed.
I was once asked to update a payroll spreadsheet that included a 100 or more various different pay grades. There was a 2% across the board pay rise. Everyone got it. I simply added a new PA column and set it to be 2% greater than the $ value of the preceding column. Copied the formula to the bottom..
Great they said!
Then they printed it and got someone to manually check every line with a pocket calculator!
That's one of the problems with using complex spreadsheets ( and maybe other complex systems). Ultimately these just become "black boxes" in which you pour numbers in at the top and read other numbers off at the bottom. But there's no telling what the gubbins inside are actually doing to the numbers. At best the users are going to be highly suspicious of the outcomes, at worst they're right to be. The only checks would be to create a duplicate, blank, system, and feed sample numbers in to test for logical outcomes. And even that might not work, if the calculation processes are unknown maybe the expected results are unknown.
>” That's one of the problems with using complex spreadsheets ( and maybe other complex systems). Ultimately these just become "black boxes" in which you pour numbers in at the top and read other numbers off at the bottom. But there's no telling what the gubbins inside are actually doing to the numbers.”
That applies to all financial systems including Post Office Horizon…
Got a client that uses QuickBooks, they don’t trust the QB Payroll ( fully linked to HMRC and Pension provider) and refuse to let go of their Excel payroll worksheets, which contain many magic numbers and calculations based on assumptions which whilst valid when originally written won’t be valid come April (2025)…
... that I was made redundant (there is a theme here, more of that later*), I was told I would get "two months salary" as redundancy pay. Well, when I was told the amount I would get it was not 2 month's pay. Now there were three ways to work this out, as we had literally just been told our pay rises (yup, before we were told 60% of the company was being made redundant, we got told of our pay rises - don't ask me, I only worked there). So It could have been twice my pre-rise monthly pay (nope, did not add up), or one month at old pay and one at new pay) not that either or twice new pay - wrong again. So, BEING A COMPLETE IDIOT, I asked. Turned out that, for the purposes of redundancy pay, two months = eight weeks !
I will never understand accountancy.
* I have been made redundant several times, mainly due to the companies I worked for going 'TITSUP' (I hope I am not being too technical for you), although the last time was officially 'paid leaver' where I got my annual pay effectively tax free to leave (thank you BT) and have been happily retired ever since.
I will admit that I have done that in the past. Admittedly it was so the content could be printed out and as Excel's concept of printing is pretty much "open printer device, spew content at printer driver while inserting as many spurious blank pages and near impossible to track down layout issues", there is a reason for that.
I performed the calculations in Excel and copy and pasted the content (not data link, just the data) into Word so it could be presented in a vaguely presentable manner.
HNZ is a minnow compared to NHS England, said to be the largest employer in Europe. Comprised of a couple of hundred or more Trusts and hundreds more entities. .
https://www.england.nhs.uk/long-read/structure-of-the-nhs/
It's so big that they themselves can't explain the structure or funding themselves, instead directing the reader to resources produced by the Kings Fund.
The NHS England machine is a very big beast that has a huge appetite for £££
30 years ago our multi national organisation was bloated it had 11 levels between CEO and the grunts. To get a new screen you had put in a justification, which had to be signed off by 2+ people!.
Many of the approvers just approved... and never disapproved.
The CEO got rid of 3-4 layers of management, and said every manager can sign of expenses up to £500, and need a 2nd line approval for up to $5000. It was a breath of fresh air.
The head of one of the divisions went in with his "annual leave card" and asked the CEO to approve him taking a week off. The CEO said "You are in charge of a multi million division - if you cannot manage your own vacation you are in the wrong job".
I hear,that on the CEO's first day, his PA told him,"We've printed out all of your emails for you... I'm ready to take dictation for your replies". The CEO said "I'll do my own emails - unfiltered. Some I'll ask you to handle. Get me my email address NOW"
Who knows - it's the organisation that gave us this
https://www.theregister.com/2020/10/05/excel_england_coronavirus_contact_error/
May be if there are any NHS Whistleblowers, we'll get to know
Ah, but the NHS being so big and composed of so many entities is highly effective at avoiding accountability. I expect I'm not the only one on here who has been passed round a circle of 3 (or more) NHS entities, all of which claims that one of the others is responsible for dealing with the issue!
Sir Humphrey explains why a hospital with no patients requires even more workers.
Excel is okay-ish for short term data fiddling.
Once they file gets older and complicated and many people use it, the data is guaranteed to be unreliable if not plainly wrong. Sometimes it might be ok when you look at it, but good luck importing it into a database. Usually the only way is to go from excel to access and from access to sql server, but still with no guarantees of correctness.
I recall once setting up something quick in Access for a department head. But, I didn't use Access as the database engine; I just used it as the read-only UI. I tied that UI to our Oracle database via ODBC.
The department head was delighted with what I'd done. I later learned they had exported (cut-and-pasted?) certain data from the Access front end into Excel, where they generated reports which probably were better done with a simple SQL query...
To determine the age, count the rings of spreadsheets.
Look for the Apple ][ on life support in Accounts Receivable running VisiCalc that feeds to multiple Lotus 123 boxes in Shipping and Warehousing which export CSV to one IBM XT (the one with the new HDD card) running Multiplan to format data for Access which exports to a shared folder as a data source to an IIS webpage that is scraped into Excel sheets for middle management who retype the numbers into emails for the CFO's PA to put into a PowerPoint.
The monthly finance meeting has a standard itinerary item for the Accounts Payable guy to read from his Dataday pocket ringbinder. The final answer to "what is the age of this system" is written on the reverse of the 1980s London Underground map at the back of this weathered leatherbound volume.
CTO's summary: The Requirements Spec phase of the ERP transfer is entering its third year and we remain optimistic.
Spreadsheets are fine if you know what you are doing.
See - Dunning-Kruger effect. Lots of people believe they know what they are doing, but objective evidence often suggests otherwise. See, for example: https://www.theregister.com/2025/03/10/incident_response_advice/
As I wrote recently in one of my few published letters to the press:
"The most important thing to know about anyone in a position of authority, is how they react to being told they are wrong."
(Oh, and for the avoidance of doubt, if you have any decision making authority, THIS MEANS YOU.)
If you know what you're doing, have excellent attention to detail, first class keyboard skills, not being rushed by the boss. The other day I noticed some implausible numbers in an Excel sheet. The person who built it had selected an incomplete range in a calculation. Easily mistake to make and I blame their employer for making them use Excel when they are more than capable in SQL and other proper tools.
Why use a database if you don't need to. These dbases are just licenses to print money for the likes of SAP and ORACLE. New zealand total population is just ove 5 million, Birmingham is over 1.1 million for the city !!!!!!
The spreadsheet will be income/outgoings for the service deptartments with sub excel sheets for specfic depts and expenditure. So whats the problem.
AIUI the problem with Horizon was that the different sides of the ledgers were updated by separate transactions via an unreliable communication link. This is why you use database applications that wrap the whole business transaction into a single database transaction and that transation is either committed in its entirety or rolled back.
There were many more problems with Horizon than just that.
Starting with the utterly inappropriate development environment, Visual Basic which is suitable for hobbyist nightmares and not a lot else. Then add in utterly insane data practices which did convoluted things to the data for no remotely sane reason such as unnecessary application side data mangling, column stuffing storing numeric and date fields in string fields and so on - every elementary basic failure was exhibited. Throw in a bundle of lies that everything is perfect when there was ample evidence everywhere that it was not and a criminal culture mindset that "5% of transactions failing" was somehow acceptable - anything over 0% of transactions failing for anything other than expected and handled reasons is unacceptable.
The lack of atomicity in database operations naturally didn't help matters but it was far from the key failing
Managing those kind of financials in a spreadsheet is criminal malfeasance .. Everyone knowing of and using the spreadsheet for that propose should be arrested !!
I have seen worse .. Using a spreadsheet to access a data base and doing all of the Queries in the spreadsheet .. Though not criminal, it should be !!
Excel definitely has its uses... however extracting data from anything Oracle ("services", not Oracle SQL server in general but that's usually nasty enough) and in particular SAP will strain the sanity of anyone attempting it due to the batshit insane data structures in SAP and the junk that Oracle spews out to somehow prop up its "services".
They needed TM1 - ADDIN for Excel (Now owned by IBM)
Great deal of TM1 expertise in that part of the world.
Users "think" they're using Excel.
All the data is in cubes in server memory; business rules and calculations are kept in rules scripts; ETL from business sources is scripted and scheduled and any manual changes a user makes are restricted and logged.
Consolidations are instant, One version of the truth!
I never found anything better!
IanB
The Register 
Biting the hand that feeds IT
Copyright. All rights reserved © 1998–2025
