See-Through Modelling. Dominic Robertson
that a flag can take are 1 or 0 for each period across the timeline, as in Figure 7.
Figure 7: Actual period flag
Switches are very similar to flags but they are non-time dependent because they either turn calculations on or off. Switches can take the value 1 or 0 (this is called a Boolean), as in Figure 8.
Figure 8: Switch
Indexation
Most PF/PFI models forecast 20 years or so ahead and indexation is an important part of the contractual process. Indexation is also the factor that can most affect a model since any change in rate now has a cumulative effect over time.
Macro-economic indexation is required to inflate real based inputs to nominal values in time. Indexation is based on ONS actual updates (possibly on a monthly basis) to bring the indexation up to date.
Independent and centralised indexation calculations are required in any model that looks to the future.
Class 3: Model control theory
Model control is about directing the model to carry out precise functions in the most efficient way. Here I discuss the optimal format of the inputs, the tracking of outputs, the various points of view described within the model, time as a dimension, the differences between modellers and managers, and overall control during model analysis.
Forecast inputs in column format for running sensitivities
Advanced control of the model requires a slick sensitivity analysis methodology, structure and content. Arranging the forecast inputs in a column format allows for fast and multiple sensitivity analysis.
The column-format forecast input sheet allows multi-dimensional sensitivity analysis without adding any unnecessary bulk to the model. This is shown in Figure 9.
Figure 9: (Forecast) inputs in column format
Tracked outputs sets in column format for audit trail
Similarly to the column-format forecast inputs, the outputs are also tracked in column-format as in Figure 10. This allows for many recorded sets without adding bulk to the model.
Figure 10: Track sheet
Points of view
Models describe the economics of projects and entities from various points of view. To explain I think it is easiest to consider the example of a project finance company and a model of that company.
The great majority of the model is written from the point of view of the project company or the directors of the project company. There are, however, occasions when other points of view are used:
rate of return on the investment of shareholders
positive value of the senior debt repayments
positive value of the interest paid on the senior debt
present value of streams of cash flows such as supplier fees (called operating costs by the company)
The shareholder investment cash flows are the clearest example of how the sign flips from positive to negative as the point of view changes. Whereas the equity invested in the project company is a positive for the company accounts, it is a negative for the shareholders. The reverse is true of the dividends that reward the investors for the initial investment; dividends are negative for the company accounts, but most certainly a positive for the investors.
The modeller must beware not to mix up different points of view into the same calculations. In particular, each result will be from one single point of view and all flows and balances that are used to calculate that result must also be from the same point of view.
It is paramount that the modeller is always clear on which point of view is being modelled. This point of view knowledge will make it far easier to carry out precise modelling as the sign of the flows in question will be much more obvious.
The time dimension
The time dimension within a financial model deserves some respect. Excel does not understand the passage of time. Each formula must be coded to deal with time, and formulas can change substantially between a quarterly, semi-annual or annual timeline (this is known as periodicity). In Excel, time creates large amounts of logic and therefore can add substantial bulk and complexity to a model file.
Since each timeline requires all the calculations that form the basis of an individual model it is generally preferable to have only one timeline in a model.
Modellers & managers: how the needs change
There are possibly two types of users for the model, the modeller and the modeller’s manager. In my experience, modellers are quite happy with the technical baggage of the model as well as the business components, as in Figure 11, whereas managers prefer to see the business finances from a business point of view, as in Figure 12.
Figure 11: Modeller view of model components
Figure 12: Manager view of model components
If modellers and managers place emphasis on different elements of the model, then how does the emphasis change and how should the model adapt to these different needs? The answers lie in the view point of each.
Managers look for the business components, while modellers start by looking for the modelling components. Since the model structure is fine for modellers it is sufficient to display an alternative entry structure that satisfies the manager. The Quick Start sheet is my answer to the manager’s needs.
See Appendix 5 for a detailed look at the Quick Start sheet.
Overall control of model analysis
As the analyst works with the model they are sure to be carrying out changes to inputs to discover how related outputs are affected. If these changes become repetitious and involved there grows a need to see both the inputs and the results in a single place rather than across sheets.
My normal way of dealing with this is to temporarily bring the inputs and outputs to a sheet called the control panel. Figure 13 shows a simple control panel with a mixture of outputs, charts and a yellow-shaded input. By changing the input the effect on the outputs and charts can be quickly gauged. The analyst can build up a feeling for the movements in model outputs and thus better understand the dynamics of the project or company finances.
Figure 13: Simple control panel
Class 4: Model testing theory
Checking