Commercial spreadsheet application programs probably are accountants' most widely used computer tools. But how accurate are the numbers they generate? While the underlying programs that run spreadsheet software are flawless, are the myriad custom formulas that users build into a typical spreadsheet equally sound?
Research by a major accounting firm found that over 90% of spreadsheets larger than 150 rows contained at least one significant formula mistake. And it takes just one small error - a single misplaced code - to produce wildly erroneous results. Such errors can be devastating because the data often are the foundation on which many organizations base their key decisions.
It's possible to make spreadsheets not only virtually error-proof, but easier to use and more understandable. This article tells how it's done.
To avoid errors, use these techniques:
- Divide a spreadsheet into three areas - input, calculation and output.
- Organize the input area by data that change infrequently or never and data that a user will change regularly.
- Don't insert any formulas or calculations into areas where data is inputted.
- Give input data cells range names with descriptive words rather than letters and numbers.
- When printing the output data be sure to include the input area also so underlying assumptions are clear.
- To enable easy identification of different spreadsheet versions, printouts should include in either headers or footers the date and time of printing.
- All calculations other than pure additions or subtractions should be rounded to the same number of decimal places used for display purposes.
1996, Journal of Accountancy, Volume 181, Number 5, May, page 75-77