Intro to Financial Modelling - Part 3: Layout & Structure - Navigation

You can read the Code or watch our free webinar about it.

In this instalment of the blog series, I will be talking about the principles covered in page 5 of the Financial Modelling Code, “Make Navigation Simple”.

Navigation is a part of many financial models that is often overlooked. There are two main reasons for this which both fall into the category of “short term thinking”:

• The builder of the model often has a very good understanding of the elements the model contains and where each element can be found in the workbook, and neglects to put themselves in a user’s shoes.
• When time is tight to deliver a model it is the sections of the model that don’t directly help calculate the output that are often jettisoned (this is also the reason that most models are too light on checks, but we will cover that in a later blog).

Both reasons usually lead to the “saved” time being spent multiple times over in small amounts every time the model is used.

Model navigation can be broken into performing three functions:

• Directory - What is in the model and where is it in the model
• Transit - Making it easy for the user to move through the model
• Orientation – Where in the model am I now

We can think of a financial model like a book (an analogy that will also be used in part 4 of this blog series). Given a cookbook with no contents and no index and a want to find a duck recipe you are left to flick through the entire book for mention of your ingredient of fancy. This is how many users are left with financial models that lack navigation – flicking between sheets while scrolling and scrolling.

To solve this problem a directory page is recommended as part of the Financial Modelling Code which in simple models could look like a table of contents and like an index in larger models. I have seen a number of good examples of directory pages in models, but the example below is my favourite and the one I use. In the below if your duck in this situation is debtors you can quite quickly see “Working capital” under “Accounts” rather than scrolling through every sheet of the model.

The second type of Navigation is transit. Placing a navigation icon in B2 of the above screenshot on every sheet which links to the navigation sheet gives the powerful combination that all sections of the model are only two clicks away.

Beyond this, the hyperlink function is massively helpful and I like to think of what the BBC do in their articles as the best role model here. When reading an article and you reach a statement that is of interest and they have more content on they put a link right there to jump to that section or separate article.  In a model where you are reviewing the financial statements and revenue isn’t as you would expect, imagine how useful it would be to have link straight to the revenue inputs and to the revenue calculations. If you think it would be useful then others will too, so consider making it easy for them to find your additional content.

Tip: To use the hyperlink function to navigate to a cell in your workbook the formula should be built like this:

=HYPERLINK("#"&CELL("address",[CELL REFERENCE YOU WANT TO LINK TO]),"Text to display in linking cell").

Indent headings - Ctrl + ↓/↑

This is a further tip to avoid scrolling. When moving around a worksheet you often want to move from one section of the sheet to another, by scrolling you must work past all the content of each section and often end up flying past the section you were looking for. By indenting headings (as in the screenshot below) of each section then you can move to column A and press Ctrl+↓ to move straight to the top of the next section or move to column B and use the same shortcut to move to the next subsection. Little build effort required, little spreadsheet real estate taken up, big productivity boost.

Think a bit more about formulae - Ctrl + [

This next tip may seem like overkill or pedantic but trust me, implement it in your next model and see how much you thank yourself later.

When working your way through a model Ctrl+[ can be your best friend as you quickly navigate to precedents (this action jumps you to the first referenced cell in the formula of the active cell). Make the most of it by, functions permitting, making the first reference the most likely cell that the user would want to move to.

In the above screenshot the user is much more likely to want to navigate to “Widget sales revenue” as the other two inputs are much more self-explanatory. By placing this reference first we can keep using just Ctrl+[ to hop back through the logic of the model.

By far the best functionality in Excel for keeping the user orientated as to the context of the cells they are looking at is Freeze Panes. On picking up a new model it is incredibly frustrating to navigate through to the cell that you are interested in, only to find you can't see any labels to the left or a timeline to the top, you feel stranded. If the builder of your model hasn’t been considerate enough to apply freeze panes to each sheet, then save yourself some time in the long run by going through each sheet and adding them.

My rule of thumb of where to place them is directly under the timeline and directly to the left of the first calculation cell under the timeline. The above picture provides a useful example.

Always be thinking about the future users of the model. What will make it easier for them to find what they are looking for and get there. Put a navigation gift in your model now that they can open later.

Next in the series

Part 4: Layout & Structure - Managing Inputs