Why spreadsheets might be your fair weather friend but are likely your nemesis
My former colleague Phil Wainwright baited me with a story that left me a tad confused, Let's clear up the confusion.
I’m a sucker for a spreadsheet story and Phil Wainwright got my attention with: Why finance teams should never give up spreadsheets, whatever vendors tell them. Phil says two things:
In summary, the spreadsheet is a powerful tool, and finance professionals shouldn't feel ashamed of turning to spreadsheets to figure out how to assemble data and analyze it. What's important is to recognize that the very flexibility and transparency of the spreadsheet that makes it so good for this kind of prototyping exercise is exactly what makes it so vulnerable to error and corruption in a production environment. Once the concept has been validated in a spreadsheet, it then becomes a matter of urgency to take that model and implement it in a more robust platform before distributing it out for routine use. The spreadsheet can still be your friend, but it will soon betray your trust if you let it loose on the world without proper supervision.
Hmmm….Never is a very long time and shaming is always a bad idea.
Phil’s analysis falls down in understanding the nature of spreadsheets. It’s a topic I’ve routinely talked about since the early 1990’s when I published the results of the first, and to my knowledge, the only publicly written labs-based tests for networked data warehouse and analysis tools. That story has long since disappeared into the annals of pre-internet publishing but the TL;DR goes something like this:
Analysis tools are complex beasts requiring the kind of mental gymnastics on setup that should be reserved for developers AND business practitioners.
Those tools that emulate the spreadsheet metaphor often work well when used by those who have some understanding of spreadsheet use. Familiarity helps but it comes with caveats.
When researching for that story I came across a series of articles that referenced Prof Ray Panko. Never heard of him? You should. He’s the world’s leading researcher on human-based spreadsheet errors. The problem that Panko researches helped me understand why spreadsheets are so darned dangerous.
Phil references Dan Bricklin, the father of the spreadsheet. Some years back (maybe 10?) I spoke with Dan at length. At the time he was working on reinventing the spreadsheet among other things. I asked Dan whether he agreed with me that spreadsheets represent a programming environment. He agreed that the complexity inherent in spreadsheet iteration of the time was pointing in that direction.
As such spreadsheets need testing AND, crucially, documenting. In my work undertaking analysis of various kinds over some 30 years, I have yet to find the finance person who is either qualified or capable of adequately documenting the logic they applied to any spreadsheet. Testing is not always easy, especially if the spreadsheet is referencing cells from other spreadsheets or tabs.
Too often, finance people are shown the auto calculation magic of applying simple formulae in the tabular form offered by the spreadsheet, along with the ease of replication across rows and columns, and then assume all is well. However, when it comes to spreadsheets, the old saw that a little knowledge is a dangerous thing, rings true, Even the most elegantly prepared spreadsheets can trap the unwary.
In this context, I’m reminded of the UK Heinekin finance office where a change in personnel was all that was needed to trigger an ongoing monthly £1 million plus error in a single line item on a reporting spreadsheet. The incumbent officer had built a terrific ‘tool’ but had not documented his processes. He didn’t need to as he had built and refined the spreadsheet over many years and was the only real user. He was only distributing the results of his work without input from others.
The successor saw what he thought was a familiar setup but made a single formula change without checking the integrity of the end result. The error wasn’t spotted for many months by which time there were a good few red faces and finger-pointing. Needless to say, the spreadsheet was ditched for a robust reporting tool upon which management could safely rely.
That was about 25 years ago. Has much changed? Most recently I was asked to review spreadsheet-based cash flow forecasts and budgets prepared by a semi-professional finance person.
It didn’t take long for me to discover that the combination of poor assumptions, a lack of reviewing procedures, scattered random annotations, notes and a lack of version control together with a failure to test against known data meant the entire exercise was pointless. In short, there were too many mistakes.
What’s worse, the problems I unearthed meant I had to review existing accounting rules and set up to ensure that what the business thinks it is measuring is what needs to be measured and in the order of importance for critical decision making. What should have been a relatively short engagement turned into a multi-month exercise of process improvement and the implementation of a dedicated FP&A tool that integrates directly with the accounting back office.
Even the simplest of spreadsheets can go wrong. I recently created a spreadsheet designed to help with balancing physical and theoretical cash. My first effort didn’t work because I made a dumb-ass error in carry-forward balancing. Thankfully I know enough to test and rectify so that staff using the sheet don’t panic when they see something unexpected. Why did I build the sheet in the first place? Because I didn’t have a reliable alternative. And therein lies the spreadsheet rub.
Most financial software, including the spreadsheet, is what I term ‘general purpose.’ There may be templates for industries but all too often, those templates are incomplete for a specific business use case. Couple that with add-on services like POS or CRM-generated feeds and it is easy to see how creating a system that works without spreadsheets is challenging.
Phil argues that using spreadsheets to prototype has merit. I don’t disagree from a design perspective. It is true that spreadsheets can provide a fast-track way to test the likelihood of meeting the right sort of information needs with the added benefit of a near-zero user acceptance testing requirement. After all, we’ve all seen spreadsheets, right?
However, prototyping is an art form in its own right. How for example do you know if you’ve got all the data needed to perform a reliable draft analysis and oh, by the way, will the spreadsheet handle all that data? As many will know, Excel has row and column limits but these are large enough that you’d never want to populate a single report that way. So how DO you break down the data into usable chunks and still prototype with confidence? Referencing other tabs or sheets might be your answer but at best the results are unwieldy, and error-prone at worst. At least in my experience,
But then you come up against another gotcha. The moment you look for a ‘real’ tool for the job. The very familiarity of spreadsheets means that getting acceptance of alternative metaphors is notoriously difficult. One way to overcome this is by using the information outflows to populate graphical representations. I prefer this approach because pictures are much easier to consume than tables, especially among operational business staff. Can this be done in spreadsheets? Kind of but not always. Ergo, the de facto fallback is the tabular spreadsheet.
Do I expect the spreadsheet to fade away anytime soon? I’ll put it another way. When I asked the partner of a medium-sized firm of professional accountants whether he could envisage a day when the spreadsheet is rendered redundant his answer was unequivocal: “Yes. The day they take it from my cold dead hands.” That, perhaps, is the truth of the matter, shaming aside.
In the meantime, it’s worth checking this analysis from Data Carpentry. It sets out all the common ways where spreadsheet error can catch you out. To use another Den-ism: you’ve been warned!