10 spreadsheet errors that ruined careers
By David Parmenter
These high profile spreadsheet errors sho0uld send the shivers down the spin of any senior manager presiding over a team that uses large spreadsheets. I will argue that there should be a maximum size of 100 rows in a modern organisation who wants to protect its reputation.
1 . Austerity cuts based on spreadsheet error
A famous 2010 academic paper, relied on by political big-hitters to bolster arguments for austerity cuts, contained significant errors; and that those errors came down to misuse of an Excel spreadsheet. Reinhart and Rogoff’s work showed average real economic growth slows (a 0.1% decline) when a country’s debt rises to more than 90% of gross domestic product (GDP) – and this 90% figure was employed repeatedly in political arguments over high-profile austerity measures. Three errors were identified. The most serious was that, in their Excel spreadsheet, Reinhart and Rogoff had not selected the entire row when averaging growth figures: they omitted data from Australia, Austria, Belgium, Canada and Denmark. They had accidentally only included 15 of the 20 countries under analysis in their key calculation. When that error was corrected, the “0.1% decline” data became a 2.2% average increase in economic growth.
2. Barclay’s bank buys more than it expected
A spreadsheet, which had been e-mailed to a law firm representing Barclays Capital Inc, prepared by a Lehman representative, contained nearly 1,000 rows and more than 24,000 individual cells. 179 contracts were to be excluded from the agreement and these were hidden and not removed from the spreadsheet. The mistake happened late at night when the Excel document was resized (unhiding the cells) and then converted into a PDF document which was attached to the agreement.
3. Canadian power company TransAlta lost $24 million
This happened when an employee misaligned the rows in an Excel spreadsheet. The copy-and-paste error led to bids being aligned with the wrong contracts, wiping out 10% of TransAlta’s profit for the year with a quick click.
4. MI5 bugged the wrong phones
A formatting error on MI5’s list of phones to be tapped resulted in the agency’s tapping 134 people entirely unrelated to investigations. The formatting error changed the last three digits of these phone numbers to 000 within the spreadsheet. As a result, random British citizens had their phones tapped by their government while the suspects went unobserved.
5. Fidelity Investment’s minus symbol blunder
While transferring financial records onto an Excel spreadsheet, a tax accountant neglected to put a minus sign on the fund’s net capital loss of $1.3 billion. The loss was then calculated as a net capital gain, which resulted in dividend estimates being off by a staggering $2.6 billion. The financial institution was forced to cancel the dividend distribution after discovering that their estimation was incorrect.
6. Emerson Electric Co. under quotes
Emerson incorrectly estimated the total cost of one contract bid. They came up short by $3.7 million all because of a single spreadsheet cell. The cell that contained the cost of electrical was not included in the formula that calculated total expenses. One cell missed, millions of dollars lost.
7. C&C Group gets investor announcement the wrong way
C&C Group, the Irish drinks manufacturer of Magners cider had to change an investor announcement made a week earlier to explain that their total revenue had actually fallen by 5% not risen by 3% as first announced. The error was found in a spreadsheet. The change led to a fall in the share price by 15%.
8. Department of Transport to assess bids incorrectly
A new financial model had been deployed by the Department of Transport to assess bids by rival companies looking to operate the UK West Coast Rail Line. The Laidlaw Report found that fundamental mistakes in the communication of assumptions in the Department’s model led to rival bids being inconsistent and incorrect conclusions being drawn about the relative merits of the bids. Having awarded the new franchise to run the railway to First Group, a change in operator from the incumbent Virgin Trains, the UK Government was forced into an embarrassing and costly climbdown.
9. Cuts to spending announced incorrectly
The respected health think tank, the King’s Fund, had to announce a revision to their critical analysis of cuts in NHS spending in Wales on finding a calculation error in a spreadsheet. Although cuts of 8.3% remained, the reduction was 2.4% less than the 10.7% originally announced, lessening the impact of their critique & leading to red faces all round.
10. AXA Rosenberg get caught trying to hid a spreadsheet error
A spreadsheet error caused an over-estimate of client investment losses. Management then did not declare this mistake. As ever, it was the cover-up that got them, leading to a fine of US$242million for this global investment manager.
Rule of 100 rows
I believe you can build a forecasting model in a spreadsheet application and can keep it within 100 rows without much risk. Pass this threshold and you expose yourself, your finance team and the organization. Forecasting requires a robust tool, not a spreadsheet that was built by an innovative accountant and that, now, no one can understand. I always ask in workshops, “Who has a massive spreadsheet written by someone else that you have to pray before you use it?” You can see the pain in the instant response. Most people know that the person who built the spreadsheet certainly was not trained in operational systems design. The workbook will be a collage of evolving logic that only the originator has a chance to understand.
Often, the main hurdle is the finance team’s reluctance to divorce itself from the spreadsheet program. It has been a long and comfortable marriage, albeit one that has limited the finance team’s performance.
The need for more robust systems in finance teams, particularly forecasting tools are further explored in three toolkits (Whitepaper + e-templates). These toolkits are on sale (over 40% discount).
Annual Planning (Budgeting) in Two Weeks or Less – Toolkit (70 page Whitepaper + e-templates)
To look inside the toolkit and buy the toolkit