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

Contents

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:

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

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

##### Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.