ICAEW.com works better with JavaScript enabled.

Excel Community | Financial modelling series

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

Author: Alexander Carse

Published: 19 Mar 2020

Hello and welcome to a new series from the Excel Community's Financial Modelling committee, in which we will work through the chapters of our Financial Modelling Code and explain how each element translates into practice.

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.

Overview

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

Directory - Finding the duck

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.

Illustration 1

Transit - Hyperlink is your best friend

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").

Transit – Give native shortcuts a boost

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.

Illustration 2

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.

Orientation – Freeze your panes

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.

Final thoughts

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

Excel community

This article is brought to you by the Excel Community where you can find additional extended articles and webinar recordings on a variety of Excel related topics. In addition to live training events, Excel Community members have access to a full suite of online training modules from Excel with Business.

Excel polaroid
Archive and Knowledge Base

This archive of Excel Community content from the ION platform will allow you to read the content of the articles but the functionality on the pages is limited. The ION search box, tags and navigation buttons on the archived pages will not work. Pages will load more slowly than a live website. You may be able to follow links to other articles but if this does not work, please return to the archive search. You can also search our Knowledge Base for access to all articles, new and archived, organised by topic.

Open AddCPD icon

Add Verified CPD Activity

Introducing AddCPD, a new way to record your CPD activities!

Log in to start using the AddCPD tool. Available only to ICAEW members.

Add this page to your CPD activity

Step 1 of 3
Download recorded
Download not recorded

Please download the related document if you wish to add this activity to your record

What time are you claiming for this activity?
Mandatory fields

Add this page to your CPD activity

Step 2 of 3
Mandatory field

Add activity to my record

Step 3 of 3
Mandatory field

Activity added

An error has occurred
Please try again

If the problem persists please contact our helpline on +44 (0)1908 248 250