You can sign up to receive each blog in the series direct to your inbox and get a set of links for the entire series. You can also download the full guide to how Numeritas complies with the Code.
Also, if you haven’t already done so; download the code from the ICAEW here .
So – on to part two of Layout and Structure:
MAKE NAVIGATION SIMPLE
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?
LABELLING
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.
DESCRIPTION OF THE MODEL’S PURPOSE
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.
CONTENTS TABLE (WITH HYPERLINKS)
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:
LOGIC FLOW DIAGRAM
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.
WORKFLOWS (WITH HYPERLINKS)
If there are 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.
HIERARCHY OF HEADINGS
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.
Grouping and collapsing
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.
FREEZE PANES
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 timline. 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.
MAKE INPUTS EASY TO FIND
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.
DOWNLOAD OUR GUIDE
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 to download our complete guide.
Next time we will look at the next section of the Code, User Interface and transparency.
