See-Through Modelling. Dominic Robertson

over-complicated logic

       is massive formulae

       is changing timelines on the same sheet

       is disorganised and has a lack of structure

       is repetitive and duplicative

       has no respect for hierarchy

       creates endless rehashed versions of the financial statements that will require constant maintenance, and

       is the use of un-auditable formulae like OFFSET.

      Basic model structure

      For a modeller, the basic structure of a PPP/PFI model is well established. This structure was shown in Figure 6.

      The operating model will contain most or all of these sheets in the order shown in Figure 6 and with the components as listed. Since this design is well-tested the modeller can concentrate on the design of the calculations, which will be particular to the project in hand.

      Business map of the model

      The manager may not want to see the model in the same overall structure as the modeller. However, we can’t change the order of the building blocks to suit the manager as some important benefits derived from the modelling structure would be lost and this would complicate life for the main user of the model. Instead the manager is offered the Quick Start sheet which allows direct navigation to those areas of interest to the manager.

      See Appendix 5 for the Quick Start sheet.

      3. Build (B)

      Shadow phase

      The objective is to re-perform the results of the old FC model. Most of the model build takes place during this phase. During this phase there are three important steps.

      Step 1 – Update:

      The objective is to update with actuals and make any further requested/specified changes.

      Step 2 – Test:

      The objective is to test the model after the updated changes.

      Step 3 – Audit:

       The updated and tested model now goes for audit where a reputable firm of model auditors review both the integrity and usability of the new model according to a set specification.

       This phase is important for the lenders as it creates an audit trail between the financial close model and the new operating model.

       This phase validates the logic that has been built and asks a whole series of questions about the model logic that the builder has to respond to – these are called queries and are normally categorised:

       Actual error, Potential error, Formatting/label query, Clarification, Numerical assumption, Information.

      4. Test (T)

      Testing a model is a vital part of the build process. In fact, it is so vital that I have dedicated a section within the body of modelling theory to testing; see ‘Class 4: Model testing theory’.

      5. Deliver (V)

      Model delivery is the act of passing a model over to the end user. This happens at various stages during the build process.

      There are various strategic moments when this is best done and certainly moments when it is not best done. In general it is better to lower delivery expectations by adding some delay rather than delivering a sub-standard product. However, this is something that comes with experience.

      There are three main model delivery events in the production of a UK PFI project finance operating model, listed and described here:

      1 Shadow delivery

      2 Update delivery

      3 Final delivery.

      During each of these delivery events the model will undergo a whole raft of ongoing tests during the build and also pre-delivery tests. It is also important that the client carries out end-user acceptance testing after Update delivery. The topic of testing will be dealt with in greater detail in a future eBook.

      1. Shadow delivery (V1)

      The user requires a model that re-performs the results from financial close (FC). This is an important milestone in the audit trail from the FC model to the operating (OP) model.

      2. Update delivery (V2)

      The objective is to deliver a model with a new forecast based upon the latest actuals and the new forecast inputs.

      3. Final delivery (V3)

      The model will have been audited by a reputable firm of model auditors. The objective is to deliver an audited model with the latest actuals and best forecast assumptions.

      Non-project finance delivery events

      The equivalent three delivery events for a non-project finance model are:

      1 Shadow delivery is all modelling prior to a new actual update

      2 Update delivery is all modelling after a new actual update

      3 Final delivery is after a possible model audit and after final changes driven by client acceptance testing.