Spreadsheets may contain hidden errors. These can present a degree of risk whenever decision making is based upon your calculations. Follow these steps to reduce the risk of errors occurring.
Many studies by academics and accountancy firms have shown that a consistently high proportion of spreadsheets contain errors. Few companies recognise this enormous risk.
Errors are easier to detect if the spreadsheet is well designed. It is best to create three distinct and separate sections (probably on different worksheets):
|Inputs||for all user inputs and defined variables (constants)|
|Calculations||to process the inputs|
|Outputs||any reports and tables to be printed|
The level of risk will depend upon the size and complexity of the model and also the use to which it will be put. The highest elements of risk are:
Would you like to learn more?
The greater the element of risk, the more thoroughly your spreadsheet should be tested.
Models should be tested by somebody else, who has the sole aim of trying to prove that it doesnt work.
Excel auditing tools can help review the flow of data through your model.
Draw a spreadsheet map to identify whether formulae appear in the correct places and whether they are copied across columns correctly.
|Click here for links to the European Spreadsheet Risk Interest Group and other Excel sites.|
Maps can be a useful aid to testing large models containing numerous formulae and functions. By describing the contents of each cell it is easy to highlight cells which contain:
I have created a spreadsheet called SheetMap containing a macro which will insert a worksheet into your existing spreadsheet. The new sheet will display an analysis of all cell contents using the following key:
|Ref||Reference to another cell||=B3|
These codes may be combined with additional indicators:
|>||Contents are copied from cell to the left|
|^||Contents are copied from cell above|
|!||The cell refers to another worksheet|
|(shading)||Coloured cells have no dependents|
|< The original.
The map >
In the example above there are two hidden errors on the worksheet. The formula in column C has not been copied correctly from C6 to C7. Also, the pink shading in D4 indicates that this value is not referred to by any formula - i.e. it has no dependents. The SUM formula in cell D9 (which is shown in the first image) has mistakenly omitted the first value in D4.
If you are interested in obtaining a copy of SheetMap.xls, it is included with the Excel functions book below.
|Get this information as a document
accompanied by Excel worksheets
|Click here for details about
file. It has been rewritten for Excel 2010.
|file: xltestingandmapping.htm||© meadinkent.co.uk 2013||CMIDX S7 P2 Y||Last updated nov11|