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

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.  You can read the Code here or watch our free webinar about it.

This week's post is written by Alistair Hynd.  Alistair is a partner at RSM where he leads the firm’s financial modelling and valuation service line. He is author of the ICAEW’s Corporate Finance Faculty financial modelling best practice guideline and chairs the its financial modelling sub-committee.

The development and use of financial models in Excel continues to proliferate, as does the variety of approaches and methodologies adopted.  Without a universally accepted set of agreed principles, it can be difficult to know what good looks like in financial modelling terms.  Recognising this, in Autumn 2018 the ICAEW launched the Financial Modelling Code to provide guidance to standard setters, procurers and modellers on good practice.  The Code sits alongside and is consistent with the ICAEW’s “Twenty Principles for Good Spreadsheet Practice”, which provides an overall framework for spreadsheet development aimed at reducing time, waste and errors arising from spreadsheet use.  The Code was shepherded to publication by a working party from the ICAEW’s Excel Community Advisory Committee, and credit is due to Stephen Aldridge for his formative work.

This was a landmark publication, representing broad cross-industry consensus from 13 organisations on the principles that financial modelling practitioners should strive to abide by.  And best practice really matters: financial loss from poor quality spreadsheet financial models has hit the headlines multiple times over the last decade.  If you have not read the Code, I highly commend it to you; this is not some theoretical discoursethe Code provides practical principles and guidance to help mitigate risk and cost.

A year on I and other contributors from the ICAEW’s Excel Community are launching a series of blogs revolving around the Code and aimed at digging a little deeper.  We will be providing insights and interpretation, examples and explanations, and practical tools to help readers get the most out of it and translate the principles into practice.

Starting at the beginning, the first chapter of the Code explores model definition and purpose.  It provides a working definition of financial modelling, and it underscores the importance of having a clear vision and plan as to what a given model is intended to achieve.  I expand on both themes in turn below. 

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 spreadsheet becomes 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 architectureit will help to keep the following themes in mind:

  • Clarity of purposebe 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 iterateinvest 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.

Part 2: Layout & Structure - Laying out a model

Anonymous