7 steps to implementing the Financial Modelling Code

What is the Financial Modelling Code?

The Financial Modelling Code, launched in 2018 by the ICAEW was the first document that describes widely accepted principles of best practice for financial modelling.

You can download “The Financial Modelling Code” from www.icaew.com/financialmodelling

Below is a guide to implementing the Code, (after little background information). You can also download our approach to financial modelling as an example of how to comply with the Code.

 

Who decided what’s best practice?

There are some very prestigious firms who have been building and testing financial models for decades. Each has developed its own internal standard. By testing each other’s models, good ideas propagated around the industry. Some firms published their own approach and called it a standard, but there has been no universally accepted definition of best practice until now.

On 16th April 2015, I sent an email to leaders in the financial modelling community to start a conversation about best practice in financial modelling. Seven firms contributed their internal modelling methodologies which were aligned and examined for their similarities and differences. In the intervening period, over twenty firms have debated the output from this exercise and after three and a half years this has culminated in the publishing of the Financial Modelling Code by the ICAEW. I’m immensely grateful to the ICAEW for supporting the objective of improving financial modelling and for the work it has done in widening the consultation and converting our findings into the accessible guidance they have published.

 

Why do we need it?

Let’s be clear; the Financial Modelling Code is not a training manual. It won’t tell you everything you need to know to build a financial model. There are two main audiences that should find the Code useful; 1) procurers of modelling services and 2) ‘Modelling standard setters’ – those responsible for financial models in their business.

Whenever we buy something complex, our knowledge of the product or service is generally less than the supplier. However a professional buyer needs to ensure acceptable quality from the supplier, hence the need for standards.

Product standards typically specify in precise detail certain characteristics of a product, for example electrical standards define the shape, size and spacing of the pins in a UK plug and socket – this is necessary for them to fit each other. The problem with financial models is that they are ‘bespoke’ to a business. At some level, a model is a simulation of a business (or project / charity or other organisation) and consequently they are all different. Hence a very detailed standard of how to build models for many circumstances is impractical. The more features we specify, the more exceptions arise.

The Financial Modelling Code aims to address this by setting out universally accepted principles, with examples of encouraged approaches and discouraged approaches. It is not a hard and fast definition and there will be occasions where a professional modeller may need to deviate from some aspects of the Code. However, they should only do so with a knowledge of the risk involved and with appropriate controls and testing to mitigate the risk.

If you are responsible for financial modelling in your business, the Code provides a framework around which you can build an internal methodology appropriate for your business. The rest of this article is for you. I will provide specific steps you can take to implement the Financial Modelling Code in your business. If you would like to see an example, Numeritas has published a document showing how our models reflect each recommendation in the code. You can download it here.

 

Implementing the code:

 

1 What kind of modelling do you do?

Models vary in their objectives, outputs, structure, level of detail, source of assumptions and many more aspects.

Fortunately, most businesses specialise in a particular field, so your methodology need only address the particular needs of your businesses.

To start with, consider the following:

• Why do you build financial models?
• How frequently do you need to build models?
• What questions do you expect models to answer?
• How much ‘common ground’ is there between models you build (ie how similar are they)?
• Who will use the models? What skills do they have?

Armed with answers to the above, you can then move on to decide how to interpret the Financial Modelling Code to create your own methodology.

 

2 Design a model planning process

The financial Modelling Code predominantly deals with how a model is built and as such is good for procurers of modelling services to set expectations. If you wish to build models that comply with the Code, you will need to some processes and procedures around it for the essential stages of planning and later for data acquisition and testing.

There are three points in the Code that are most relevant here:

• Understand the scope of a financial model
• Determine the goals of your model
• Plan and assess the workbook layout

The first two are activities best carried out in a workshop session with relevant stakeholders. Your objective with these workshops is to come away with a specification for a model that is as complete as possible. To ensure success this needs forward planning- covered by the next step.

Planning and designing the workbook layout, whilst a pre-build activity is a critical one to get right. If you are able to engage the experienced modellers at any stage, this is where it makes the most difference. The last thing you want is to have a half developed model and then realise that you can’t achieve some of your objectives due to the way you have structured it.

 

3 Create checklists

Create checklists and templates to capture the output of workshop sessions – these should include high level questions such as:

• What is the frequency and length required for the forecast period?
• What entities are we modelling?

You also need to capture detail such as:

• What assumptions drive each line on the P&L and balance sheet
• What asset categories do we need to model
• What are the terms of any financing facilities

We’ll come to building the model in a moment, but other processes related activities should include standardisation of how you document your assumptions and data sources.

 

4 Design a testing policy

Research shows that testing models is a vital part of the process. There are tools available to help with this, including our own free tool, which you can download here: http://numeritas.co.uk/free-software. You can find out more about how to test in this webinar: test your own models.

Models are built by humans and an error rate of 2-5% is known to be typical. Without testing you are leaving yourself vulnerable to the errors lurking in your workbooks. For high value decisions, your policy may be that you get external assurance from a firm like Numeritas. In any case, before you rely on a model, you should ensure that appropriate testing has been performed.

 

5 Build a spreadsheet template

If you wish to build models that comply with the Code and which have a degree of consistency across your organisation, one of the most valuable investments you can make is to spend time building a spreadsheet template that will form the basis of all your models. Below are some of the main features that you can build into a template that satisfy elements of the Code and save time every time you create a new model:

 

Timeline

The timeline is the starting point for a model – you need to decide whether you standardise on a single ‘periodicity’ ie monthly, quarterly, semi-annual or annual. It may be that you will use a different level of detail for different types of analysis, so you may want to build more than one timeline, but here are some useful tips to building your timeline:

• Make timelines dynamic – all the timelines in a model should reference a single date input to determine the start date of the model.
• Keep different timelines separate – as the Code suggests, use different columns for different time periods.
• Build in useful standard timing flags – good examples are year-end flag, month counter, month in year counter, actual and forecast flags. Flags (more detail in the Code about flags – these are rows with 1/0 or TRUE / FALSE used to control logic concerned with the timing of events).

Standard reports

• If you use the same reports, KPIs or metrics to assess decisions, consider including these in your template. These will often include your standard management reporting lines.

Styles

Styles allow you to standardise cell formatting. Learn how here.

• As a minimum create a style for input assumptions that is unlocked (on the Protection tab of the Format Cells dialog).
• Other useful styles are subtotals and some number formats that you can easily apply.
• You may also want to set up standard styles that reflect your corporate branding – for example section headers.

Checks

• Set up standard checks using a standard format. If you design your checks to be zero when not triggered, conditional formatting can be used to highlight any errors that occur.
• Many firms have a master check at the top of every sheet, which refers to the ‘check of checks’.

User guide

• A sheet that is a placeholder for a user guide will help remind model builders that this is an important part of any model. it is a good place to include workflows that help users to perform routine tasks with the model.

Navigation

• When a user first encounters a model, it can be bewildering. An ‘index’ of the main sections of the model can help understand the model quickly. We like to use hyperlinks in the navigation sheet to allow users to jump to the section they are interested in.

Naming conventions

• Decide how you are going to name versions of your model. Some people like to include the date in reverse order to act as a natural sort. Master and minor version numbering can also be useful – for example “20181113_Model_V_01.01.xlsm”.
• If you use defined names (named ranges), a naming convention can make them easier to apply.
• If you use VBA, design a naming convention that identifies the data type eg str as a prefix for a string variable.

Sign convention

The Code recommends having a sign convention – choose one and stick to it. This is a way to avoid confusion and can help avoid easily made mistakes with serious consequences.

 

6 Document your methodology

Having designed your methodology, you will need to disseminate it throughout your organisation. This requires you to document the process and the standards that you expect.

Make sure the checklists, model template and guidance are accessible and known about. Numeritas uses methodgrid to store this kind of information.

Lastly you need to get everyone else on board – that brings me to my last point:

 

7 Training

Without training, none of the above will make a difference. As mentioned above, the Code is not intended as a training manual, so your job isn’t done yet. Your methodology and template will set your people on the right path, but building robust models is a specialised job and requires specific training, over and above what is learnt by trainee accountants. Invest some time in training yourself and your fellow modellers in the essential techniques for financial modelling. Several companies provide good quality training and some don’t – ask them how they comply with the Financial Modelling Code to make sure they know what they are talking about.

There are many free resources on our website that may also help you and we run regular free webinars and seminars in modelling best practice. If you want us to provide full training courses for your staff, we can do that too.

Remember to download our example describing how Numeritas addresses the Financial Modelling Code.