See-Through Modelling

The Technical Blueprint to Financial Modelling Using Lessons Learned from PFI

Dominic Robertson

• Publisher: Harriman House
• Year: 2013
• ISBN:
  9780857193148
• Edition: 1
• Topic: Business
• Subtopic: Accounting
• Index: Business

Building and maintaining effective financial modelsSee-Through Modelling provides a solid theoretical and practical basis for becoming an advanced financial modeller in Excel. It gives the theory and practical detail necessary to build and maintain a financial model yourself. This is done with particular reference to project finance and by drawing upon the lessons learned from UK PFI.In this book Dominic Robertson covers the key aspects of financial modelling, including:- Financial theory- Modelling theory- Excel theory and techniques- A step-by-step practical guide to building a project finance operating model- Computer set-up and efficient use- Keyboard skills- Macro-economic data collectionHe also includes key practical techniques such as how to:- Greater strategic vision due to vast forecast flexibility - Lower risk of modelling errors due to standardised modelling- Decrease reliance on individual analysts due to increased ease of model interchange- Clear, detailed and holistic modelling function training outlineLearning to build a UK PFI project finance model is an extremely good place to start to learn financial modelling. UK PFI is like the world in miniature with simplified operations and simplified finance but containing all the accounting and cash elements that make for a wide-ranging experience.See-Through Modelling is for finance directors who are looking for a deeper understanding of the dynamics of their enterprise and those who want to understand the benefits of adopting a see-through modelling strategy within their enterprise. It is also an invaluable resource for aspiring financial modellers in general and project finance modellers in particular.

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. Struc...

Table of contents

• Cover
• Publishing Details
• About the author
• Preface
• Introduction
• PART 1: THEORY
• PART 2: PRACTICE
• PART 3: FURTHER LESSONS
• APPENDICES
• Lazuli Solutions