ICAEW.com works better with JavaScript enabled.

Excel Community | Financial Modelling series

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

Author: Alistair Hynd

Published: 23 Jan 2020

Hello and welcome to a new series from the Excel Community's Financial Modelling committee, in which we will work through the chapters of our Financial Modelling Code and explain how each element translates into practice.

Determining what constitutes a financial model

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: 

Determining what constitutes a financial model

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. 

Characteristic

Financial model

Other spreadsheet

Use

Forecasting, decision analysis and support

Reporting, presentation or review of financial data

Context 

Tend to reflect whole business / “Big picture” 

Tend to be “narrow beam” task focused

Lifecycle Either one-off transactional or enduring/rolling business forecast and planning tools  Vary from one-off ad hoc analysis to tactical tools supporting regular business reporting cycle 
Period/timeline under review  Multi-period problems and data Single or dual period analysis 
Formulae Uses more complex formula construction and Boolean logic, such as IF, AND, INDEX, MIN etc  Typically uses formulae no more complex than basic mathematical operators, SUM etc
Architecture Multiple linked worksheets Single worksheet or file
VBA Usually limited More frequent, automation of processes
Data Multiple data types Homogeneous, simple data

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.

Shaping the purpose of a financial model

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

Conclusion

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

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel
Topics