Introduction
Several researches have showed that spreadsheet errors are quite common and difficult to eliminate even after careful development of spreadsheet. Generally, errors are more pronounced in spreadsheets containing numerous formulas. These errors are sometimes undetected because of some of the constraints experienced during spreadsheet development. Spreadsheet errors significantly affect the quality of organizations’ reports and consequently lead to wrong decisions being made.
In order to address the effects of corporate failures, in the year 2002, U.S. Congress ratified the Sarbanes-Oxley Act, which makes it mandatory for all corporations to put in place well-controlled financial reporting systems. These systems facilitate independent inquiry into the affairs of corporations by auditors which lead either qualification or disqualification of corporation’s reports.
Scope of spreadsheet errors
Reports from a large number of consultants have showed that, about 20% to 40% of all spreadsheets contain errors. A report from Dent (Dent, 1995) revealed that an audit performed in a certain mining company discovered errors in approximately 30% of all the audited spreadsheets.
In addition, report from Freeman (Freeman, 1996) revealed that data from a consulting firm in England, Coopers and Lybrand, found that, about 90% of all the spreadsheets which contained more than one hundred and fifty rows contained numerous errors. Moreover, according to the author Ditlea (Ditlea, 1987) an audit performed on four large spreadsheets by a Price-Waterhouse consultant found one hundred and twenty eight errors.
Exposer to the risk of spreadsheet errors
With the help of spreadsheet programs, end users develop analyses covering multiple cells. End user developed spreadsheets are more exposed to errors because few users implement the disciplines necessary for the systems’ development. If there are zero incorrect cells, the risk of making bottom-line errors in the spreadsheet is greatly reduced. The end user developed spreadsheets are therefore quite dangerous to organizations and can even lead to winding up of an organization.
Types of spreadsheet errors
Errors in operational spreadsheets
Generally, there is a misconception among many people that errors are only caused by human actions such as mistyping of numbers, wrong application of formula, and inserting formula in the wrong cells. According to various researches, human beings are likely to cause about 0.5% error when they perform simple mechanical jobs such as typing.
These errors are rarely detected and therefore affect the quality of the reported work. Research also shows that, the error rate increases to about 5% when human beings perform complex logical works, such as program writing. There exist many other types of errors which occur during spreadsheet development. These types of errors are more technical and not easily detectable. Panko and Halverson (Panko & Halverson, 1996) give a list of error types as described below;
To start with, there are quantitative errors which lead to a spreadsheet giving incorrect result. Qualitative errors are also common and they normally lead to quantitative errors at a later stage especially during maintenance. These errors are also referred to as “Latent errors” by Reason (Reason, 1990).
According to Panko and Halverson (Panko & Halverson, 1996) quantitative errors can be divided into three types. The first type of quantitative errors is mechanical errors which are defined as simple mistakes such as typing a number in the wrong cell and mistyping of numbers.
The second category of quantitative error is logic errors which involves misapplication of formula as a result of poor reasoning. Logic errors are usually more difficult to detect than mechanical errors. The third category of quantitative error is error of omission. This type of error involves leaving out some numbers in the computation. Panko and Halverson compared spreadsheet errors with poison because of their detrimental effect on the quality of the spreadsheets.
Errors in program development
Errors by life cycle stage
In programming, the rate of spreadsheet errors varies from one stage to another. A number of researches have shown that many errors are committed by programmers when they are building various modules. However, these errors are easily detected at later stages of development and corrections done. In order to facilitate detection of errors, the module is submitted to a code inspection and data testing.
Errors during cell entry
These errors occur during the process of inserting formulas in the spreadsheet. Code inspection and rigorous testing may be used in detection of these types of errors. However, code inspection is a very difficult technique and may not detect all spreadsheet errors.
Emerging theory
There is an emerging theory which holds the argument that human beings are capable of performing multiple tasks due to their flexibility. However, the same cognitive ability that enables human beings to work this way exposes them to great risk of making errors.
Conclusion
Researches done to date in the development of spreadsheet portray a grim picture. Spreadsheet errors subject organizations to great danger of financial and operational problems. In some instances, organizations have been forced to wind up their operations as a result of misreporting of their activities some of which lead to stiff penalties being imposed on them by the governments.
In addition, researches have shown that one type of error can result to other multiple errors. Despite all the above dangers, a number of organizations still do not find the need to institute appropriate measures so as to mitigate the effects of spreadsheet errors.
References
Dent, D. (1995). Managing the Quality of Quantitative Analysis. Sloan Management Review, 34(2), 69-75.
Ditlea, S. (1987). Spreadsheets Can be Hazardous to Your Health. Personal Computing, 60-69.
Freeman, G. (1996). Estimation of means and standard errors in the analysis of non- orthogonal experiments by electronic computer. London: Royal Statistical Society.
Panko, R. R., & Halverson, R. P. (1996, January). Spreadsheets on Trial: A Framework for Research on Spreadsheet Risks. Proceedings of the Twenty-Ninth Hawaii International Conference on System Sciences, Maui; Hawaii.
Reason, J.T. (1990). Human error. Cambridge England; New York: Cambridge University Press.