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 discourse: the 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 a spreadsheet becomes a financial model. Some examples are shown in the table below.
Forecasting, decision analysis and support
Reporting, presentation or review of financial data
Tend to reflect whole business / “Big picture”
Tend to be “narrow beam” task focused
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
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
Multiple linked worksheets
Single worksheet or file
More frequent, automation of processes
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:
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