5 of the Worst Spreadsheet Blunders
When it comes to complex data blending, spreadsheets should be handled with care. Errors can easily leak out from spreadsheets, contaminating reports and causing mistakes that can cost companies millions of dollars. A study by financial modeling company F1F9 a few years back found that close to 90 percent of spreadsheet documents contain errors. In fact, the report found that spreadsheets contain errors in one percent or more of all formula cells: in large spreadsheets with thousands of formulas, there will be dozens of undetected errors.
Errors like this have led to some catastrophic spreadsheet slip-ups. Here’s a look at some of the worst examples of spreadsheets gone wrong—cautionary tales for anyone relying solely on spreadsheets for business insight.
1. Plus And Minus Mix-Up
Investment bank Fidelity once estimated that its Magellan fund would make a $4.32/share distribution at the end of year. But it didn’t. In a letter of explanation to shareholders, the bank admitted, “A tax accountant is required to transcribe the net realized gain or loss from the fund’s financial records to a separate spreadsheet, where additional calculations are performed. The accountant omitted the minus sign on a net capital loss of $1.3 billion and incorrectly treated it as a net capital gain on this separate spreadsheet. This meant that the dividend estimate spreadsheet was off by $2.6 billion…”
2. The Billion-Dollar Blunder
Fannie Mae once stated in a news release of third-quarter financials that it had discovered a $1.136 billion error in total shareholder equity. The home mortgages provider’s senior vice president for investor relations explained in a remarkable piece of understatement, “There were honest mistakes made in a spreadsheet used in the implementation of a new accounting standard.”
So if you’re using spreadsheets for critical business reporting, remember to have someone check the figures.
3. Cut-And-Paste Fail
$24 million. That’s how much a simple spreadsheet error cost TransAlta, the Canadian power generator, buying too many US power transmission hedging contracts at higher prices. How did it happen? The then Chief Executive Steve Snyder made the call to analysts. According to Reuters, he said, “It was literally a cut-and-paste error in an Excel spreadsheet that we did not detect when we did our final sorting and ranking bids prior to submission.”
4. Off The Rails
The award of a new contract by the UK Department for Transport to run the West Coast mainline rail franchise was cancelled because of “technical flaws” in the bidding process. An investigation by PWC revealed that the spreadsheet from which all calculations were derived was fundamentally flawed. It was riddled with errors and double counting, with some figures failing to take inflation into account. Furthermore, the wrong formulas were used to calculate variants such as future revenues and passenger numbers, and there was no auditing in place to check the veracity of the process. Three civil servants were suspended as a result.
5. A Whale Of A Mistake
One of the more noteworthy spreadsheet blunders was uncovered by JPMorgan during its investigation into the so-called London Whale scandal, for the trader whose outsized bet landed the venerable Wall Street firm in hot water. The financial model the traders were using was operated through a series of spreadsheets that added a key measure that should have been averaged instead. The result: risk officers at JPMorgan believed the credit derivatives bets were half as risky as they actually were.
So how do you avoid these kind of spreadsheet errors in your business? If you or your team regularly spend hours or days mashing together data from multiple sources, you should consider using an analytics tool. Spreadsheet creation is a manual process that, 9 times out of 10, results in errors. That means the data you rely on to make decisions, to track progress, or to forecast results, is simply not accurate. A data analytics tool would not only save you many hours of spreadsheet wrangling, it could just save your business too.