One study found that over 90% of spreadsheets contain mistakes. With management decisions being made on the basis of your spreadsheet reports, how can you minimise the risk of errors?
Spreadsheet Study
Spreadsheets have become an essential tool for financial controllers, but how accurate are the numbers they generate? Research by a major accounting firm found that nearly one third of spreadsheets have errors and 90% of spreadsheets with over 150 rows contain at least one significant formula mistake. Such errors can be devastating because the data is often the foundation on which many organisations base their key decisions.
Over confidence was found to be the most serious aspect of spreadsheet errors. In one study, subjects said they considered there was an 18% chance that their completed spreadsheet contained errors, in fact, 86% did.
Basic Spreadsheet Design
Simple input-output structure. When laying out a spreadsheet, it is best to divide it into three areas – input, calculation and output. The input area should not contain any formulas or calculations.
Headers and Footers. To enable easy identification of different spreadsheet versions, headers and footers should show the date and time of printing.
Use colour and Name Key Cells. Use colours in cells to highlight the cells where data should be entered. For added security, add cell protection in cells that contain formulas or for data that should not change.
Add explanatory comments. It also hepls to add comments to certain cells.
Rounding It is surprising how many printouts from spreadsheets contain rounding errors. They ma seem insignificant, but they can totally undermine confidence in a spreadsheet. To avoid problems, round all calculations to rthe same number of decimal places, ideally two. Use ROUND function provided by spreadsheet program.