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