New Zealand's $16B health dept managed finances with single Excel spreadsheet
theregister.com
theregister.com
Notably, one major issue was through a significant reliance on the use of an Excel file to manage the consolidated financials of the organisation. This spreadsheet was the primary data file used by HNZ to manage its financial performance. It consolidated files from each district into a single spreadsheet, and key reports, such as the monthly finance report, were produced from it. The use of an Excel spreadsheet file to track and report financial performance for a $28bn expenditure organisation raises significant concerns, particularly when other more appropriate systems are present on the IT landscape.
This Excel file is flawed in that:
• Financial information was often 'hard-coded,' making it difficult to trace to the source or have updated data flow through.
• Errors such as incorrectly releasing accruals or double-up releases were not picked up until following periods.
• Changes to prior periods and FTE errors in district financial reporting Excel submissions, would not flow through to consolidated file.
• The spreadsheet can be easy to manipulate information as there is limited tracking to source information where information is not flowing directly from accounting systems.
• It is highly prone to human error, such as accidental typing of a number or omission of a zero.
--
[0] https://www.tewhatuora.govt.nz/assets/Uploads/HNZ-Financial-...
I don't think that fact is made very clear in the OP article.
Just the massive, massive ecosystem of online tutorials and answers and YouTube videos for every conceivable question [1] is incomparable.
[1] Except the one you want the answer to at a given moment, amirite fellas?
Everytime I relearn how to do a vlookup, none of the examples online do what I want. It's like they are trying to do something completely opposite of what I want. It's super weird, because what I want to do is always a pretty common thing to need done.
I'm not sure how that'd help in any of the cases where I use vlookup.
Excel has many issues, but the cost of replacing it is surprisingly high.
I'm going through this right now. Not anywhere in this scale, but just in terms of deciding whether to move an order-tracking/accounting system I've built in Excel to a "real" database.
The database I am considering (Panorama X for Mac) is quite inexpensive, I've heard very good things about it, and has a spreadsheet-like UI. However, I've used Excel enough to know that I haven't tapped more than a small fraction of its ability, especially things like Power Query. As much as I loathe VBA, what if the cost of moving to a "real" database isn't the up-front cost, but the longer-term inflexibility of Panorama (and, pretty much, anything else in my price range) compared to the beast that is Excel?
Nail guns have a lot of downsides vs using screws, but being much faster and cheap offsets quite a lot.
The reason for the saving was enforcement of constrains in various Access tables so a subtle entry error in Excel was discovered.
The transition to DB file was done by a single person in a month who had not worked previously with DB (however he was young and talented programmer I must admit).
And the Access DB in a single file with its UI was quite similar to Excel so guys maintaining it quickly learned it and really liked it.
So it is still puzzles me why such single file DB with nice UI are not more popular? Why it is always Excel?
Because it needs skills that often aren't immediately available whereas a lot of people have enough Excel experience to lash up even a moderately complex spreadsheet.
In a fair number of places that I've worked the finance and IT departments have a fairly substantial level of enmity which also discourages use of anything which involves them. Meanwhile, everyone still needs to do their jobs and there's a business analyst with decent Excel chops who will have something you can use “temporarily”. A few years pass, then a few more, and now everything lives in Excel as the calcified scar tissue of broken official processes…
A space seemingly ripe for Line Of Business Applications As A Service: LOBAAAS :-)
TBH, the whole SaaS took off because of the ability for individual departments to just get their resources using nothing but a company credit card.
If Deloitte is going to do this it will take 3 years, take about 20 people and cost tens of millions.
>> - as crappy as that is, thats pretty common.
No. This is not a common practice. An Accounting / ERP system with full audit trails and data entry compliance is. Exporting data from such a system into Excel for analysis is. But Excel as the [re-reads] Primary data file is not a practice at all.
Not sure what 'journals' is supposed to mean here but “consolidation, business-critical reporting, and analysis.” does not mean operations day to day use, it means high level management reporting
My position is that the article (or the consultants who are trying to sell the ERP) are exaggerating the role of the spreadsheet.
https://www.tewhatuora.govt.nz/assets/Uploads/HNZ-Financial-...
The phrase “consolidation, journals, business-critical reporting, and analysis.” is in the actual report referring to multiple spreadsheets (p60). But the Register has reported that as referring to the main spreadsheet. There's no way a top-level consolidated spreadsheet would also be journal entries for low level accounting.
And I note the report is by Deloitte who I trust about as far as I can throw them, although the organisation in question undoubtedly messed up here, Deloitte are angling for a nice juicy government ERP implenmentation lasting years and costing tens of millions
Yeah, everywhere I've worked that's common, even when the underlying data is managed much more successfully, the top folks really want everything on a spreadsheet.
Maybe there is an error in the reporting, but it can always be corrected. The idea isn't to explain where $16.129626356 bn dollars went, it's to explain where ~$16 bn dollars went.
I can think of many processes just off the top of my head where commercial software outputs something like a CSV file, I add my data to it, then generate a report. I don't think the process is insane at all. We catch errors by predicting the data to be entered and flagging if off by 10%, and then also looking at the distribution of the data entered (it should look like a skewed bell curve). We then have a stand-up meeting where we review the outputs and then have a process for later correction. Of course it's possible for errors to creep in, but the errors are very unlikely and have somewhat limited scope.
My point is that the situation in NZ seems entirely resolvable, and they shouldn't just throw it out if it was somewhat working. The whole "Excel" thing seems like a nothing-burger.
Few examples:
- incorrect schedule for the electricity grid for an entire country
- incorrect assessment of an airport use for an airline (causing few millions USD loss in revenue)
- incorrect financial position assessment for a mine (resulting incorrect deciosion to optimize the wrong business process, not sure about how much they lost)
Making illegal states unrepresentable is a concept that benefits programming langues and business processes alike.
That said, I’ve always thought there was a product that sits somewhere in between Excel and full blown custom software that provides some of the controls we need while still being and build able by someone with low/average technical skills
I suppose MS Access offers this too, although FM feels more user-friendly, MS Access felt like Internet Explorer 4, where an error dialog would pop-up for every little JavaScript error (disclaimer: this opinion is from 25 years ago).
I built a mini ERP for my father's company when I was 16. Started out as an excel spreadsheet, but then added inventory, accounting, and printing contracts and reports.
He kept using it until retirement and was very happy with it. I could learn & apply SQL. Win-win.
I'm on a Mac so can't move to Access. I've thought about FileMaker, but am considering Panorama X because unlike FileMaker it reportedly allows undoing almost anything, while FileMaker is like the typical database in a record commit not being undoable.
(Yes, I know that not allowing such is good practice, which is why I am not a database admin.)
I've heard good things about Panorama X, and it has a spreadsheet-like UI. However, I've used Excel enough to know that I haven't tapped more than a small fraction of its ability, especially things like Power Query. As much as I loathe VBA, what if the cost of moving to a "real" database isn't the up-front cost or conversion time, but the longer-term inflexibility of Panorama (and, pretty much, anything else in my price range) compared to the beast that is Excel?
App Script is quite powerful and easier to write and understand than VBA imho.
Untill, a consultant replaced it with an 'enterprise' solution that cost 10000x more to run and maintain.
Sometimes, well designed systems, however simple they might be, would be all that is required.
From a financial perspective, there are many ways you could enforce checks to ensure the model is balanced - it just takes time. Data entry can be an issue, but you can automate that too. The deloitte report is saying the health department should benefit from adopting a gigantic erp system but you could get 90% of the benefit by employing a couple people that really know what they’re doing.
You could say that excel should allow those things to happen but the flexibility is precisely what makes it so valuable.
The people writing the reports are consultants. Consultants recommend things that benefit consultants. In their case, a multi year process and tens of millions of dollars trying to install ERP software is more a windfall for the vendors and not the companies.
Sure but to compare it to tech, this is why we build tooling like linters and typed languages so that we don’t lose millions of dollars because we needed the average developer to implement a feature. I imagine in heath care it’s even more skewed because a SW eng can see the compiler output and make changes but the person entering the data isn’t (for example) the nurse that’s using the needles to be accounted for
Generally those aren't a fault of excel, but a fault of someone doing something dumb in excel. People do dumb stuff in every problem and it's nearly impossible to prevent it.
Austria did the same with their national health insurance, to reduce costs and organisational overhead. It massively increased costs and organisational overhead.
tl;dr: they promised savings of one billion Euros but it instead cost over 200 million.
I am not saying Germany should merge all insurances into one, but I still think 100+ is one order of magnitude too much.
I'm sure this was /s but capitalism doesn't work for things like insurance.
There was an article shared here on HN sometime in the middle of last year that broke down how versatile Excel is and how different companies are trying to steal just a niche use of Excel (I remember reading about Airtable doing the automation bit much easier than in Excel, some company doing charts easier than Excel and so on). I wish I could find it again.
My view is that if you switch companies, and at the last you used SAP and the new one you have to use Oracle, it is going to be a learning curve with very little added value for you (except the fact that now you know to proprietary systems). With Excel, well, it is going to be the same Excel, just different data.
excel is flexible and easy for reporting/analysis for data of varying sizes from small to not ginormous whereas doing anything in an ERP requires an, often, low paid SWE with a thick accent in a different time zone at, comparatively, high cost and big delay
i.e., I can often solve problems in excel in an afternoon that would take months to solve with one of these SWE's
Also opens a lot of doors for integrating with private tools or data sources, especially with MCP tooling or such. I published a POC of what I mean at https://supersheets.ai/.
[0]: https://www.notboring.co/p/excel-never-dies [1]: https://news.ycombinator.com/item?id=32346288
I understand the decision though, because investing in a IT system is so friggin expensive, require 10x more people and there is a huge lack of talent to deliver something on time, that works, and actually saves money.
In the past, the organisation has probably tried (maybe even several times) to replace the Excel mess with an IT system, only to receive something that costs them a lot of money and solves the wrong problem..
I guess the solution is to have an intermediary that can be the in-between, but don't have them be from the IT systems supplier, because they'll bone you (including promising you everything you want without checking with their developers if any of it is possible; with a good 3rd party they'd be able to ask the devs if it's possible and to see a proof-of-concept).
Or ever.
Excel allows managers/staff to iterate/change/adapt faster and independently while changing anything about an automated system requires much effort (in communication, in validation, in budget)
It's now a SharePoint database - I don't know what I think of that, but at least it was built for the job and has some solid integrations with other systems in it that make life easier.
Related story when NHS England copied-pasted stuff for COVID stats but most of the data got lost because they are using an old version and there was a 65k line limit...
> It gets worse: Health Minister Simeon Brown last week delivered a speech in which he said HNZ operates “an estimated 6,000 applications and 100 digital networks” – or roughly one application for every 16 staff members.
> He also said HNZ’s Senior Leadership Team members “have only just begun weekly in-person meetings, and have continued to operate from different offices, despite the majority living in Auckland and the organization being two and a half years old.” Most work in different buildings.
> However the Minister doesn’t have a plan to get off Excel, or fix HNZ’s tech leadership woes.
HNZ's Senior Leadership should be reassigned to clearing fatbergs from sewer mains, and some competent grownups brought in.
And probably similar, further up in the NZ government. Two and a half years is how long it took for the 1940's USA to go from Pearl Harbor to D-Day.
Also see ‘the financial black hole’ that ACC was supposedly in when John Key came along. It’s the playbook being played.
Don’t look to the US for guidance though, that healthcare system and that political direction is going to be fascinating to look at in two and a half years.
And if you do choose to accept it as unvarnished truth, welp, that's an ideological choice, not a rational choice.
The current government would dearly like to bring in more private providers to our healthcare systems, so are following the classic neoliberal playbook.
1) Underfund public services
2) Point out how poorly they're performing
3) Bring in private companies to do it "more efficiently"
4) Pay them the money that you refused to pay the public service. And then some more.
5) Private companies make bank
6) Services further worsen.
7) But hey, shareholder value was great.
If you want an example of this, just research the history of our railway system.
Or, you know, the last National government when the Minister of Health directed funding for surgery from the public system to the private system.
And then resigned to become the CEO of a private healthcare company.
Which is why when NZ comes in the top 3 of "least corrupt" countries, I painfully point out that we're in the top 3 of _perceived to be_ least corrupt countries.
Once again, The debacle that was NZRail -> TranzRail -> Toll -> KiwiRail is illustrative.
Toll, an Australian company, ran down our rail network even harder than TranzRail did, and then forced the government to buy it off them at a massive premium by threatening to just shutter our entire rail system.
And we gave fucking knighthoods to the two merchant bankers who advised the government to sell NZ Rail... _and then bought it_ in a consortium with an American company which asset stripped it and then chose not to maintain the railway lines because it provided them a significant tax write-off in it the US on accounts of depreciation or similar.
And of course, Sir Fay and Sir Richwhite now reside in overseas tax havens, although IIRC one of them still owns an entire island here.
Oh, and the NZ government had to pay for the release of one of their children when they were detained driving through Iran for their Instagram account about how everyone is kind.
Ah, neoliberalism.
(As you can tell, I have some strong feelings on this. Because I lived through it. Was it all bad? No. It gave us one of the most efficient agricultural sectors in the world.
Was it mostly bad? Well, inequality surged upwards in conjunction with neoliberal policies. So, yes.)
Like you are doing now?
>neoliberal playbook
Hardly. They've made almost no changes to the policies of the previous government, which increased the size of the public service by around 25%. They haven't even dropped it back by 20%!
The rest of your comment is very conspiratorial and repeats the sort of trite anti-capitalist sentiments that you see on places like /r/NewZealand. Hardly fitting for a tech forum like this one.
>Sir Fay and Sir Richwhite
You reveal yourself. No native New Zealand/Commonwealth/British English speaker would refer to someone as 'Sir [last name]'. There is no such person as 'Sir Fay' (he's 'Sir Michael Fay' or 'Sir Michael') and there's certainly no such person as 'Sir Richwhite' because David Richwhite didn't receive a knighthood.
As far as I can tell from their wikipedia pages, Sir Michael Fay lives in New Zealand and David Richwhite lives in London. Hardly tax havens. They own an island near Whitianga which they're doing conservation work on.
>(As you can tell, I have some strong feelings on this. Because I lived through it. Was it all bad? No. It gave us one of the most efficient agricultural sectors in the world. Was it mostly bad? Well, inequality surged upwards in conjunction with neoliberal policies. So, yes.)
Yes let's please go back to carless days once a week and having to apply to the government to be able to import foreign magazines. Let's go back to assembling Japanese cars in New Zealand because of stupid tariffs. Let's go back to making things here again, inefficiently, so there is no consumer choice, unless you like choosing between one or two domestic assemblers of goods really made overseas anyway.
Let's go back to not having state-owned enterprises but having them all be run as government departments within the civil service. What a great system that was, very efficient.
New Zealand is far better off because of those reforms. It has made everyone much richer. We are less equal than we were, but so what? Would you rather we were all equally poor or that we were all richer but some were richer than others?
>Oh, and the NZ government had to pay for the release of one of their children when they were detained driving through Iran for their Instagram account about how everyone is kind. Ah, neoliberalism.
Neoliberalism is when embassies help their citizens... or something?
> The rest of your comment is very conspiratorial and repeats the sort of trite anti-capitalist sentiments that you see on places like /r/NewZealand. Hardly fitting for a tech forum like this one.
It's what they did in the 90s, and it's what they are doing now. Obviously I'm not yearning for the days of Muldoon, and I acknowledged that the reforms have made our agricultural sector incredibly competitive. But you pretend like it didn't also do a lot of damage and saw critical national infrastructure asset stripped.
I'm sorry if I hurt your feelings by having an opinion different to yours or saying mean things about the political parties you support, but I'm allowed to have my own opinions.
I worked in a line of business leadership role for a multi-billion dollar organization. The ledger/financial ops stuff for super high-level reporting and payments were done by old mainframe apps. Everything else was done on a series of Excel spreadsheets hosted on a fileserver somewhere. All sorts of complex stuff was done in there. Finance guys are good at finance and frankly, stuff worked.
They eventually transitioned to a modern at the time Peoplesoft system. The spreadsheet system was better in almost every way to the business units. The ERP was better for the financial ops teams. The shortfalls of the various ERP modules meant that the excel work was pushed down to the lines of business -- we probably had exponentially more spreadsheets, made by idiots who had no idea what they were doing. It also worked, but it was slower and certainly alot more expensive. The tools available for things like budgeting were primitive and ineffective - I literally used a P-Card to buy quickbooks and a built my own little finance shop.
Yep, yep. There's yer trouble. Larry found another app to stack on his DB and he knows he's got the finance guys by the balls with vendor lock-in, so it doesn't matter how crap it is.
Fortunately under my BDFL rule he's going to Devil's Island.
> before the "aviator sunglasses" phase
Before? Already there. Got the combination cap too. Been practicing the smile I'll use for the portrait mural that every large building has to have painted on the side.
What is this?
How does something like SAP solve this problem? I would have loved to just drive by but actually let's have a discussion about it. Even Odoo or something more "move fast". How would I sell the product to someone who runs their excel spreadsheets and is price concious so will actually balk at the pricetag to replace the thing that works fine and passes audits.
You surely don’t mean Excel, do you? It did not pass the audit.
The main selling point of any ERP is that it may be used to solve the problem in a cost-efficient way. And the problem here is not a single report or aggregation of data sources, it is an enormous list of requirements, implementing which with a manual or semi-automated processes based on Excel would be even more expensive or risky. Those guys in NZ failed even to understand what they actually need (i.e. produce that list of requirements).
They aren't even able to produce a list of requirements, so not only do you need a consultant to generate the list, they then need another consultant to work with whichever solution provider to meet the requirements.
This will cost astronomical amounts of money, take years and in the mean time, what is the solution other than the excel documents and better processes.
The excel document didn't fail the audit, the processes did. I guarantee if they followed correct processes and did it in physical ledgers an auditor will pass that audit, they sure would be annoyed about it though.
As others say in this thread, the world runs on Excel.
Where all of the answers go.
2. name it 'prod'
3. Move ALL infra onto it
4. ????
The healthcare system of a country was run in Excel. Didn't you read the title?
> so it should be used less, not more
I never said it should be used more, I said the fact that it could be used for that, makes it an impresive achievement for the app.
Certainly the wrong tool for the job, but nevertheless impressive for both the technology and the operators if it somehow works.
Perhaps we should re-evaluate how and when we use things when presented with evidence that others are getting massive value out of tools we typically would have been biased against.
Still, if many successful enterprises started SQLite for things of this scale, I should probably reach out to them to see whether there's anything missing from my analysis.
Obviously I have read not just the title, but also the article.
>I said the fact that it could be used for that, makes it an impresive achievement for the app
You can try to use a fork to dig a tunnel, but it is hardly an impressive achievement of the fork that it can be used this way, neither you could possibly dig anything big with it. It would be an impressive achievement for Excel, if it actually worked, but the article mentions that there were problems with accounting done this way.
Excel is a powerful tool that can handle massive amounts of data, no doubt. However its feature set is not enough for this kind of tasks. As @zimpenfish said in another comment in this thread, the problem was both in Excel and in processes. Task-appropriate tools help to build good processes around them.
From what little information there is in the article, it sounds like lack of process was equally to blame as using Excel.
(Not that I would suggest using Excel, personally - I'd go with a small database and a simple web UI but then I'm not a Corporate Consultant looking to charge ££££ per day to faff around for 5 years and then abandon the project.)
There's nothing simple in running accounting in a healthcare system with 16B budget. A small database with simple web UI would have the same problems as Excel spreadsheet. The list of requirements for software automation of such thing would be on four digit number of pages scale. It does require a proper ERP.