Hello and welcome to the continuing 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 here or watch our free webinar about it.
This week's post is written by Alexander Carse. Alexander is a director at White Box Financial, a financial modelling consultancy focused on providing highly experienced consultants to solve particularly challenging modelling tasks. Prior to this Alexander worked as Head of Valuation for John Laing. He has been building financial models for a decade across all industries and worked on developing the Financial Modelling Code from its infancy through to its publication.
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”:
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 - 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.
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.
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.
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.
We will return next month with Part 4: Layout & Structure - Managing Inputs.