Judith A. Ross
The article discusses the findings of a study on spreadsheet error and presents five steps that ensures that managers can make spreadsheets more accurate.
The research suggests that spreadsheet developers usually err when they make an inputting error, when they use incorrect logic in one of the spreadsheet's formulas and when they simply leave out a piece of information. To avoid these pitfalls the research suggests conducting a random audit of the spreadsheets that are critical to a business organization's function.
Companies should institute protection computer programs that guard against error. Authorship and responsibility for spreadsheets should be clear and the sheets themselves and the information in them should be self-explanatory. Results should be checked by hand. Managers should construct a detailed spreadsheet design plan before beginning construction of one.
Managers can make spreadsheets safer by taking five relatively easy steps:
- Conduct a random audit of 25 to 50 spreadsheets to determine whether there are any problems - and their extent if there are. Begin with spreadsheets that are critical and use a team of auditors rather than an individual because an individual is likely to catch only about half the errors.
- Build protection into all spreadsheets as a way to prevent errors. A protected spreadsheet lets users change assumption numbers while preventing them from typing a number where a formula should go.
- Prevent spreadsheets from turning into "black boxes" by having them written in clear code, clarifying who is responsible for important spreadsheets, and documenting how they will be maintained if the developer leaves the company.
- Check the results manually. Although the numbers on a completed spreadsheet may look reasonable, they often are not.
- Complete a detailed design before starting to build and use a spreadsheet. The design should be inspected formally by users to ensure that the developer understands what functions are needed, and by people with development expertise to prevent problems.
1996, Harvard Business Review, Volume 74, Number 5, Sep/Oct, pages 10-12