DATE: Thu 31 Jan 2019
BY: Stephen Aldridge
Continuing with our breakdown of the Financial Modelling Code published by the ICAEW late last year; this time we look at ‘User interface and transparency’. There is some overlap with the last blog about ‘Layout and structure’ - both sections are largely about designing a model that makes it easy to avoid errors. In the process, your model will become user friendly.
Let’s dive in to this section of the Financial Modelling Code to see what it says on the subject:
Include a worksheet dedicated to being a user guide.
Some models, particularly long-life models in a corporate setting, may have many different users over time. How are these users to know where to start? As a model builder, you can make your models easier to use by addressing this question in your design. A good start point is to include a brief description of the purpose of the model and any key information the user should be aware of. The financial modelling code recommends including a user guide sheet in the model or embedding a separate user guide document in the workbook to avoid the user guide being separated from the model.
A key aim is to make a model intuitive to use. Hence I particularly like the third recommendation – “Add contextual user guidance throughout the model where appropriate”.
This can be done simply, by making the model ‘self documented’ ie include notes or descriptions in the appropriate places in the model so that a user naturally receives guidance as they need it.
If you want to go to town (and have the time or budget to do so), there are a number of techniques you can use to add “contextual user guidance”. These include:
If you didn’t read the last blog, take a look here – there is an example of a workflow that guides a user step by step through a process.
The code specifically mentions two types of duplication here; Inputs and Calculations.
Arguably the more important one is inputs – having duplicates for a single input assumption is potentially dangerous as users will not go looking for a second input if they think they have already changed it! You might think this is stating the obvious, but unless you are very familiar with a model, you may not know that it already has an input for inflation or bank base rate, for example. This becomes a greater risk when there is more than one person working on the development of a model and is a good reason for sticking to other best practice rules (such as consistency which we will deal with in a later blog) which make the location of some inputs more predictable (by adopting common conventions, good labelling and considered layouts).
Less dangerous (but not without risk) is the duplication of calculations. This is arguably more about efficiency than risk; it is more efficient to do a calculation once than multiple times. If it is needed in multiple locations, it makes sense to refer to the single instance of the calculation. The risk that could emerge from duplicating a calculation is that you don’t do it the same way, so you get two different results. Avoiding duplication in calculations is one reason why professional modellers often prefer to use INDEX and MATCH instead of VLOOKUP or HLOOKUP as the test is often the same for groups of cells within a range with the same formula.
One of the common uses for a financial model is to continuously forecast the future performance of a company, using the most recent balance sheet as a start point.
The financial modelling code provides some guidance here, recommending that we “don’t over-write forecast data with actuals on a rolling basis”. A better approach is to have two distinct input areas – one for forecast assumptions, and a second for inputs for historical results that should flow through to reports. Which values flow through to the reports is determined by the logic in the formulae, based on which time period the cell relates to.
If we track across the timeline in a report, at a certain point, the values switch from historical data to forecast – this date being determined by a single input for the forecast start date. The reports themselves should clearly show which periods are forecast and which are actual (ie historical).
The code specifically mentions dummy data: Generally, dummy data should only be included in a model during the development process. At this stage it is very helpful as it allows the developer to check the calculations they are building, ensuring values are the right sign, format (eg % or currency) and sense checking them as part of the build process. Once the model is built, dummy data is best removed and replaced by real assumptions or highlighted in some way to signify that the values need to be updated.
Models can contain a lot of ‘workings’ – these are important for the developer, but users do not necessarily need to get involved with the detail. it can be tempting therefore to hide certain sections of a model. This makes it a ‘black box’ (which, let’s face it, many computer programs are – you don’t see the workings, only the ‘user interface’). Why then do the ICAEW recommend that you don’t hide things?
Spreadsheet models are often criticised by IT professionals for lacking the rigour normally associated with computer programming. Unlike commercial software, which is addressing a widespread need for many users, many models are one-of-a- kind. Development and testing costs cannot therefore be spread across thousands or millions of customers, they are borne by just one customer. This is why best practice is so important – these techniques have been developed to avoid errors. However it is also important that the calculations are transparent and can be tested and checked. This is often a formal process – lenders will typically demand a model audit is performed (click to download our guide).. This will require that all calculations in the model are visible to the auditor.
We are drawing a distinction here between rows which are hidden (ie set to zero height) and those which are grouped and collapsed as shown in this screen shot.
Last time I talked about some other features that can enhance the user interface – Navigation and workflows for example.
The workflow shown last time helps users to remember the steps necessary to perform a set of tasks. Such workflows can be designed to walk through a series of steps to perform a particular task – in this case the month end update process.
It is fair to say that many other sections of the code provide valuable guidance to improve the ease of use and transparency of a model, so I won’t repeat them all here. If you haven’t already read the other blogs in this series you might like to register to receive the blogs in your inbox (click the button below).
Next time we’ll be looking at one of the most important features of best practice, Consistency.
Stephen is a Chartered Management Accountant and has over ten years of financial modelling experience both at KPMG and Deloitte. His early career included engineering, sales and corporate management roles. In 2004, Stephen joined Numeritas as a co-owner and a Managing Director.
We love to hear what you think. please note that comments are moderated so there might be a slight delay. Your email address will not be published.