The value of quality

Many studies over the past ten years have shown that up to 90% of models contain significant errors. Generally these errors go undetected, but there have been some well-publicised cases where they have resulted in financial loss and embarrassment.

For example, an employee at a US investment fund omitted the minus sign on a figure of $1.2 billion while transferring data between spreadsheets, thus “creating” a $2.3 billion gain instead of the actual $0.1 billion loss. The company announced to shareholders a distribution of $4.32 a share; a month later, when the error was detected, it had to announce that there would be no distribution (further information at Dr. Panko’s website; see “Other sites”).

The vast majority of such errors could have been avoided by following a small number of key quality principles in building models, as explained below. (References to spreadsheet features assume the use of Microsoft ExcelTM, but are equally applicable to other spreadsheet software.)

Key quality principles

The hallmark of a good model is that it is clearly laid out and easy to understand, so minimising the possibility of errors such as that mentioned above. Some key steps to achieving this are:

  • Design, then develop, then test
  • Separate the inputs from the calculations
  • Use only one formula per row
  • Avoid balancing figures
  • Use protection and locking

Continue Reading

Modelling issues

There are many issues that can present a challenge in modelling. Discussion of these can be found at some of the websites listed on our Links page, and there are also many books covering spreadsheet software packages, although these tend to focus on technical points rather than quality and business issues.

Some of the most frequently encountered issues are:

  • Circular formulas
  • Interest calculations
  • Currency and inflation
  • Mixed time periods
  • Large models
  • Visual BasicTM for ExcelTM

Continue Reading

Back to topAdministration