Let’s dive in to this section of the Financial Modelling Code to see what it says on the subject:

INCLUDE USER GUIDANCE

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:

  • Messages that are driven by formulae to display in certain circumstances.
  • Data validation that provides choices of valid input values (e.g. a drop down list) or displays a message when a cell is selected. This can also display a message if invalid data is input.
  • Using conditional formatting: – cells that are not used in the current scenario (e.g. manual repayment inputs are not needed if you are using an annuity repayment profile) can be greyed out using conditional formatting. This makes it obvious to a user when an input will not have an impact.
  • Hyperlinks to other parts of the model that are associated with the current section.

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.

AVOID DUPLICATION

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.

IDENTIFY AND SEPARATE FORECAST OR DUMMY DATA

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.

DON’T HIDE THINGS

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.

OTHER IDEAS

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’ll find them in our News section.

Download our full guide to find out how we implement the Financial Modelling Code.

Download the guide