The risks of Excel for modelling and decision-making
The recent revelation that the number of Covid-19 cases in the UK was underestimated by almost 16,000 due to an Excel ‘glitch’ seriously embarrassed the UK government and Public Health England. Leading Synergy Group executive director and Excel modeller, Ben Savage, to urge spreadsheet aficionados to consider the way in which Excel is being used for modelling.
Excel is everywhere and can be used by everyone, says Ben. “Excel was released more than 30 years ago and has sold over a billion copies. Its name is synonymous with spreadsheets and it is one of the most influential pieces of software ever written. It’s an awesome tool.”
And Excel has been highly successful in bringing spreadsheets to the masses. Yet it is this ubiquity and familiarity that tends to breed complacency and opportunities for disaster.
Master of none
“Excel is such a useful tool that it has become the default go-to for a range of problems,” says Ben. “You can write reports in Excel, though arguably Word would be a better solution. You can build Gantt charts in Excel, despite MS Project being better placed to do so. You can build charts and tables and present an Excel dashboard instead of using PowerPoint or other visualisation alternatives. You can even build games and render 3D images in Excel if you set your mind to it!”
To fully appreciate the breadth of the tool, you can view an entire gallery of Pasokonga paintings magically created in Excel by Tatsuo Horiuchi.
And it’s this capacity – to be everything to everyone – that poses a risk to individuals, companies and governments that rely on Excel spreadsheets as databases and repositories of critical data, analysis, and modelling. Oftentimes numerical literacy and advanced Excel skills is conflated by management with modelling.
“Many managers see pivot tables, lookups, sparklines and arrays and jump to the conclusion that the spreadsheet author has built a rock-solid analytical tool,” explains Ben. “Too often it is the advanced Excel users that get in trouble; they have amazing Excel skills but lack experience in model design and analytical techniques.”
Trusted modelling
“When you are working with a tool where zero isn’t always equal to zero, where you can have hidden sheets (and even very hidden sheets), and where everyone considers themselves an advanced user, you will run into issues,” says Ben. And when workbooks grow rapidly without auditing or review by an expert, this can result in catastrophic errors, as demonstrated by the data collection issue encountered by the UK Government.
“There is a perception that Excel is a hammer and anything involving data is considered a nail. Whilst this is far from the truth, Excel can be an amazing asset for illustrating transparent and defensible modelling processes.”
Ben says that Excel can be the best tool for modelling, as long as formulae, styles and documentation are developed properly from the start, or assessed, validated and checked by a genuine expert along the way. Excel experts are not necessarily modelling experts, and Ben notes it can be difficult to appreciate the distinction.
According to a 2005 paper published by Raymond Panko in the Journal of Organizational and End User Computing, audits of real-world spreadsheets uncovered errors in 94% of spreadsheets, noting: “In large spreadsheets with thousands of formulas, there will be dozens of undetected errors. Even significant errors may go undetected because formal testing in spreadsheet development is rare and because even serious errors may not be apparent.”
The paper concedes that: “In general, errors seem to occur in a few percent of all cells, meaning that for large spreadsheets, the issue is how many errors there are, not whether an error exists.”
“Many managers have never had critical models reviewed and executives justifiably want to know the data they receive is correct,” says Ben.
Due to the prevalence of errors in critical Excel models, Synergy Group developed a review approach to identify and resolve errors across many types of modelling. The review process involves an ecosystem analysis, model health checks, and systematic audits of every formula within every spreadsheet. This provides an accurate base for data to be derived and provides comfort in the veracity of modelling outputs.
“A successful model is not necessarily the one with the largest dataset or the most complex equations, but rather any tool through which we can make more intelligent and informed decisions,” explains Ben. “Excel can support successful and transparent modelling if properly developed and reviewed.”
