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
This 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
