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 the benefits are enormous.
PFI 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.
