Authors
Anthony Berglas & Peter Hoare
Abstract
Spreadsheets have become an essential tool for performing financial modeling and analysis. But spreadsheets can contain significant errors. Freeman (1996) reports that a survey found that 90% of spreadsheets with over 150 rows contained at least one significant formula mistake.
Overconfidence is perhaps the most serious aspect of spreadsheet errors because it reduces the extent to which people validate their models before using them to make important decisions. The following guidelines should be used when writing spreadsheets:
- Never place constants in formulae.
- Follow a simple input-output structure.
- Repeat important input values from other worksheets.
- Break up complex formulae into small pieces that can be verified independently.
- Avoid having formulae that refer to other cells below them or to their right.
- Document important formulae in the worksheet.
- Add redundant verification formulae to the model.
- Collect redundant input values and use validation formulae to check the results.
- Be careful when using the Solver.
Sample
Formula inconsistencies are highlighted using the Go To Special dialog. For example, F9 is inconsistent with C9.
Inconsistent formulae are a potentially serious source of error.
Publication
1998, Australian CPA, Volume 68, Number 3, pages 42-45
Full article
Not available
