DATE: Mon 17 Dec 2018
BY: Stephen Aldridge
In the third blog in our series about the ICAEW Financial Modelling Code, we complete our look at Layout and Structure. Following our discussion of structure last time, this week we look at navigation and inputs.
Also, if you haven’t already done so; download the code from the ICAEW here .
So - on to part two of Layout and Structure:
Many models are complex. Many are large. If you are a model developer you probably get deeply engrossed in the models you build will become intimately familiar with their content. You know where each section of calculation is because you put it there.
If you are a user, faced with a new model, you are in an entirely different position. There is very little in the way of navigational aids natively built in to Excel. You may have some third party software (download our free tool here) that helps you to get a feel for structure or to examine individual formulae, but you are heavily reliant on the developer to have given you some clues, context and guidance.
So what can you as a developer do to make it easier for a user to find their way around a model and understand its content?
Be concise. Be precise. Be unambiguous.
A small plea; as a model auditor as well as a model builder, if there is one thing I wish developers would pay a little more attention to, it is labelling. A significant risk in modelling is caused by ‘pointing errors’ ie a reference to the wrong cell. These can commonly occur when you have similar items, and inadvertently point to the wrong one. This risk is amplified by inadequate labelling. For example If your inflated values aren’t labelled ‘nominal’ or ‘inflated’ you are more likely to inflate twice. I could bore you with more examples, but hopefully, you get the point.
This is often overlooked, but is a worthwhile inclusion – a brief description of the model and what it aims to achieve can be very helpful in setting the context.
This can be done quite simply, but it needs to be refreshed if significant changes are made to the model. **TIP** - beware when using standard hyperlinks – these do not update when the model structure changes, whereas if you use the HYPERLINK function, they do.
A link at the top of every sheet that takes you back to the contents table makes it two clicks to anywhere in the model – clicking the N in the top left of every Numeritas takes you to a contents table that looks like this:
If the logic of a model is not obvious or requires some explanation, you can incorporate a diagram into your model using text boxes and arrows (from the Excel Insert tab on the ribbon). These can also be hyperlinked to the appropriate parts of the model.
If there is a sequence of steps that need to be taken in a model, for example to update monthly figures, set up a workflow of steps, with hyperlinks to the input sections you need.
The example below has a few key steps needed to set up a new project in this template model:
In this example, the user can follow the hyperlinks to step through each of essential settings required to set up the model, marking their status as closed as it is completed.
In order to organise inputs and calculations, which can be extensive, it is helpful to subdivide them clearly. For example, we might have a number of loan facilities in a model. Under a main heading “Financing” we would then have each facility and within those, sections for the control account, interest calculations, repayment calculations etc. In this example at least 3 levels of heading is useful.
At Numeritas we like to have a header row for each section so that it is clear what each section of the model is designed to do. The heading styles are:
The visual clues of the density of colour or border indicate intuitively what level of title is being used.
You may have seen grouping in Excel workbooks – it appears as a ‘tree-view’ of plusses and minuses at the top and on the left of the Excel window. These relate to groups of columns or rows that are defined by the developer as having a common purpose (for example, part of the same calculation). Clicking on these expands or collapses these sections of the workbook, allowing easier navigation.
In the example below there are five levels and those below the second level are collapsed. Clicking on the plus signs opens up the row in the next level.
One of the simplest ways to assist with navigation is to set suitable freeze panes areas at the top and left of each Excel worksheet. It is common to have a timeline running across the top of the worksheet. The first few rows can be frozen (the button is on the View tab in the Windows group) so that they remain visible as you scroll down the worksheet. Similarly a few columns on the left can be frozen so that row titles remain visible as you scroll across the timeline. This enables you to always identify which time period and row title applies to the cells you are viewing in any part of the worksheet.
The user is mostly concerned with inputting assumptions and reading the outputs – in the form or reports, charts and summary tables. It is therefore important to set out the inputs in a way that the user will be able to find what they are looking for and provide a complete set of input assumptions for the scenarios they want to assess with the model.
If there is a particular order in which you should input data, it makes sense to lay out the inputs in that order, or alternatively to include a workflow (mentioned earlier). These are a great way to guide the user through setting up the model – some fundamental settings are usually required before other inputs will start to make sense, so I like to set these out as a workflow as described earlier.
Users are less familiar than the developer with the way the inputs are laid out. The user’s job can be made easier with a little thought about positioning of inputs, grouping together related input values and providing notes to explain anything that is not immediately obvious.
The simplest aid here is to use a consistent background colour for input cells. This is preferable to using font colours as this can’t be seen in an empty cell, whereas the background colour remains visible. Borders can also help identify related groups of input cells.
I hope that gives you some ideas for improving the accessibility of your models. To find out how Numeritas approaches each of the recommendations of the ICAEW’s Financial Modelling Code, click the button below to download our competed guide.
Next time we will look at the next section of the Code, User Interface and transparency.
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.