Bad math, transposition errors, and “fat finger” errors run rampant in companies of all sizes. Almost 90 percent of all spreadsheets have errors. Even the most carefully developed, tried, and tested spreadsheets have errors in 1 percent of all formula cells. In larger spreadsheets with thousands of formulas (that, let’s face it, exist in every company), there are dozens of errors.
And what are the implications of all these boo-boos? Almost one out of five large business have suffered financial losses as a result of spreadsheet errors. In most cases, large companies can absorb that loss, but small and medium businesses (SMBs) usually cannot.
Why are there so many errors? There are really two reasons. First, spreadsheets — even though they have launched thousands of new companies and caused millions of layoffs —are still created by people. And people make mistakes. Second, as a spreadsheet grows, complexity grows as well. And with complexity comes more opportunities for mistakes. In some cases you may be very lucky and the mistakes do not have an impact on final results. But others can have a lasting impact. With that in mind, let’s look at the ten costliest spreadsheet fails to date.
1. TransAlta. A cut and paste error in a spreadsheet cost this company $24 million. The mistake caused the Canadian power generator to buy more US power transmission hedging contracts at much higher prices than it should have.
2. Fidelity. The company’s well-known Magellan fund was forced to cancel a $4.32/share year-end dividend distribution. The problem? A missing negative sign. A tax accountant omitted a minus sign when s(he) transcribed the net capital loss (of $1.3 billion) from the fund’s financial record to a spreadsheet. This turned the loss into a gain, causing the dividend estimate to be off by $2.6 billion.
3. MI5. Even spies can make mistakes. In 2010, the British intelligence agency bugged more than one thousand wrong phones. A formatting error on a spreadsheet caused the agency to apply for data on all telephone numbers ending in “000” instead of the actual last three digits.
4. Reinhart-Rogoff. Harvard professors are not immune from spreadsheet mistakes. Carmen Reinhart and Kenneth Rogoff’s report—“Growth in a Time of Debt”—was a highly regarded paper that concluded that when a country’s debt hits 90% of its GDP, their economies will shrink by 0.1%. As you can see by the image, the professors’ average formula excluded five countries from the list. By correcting that error, the results changed dramatically. The actual results are that when a country’s debt reaches 90% of its GDP, its economy will grow by 2.2%.
5. Eastman Kodak. The company was forced to restate financial results for two quarters by $2 million and $13 million, respectively, due to an erroneous spreadsheet error that calculated the severance and special pension-related termination benefits accrued by just one employee. This came at a time when the company was losing $100+ million every quarter. When announcing the restatement a company spokesperson stated, “There were too many zeros added to the employee’s accrued severance.”
6. University of Toledo. University officials discovered an error that meant they had $2.4 million less than they had budgeted for. This at a time when they faced significant state funding reductions. The mistake was a typo in a spreadsheet formula that led university leadership to overestimate enrollment and (therefore) revenue.
7. Red Envelope. The company’s CFO resigned and company shares lost more than a quarter of their value. What happened? Weak Valentine’s Day sales and a budgeting error. This budgeting error was due to a number being entered incorrectly in one cell of a spreadsheet which resulted in the overestimation of gross margins.
8. Emerson. The construction company came up $3.7 million short in their estimation of the total cost of a contract bid. One cell in a spreadsheet (which held the costs for electrical work) was not included in the spreadsheet formula that calculated total cost.
9. London Olympics. When a staffer accidentally inserted “20,000” into a cell instead of “10,000, the London Olympic Committee ended up selling 10,000 tickets for non-existent seats at four minor heats of synchronized swimming. When the mistake was caught, the committee had to upgrade ticket holder to tickets for major events—at a loss.
10. Barclays Capital. When Lehman Brothers went bankrupt, Barclays bought some of the company’s assets, including the unintentional purchase of 179 contracts. Cells containing the unwanted contracts were hidden (vs. being deleted) in a spreadsheet with nearly 1000 rows and 24,000 cells. When the spreadsheet was converted into a PDF to be posted to the bankruptcy court’s website, the cells appeared again. Barclays Capital filed a legal relief motion, but in the end had to swallow the losses, for an undisclosed sum.
11. The “London Whale” incident. Due to several faulty equations in a spreadsheet (used to model risk) and a process that mandated the copying and pasting of a large number of cells, JP Morgan severely underestimated the downside of its synthetic credit portfolio, which ultimately led to the bank suffering approximately $6.5 billion in losses and fines.
12. Fannie Mae. While in the midst of changing its accounting system, the company’s finance team relied on spreadsheets to make some needed calculations required by a new accounting standard. One problem. The spreadsheets contained errors that skewed results by over $1.1 billion. The company discovered a $1.136 billion error in total shareholder equity due to “honest mistakes made in a spreadsheet used in the implementation of a new accounting standard,” and it had to restate its 2003 third-quarter financials.
