Welcome Guest. | Log In| Register | Membership Benefits


Applications Development: Finding Spreadsheet Errors

Most spreadsheet models have design flaws that may lead to long-term miscalculations

By Ray Panko
Issue column appeared: May 29, 1995

An oil and gas company in Dallas lost millions of dollars in an acquisition deal, and several executives were fired. The root of the problem: A spreadsheet model contained an error. The executives had based their actions on inaccurate spreadsheet data.

Are such errors common? Only a few cases of spreadsheet disasters like this have been published, but consultants and independent audits have found errors i n as many as 30% of the spreadsheet models that are created with today's ubiquitous off-the-shelf spreadsheet applications.

Your company may have tens of thousands of spreadsheet models in use. What if even 1% of these have errors? When was the last time you went to an important meeting and didn't rely on someone's spreadsheet analysis? During these meetings, you can question odd results. But you can rarely stop to do a full top-to-bottom audit of the spreadsheet.

Errors Happen
Several experiments, including those done at the University of Hawaii, have analyzed these errors. Some errors are mechanical, such as typing the wrong number or pointing to the wrong cell when entering a formula. In experiments, mechanical errors were found in about 1% of spreadsheet cells. While this number may seem high, it compares favorably with the undetected error rates among expert typists.

Even more important are logic errors--those that use an incorrect formula. Logic errors are bad for two reas ons. First, they are difficult for the model's developer to detect. Second, logic errors are fairly frequent. Our experiments and others found undetected logic errors in about 4% of all cells. This actually corresponds favorably to error rates in software development. Numerous studies have shown that even experienced professional programmers make undetected mistakes in 3% to 7% of all lines of code before they begin the debugging stage.

However, programmers have long known the importance of deep debugging, running test data against their programs and doing de-tailed, line-by-line code inspection. Programmers also know how difficult it is to do debugging well. As a result, almost all corporations require deep debugging processes. These reduce errors to about two in every 1,000 lines of code. Corporations also reduce errors by having the software audited by people who were not involved in the development and can lend a fresh eye to the figures.

As information systems managers would expect, most develope rs of spreadsheet models do take action to reduce errors. Unfortunately, in most cases, these actions stop well before deep debugging. Error reduction efforts are often cursory. Surveys have shown that most spreadsheet models have design flaws that are likely to lead to long-term errors. The surveys also found only about one in 10 companies has policies for producing spreadsheet models. Even when policies exist, they are rarely enforced. Crucially, the external audits so important in program debugging are quite rare.

Dangerous And Out Of Control
What we seem to have today is a spreadsheet situation that is dangerous and out of control. If you are concerned about spreadsheet errors in your own organization, you might begin with an audit of a hundred or so important models. This process is time-consuming, but it's also the only way for you to make sure your company does not have a serious problem with spreadsheet quality. It's also necessary if you want to establish effective spreadshe et development policies. Without hard data, end users are not going to give up their traditional freedom.

If you do an audit, be sure to look at numerical errors, qualitative errors, and development practices. These will give you a good understanding of the real situation. And be sure to keep things anonymous. You aren't looking to assign blame. You are trying to get an honest and accurate diagnosis.

Staying away from blame is also crucial in the long term. Humans err. In software development, we have learned the hard way that we have to treat programming errors as things that are bound to occur, even with the best developers. It is important to establish a blame-free atmosphere when working with end-user developers, too.

This column appeared in Final Word, a forum for professionals with opinions on information management. Your contributions are welcome. IW cannot return unsolicited manuscripts. Please send submissions to: InformationWeek, 600 Community Drive, Manhasset, N.Y. 11030, or E-Mail them to mfaden@cmp.com