When I introduce our Spreadsheet Integrity Services I often highlight studies that revealed that over 88% over spreadsheets contain errors.
This is a scary statistic, but I noticed that few people truly grasp the impact of even the seemingly smallest error in spreadsheets. Many of my prospective clients don’t even seem very shocked – of course spreadsheets are unreliable – we know that! Yet, every day organisations fail to identify and solve spreadsheet errors, resulting in millions and sometimes even billions of dollars in damage.
In this post I wanted to highlight some of the more dramatic spreadsheet horror stories, and how they could possibly impact your organisation.
How to lose $24 million in one Ctrl-V
- The Year: 2003
- The Company: TransAlta Corp
- The Loss: $24 million (Canadian)
- The Error: Copy-paste
- The Solution(s): 1) Automation, 2) Proper Review
Possibly one of the most common spreadsheet errors is the “copy-paste” error. Most spreadsheets are manual contraptions – they require users to copy data from one worksheet to another (possibly across different workbooks) and more often than not this process skips a row or column, or may copy formulas instead of data.
Sources indicate that TransAlta used a manual spreadsheet to bid on Transmission Congestion Contracts, and accidentally bid on $24 million worth of contracts they didn’t want. Granted, I am not sure what a TCC is, but I am certain that this error could have been avoided by doing two things namely 1) automating the bid process and 2) instituting a proper review system.
How this error affects your business: This type of error has a far greater implication – many organisations don’t invest in automating their spreadsheet systems which means that over and beyond the risk of copy-paste errors, they lose productivity (and of course, profits). For example, one of our clients took one to two days to create a sales report from disparate sources. Through automation we were able to cut that time down to less than an hour.
How to buy (hidden) toxic deals
- The Year: 2008
- The Company: Barclays
- The Loss: Debts related to 179 toxic deals
- The Error: Hidden rows
- The Solution(s): Full audit
When Lehman Brother was sold to Barclays, a spreadsheet was created containing all of the assets that would be included in the deal. Included in this spreadsheet was 179 toxic debts. How would Barclays agree to acquiring these debts? Easy. They don’t see it because it is contained in hidden rows.
Whether intended or not, many people hide rows in spreadsheets as a means to exclude information. In this case the exclusion was intended – Barclays was in fact aware of these debts but they explicitly did not want to include them in the buyout. The inclusion occurred when the spreadsheet was converted to PDF and the rows reappeared during the process of setting up the contracts.
A full audit of the spreadsheet would have revealed the hidden rows – it isn’t acceptable modeling practice to use hidden rows to exclude critical figures. A better practice would have been to create a unique copy of the workbook where those rows were explicitly removed or excluded by formula.
How this affects your business: Hidden rows and columns typically do not affect the results of a spreadsheet – not only are they mistakenly used to exclude information, they are often used to to perpetrate fraud. Whether you are making deals or submitting financial statements for reports, the spreadsheets must be audited to identify hidden rows and columns.
The $1.3bn minus sign
- The Year: 1995
- The Company: Fidelity (Magellan Fund)
- The Loss: Overestimated net profits of $1.3bn
- The Error: Incorrect operator
- The Solution(s): 1) Full audit, 2) Better use of formulas
This is possibly one of the most famous spreadsheet errors of the 1990’s. An accountant mistakenly forgot to put a minus sign in front of the net capital losses of the Fidelity Magellan fund (to the value of $1.3 billion), resulting in a $2.6 billion error.
This is a perfect example of how a small error can have a huge impact. The solution here has a lot to do with good modeling practice. Often accountants rely on operators to indicate negative amounts because the use a function similar to SUM() to do the math for them. Here SUM(-1.3,+1.3) would equal 0 but without the minus sign it equals 2.6. In these cases it might be more prudent to build the operator into the formula and avoid the SUM function for example =A1 – A2. This means a positive number can be inserted into both cells but the result will still be correct.
How this affects your business: If formulas are not built correctly then even full audits can miss these tiny mistakes. However a full audit is always necessary when submitting critical financial statements for publication. More importantly, best practices for spreadsheet use must be ingrained in the company’s operations.
Your spreadsheets have errors
I have highlighted only three of the more famous cases, but there are many more. In fact, it isn’t uncommon to see spreadsheet errors resulting in errors to the value of many hundreds of thousands of dollars.
Spreadsheet errors can not be avoided – even the bet analysts make them. They can however be properly managed by applying an effective review system that includes technical audits of critical spreadsheets.
Here is how you can begin to save your company embarrassment, time and money:
- Implement a proper spreadsheet review process
- Perform external technical audits on mission critical spreadsheets
- Replace manual spreadsheets with automated systems