statements are the central element and first main result in a financial model
further results of the model should be derived by using the financial statements as much as is possible
the first results to build are the financial statements.
Navigating the model and the tree analogy
To find the appropriate inputs the most efficient path into the model is by starting in the financial statements.
A standard question facing the analyst could be: “What effect will a change in RPI have on the dividends?”
This question refers to a result from the financial statements – dividends – and also an input into the model in the form of a forecast RPI inflation rate. So where should the analyst start? Should the analyst go straight for the possible input, or start somewhere else to ensure the right input is found?
Using the tree analogy it is clear that travelling down a root system from the tree trunk to the roots with intelligence will quickly lead to the input in question. Conversely, testing inputs and tracking up from the roots to the results is by far more time-consuming and inefficient.
The reason is that travelling up a tree diagram in Excel the modeller will be confronted by a trace dependents window like the one in Figure 5.
Figure 5: Trace dependents window
At this point the modeller will have to repeat this step four times to find the best next cell to track to. Alternatively, the modeller can track back from the operating costs in the financial statements using F5 + Enter or simply double-clicking the reference and making intelligent choices through the tracking process.
Class 2: Model content theory
Model content deals with the whereabouts of business and modelling components within the model structure. In this section I discuss all the model components including inputs, calculations, outputs, formulae, event flags, switches and macro-economic indexation.
Outputs, calculations and inputs
The model can be thought of as an ‘i/o’ or ‘input/output’ system. This means a system that takes inputs and produces outputs from those inputs. All computer software is a type of i/o system.
The project finance financial model takes inputs in the form of actuals and forecast inputs and uses coded calculations to produce outputs. The outputs are primarily the company financial statements but also the further calculations thereon, like the shareholder returns and the debt cover ratios. It is clear that to properly drive the model the user needs to at least be able to distinguish between the inputs and the rest.
Since there is a distinction between the inputs, calculations and outputs of a model it is also normal for the modeller to adopt a convention in this regard. For example, a yellow background is the wide-ranging convention for inputs. Calculations are performed on calculation sheets rather than input sheets and output (or report) sheets are marked by colouring the tab green.
A detailed look at the model components
Below the sheet names are the next level of headings in the model hierarchy. I call these components and they are of two generic types: business components and modelling components. Figure 6 shows sheets as boxes and components in the list below the boxes.
Figure 6: Model hierarchy, sheets and components
The aggregate of the business components and the modelling components make up all the components in the model. Understanding which components are to be modelled is one of the crucial first steps in building the model.
Business components
As discussed, the business is made up of a series of chunks of logic called components that divide into two classes: business components and modelling components.
Here I discuss each of the components in detail.
The London Interbank Offered Rate often forms part of the basis for the variable interest rate on debt. The most frequently used LIBOR is 3-month or 6-month. If required, the model will need to have an actuals plus forecast LIBOR strip across the timeline of the model.
The macroeconomics component should contain all calculations relating to the calculation of indices such as RPI and RPIX.
The operating revenues component deals with all revenues both on an accrued and a cash basis and therefore also the working capital implications of the delay between the accrued and the cash. This can become quite a large component, depending on the type and complexity of the company. Included in this component are:
revenues accrued and received
debtor balance calculations.
The operating costs component deals with all costs both on an accrued and a cash basis and therefore also the working capital implications of the delay between the accrued and the cash. This can become quite a large component, depending on the type and complexity of the company. Included in this component are:
operating costs accrued and paid
creditor balance calculations
any prepayments and the prepayments balance.
The capital expenditure component deals with all capital expenditure both on an accrued and a cash basis. Normally for a project in operations this has all happened during the construction phase so this component should be empty.
However, for completeness the items included in this component would be:
capital expenditure accrued and paid (excluding any depreciation calculations)
creditor balance calculations relating to any assumed or known delay between the accrued expenditure and the paid expenditure.
The life cycle expenditure component deals with all life cycle costs both on an accrued and a cash basis, and therefore also the working capital implications of the delay between the accrued and the cash. Included in this component are:
life cycle expenditure accrued and paid (excluding any depreciation calculations)
creditor balance calculations relating to any assumed or known delay between the accrued expenditure and the paid expenditure.
In the accounting component I deal with all depreciation and amortisation issues that affect the P&L. In particular, these items are:
depreciation of all assets for accounting purposes
amortisation
