Modelling
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
Design, then develop, then test
Before starting to build a model, it is essential to plan out its overall structure and agree a detailed design for the reports and sensitivities, in order to ensure that the model will actually deliver what is required of it. This process will effectively determine the inputs and outputs; all that remains then is to build a bridge between the two with the appropriate calculations. These should be tested at each stage of developing the model as well as when the model is complete. In addition to more specific tests, it is essential to check for reasonable-ness; are the results about right, and do they change sensibly when the inputs are altered?
Separate the inputs from the calculations
Users should be able to see the assumptions underlying the model results simply by looking at the printouts. If numbers are hard-coded into the calculations this becomes impossible, and it is then also very difficult to change the assumptions. Consequently all assumptions, even “constants” like the number of days in the year, should be set out as inputs and not hard-coded into formulas. The model should have a separate area (generally one or more sheets) containing all the input values.
Use only one formula per row
It is often very tempting to use different formulas within a row, for instance in a quarterly model where tax is paid annually. However, this makes the model very prone to errors, extremely difficult to check, and also very inflexible. In fact it is easier simply to set up a row containing calculated “flag” values, and write a formula which pays the tax when the flag is set to 1. When designing a formula, the question should always be “What is the relationship between all the factors which can affect the results on this row?”, not simply “What is the easiest way to calculate the answer in this cell?”
Avoid balancing figures
Balancing figures are extremely dangerous because they can very easily conceal errors in a spreadsheet. Consequently cash, for instance, should always be calculated as cash balance brought forward plus inflows minus outflows, not simply as the amount required to balance the balance sheet. It is then essential to check that the balance sheet remains in balance under all the different sensitivities and scenarios in the model.
Use protection and locking
The integrity of a carefully constructed spreadsheet can be easily destroyed by a user who, for example, wishes to see the effect of raising turnover to 100m in period 5, and simply types “100″ directly into the cell, overwriting the turnover calculation. To prevent this, all sheets (or at least those containing calculations) should be protected, and input cells should be formatted as unlocked.
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
Circular formulas
Circular formulas are hardly ever necessary, except perhaps in certain front-end-fee calculations. In nearly all other cases they should be eliminated, because they obscure the logic of the model and can easily lead to spurious results. It can be difficult to trace the chain of circularity; one useful approach is to break the chain temporarily by replacing the formula in a “circular” cell with a fixed input value. If a circular calculation is absolutely necessary, it is important to control it by a switch (allowing linear calculation), or by using Visual Basic to manage the iterations.
Interest calculations
One area where circular formulas are often used is in calculating interest, based on the average of the opening and closing cash balances. This is not necessary, and it is much better to calculate an approximate closing balance before interest, and then calculate the interest on the average of this and the opening balance. For complete (but perhaps spurious) accuracy, it is even possible to “gross-up” the closing balance to include interest without causing circularity.
Currency and inflation
For economic and public-sector models, reports may be required in “real” rather than nominal (money of the day) values. However, the underlying calculations should always use nominal values and then convert these to real. A model which attempts to calculate directly in real terms is likely to give misleading results, because it fails to convert carried-forward balances to the correct brought-forward values for each period. Similarly, multi-currency models should use local currency as far as possible, converting to GBP or USD only for final reporting. The only exception to this is in certain high-inflation economies with FX rates fixed to USD.
Mixed time periods
Project finance models often require monthly or quarterly periods at the start of the model, followed by six-monthly or annual periods later, and this can make it difficult to use a single formula for the whole of each row. One possible solution is to use a calculated flag row indicating the number of months in each period, which can be used as a multiplier in, for example, interest calculations. An alternative approach, if the model is not too big, is to perform all the underlying calculations using the smallest time period, and then consolidate for reporting purposes.
Large models
It is tempting to use multiple files for large models, but this should be regarded as a last resort. Multiple-file models are prone to hidden circularities, and can easily become corrupted unless all files are open in memory when any structural changes are made. It is preferable to reduce the size of a large model by eliminating any unnecessary graphics and formatting, and by simplifying formulas. In particular, if there are large blocks of repeating formulas, these may be replaced by an array formula, by a reference to a named formula, or by a custom Visual Basic function.
Visual BasicTM for ExcelTM
Many spreadsheet users limit their use of Visual Basic to recording with the Macro Recorder, for instance to generate a standard printout. However, VB for Excel is actually a complete Microsoft Windows application development environment, and it can also be very valuable in managing complex calculations, for instance with custom functions. However, as in any programming language, it is essential to ensure that VB routines are properly designed (with minimal use of GoTo), carefully written (with explicit variable declarations, standard indentation and liberal comments) and thoroughly tested (covering all paths through the code).
