Excerpt from the actual review document (Health NZ Financial Management Review, [0]):
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.
So actually top level planning/reporting is done by everything flowing into one big spreadsheet - as crappy as that is, thats pretty common. Thats not the same as "managed finances with single Excel spreadsheet". Like, they do have other systems.
25 years ago a small Norwegian company in oil industri replaced a big Excel that it were using to manage its inventory with Access database and discovered that they have like extra 200K USD in equipment.
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?
Someone builds an access database and it works for a bit but then they leave and no-one else knows how it works so they start exporting the data to excel instead. Access is all about restrictions (the form has these fields; the data is structured like so; input will be validated by these rules) ... Restrictions deliver some value but are brittle - its about as hard to maintain as any other custom built app would be.
as an anecdote, my own mother, who studied french in university and works in education, has built access DBs for places she's worked before. It seems its a very approachable and beginner friendly UI for non-programmers, somehow.
> why such single file DB with nice UI are not more popular?
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.
At many places there's also some nasty politics around siloing where a database is an IT System so it has to be owned by the centralized IT organization, operated by a DBA team, have a budget, be audited by security, etc. and half of those groups look down on Access and want to architect for job security by picking some Oracle/SAP product where even if you have an unlimited budget you are looking at years of guaranteed delay before you can use it with a significant risk of failure.
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…
> At many places there's also some nasty politics around siloing where a database is an IT System so it has to be owned by the centralized IT organization, operated by a DBA team, have a budget, be audited by security, etc. and half of those groups look down on Access and want to architect for job security by picking some Oracle/SAP product where even if you have an unlimited budget you are looking at years of guaranteed delay before you can use it with a significant risk of failure.
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.
Excel is really easy to use for smaller data collection and manipulation. There's no input validation, signing and other complicated design decisions needed to start an excel spreadsheet. There's alot of tools out there for easy integration. So its not something you need to switch away from once you have rather static requirements. Billions are just numbers that fit inside a cell just fine until someone complains.
FWIW, having a human regularly count the equipment would have also uncovered the error. As well as other interesting reasons that discrepancies arise, such as theft. Always important remember that these systems are tools, not the truth, and have to be reconciled to the truth on a regular basis.
Simple counting would not uncover the error. The company was required by support contracts to have some equipment in store in case of failures to quickly replace what failed. And the error was related to not updating in one Excel sheet information that a particular long-term contract was replaced with different one requiring less equipment on storage after updating another sheet. The Access DB with the support of enforcing complex cross-table constrains detected that.
Replacing this excel sheet with a 'proper' system would cost between $5million and $20million probably, depending on what sort of consultancy delivered it
And would be inflexible, and maybe still wrong, and not necessarily more transparent... So it may mean several extra million dollars per year in ongoing maintenance if things turn out typically...
Excel has many issues, but the cost of replacing it is surprisingly high.
>And would be inflexible, and maybe still wrong, and not necessarily more transparent...
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?
>[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.
>So actually top level planning/reporting is done by everything flowing into one big spreadsheet - as crappy as that is, thats pretty common.
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.
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.
I've worked on dozens of Excel to ERP migrations. Excel is way more common as the primary data source including financial data. If you only look at financial data by volume (and not count) maybe ERP win, but I wouldn't be sure.
> and was used for “consolidation, journals, business-critical reporting, and analysis.”
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.
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
If the Excel file is just a snapshot of the financial situation and it doesn't feed back into the main system, I find it harder to see the issue. Essentially it sounds like a manager takes the Excel file, makes some small changes, writes some equations, produces tables/graphs and then writes a report.
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.
If you spend a billion and a couple years redoing the generally working system to (maybe!) increase the number of significant digits on a dashboard for no good reason, you may have screwed up even more.
I think the important part that's not clearly portrayed in the OP article is that the "one Excel file" is actually not a single file used across the whole organisation, but rather a sort of master overview file that "consolidated files from each district into a single spreadsheet".
I don't think that fact is made very clear in the OP article.
Some of those defects seem as if they could be used to improve spreadsheet software more than they mean that spreadsheet software is intrinsically inappropriate.
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-...