See-Through Modelling. Dominic Robertson

has made me appreciate the importance of dealing with cash, from the working capital assumptions to the cash flow waterfall. PFI requires an organised cash-centric modelling solution with no duplication. All companies would do well to do the same – even though they may report along more accounting lines.

      Macro-economic indexation

      Macro-economic indexation has always caused confusion. I have built and rebuilt indices seeded by data from the Office for National Statistics (ONS) for many PFI projects and other businesses and have come to the conclusion that it does matter how indexation is modelled.

      See-Through Modelling adopts a simple and effective method of calculating forecast indexation taking into account the very latest actual data from the ONS. I really believe that this method helps shed light on an often misunderstood area of business life.

      PART 1: THEORY

      I am certain that a minimum of knowledge on the theory of modelling, finance, Excel and the computer is necessary to build a good model. Part 1 will also look at the FAST theory, which is in my view the most complete modelling standard available. My objective here is to give you the core theory of computer and business modelling.

      Part 1 is accordingly split into five chapters:

      1 Modelling theory

      2 Finance theory

      3 FAST theory

      4 Excel theory

      5 Computer theory.

      Chapter 1. Modelling theory

      Definition of modelling

      Business modelling includes a variety of different types of modelling. My interpretation of business models is that they are written in Excel with some use of Access and Visual Basic for Applications (VBA) and offer solutions to common business problems.

      Models can belong to one or more of the following categories:

       Financial modelling – the PFI model is a financial model and a financial model has a P&L (profit & loss), CF (cash flow) and BS (balance sheet) as main results

       Econometric modelling – the results vary but the main content is of an econometric nature including any or all of:

       elasticities to derive volume, supply or demand of goods or services

       regression analysis or other statistical means to forecast volume, supply or demand of goods or services

       Deterministic modelling – where the model derives one set of results

       Probabilistic modelling – where the model can derive a distribution of the set of results

       Simulation modelling – where the model can be run a number of times, while changing one or more variables across a pre-determined range, to derive a distribution of the set of results

       Operational modelling – where the model uses management accounting and other actuals to forecast and where the actual updating process happens at regular intervals

       Strategic financial modelling – where the model provides the company’s senior management with answers to the possible direction of the company’s future finances

       Budget financial modelling – where the model provides short-term detailed financial variance analysis by comparing actuals data to budget data.

      A short history of modelling

      Business modelling began roughly at the time that computers began to find their way into the business and finance workplace. Apparently Deutsche Bank in London had computers in 1987 and actually used them to provide numerical financial analysis.

      VisiCalc was the precursor to Lotus 123 and Excel, and had much the same basic grid structure. The initial idea was to provide an extremely flexible grid system rather than attempt to better answer specific questions such as company financial analysis.

      Initially the spreadsheet was used to sense check what a person had already calculated using other methods. Compare this to now where the spreadsheet is at the heart of all company finance and any transactions. The complexity of spreadsheets has grown also, largely driven by the technical advances in computing.

      Excel has become the leading spreadsheet software in the world. Excel is used to model financial, economic, scientific or other data. However, Excel is also the software of choice for presenting data that may be calculated in other more complex software.

      The four founding principles of modelling

      In my view there are four defining principles of modelling. All modellers would do well to ask themselves if their models abide by these principles.

      The four founding principles of modelling are:

      1. A model is a model, not reality

      The word model actually means a representation of reality that is normally smaller than the original. This is the essence of a model. Modellers, especially financial modellers, must remember that a model must stop modelling reality at some sensible and optimum point. This is the art of modelling. Just because it can be imagined it does not mean that it should be modelled.

      2. A model must be as simple and clear as possible

      This sounds obvious but most models do not achieve this goal.

      3. A model must answer the commercial needs of the user

      The modeller must not get carried away with the exciting technicalities of modelling, but rather remember that the ultimate objective is to answer the commercial questions of the user.

      4. As above so below

      The model must be see-through to all levels of the organisational structure.

      The modelling mind-set

      Models can look and feel complicated. To build trust in models I propose a three-step process. It boils down to concept, application and maintenance.

      These three steps help modellers know, firstly, what state the Excel model is in, secondly, how to make it work properly and thirdly how to maintain this performance. This book will give you the tools to achieve a greater level of trust in your models.

      The body of modelling theory

      In order to describe the important facts about modelling I like to think of the body of modelling theory. The body of modelling theory is divided into five distinct classes:

      1 Structure theory – the necessary structure of the model

      2 Content theory – the logic within the model structure

      3 Control theory – the controls that govern the behaviour of the model

      4 Testing theory – checking and testing the model

      5 Building theory – building the model.

      Class 1: Model structure theory

      The model needs a structure to give it shape. The structure of a model is about organisation, hierarchy and sheet layout, the flow of information and the type of links that best connect the model. These topics are dealt with here, culminating in a discussion of the tree analogy, a visual way of looking at model structure.

      Organisation