See-Through Modelling. Dominic Robertson
is in my view the single most important area of modelling.
Modelling is all about organisation. This includes the names of each and every line item, the units, the choice of row and column to place the line items, the patterns across sheets, the changes in pattern, the order of line items, the hierarchy of all the line items, and the choice the model builder makes about either showing or not showing intermediary line items. This information is either explicitly visible or available for interpretation, or it is implicit and therefore hidden. I am a firm advocate of making this information as open, simple and transparent as possible.
I believe that a model should be organised to represent the entity that is being modelled. If this is a company then the natural organisation follows that of a company: operations, accounting, finance, tax, the financial statements, and the returns and covenants.
A structural look at the model components
The model is made up of both business and modelling components. This represents the first big subdivision within a model. Here is a fuller list of those two types of component particularly referenced to project finance.
Business components deal with the company finances that are being modelled:
LIBOR
macro-economic indexation
operating revenues
operating costs
capital expenditure
life cycle expenditure
accounting amortisations, including:
fees amortisations
fixed asset depreciation
finance debtor calculations
finance, including:
debt finance
subordinated finance
equity finance
corporation tax
tax depreciation
tax losses
VAT
profit & loss
cash flow
balance sheet
cover ratio analysis
investor return analysis
project return analysis.
Modelling components deal with the necessary modelling technicalities that make a good model work:
project details
notes
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