Excel, Uncontrolled (and it is way to often)

Home / Application Development / Excel, Uncontrolled (and it is way to often)

Excel, Uncontrolled (and it is way to often)

If you depend on spreadsheets, any spreadsheet not just Excel, for your business decisions you are depending on a very fragile tool. 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.

Recent Posts

Leave a Comment

Contact Us

We're not around right now. But you can send us an email and we'll get back to you, asap.

Start typing and press Enter to search