Errors in Field Audits of Operational and Preoperational Models
Anecdotal incidents of spreadsheet errorsare intriguing. But are they extremely rare isolated incidents? Quite simply, they do not tell us how frequently spreadsheet contain errors. Are incorrect spreadsheets one in a million? On in ten? Nearly universal? The answer certainly matters.
This page contains information from a number of field audits. All but one looked at samples of operational spreadsheets. The one exception (Hicks, 1995) was a massive spreadsheet about to become operational. The fact that so many studies have been done and have all found a significant number of errors is very sobering. The following table indicates the rates of errors found in field audits. Note that since 1995, when better auditing techniques began to be used, errors have been found in 88% of the 113 audited spreadsheets that reported the number of spreadsheets audited.
Authors Year Number of SSs Audited Average Size (Cells) Percent of SSs with Errors Cell Error Rate Comment
Davies & Ikin
Only serious errors were reported.
Cragg & King
50 to 10,000 cells
Only errors large enough to require additional tax payments Dent 1994 Unknown 30% Errors caused by users hard-wiring numbers in formula cells. Henceforth, all future computations would be wrong.
One omission error would have caused an error of more than a billion dollars.
Coopers & Lybrand
More than 150 rows
Off by at least 5% In accounting, such errors are material.
Only significant errors that could lead to an incorrect decision.
2,270 & 7,027
In Model 2, the investment's value was overstated by 16%. Quite serious.
Only errors large enough to require additional tax payments**
Clermont, Hanin, & Mittermeier
1.3%, 6.7%, 0.1%
Computed on the basis of non-empty cells and so included text rather than just numbers and formulas. Interview I* 2003 ~36 / yr 100% Approximately 5% had extremely serious errors Interview II* 2003 ~36 / yr 100% Approximately 5% had extremely serious errors Lawrence and Lee 2004 30 2,182 unique formulas 100% 6.9%*** 30 most financially significant SSs audited by Mercer Finance & Risk Consulting in previous year. Powell, Lawson, and Baker 2007 25 64% Although 64% of the spreadsheets contained errors, many errors had no impact on bottom line values. When these zero-impact were removed, 44% of the spreadsheets had serious errors. In the 10 spreadsheets for which error size was listed, all had errors of $100,000 or more, 7 had errors of $1 million or more, 6 had errors of $10 million or more, and 1 had an error of more than $100 million. Overall, 40% of the spreadsheets had significant errors. Quite a few of these were very serious errors.
Total since 1995
*In 2003, the author spoke independently with experienced spreadsheet auditors in two different companies in the United Kingdom, where certain spreadsheets must be audited by law. Each audited about three dozen spreadsheets per year. Both said that they had never seen a major spreadsheet that was free of errors. Both also indicated that about five percent of the spreadsheets they audited have very serious errors that would have had major ramifications had they not been caught. Audits were done by single auditors, so from the research on spreadsheet and software auditing, it is likely that half or few of the errors had been caught. In addition, virtually all of the spreadsheets had standard formats required for their specific legal purposes, so error rates may have been lower than they would be for purpose-built spreadsheet designs.
**The low cell error rate probably reflects the fact that the methodology did not inspect all formulas in the spreadsheet but focused on higher-risk formulas. However, error has a strong random component, so not checking all formulas is likely to miss many errors.
***This was the percentage of cells containing "issues." Issues were not necessarily quantitative errors. Some were qualitative errors. This probably indicates why the Lawrence and Lee rate is higher than the rate of errors in other studies.
One problem with field audits is that the probably catch only a fraction of all errors in the spreadsheets they study. We base this statement on three sets of research. First, experiments in spreadsheet code inspection have found that subjects only catch about half of all errors. This is not surprising, because problems in error detection have long been seen in programming. The Human Error website, http://panko.cba.hawaii.edu/HumanErr/, shows that experiments in program code inspection have also seen low error detection rates. This is why programming code inspections in industry use teams of three or more people. Even so, as noted at the Human Error website, group code inspection still catches only 80% of all software programming errors.
Another concern is inspection speed. In programming code inspection, we know that fast inspection rates result in very low detection rates. In spreadsheet auditing experiments, inspection rates are sometimes very fast or are not reported. In the studies that apparently spent a great deal of time on the audits, almost all audited spreadsheets were found to contain errors.
One brief piece of terminology. The cell error rate (CER) is the percentage of formula and (usually) numerical cells that contain an error. It is similar to the error rate measure uses in programming: faults per thousand lines of noncomment source code.
Butler, Raymond, Tax audit study, personal communications by electronic mail, August and September, 1996 and in August, 1997. Mr. Butler is Team Leader, Central Computer Audit Unit, HM Customs & Excise Computer Audit Unit. Data collected 1992.
273 spreadsheets were audited using spreadsheet auditing software. The staff used the auditing program to find core formulas that were copied. They examined the core formulas and then examined the general coping for that formula rather than examining all copied cells. They only reported errors serious enough to lead to a demand for additional tax payment. They found such errors in 10.7% of the spreadsheets.
Butler, Raymond, "Is this Spreadsheet a Tax Evader? How H. M. Customs & Excise Test Spreadsheet Applications" Proceedings of the Thirty-Third Hawaii International Conference on System Sciences, Maui, Hawaii, January 2000. (.pdf format)
Spreadsheet models are commonly used by UK Taxpayers to calculate their liabilities. The risks of error from spreadsheets have been exhaustively documented, and applications in this domain are no less at risk of error than those in any other. Officers of H. M. Customs and Excise in the United Kingdom have been performing field audits of taxpayers’ spreadsheet applications since 1985. Building on the experience gained, H. M. Customs and Excise computer auditors have developed a testing methodology and supporting audit support software for use by generalist tax inspectors. This paper briefly summarizes the audit experience, describes the methodology and outlines the results to date of a campaign of spreadsheet testing started in July of 1999.
Clermont, M., Hanin, C. & Mittermeier, R. (2000, July). A spreadsheet auditing tool evaluated in an industrial context. In Proceedings of the EuSpRIG 2000 Symposium, Spreadsheet Risks—the Hidden Corporate Gamble. (pp. 35-46). Greenwich, England: Greenwich University Press.
Coopers & Lybrand in London. Description available at http://www.planningobjects.com/jungle1.htm. Contact information is available at that webpage.
The company audited 23 operational spreadsheets that had more than 150 rows. Errors in 21 (91%) made bottom-line values off by more than 5%.
Cragg, P.G. & King, M. "Spreadsheet Modelling Abuse: An Opportunity for OR?" Journal of the Operational Research Society (44:8) August 1993, pp. 743-752.
By Dr. Cragg: Spreadsheet models are increasingly being used in decision making within organizations. With questions about the quality of these models, an investigation was conducted into the spreadsheet practices in ten firms, with an emphasis on the process of building spreadsheet models. The study showed that spreadsheet models were usually built in an informal, iterative manner, by people from all organizational levels. These people had received very little training in the building of models, which could help explain why at least 25% of the models contained errors. Other problems were also found. It was evident that the spreadsheet practices in the firms were inadequate. There is a need for increased training as well as setting and enforcing organizational spreadsheet standards. This could provide an opportunity for OR workers if carefully handled. Although the study was viewed as exploratory, it indicated a need for further study into the effect of formal design practices on the incidence of errors and model creation time. Abstract by P. G. Cragg.
Our summary: Audited 20 operational models from 10 firms. 150 to 10,000 cells in size. Audits lasted only two hours each, which by program code inspection guidelines was quite short. Cragg and King acknowledged that they probably missed some errors. Five (25%) were found to have quantitative errors. Spreadsheets had been in use a median of 6 months and had an average of 7 updates. Only one had been tested by another person. All but one had been tested with data. Half had poor layout. Only a third used cell protection. Eight were for use by others, but in only two cases did others have input on the design. In 6 cases, there had been problems with the model previously. Development had been informal and iterative.
Davies, N. & Ikin, C. "Auditing Spreadsheets," Australian Accountant, December 1987, pp. 54-56.
Audited 19 operational models from 10 different people in 10 different firms. Four (21%) has serious errors, including a $7 million error in interdivisional transfers, different exchange rates for Australian dollars in the same time period, and a negative balance for stock on hand.. Thirteen had no documentation, and only ten used cell protection. Only five were free of quantitative errors and qualitative (potential) errors, and these five were from two of the ten participants. Yet subjects expressed strong belief in their spreadsheets being error free. Only half used cell protection. Manual audits were "rare."
Dent, A., personal communication with the first author via electronic mail, April 2, 1995.
In an Australian mining firm, and audit found that 30% of the spreadsheets audited had been corrupted because cell protection had not been used, and users typed numbers into formula cells.
Hicks, Lee, Audit of Capital Budgeting Spreadsheet at NYNEX, personal communication with the first author via electronic mail, June 21, 1995.
A 3-person code inspection of module in a spreadsheet about to become operational for doing capital budgeting at NYNEX. The team found errors in 45 of 3,856 cells, for a cell error rate of 1.2%. There were 27 mechanical, 14 logic, and 2 omission errors. Two errors were not classified. One omission error would have produced an error of almost a billion dollars.
KPMG Management Consulting, "Supporting the Decision Maker - A Guide to the Value of Business Modeling," press release, July 30, 1998.
Reports on an audit of 22 spreadsheets by KPMG Management Consulting. Spreadsheets were from over 21 major UK banking and financial organizations. Twenty (91%) had significant errors. 92% of the spreadsheets dealing with tax issues had significant errors; 75% had significant accounting errors, and 59% were judged to have poor design. Contact is Mr. David Parlby, Partner in charge of Business Modeling at KPMG Management Consulting.
Lawrence, Robert J. & Lee, Jasmine (2004). “Financial Modelling of Project Financing Transactions,” Institute of Actuaries of Australia Financial Services Forum. The Institute of Actuaries of Australia, Level 7 Challis House 4 Martin Place, Sidney, NSW Australia 2000.
Examined spreadsheets created to justify project funding. The 30 most financially significant spreadsheets of this kind audited by Mercer Finance & Risk Consulting in previous year. An error of 2% in the bid price can lose the business. The average spreadsheet had 2,182 unique formulas.
Lukasik, Todd, CPS. Personal communication by e-mail, August 10, 1998.
Audited two operational spreadsheets by recreating the analyses with a financial analysis program (Alcar) and then examining the results to identify errors. Spreadsheet 1: 7,027 cells, 156 errors, Cell Error Rate (CER) of 2.2%. Model 2: 2,270 cells, 57 errors, CER 2.5%. In Model 2, the amount of money required to fund the project was understated by 16%, making the project look substantially more attractive than it really was. This was a very serious error.
Powell, Stephen G.; Lawson, Barry; and Baker, Kenneth R. (July 11-13, 2007). “Impact of Errors in Operational Spreadsheets,” Eusprig Conference Proceedings, University of London, Greenwich 2007, 57-67.
Used a spreadsheet auditing methodology created by the authors. An earlier version of this methodology had been used to examine 50 spreadsheets, although in the earlier study, the authors simply audited spreadsheets without talking to the spreadsheet authors to understand the spreadsheet. In this 2007 paper, the authors did discuss the spreadsheets initially with the spreadsheets' authors. In addition, Powell, et al. discussed the issues they identified with the spreadsheet authors. Some of the issues they identified were not considered to be errors by the spreadsheet authors; others were identified as errors, but the spreadsheet authors said that these errors had zero impact. If the study had only reported spreadsheets with issues, it would have reported that 84% of spreadsheets were incorrect.
Copyright 2008 Panko.