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.