macros
top level outputs
forecast inputs
fixed inputs
management accounting actual inputs
event flags
output track
model checks.
Hierarchy and sheet layout
This is linked to the previous discussion on organisation. All models should have a deliberate hierarchical organisational structure. This should manifest itself in an accessible layout of the sheets in Excel.
I would suggest allocating the business and modelling components across sheets as shown in Figure 2.
Figure 2: Business and modelling components allocated across sheets
Model flow
Model flow is defined as the logical direction of information in the model. In much the same way as for that of a book where the reader will read from top to bottom and left to right this is also the model flow across and within sheets.
Model flow is from top to bottom and left to right.
In practice this means that logic in the model will try to use information from above and behind it as far as is possible. This gives the modeller and the user a clear starting point for where to place information and how to perform calculations. Every now and then it is necessary to use information from ahead of the present calculations and I call these counter flows.
Model counter flow is any flow of information that travels against the model flow.
For example, when calculating the corporation tax in any period of the model it is necessary to start with the profit before tax line from the financial statements and possibly make some adjustments in order to derive taxable profit. The tax sheet normally resides just before the financial statements sheet since the results of the tax sheet are used in the financial statements, so the profit before tax line used in the tax sheet is defined as a counter flow.
The number of counter flows should be kept to a minimum in order to preserve the conventions and maintain the integrity of the model.
Examples of unavoidable counter flows include:
profit before tax (PBT) in the corporation tax calculation
using beginning balances to calculate interest on a loan
cash available subtotals from the financial statements are used throughout the model to allocate cash down through the cash flow waterfall.
Links
A model contains thousands of links between cells sometimes on the same sheet and sometimes across different sheets. These links are called cell references. Here I discuss the definitions and implications of at-source cell referencing for best-practice modelling and daisy chain referencing, which is the poor alternative.
At-source cell referencing
Whatever inputs are required for a particular calculation there will exist the optimum at-source location for all the ingredients. In other words, whether the ingredients are raw inputs or other calculations, for each one there will be a single best location source.
At-source referencing means collecting inputs for a calculation from the correct place of first calculation or input rather than from any other location in the model. The overall picture of the references in the model is one where there are no daisy-chains.
Daisy chains referencing
Daisy chains are referenced links in the model that do not go back to the original source. Daisy chains are not good because if a calculation is deleted or changed the repercussions can be difficult and time-consuming to predict and repair.
Suppose that the RPI index is used by two different costs as part of the creation of nominal cost lines in the model. Figure 3 shows the trace precedent arrows in Excel showing the two types of references that could be made. Example 1 shows that Cost 2 uses the index from Cost 1, whereas Example 2 shows that in both Cost 1 and Cost 2 the reference to the index is the same.
Figure 3: Example of a daisy chain link and a correct parallel reference
Example 1 is the wrong way of referencing the index and Example 2 is correct.
As the model becomes more complex and the number of links reaches the tens of thousands, correct parallel at-source referencing becomes very important for the integrity of the model. Parallel at-source referencing allows for:
chunks of logic to be deleted or added more freely without risk of errors
faster tracking to dependents from inputs in cases where this is necessary
The tree analogy
The tree analogy is one of the central concepts in this book. The tree analogy (shown in Figure 4) helps explain the structure of a model as well as hinting at the most efficient way of getting around the model.
Figure 4: Tree and root system
The tree analogy suggests that:
root ends are the inputs into the model
roots are the main calculations in the model
the tree trunk is the financial statements
the tree’s branches are the further results and analysis in the model.
Furthermore, the tree analogy also suggests that:
further results of the model are made of elements drawn from the financial statements
financial statements are made of elements drawn from the main calculations and inputs in the model, but not the further results.
Model structure and the tree analogy
Considering the structure of a model the tree analogy also suggests that:
financial
