Excel, Uncontrolled (and often it is)
If you depend on spreadsheets, any spreadsheet not just Excel, for your business decisions you are depending on a very fragile tool. Google sheets are at least as susceptible, perhaps more so given the multi-user feature. Spreadsheet Horror!. There are far too many ways a complex spreadsheet can be working one day and an instant later be very flawed. In this article about the role of Excel in the London whale case James Kwak examines the fragility of this tool that is everywhere. Here is a table taken from: What We Know About Spreadsheet Errors
Table 1 contains data from 13 field audits involving real-world spreadsheet spreadsheets.
Table 1: Studies of Spreadsheet Errors
| Authors |
Number of SSs Audited |
Average Size (Cells) |
Percent of SSs with Errors |
Comment |
| Davies … |
19 |
|
21% |
Only serious errors |
| Cragg… |
20 |
50to10k |
25% |
|
| Butler |
273 |
|
11% |
Only errors large enough to require additional tax payments |
| Dent |
Unknown |
|
30% |
|
| Hicks |
1 |
3,856 |
100% |
One omission error would have caused an error of more than a billion dollars. |
| Coopers … |
23 |
> 150 |
91% |
Off by at least 5% |
| KPMG |
22 |
|
91% |
Only significant err’s |
| Lukasic |
2 |
2,270 & 7,027 |
100% |
In Model 2, the investment’s value was overstated by 16%. Quite serious. |
| Butler |
7 |
|
86% |
errors required additional tax payments |
| C, H & M |
3 |
|
100% |
|
| Interview I* |
~36 / yr |
|
100% |
~5% extremely serious errors |
| Interview II* |
~36 / yr |
|
100% |
~5% extremely serious errors |
| Lawrence and Lee |
30 |
2,182 unique formulas |
100% |
30 most financially significant SSs audited in previous year. |
| Powell, Lawson, and Baker |
25 |
|
64% |
11 of 25 spreadsheets contained errors with non-zero impacts. Among the ten spreadsheets with non-zero impacts for which error size was reported, all 10 had an error that exceeded $100,000, 6 had errors exceeding $10 million, and 1 had an error exceeding $100 million. |
It is somewhat amazing that we don’t have more business problems than we do, or perhaps we really do have those problems. Perhaps you don’t have the margin you think you have? Perhaps you are paying too much for your cost of goods? Or your employees bonuses are overstated by accident, making it harder for the company to pay higher bonuses because it can’t deploy that money to grow?
So what do we do about it? What can we do? More cross footing, more analysis of the spreadsheet, more time spent worrying about if it’s right than actually making the decisions? The whole process of building business processes around spreadsheets seems to me, though, flawed enough to warrant some high concern if you own a business. If you are a typical you depend on spreadsheets that are created and filled by assistants. Some very talented, some only clerical but none with the vested interest in correctness that you have. If you are one of those tasked with creating these spreadsheets and you hear about these errors you are likely to be torn between getting your work done quickly and looking hard for errors so you don’t deliver faulty spreadsheets that cause huge business problems. Either way this is a drag on the business.
Ultimately if the business process is important enough and the consequences of error big enough the real answer is a custom program to manage the data in a consistent and well tested way. While programs may not be perfect a well crafted program will control data entry, have formulas in one place not based on relative positioning and subject to change based on adding columns or rows. The results will be repeatable and, if well crafted, much less prone to operator error. In the end there will always be spreadsheets, they are simply too handy and flexible. But when spreadsheets become the source for data rather than the analytical tool it’s time to think about the reliability of a custom application for your business sake.
