Model errors

Download a pdf copy of this help file  here

Your company may have hundreds or thousands of spreadsheet models in use. If even 1% of these have errors, you could be making many decisions based on quite inaccurate information.

Errors come in several forms:

• Syntax errors where a formula is incorrectly put together. For example, you mismatch brackets, forget to make a formula into an array formula (by entering with Ctrl+Shift+Enter instead of Enter), use the wrong function, etc.

• Mechanical errors which are hitting the wrong key, pointing to the wrong cell, etc. About 1% of spreadsheet cells contain such errors;

• Logical errors which are incorrect formulae due to mistaken reasoning or a misunderstanding of a spreadsheet's function. These errors are more difficult to detect than mechanical errors and occur in about 4% of spreadsheet cells;

• Application errors where the spreadsheet function does not perform as it should. Some versions of Excel don't update all formulae correctly automatically - use Ctrl+Alt+F9 instead of F9 to be sure;

• Omission errors where a necessary component of the model has been forgotten. These are the most difficult errors to detect;

• Administrative errors, for example using an old version of a spreadsheet or graph, failing to update a model with new data; failing to get the spreadsheet to recalculate after changes, etc.

In ModelRisk, when an error occurs, not only an error message is returned, but also an explanation of what is wrong. You can read about it here.

A quick Internet search for 'spreadsheet model errors' will provide you with a wealth of individuals and organizations who research into the source and control of spreadsheet errors. For example, the European Spreadsheet Risks Interest Group is dedicated to the topic. Raymond Panko from the University of Hawaii is a leader in the field and provides an interesting summary of spreadsheet error rates and reasons at http://panko.cba.hawaii.edu/SSR/home.htm.

Looking at the error percentages, for large models the question is not 'Are there any errors?' but 'How many errors are there?' A company can help minimise model errors by establishing and enforcing a policy for model development and for model auditing.

Common errors in risk modeling

Apart from the errors described above, there are three very common mistakes we often come across in auditing risk models, even at the more elementary level. These mistakes probably constitute around 90% of the errors we see. We strongly recommend studying them, and going through the examples thoroughly.

Common error 1: Calculating means instead of simulating scenarios

Common error 2: Representing an uncertain variable more than once in a model

Common error 3: Manipulating probability distributions as if they were fixed numbers