Spreadsheet errors.
Listening to Hannah Fry’s recent podcast in her “Uncharted” series about spreadsheet errors in Reinhart and Rogoff’s study Growth in a Time of Debt reminded me just how often simple spreadsheet errors can occur and what an impact they can make. The error in this case was the calculation of an average using an incomplete row, therefore accidentally only including 15 of the 20 countries being analysed. When the error was corrected, a 0.1% decline became a 2.2% average increase in economic growth.
Am I surprised? Not really.
Spreadsheets are full of errors. Here are some other tales:
1. The London Whale case back in 2013 caused a US$6.2 billion loss at J P Morgan. Whilst the case was far more complex than just involving a spreadsheet error, the bank’s task force reported that the model used in the trades:
-
- “required time-consuming manual inputs to entries and formulas, which increased the potential for errors”; and
- “operated through a series of Excel spreadsheets, which had to be completed manually, by a process of copying and pasting data from one spreadsheet to another”.
2. In 2012, the UK government had to arrange a rebid of the West Coast Mainline, owing to a spreadsheet error where there was confusion between real and inflated figures. This resulted in an additional cost to the taxpayer of £40m.
3. In October 2020, Public Health England reported that Covid cases for the week from September 25th were 50,786. They failed to report an additional 15,871 cases because a pre-2003 version of Excel had been used which has fewer rows than the current version.
4. In 2012, 20,000 tickets were issued for an Olympic swimming event which only had a capacity of 10,000. This was caused by a simple typing error in Excel.
5. And the wedding spreadsheet? A friend told me that her daughter had invited 160 people to her wedding and plans were made accordingly. But later on, they spotted that the guests’ names on the spreadsheet started at row 40 and ended at row 160. Only 120 guests were really on the list.
What are the lessons from all of these stories?
- Excel is a dangerous tool.
- Always question what the results are telling you.
- Check and check again. Get someone else to check your spreadsheet too.
- People often work under too much time-pressure, with too many distractions and are given insufficient time to think.
- Too many assumptions are made by senior management about the capability of modellers.
- The key skills that any modeller needs are logic, rigour and a sense of number.
Having worked with models for over three decades, I don’t really trust many of them at all.
For Hannah Fry’s podcast on BBC Sounds please click here: Hannah Fry’s Devil in the Detail Podcast