The ICAEW have published new guidance for financial modelling in spreadsheets – “The Financial Modelling Code”. This was launched last week and we’re delighted to see this reach a conclusion. This is the first of a series of blogs that will explore each of the key tenets of the Financial Modelling Code (or Code as I’ll refer to it for brevity).
The Code starts off with “Model definition and purpose” within which there are two key areas to consider:
1) Understand the scope of a financial model
2) Consider the goals of your model
This week we’ll explore the importance of these two points and how to approach them, but first let’s set the context;
Amongst the wide variety of ‘financial models’, the Code gives us a definition; this is the scope of the Code and the type of Financial Model it covers:
“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.”
There are many other definitions of what a “Financial Model” is, but the scope of the Code is restricted to this definition – a significant and important sub set of all the kinds of financial models one could define. This is the type of model we build at Numeritas for the most part and describes models used to support investment and strategic decisions in many organisations.
Understand the scope of a financial model
To start the journey of building a model, we need to decide what is in-scope. This may seem obvious, but the Code encourages us to consider first of all, whether a spreadsheet is the right tool for our needs; this decision deserves proper consideration.
Spreadsheets are ubiquitous in large part due to their flexibility and the familiarity of their grid structure for setting out calculations, so we often turn to them as our first choice for a huge range of needs. There is however an increasing number of alternatives, designed for specific tasks.
In parallel to the emergence of these applications, the scope of what Microsoft Excel can handle is continuously growing. Power Query is native in Excel and there is a standalone PowerBI desktop application from Microsoft. These are well worth exploring if you are more focussed on manipulating data rather than performing calculations. Excel is frequently used as a simple database; at some point a more ‘industrial’ scale database should be considered if your needs are more sophisticated. Applications that work with Excel are also increasing – our next webinar will be on ‘weaponising Excel’ – looking at the use of xOverTime, an Excel-enabled cloud database that helps business users collaborate and manage data to drive business decisions. This environment provides an enterprise grade wrapper around your Excel based business processes. Register for the webinar here
Determine the goals of your model
When embarking on any journey, you should start with your destination in mind. This is equally true of building a financial model; spending adequate time in the planning stage will save you wasted time later.
Firstly, think about who will want answers from the model? Assemble these people (your stakeholders) for a workshop to work through their requirements. This will give you a much better chance of building a model that complies with the ‘ICAEW’s 20 spreadsheet principles’ number 8; “Design for longevity” which is mentioned in this section of the Code. Prepare for such a meeting by listing questions to prompt your stakeholders to think through their requirements properly. A non-exhaustive list of such questions might include:
-
- Who will operate the model
- Who will input data into the model?
- What strategic direction is your company taking – what changes may be on the horizon?
- Which entity(ies) are within the scope of the model
- To what extent are you trying to simulate the operations of the business?
- What data will you be able to get and from whom?
- Where will the forecast assumptions come from?
- Do you want to input actual data on a regular (eg monthly) basis?
- Do you have banking covenants or similar that you need to comply with that should be in the model?
Striking the right balance between detail and ease of use is particularly tricky to get right. Remember that you are not building an accounting system – there is a great temptation when designing a model, to lean towards ‘spurious accuracy’. Always remember that when forecasting the future, the most likely outcome is that your assumptions will be wrong. The value of a model is in large part that it helps you to understand the relative risk of the options in front of you. Don’t build calculations that are more accurate than your assumptions can reasonably be expected to support.
Over time, there may be calls to ‘adapt’ an existing model to answer different questions than it was originally designed for. The more you can anticipate these up front, the better. These potential future requirements should influence your thinking about the structure of the model so that you could accommodate them later if necessary. Having said that, the more flexibility you build in to a model, the more complex it becomes. More complex models usually require more input assumptions and a greater level of understanding in order to use them. It’s a good idea to introduce some healthy challenge about whether a model is suitable to carry out more and more analysis.
If more analysis is definitely needed, consider whether your existing model is the right tool for the job, or whether a separate exercise should be undertaken. You need to weigh this with the Code’s recommendation that a model is contained in a single spreadsheet.
The Code also specifically mentions structuring that allows a user to extend the timeline of the model. There is a trick to doing this safely – rather than just copying and pasting the last column (ie last time period) to extend the timeline across the worksheets, insert columns before the last time period and then copy the formulae across from the column before the insertion, all the way to the end of the new timeline, overwriting the formula that is there. This technique ensures that formulae that sum the entire timeline continue to do so with the new extended timeline (assuming the rest of the model complies with best practice of course).
You can also download our guide to the Numeritas approach to complying with the Code.
Next week we’ll move on to Layout and structure.