Intro to Financial Modelling - Part 1: Model definition and purpose

The code focuses on spreadsheet based financial models, recognising that other platforms are available, and many of the points made here apply to other software packages and types of models. 

For any given modelling task, it is critical to start by assessing whether a spreadsheet based financial model is the right tool for the job; to do that you need to understand what a financial model is and is not.  The Code offers the following definition of a financial model as: a time-based set of financial calculations within a spreadsheet workbook which aims to create a financial forecast based on one or more input set of variables. 

Note that this working definition is the focus of the Code and represents what we typically mean by a financial model.  It is by no means universal.  There are many other applications and uses of spreadsheets that would be described as financial models e.g. banks’ capital pricing tools, timetabling applications, and financial consolidation tools. 

The graphic below expands on the definition: 

It is perhaps difficult to describe a financial model to someone who’s not seen one before.  However, like the proverbial “Duck test” (if it looks like a duck, walks like a duck, and quacks like a duck…) there are some further practical/observable characteristics that can assist in working out when a spreadsheet becomes a financial model.  Some examples are shown in the table below. 

The characteristics shown above are not absolute, nor mutually exclusive, nor exhaustive. For example, multiple linked worksheets are not alone necessarily an indicator that a spreadsheet is a model.  The table is provided as a guide only, there will always be exceptions, for example in the banking sector there are numerous examples of financial models whose characteristics sit more readily in the second column (e.g. credit and capital pricing tools, valuation, option models) but which would be considered to be financial models by most practitioners.  Similarly, we have been presented with numerous so-called financial models that are simply crude budgets or static system extracts.

That said, experience shows that where a spreadsheet exhibits a number of the characteristics shown in the first column, then it is typically the case that the guidance in the Code is applicable.

Having satisfied yourself that a spreadsheet-based model is the right tool for the job, you then need to be clear on the purpose and planned use of the model.  In determining the model’s goals and designing its architecture, it will help to keep the following themes in mind:

• Clarity of purpose: be crystal clear on what the model is and isn’t for, spend time exploring the boundaries of the model’s purpose. Don’t try to do too much and meet too many (sometimes competing) requirements within one model. 
• Start with the end in mind: be clear on your audience’s needs and the output they will require.  In designing the model make sure that the information and data needed to populate the model and deliver those outputs is actually available and of appropriate quality.
• Design first but iterate: invest time thinking through the key modelling issues upfront, designing in anticipation of likely model use and evolution.  However, recognising that “no good plan survives enemy contact”, you will need to adapt and change the plan as new information or constraints emerge.
• Build for the long term: the model should be as future proof as possible, so that it is usable and serviceable over time.  This requires you to pay attention to the challenges of multiple users/owners; building the model in a modular, extendable way; and coping with evolving business needs.  A good modeller anticipates potential future changes and builds in an appropriate level of redundancy.
• Manage and control the development environment: version control and proliferation remain a major modelling risk and challenge. Where possible contain the model to one workbook and avoid linked files. If multiple input sets are needed, design the core model as analysis engine and use a scenario selector front end to avoid version proliferation (this is covered in some detail in the ICAEW CF Faculty’s guideline).

Spreadsheet based financial models are ubiquitous and of very variable quality.  If you are going to use one, you need to first be clear that it is the appropriate platform for the job in hand, and secondly be very clear on its purpose.

That clarity of purpose is absolutely key, a financial model typically has two core roles: to calculate a set of financial outcomes and to communicate those clearly to stakeholders.  In setting out to develop a financial model, do you understand what you are trying to calculate and are you clear on how best you can communicate the results to stakeholders?

If you can keep those questions in mind and follow the principles encapsulated throughout the Code, you will produce much better models, both in terms of integrity and relevance.

Next in series

Part 2: Layout & Structure - Laying out a model