First presented at CALECO
First presented at CALECO
The same features that make spreadsheets easy to use also make them difficult to read and maintain. You can start building a model without having to worry about what comments to write, how to declare variables, or how to structure your code into modules and procedures. This may not matter for programs small enough to be understood in their entirety; but programs never stay small. Some can grow very large indeed: an article in Personal Computer World, May 1995 cited an oil-business model with 80 linked worksheets totalling 120 megabytes.
The problems facing large programs were highlighted by an article in New Scientist [Ward 1997] which summarised research by Professor Raymond Panko of the University of Hawaii. Panko reviewed ten years' worth of studies on errrors in spreadsheet use, and estimated that people make around one error every hundred cells, and that on average, 30% of spreadsheets contain errors, including wrong data and wrong formulae. He cites research by Coopers and Lybrand [Freeman 1997] which shows that over 90% of spreadsheets with over 150 rows contained at least one significant mistake in an formulae.
Details of this research are available from Panko's Spreadsheet Research Web page [Panko: Spreadsheet research]. This page gives some horrendous examples of the damage faulty spreadsheets can do. For example, a Fortune 500 firm used discounted cash flows in a spreadsheet for evaluating investment proposals. The discount rate and discounting formula had been established long ago by an employee who had since left, and were not documented. Although the prime rate rose from 8% to over 20% between 1973 and 1981, the spreadsheet was kept at 8% [Dhebar 1993]. In another example, Price Waterhouse found 128 errors covering 120 line items while auditing a spreadsheet model that had already been in use for some months [Ditlea 1987].
There are already programs that check spreadsheets for errors: [Ditlea 1987] mentions Cambridge Spreadsheet Analyst, and the London company Spreadsheet Innovations sell a program called Spreadsheet Professional. Their program can generate a map showing the spreadsheet's structure, and also check formula cells for errors. These checks are based on a catalogue of errors which its authors constructed from an analysis of over 200 models. In addition, a number of organisations issue guidelines to help programmers write spreadsheets for maintainability [Institute of Chartered Accountants: Spreadsheet design], and there are also books on doing so [Richardson 1996]. MM's approach is different, tackling the problem before it starts rather than once it has struck.
First presented at CALECO
First presented at CALECO