Once you have defined the model calculations, the inputs required in the model should be implicit. It is frequently worth considering in more detail what inputs are required and how they are to be sourced.
Business Dynamics, Spreadsheet Modelling Best Practice Chapter 4-29
The table below is an example of a data input list. A data input list of this form is useful because it:
• establishes the level of detail required in the input assumptions at an early stage, in a form that is easy to communicate;
• highlights where special effort will be required in data collection – and assigns responsibility for producing the data; and
• in the status column, lists the type of estimate that will be used for each input assumption. This can help identify the inputs that will be candidates for sensitivity analysis.
Data input Format Units Frequency Status Validation Source Notes
Interest rate %, 2dp % One entry
only. Policy variable. Must be > 0. Finance dept. The rate applied on any overdraft balances.
Sales Numeric 000's units One entry
per month. Forecast 0. Must be > Marketing dept.
Price Numeric £ One entry
per month. Agreed estimate. Must be > 0. Marketing dept.
Debtor period Numeric Days One entry
only. Estimated from budget data. Must be > 0; must be < model period. Finance dept. Capital expenditure
Numeric £000's One entry
per month.
Forecast Capex
model (auto transfer).
Asset life Numeric Years One entry
only. Policy variable. Must be > 10. Finance dept. Calculated on a straight line basis. Balance sheet –
opening balance Numeric £000's Start only. year Firm figure. Finance dept.
Year end Date Start year
only. Firm figure. Finance dept.
Business Dynamics, Spreadsheet Modelling Best Practice Chapter 4-30
Hints and tips for model specification
Are constants constant?
Avoid using hard coded numbers in the definition of a formula. For example, the formula: Sale price including VAT = Sale price excluding VAT × 1.175
assumes that the VAT rate is 17.5%. It works fine, until the VAT rate changes.
To avoid having to search through every formula in the model for calculations which assume the VAT rate, set up an input cell for the VAT rate and refer to that instead. For a single cell of this sort, it is useful to create a range name, say “VAT_rate”, so instead of typing the constant you use the formula:
Sale price including VAT = Sale price excluding VAT × (1 + VAT_rate)
If there is any change to the input assumption, or you want to run a sensitivity, the constant can be changed throughout the model instantly. Even if the constant never changes, the new formula is much simpler to read and understand.
Real or nominal prices?
In financial models, a common problem is deciding whether to base monetary calculations on real or nominal prices. Real prices are prices of a particular base year and do not increase with inflation. Nominal prices, or money of the day, do include the effects of inflation.
Real prices are frequently used for projections because it is possible to separate market trends from increases caused purely by inflation. For the same reason real prices are often used for summary results, particularly to calculate year on year changes in revenues or costs.
Using nominal prices is more accurate for any calculation involving monetary values over more than one time period. You should always use nominal prices for calculations of debt and interest, depreciation, tax or any stocks, such as assets and liabilities on a balance sheet. Any moderately complex financial model must, therefore, include most or all of its calculations in nominal prices. However, real prices can be useful for input assumptions or for some results. So, the steps to go through are:
• inputs, often in real prices;
• conversion of any inputs into nominal prices;
• all required calculations carried out in nominal prices; • conversion of results back to real prices, if required; and • presentation of results.
To make conversion between real and nominal prices easy, it is a good idea to place the calculation of an RPI index in a clear place where it can be referred to quickly.
Business Dynamics, Spreadsheet Modelling Best Practice Chapter 4-31 A B C D E F G H I J 1 Units Constants 1999 2000 2001 2002 2003 2004 2 Inputs 3 Inflation % p.a. 3.0% 3.0% 2.5% 2.5% 2.5% 2.5% 4 Revenue (mid 1999 prices) £ real 100 100 110 120 120 120 5 6 Calculations
7 Inflation index index (1999=1) 1.00 1.03 1.06 1.08 1.11 1.14
8 Revenue £ nominal 100 103 116 130 133 136
9
Figure 14: Example of real and nominal prices
Avoid circular references
Circular references should be avoided in model calculations. Frequently they are a sign of an error in the logic.
The best way to avoid a circular reference in your specification is to construct your calculation rules so that they always read from top to bottom and left to right: so that formulae always refer to cells above or to the left.
A common example of a circular reference arises when calculating interest on a bank overdraft. Consider the following example of the wrong way to do it.
A B C D E F G
1 Units Formula Constants Year 1 Year 2 Year 3
2 Inputs
3 Opening bank balance £000s (500.0)
4 Forecast revenues 300.0 300.0 300.0
5 Forecast costs (200.0) (200.0) (200.0)
6 Interest rate on overdraft % p.a. 8.0%
7
8 Calculations
9 Opening bank balance £000s
If year = 1 THEN Initial opening balance ELSE Closin
H
g balance previous year (500.0) (434.8) (363.9)
10 Add: revenues £000s Forecast revenues 300.0 300.0 300.0
11 Less: costs £000s Forecast costs (200.0) (200.0) (200.0)
12 Less: interest on overdraft £000s
If Closing bank balance <0 THEN Closing bank balance × Interest rate on overdraft
ELSE 0 (34.8) (29.1) (22.9)
13 Closing bank balance £000s Sum of the above four rows (434.8) (363.9) (286.8)
14
Figure 15: Example bank overdraft: the wrong way
This calculation contains a circular reference because the interest on overdraft is calculated from the closing bank balance which is itself calculated from the interest. By default, Excel will refuse to calculate the spreadsheet above, although it is possible to allow it to iterate and produce the results above. For large models, iteration is too time consuming to be practical.
Business Dynamics, Spreadsheet Modelling Best Practice Chapter 4-32
Without a circular reference, interest can be calculated like this.
A B C D E F G
1 Units Formula Constants Year 1 Year 2 Year 3
2 Inputs
3 Opening bank balance £000s (500.0)
4 Forecast revenues 300.0 300.0 300.0
5 Forecast costs (200.0) (200.0) (200.0)
6 Interest rate on overdraft % p.a. 8.0%
7
8 Calculations
9 Opening bank balance £000s
If year = 1 THEN Initial opening balance ELSE Closin
H
g balance previous year (500.0) (432.0) (358.6)
10 Add: revenues £000s Forecast revenues 300.0 300.0 300.0
11 Less: costs £000s Forecast costs (200.0) (200.0) (200.0)
12 Balance before interest £000s Sum of the above three rows (400.0) (332.0) (258.6)
13 Less: interest on overdraft £000s
If Balance before interest <0 THEN Balance before interest × Interest rate on
overdraft ELSE 0 (32.0) (26.6) (20.7)
14 Closing bank balance £000s
Balance before interest - Interest on
overdraft (432.0) (358.6) (279.2)
15
Figure 16: Example bank overdraft: the right way
This calculation bases its interest calculation on an intermediate balance before interest is calculated – avoiding a circular reference.
Sharp eyed readers will have noticed that these two methods produce different results, despite having the same inputs. The difference is caused by the compounding assumption implied by the two examples.
The first example (with the circular reference) assumes that interest is compounded instantly and interest on that interest starts to accrue immediately. The second example assumes that interest is compounded annually at the end of the year. The truth will depend on the type of debt being modelled, but is likely to be between these two extremes. All models contain simplifying assumptions; the calculation of interest is one that is often overlooked. It is good practice to explicitly state the assumptions you are using. In this case, the difference between these two examples illustrates that this is a significant assumption.
Business Dynamics, Spreadsheet Modelling Best Practice Chapter 4-33
Chapter summary Specify
• Writing a model specification creates a definitive statement of what the model will do, and how it will do it.
• Writing a model specification makes building the model quicker and less prone to reworking, multiple objectives and errors.
• Specify the model outputs first, then the calculations and then the inputs.
• Testing a model is much more effective if you have a written model specification.
• Bubble diagrams are a good way to understand and communicate the general structure of calculations.
• Calculation tables are a rigorous way to specify a model – especially when the calculations are complicated and you have to get it right first time.
• Prototyping model calculations is especially useful when the problem to be modelled is not yet well understood.
Business Dynamics, Spreadsheet Modelling Best Practice Chapter 5-34
5 Design
Good model design is one of the most striking features of a best practice spreadsheet model. A clear model design makes a model easy to use and understand. As a result, you are less likely to make errors while using the model and more likely to spot the mistakes you do make. A well designed spreadsheet is also easier for another person to pick up.
In this chapter we will:
• consider when a spreadsheet should be used for a modelling problem and
when other software packages are more suitable;
• detail six golden rules of spreadsheet design that all models should follow; • discuss methods for consolidating data in a spreadsheet; and
• consider when to use macros in the spreadsheet.