Those mistakes were made by the intelligent people, who just did not have the right training to develop and validate their spreadsheets. Spreadsheet errors are inevitable, like programming errors. While it is impossible to avoid them entirely, you can improve your chances by following the SSMI methodology, which is based on proven principles from Computer Science, Software Engineering and Information Systems. These news articles show that spreadsheets errors can have important monetary and human consequences.
- JP Morgan realized a $6 billion trading loss due, among other things, to an error in an Excel spreadsheet used by an analyst: the formula divided the difference between the old and the new interest rates by their sum instead of their mean, thus decreasing the risk indicator by a factor of 2.
- Virgin Rail lost the management of the West Coast Main Line train line in England following a tender. Richard Branson challenged this decision after the analysis of the spreadsheets used by the government to evaluate the bids showed that they did not correctly model the risk and value of cash flows. The government had to cancel the award of the contract. The costs to the taxpayer to correct and re-launch the tender are estimated at GBP 100 million.
- An error in an actuarial spreadsheet caused Mouchel to write down its profit by over £4 million. Even though the error was made by an outside firm, it led to Mouchel’s chief executive to stand down.
- In 2003, TransAlta suffered a loss of $24 million because of an error in handling the Excel file used to prepare a bid for the State of New York. They provided a low bid because they underestimated their costs, and they won! Now, they have to honour their contract at a loss.
- A paper published by two renowned economists has been found to contain a calculation error. A doctoral student, from another university, was unsuccessfully trying to replicate their results and finally asked the authors for their spreadsheet. He found an error where the data of five countries was excluded from the calculations. The calculation used values in rows 30 to 44 when it should have been rows 30 to 49.
Those are obviously not the only five mistakes that were made. Errors in spreadsheets occur on a daily basis, many of which have to be found yet. See the EuSpRIG Horror Stories page for a comprehensive and regularly maintained list.