See-Through Modelling. Dominic Robertson

these difficulties already mentioned, it is also important to recognise another major problem in modelling, which is that financial models tend to be managed by a single person. While this is best practice in terms of focused organisation it is also highly risky in terms of possible human error. Errors in spreadsheets are a well-reported phenomenon and here are some possible reasons why:

       lack of time to double-check analysis

       models create a bottleneck during analysis where only one person can perform the work – this is due to the possibility of important logical changes that need to occur in a single file

       only one person understands the complexities in a model

       managers have no detailed insight into the model logic

       the risk of human error, especially for the lone modeller, is high

       lack of good channels of communication.

      This problem of spreadsheet risk is very real and permeates a wide variety of very large companies and industries.

      The problem outlined here is thus financial, technical and human so the modelling solution should confront these same issues.

      The modelling solution

      Different approaches in the modelling market

      There are different approaches in the modelling market, varying from the excessively light to the excessively heavy solution. The light approach delivers a skeletal starting model where substantial addition and maintenance may be required over time to satisfy the long-term commercial objectives.

      The heavy approach delivers large models with unnecessary duplicate logic, effectively creating a greater need for maintenance, a lack of clarity and higher risk.

      The right approach provides the commercially required logical content from the start with the clear understanding that maintenance will always be required.

      The right approach

      The right approach to modelling is a holistic one taking into account all of the issues surrounding the modelling problem – financial, technical and human – and not just some of the financial and technical ones.

      The approach I propose in this book covers the following areas:

      Financial & legal – dealing with the theoretical financial, commercial and legal issues, and solutions:

       the project characteristics and legal entities involved

       the industry characteristics

       the macro economy

       the government

       strategic or operating solutions

       seeding the future with the past

       the relative importance of cash and accounting.

      Technical:

       using Excel

       using a computer

       using other software (the minimum required).

      Human:

       managing the build

       managing model control

       managing model delivery

       checking the model for errors.

      This approach is illustrated in Figure 1.

      Figure 1: The holistic modelling solution of See-Through Modelling

      Lessons learnt from PFI

      Use of Microsoft Excel for financial modelling

      Microsoft Excel has become the tool of choice for financial and business modelling, and the UK PFI has gone a long way to prove this. The 900 or so UK PFI projects have required meticulous planning, reporting and human interchange, and this has all been done in Microsoft Excel. This is not withstanding the availability of many other software solutions.

      As with most solutions Excel has its strengths and its weaknesses. First the strengths:

       Flexibility – Excel is a simple grid across as many sheets as the user needs with the power to add formulae in multiple cells.

       Grid structure – this is ideal for organised data such as a financial model.

       Ubiquity – all offices are now equipped with Excel so everyone will be able to open the file.

       Standardisation – with an increasing drive towards standardisation Excel is proving that it can perform better.

      Now the weaknesses:

       Flexibility – Excel allows just about anything the user wants in terms of content, formatting and structure which makes the software very error-prone and open to abuse.

       Grid structure – this can be rigid as the 2-dimensionality of each sheet precludes multi-dimensional modelling.

       Ubiquity – since everyone has Excel this means that experiments in new, more specialised, software are generally doomed to failure.

      In order to widen the market as much as possible the original designers of Excel began with the objective of making the software as flexible as possible. To this end they have succeeded as the Microsoft Office package, which includes Excel, is now quasi-ubiquitous amongst the business community worldwide.

      As a result of this flexibility, when using Excel for relatively complex tasks such as financial modelling there has been a need to impose some rigidity and structure on the software. The FAST modelling standard – outlined later in this book – does exactly that.

      Excel is currently the best solution for financial modelling.

      The strength of the operating model

      The financial statements of a strategic operating model contain the perfect mix of actuals plus forecast – past, present and future. This is crucially important for full and easy company reporting.

      Operating models have two sets of distinct inputs: actual historic values and future forecast assumptions. The combination of these two input sets produces results in the form of financial statements with a mix of actuals and forecast. This means that the forecast is being reassessed every time a new set of actuals is added to the operating model, providing the model with a firm foundation in reality.

      The vast number of UK PFI projects and the single-minded focus of these projects has opened our eyes to how useful an operating model is. To date, I have not come across a modelling project that would not benefit from a future seeded by the past – from debtor and creditor levels, to growth factors for individual revenue streams.

      The cash-centric approach of PFI

      UK PFI is part of project finance. In PFI, as in all project finance, the cash flow waterfall constrains and directs all the cash movements such as payments to suppliers, tax payments, bank debt service, funding of cash reserves and finally the rewarding of shareholders and investors.

      From conversations with project developers, bankers and investors it is clear that project finance is all about cash. Above all, everybody involved is asking the question: “How much cash is there?” This is the important question because if properly understood by the company directors and modellers