The true significance of bugs in spreadsheets

There are many reports of extremely high occurrence rates for bugs in spreadsheets. From reading them, you might think that very few spreadsheets are error-free.

However, many people who are aware of the likelihood of errors in spreadsheets go to great lengths to find and remove them. I have found few significant errors in the often large and complex spreadsheets I have reviewed (mainly in the insurance industry).

In my view the true significance of bugs lies not in their existence, which can lead to spreadsheets producing erroneous results, but in the enormous amount of time and effort that goes into preventing them. Spreadsheets are usually built and maintained by people who have little or no software engineering expertise. These people often:

• Do not have good software development processes
• Are not aware of the characteristics of good software and how they apply to spreadsheets
• Do not know good methods of testing and reviewing software
• Do not know how to design software (especially spreadsheets) so as to reduce the likelihood of bugs

I believe that the use of simple software engineering techniques can help. Some of these techniques are described, somewhat briefly, in other notes on this site.

Resources

Managing the operational risks of user-developed software

This is a paper I wrote for a workshop at GIRO 2002. It is available from my publications page.

Ray Panko

Professor Panko, who is at the University of Hawaii, has done a great deal of research into spreadsheet errors. His page at http://panko.cba.hawaii.edu/ssr/ has many useful resources, including links to many of his papers.

Is this spreadsheet a tax evader?

This paper, by Ray Butler of H.M. Customs and Excise, summarises the audit experience, describes the methodology and outlines the results to date of a campaign of spreadsheet testing started in July of 1999. Of the seven spreadsheets selected for audit, six contained significant errors. It was published in the proceedings of the 33rd Hawaii International Conference on System Sciences in 2000, and is available at http://www.eusprig.org/hicss33-butler-evader.pdf.

HM Customs and Excise

Somewhat unexpectedly, some of the most interesting and useful work on spreadsheet errors and auditing is currently coming out of HM Customs and Excise. They have developed some software (SpACE) that they are now making available to businesses. Of particular interest is their document on Methodology for the Audit of Spreadsheet Models.

European Spreadsheet Risks Interest Group

This special interest group provides a forum for research on spreadsheets. Its website is at http://www.eusprig.org and includes an excellent collection of spreadsheet horror stories. EuSpRIG organises an annual conference on spreadsheet risks.

Software Engineering: A Practioner's Approach, European Adaptation

By Roger S Pressman, adapted by Daniel Ince. A comprehensive guide. Visit the book's page at Amazon.