Workbook Structure and Hyperlinks for Financial Models
13 February 2020 | Written by Jennifer Harrison and Lance Rubin
Introduction to the co-author
Jennifer Harrison is a partner at Reputation Edge, a boutique corporate consulting firm based in North Sydney. She has expertise in financial services and technology.
Jennifer is a trusted advisor with a deep understanding of corporate finance, strategy and risk, and is a recognised expert in shareholder value creation. “I think of reputation as an asset, it’s not on your balance sheet, but it is in the market. It’s a source of risk and return and it delivers value for your stakeholders.”
Jennifer enjoys bringing the discipline of finance into non-financial decisions. She works with clients on a wide range of assignments including strategy, innovation, growth, partnerships, capital raising and communications.
Jennifer is also an engagement facilitator and sessional academic at Macquarie Business School. She is the lead facilitator of Finance for Non-Finance Managers a 2-day short course and she is the co-convenor of Contemporary Issues in Applied Finance, a new elective being offered in 2020 to postgraduate students.
Why did Jennifer select the topic and why is she passionate about it?
Before becoming a consultant, Jennifer was a corporate and investment banker for almost 20 years. Despite having a reputation for being highly technical, she was never taught how to model.
“I remember being a graduate and being requested by my manager to prepare a cash flow model of an oil and gas exploration company. I had no clue where to start. Well-meaning colleagues tried to help by emailing me finished models.
So then I had a library of models but they were for a whole bunch of different industries and different purposes.
The expectation was that by looking at these models, I could somehow learn though visual inspection how to build a model for myself from scratch.
It was a truly horrible experience. I almost quit.
If I had known then, what I know now, I would have had a much happier experience.
What I really needed was an understanding of the basic levels of functional logic (e.g. Inputs, Workings, Outputs) that you find in every good model.”
Over time, Jennifer learned to model incrementally through a process of trial and error. It was not until many years later that she truly mastered the skill, thanks to an Oprah moment.
“In 2005, I started a side hustle as a freelance trainer working on London City and Wall Street bootcamps for graduate investment bankers. I got to see how they trained their graduates in financial modelling.
They started with the absolute basics and progressively stepped it up. It was totally hands-on in Excel. You simply cannot learn financial modelling by watching someone else do it.
I realised that a big problem in expecting people to self-learn financial modelling is that too often they are thrown in the deep end with a model that is way too complex. You have to crawl before you walk, and you have to walk before you can run.
In this way, you will also appreciate that most modelling is just variations on a theme. Once you have a solid foundation, you can progress from there.”
Topic and context in a bit more detail
Jennifer’s Oprah moment was that even the messiest and most complex of models have an underlying functional core logic that holds the model together. Normally this functional logic goes as follows:
Inputs (raw data and assumptions going in – mostly forward looking);
Workings (breaking down complex items for transparency; using a line by line build-up of logic/calculations rather than having it all in one big long complicated messy formula);
Outputs (the raw answers coming out);
Analysis (crunch the numerical outputs to get some perspective on trends and ratios);
Application (is there a deal to be done here? E.g. Can we raise debt / IPO / buy or sell the company? Will the company breach its debt covenants?)
Now it’s super nice if this logic is self-evident and even nicer if the layout/structure of the workbook (and each individual worksheet) works with this logic not against it.
Sadly, this is not often the case and Excel does not prevent the mess that is often created. Excel is a very clever calculator that can cope no matter which way the logic is laid out (horizontal, vertical, left to right or vice versa).
Once you understand the flow of a model, it’s like driving a car. In the beginning some of the tasks of driving are not so easy, but over time once you get the hang of it, certain things become automatic and you know what to look for and where.
Jennifer’s Toyota has a steering wheel, a brake pedal and an accelerator. Every car has these in pretty much the same place.
Her Toyota also has a stick gear shift and a foot brake. Other cars may have a column shift and a hand brake.
So occasionally something is located in a different place, and it might take you a minute to recognise and find it, but you know it has to be there along with its function in helping you drive the car. The logic of the steering wheel does not need to be explained to you again each time you get into the car.
So, in much the same way when you use or inspect a model, you would expect to find similar functional logic areas as mentioned above. If you can drive one model, you should be able to drive another one, provided it has followed similar structural elements.
If you have had the benefit of learning how to build a financial model from a totally blank sheet in Excel, you will understand that models are not created in a linear fashion.
That’s not obvious to someone who has never seen a model built step-by-step.
You can totally be forgiven for thinking you start at the top of the first worksheet in cell A1 and work your way to the bottom, and just keep going left to right and top down through all the tabs.
The reality is that most models are built in a spiralling fashion, with formulas jumping from one worksheet to another.
In a typical three-statement or 3-way model (containing an Income Statement, Balance Sheet and Cash Flow) for general investment banking purposes, the layout of the tabs could be expected to be roughly the following going from left to right in the model:
Cover Sheet;
Inputs;
Workings;
Income Statement (Output);
Balance Sheet (Output);
Cash Flow Statement (Output);
Ratios (Analysis);
Debt Capacity and Amortisation Schedules (Application);
Discounted Cash Flow (DCF) Valuation (Application);
Leverage Buy Out (LBO) Valuation (Application).
The names of the tabs would be abbreviated to be nice and short as this avoids having huge formulas created in Excel when working with off sheet references. So the actual names of the tabs would be as follows:
Cover;
Inputs;
Workings;
IS;
BS;
CFS;
Ratios;
Debt;
DCF
LBO
Another handy tip to help the user and modeller move quickly and efficiently from one sheet to another, use keyboard shortcuts, not the mouse:
Ctrl + PgDn to go right ->
Ctrl + PgUp to go left <-
Speaking of navigation, hyperlinks are a great tool to use.
Just like you navigate a website (which has no worksheets) you click the links within the page to drill down to more information and jump back to the home page.
Having these links (called hyperlinks) in an Excel workbook can be useful to help the drivers of your model navigate through it easily and quickly.
If the hyperlinks are all located on 1 page, often referred to as a table of contents like Word documents and PDFs, make this navigation even easier.
Below is an example of these hyperlinks within a Table of Contents.
Jennifer enjoys bringing the discipline of finance into non-financial decisions. She works with clients on a wide range of assignments including strategy, innovation, growth, partnerships, capital raising and communications.
Jennifer is also an engagement facilitator and sessional academic at Macquarie Business School. She is the lead facilitator of Finance for Non-Finance Managers a 2-day short course and she is the co-convenor of Contemporary Issues in Applied Finance, a new elective being offered in 2020 to postgraduate students.
Why did Jennifer select the topic and why is she passionate about it?
Before becoming a consultant, Jennifer was a corporate and investment banker for almost 20 years. Despite having a reputation for being highly technical, she was never taught how to model.
“I remember being a graduate and being requested by my manager to prepare a cash flow model of an oil and gas exploration company. I had no clue where to start. Well-meaning colleagues tried to help by emailing me finished models.
So then I had a library of models but they were for a whole bunch of different industries and different purposes.
The expectation was that by looking at these models, I could somehow learn though visual inspection how to build a model for myself from scratch.
It was a truly horrible experience. I almost quit.
If I had known then, what I know now, I would have had a much happier experience.
What I really needed was an understanding of the basic levels of functional logic (e.g. Inputs, Workings, Outputs) that you find in every good model.”
Over time, Jennifer learned to model incrementally through a process of trial and error. It was not until many years later that she truly mastered the skill, thanks to an Oprah moment.
“In 2005, I started a side hustle as a freelance trainer working on London City and Wall Street bootcamps for graduate investment bankers. I got to see how they trained their graduates in financial modelling.
They started with the absolute basics and progressively stepped it up. It was totally hands-on in Excel. You simply cannot learn financial modelling by watching someone else do it.
I realised that a big problem in expecting people to self-learn financial modelling is that too often they are thrown in the deep end with a model that is way too complex. You have to crawl before you walk, and you have to walk before you can run.
In this way, you will also appreciate that most modelling is just variations on a theme. Once you have a solid foundation, you can progress from there.”
Topic and context in a bit more detail
Jennifer’s Oprah moment was that even the messiest and most complex of models have an underlying functional core logic that holds the model together. Normally this functional logic goes as follows:
Inputs (raw data and assumptions going in – mostly forward looking);
Workings (breaking down complex items for transparency; using a line by line build-up of logic/calculations rather than having it all in one big long complicated messy formula);
Outputs (the raw answers coming out);
Analysis (crunch the numerical outputs to get some perspective on trends and ratios);
Application (is there a deal to be done here? E.g. Can we raise debt / IPO / buy or sell the company? Will the company breach its debt covenants?)
Now it’s super nice if this logic is self-evident and even nicer if the layout/structure of the workbook (and each individual worksheet) works with this logic not against it.
Sadly, this is not often the case and Excel does not prevent the mess that is often created. Excel is a very clever calculator that can cope no matter which way the logic is laid out (horizontal, vertical, left to right or vice versa).
Once you understand the flow of a model, it’s like driving a car. In the beginning some of the tasks of driving are not so easy, but over time once you get the hang of it, certain things become automatic and you know what to look for and where.
Jennifer’s Toyota has a steering wheel, a brake pedal and an accelerator. Every car has these in pretty much the same place.
Her Toyota also has a stick gear shift and a foot brake. Other cars may have a column shift and a hand brake.
So occasionally something is located in a different place, and it might take you a minute to recognise and find it, but you know it has to be there along with its function in helping you drive the car. The logic of the steering wheel does not need to be explained to you again each time you get into the car.
So, in much the same way when you use or inspect a model, you would expect to find similar functional logic areas as mentioned above. If you can drive one model, you should be able to drive another one, provided it has followed similar structural elements.
If you have had the benefit of learning how to build a financial model from a totally blank sheet in Excel, you will understand that models are not created in a linear fashion.
That’s not obvious to someone who has never seen a model built step-by-step.
You can totally be forgiven for thinking you start at the top of the first worksheet in cell A1 and work your way to the bottom, and just keep going left to right and top down through all the tabs.
The reality is that most models are built in a spiralling fashion, with formulas jumping from one worksheet to another.
In a typical three-statement or 3-way model (containing an Income Statement, Balance Sheet and Cash Flow) for general investment banking purposes, the layout of the tabs could be expected to be roughly the following going from left to right in the model:
Cover Sheet;
Inputs;
Workings;
Income Statement (Output);
Balance Sheet (Output);
Cash Flow Statement (Output);
Ratios (Analysis);
Debt Capacity and Amortisation Schedules (Application);
Discounted Cash Flow (DCF) Valuation (Application);
Leverage Buy Out (LBO) Valuation (Application).
The names of the tabs would be abbreviated to be nice and short as this avoids having huge formulas created in Excel when working with off sheet references. So the actual names of the tabs would be as follows:
Cover;
Inputs;
Workings;
IS;
BS;
CFS;
Ratios;
Debt;
DCF
LBO
Another handy tip to help the user and modeller move quickly and efficiently from one sheet to another, use keyboard shortcuts, not the mouse:
Ctrl + PgDn to go right ->
Ctrl + PgUp to go left <-
Speaking of navigation, hyperlinks are a great tool to use.
Just like you navigate a website (which has no worksheets) you click the links within the page to drill down to more information and jump back to the home page.
Having these links (called hyperlinks) in an Excel workbook can be useful to help the drivers of your model navigate through it easily and quickly.
If the hyperlinks are all located on 1 page, often referred to as a table of contents like Word documents and PDFs, make this navigation even easier.
Below is an example of these hyperlinks within a Table of Contents.
Hyperlinks built automatically within a Modano model
Another tip on navigation is to sneak a clever little structure into Column A or B in your model.
Jennifer likes to put this structure into column B because column A is where she write notes to herself.
She learned this trick when she first started modelling. Back then it was simply not possible to lay out a model across multiple tabs; you had to build it all on one worksheet and often you ended up with quite a “skyscraper” or what’s also called a “deep sheet” model.
(OK Boomer … yawn … yes it was back in the days of Lotus 123 when every command started with the slash key / and you had to code a long string to print on that funny old computer paper).
Even today, some sheets might have over 1,000 rows making the task of quickly and efficiently navigating up and down the sheet quite challenging.
Using named hyperlinks (like salaries and wages assumptions) can also be used to quickly jump down to row 825 where you want to find some key assumptions relating to FTE and salaries.
So, here’s another clever tip.
Use Column B for headings but make it skinny (say <1.00) and then use Column C for line labels.
Only have blank cells in Column B between the headings and now you have a customised in-built structure you can easily navigate that will help you zip up and down your worksheet with speed and precision.
You use Ctrl + [Up Arrow] and Ctrl + [Down Arrow] to jump the blank spaces in Column B.
Use an X to mark the spot of any intermediate landing points and also at the bottom of the utilised range to avoid ending up at row 1,048,576 … but if you do then Ctrl + [Up Arrow] will get you back to the bottom of your utilised range or Ctrl + Home will take you all the way back to the top at Cell A1.
The below screenshot shows the idea.
Another useful navigation and structure trick that is helpful when writing formulas quickly and copying right (Ctrl + R) is to hide the columns to the right of the last column in the model.
Most models have a periodicity (monthly, annual etc) that ends. By hiding the unused columns in the model, you can quickly copy formulas or navigate to the end by using Ctrl [Right Arrow] in 1 click. More on that below.
Quickly and efficiently laying out the workbook for a clean sheet model
The first thing Jennifer does is save the file with a useful file naming convention and in the appropriate folder.
Then she plans how many sheets in total she expects to have in the model and adds a spare one. So in her Investment Banking (IB) model example above she had 10 sheets, and plus 1, makes 11.
She would double click on each tab to give it the short descriptor name she wanted and the one at the end is named “Spare”.
There are two methods to arrive at the same result of consistent formatting throughout the model:
Group the sheets and format them all together (e.g. skinny Column A & B); or
Set up one sheet and the copy it the requisite number of times.
Beware using the group sheet mode can be dangerous if you already have any data on the sheets because it will get overwritten.
However, when you are setting up a new workbook, that’s not a problem. The second method is fiddly when you have more than a handful of sheets.
Sometimes it can be helpful to combine both methods.
Excluding the Cover sheet (and the Log if you have one) put all the sheets into Group mode.
You can now format them all together for consistent look and feel, including the Spare.
In her experience, models generally always get added to; they rarely get retracted from. If you need to add an extra sheet, copy the Spare and rename it. (Don’t use the Spare because then you don’t have a Spare!)
Now size the columns and set up all the column headings.
If you want to hide the unused columns, just leave one skinny column after the final period (makes it much easier if you need to insert any extra rather than unhiding columns) and then you can hide the rest using Ctrl + Shift + ->Hide.
Alternatively, you can mark the last utilised column with a wall of X’s (as on individual sheets when you get there you can mark the last utilised row with the same).
We do this in order to make it easier to block copy formulas to the right (using Ctrl + Shift + ->, then let up on Ctrl and hit <- once, then Ctrl + R. This takes a bit of practice to get it in the muscle memory.)
You can now also set up any universally desired formats for numbers and text in the correct columns and use Freeze Panes (Alt W + F + F) to ensure the row and column titles, and any other necessary information, are always visible on the screen.
If you had to teach this topic in a class to school kids what key tips would you given them to focus on?
Ain’t nothing wrong with keeping it simple; and
Best practice principles never go out of style, especially Transparency
A simple and transparent model is easy to use, change and navigate.
You are much less likely to make mistakes in the first place, and if you do, then you are much more likely to pick them up yourself before any bad reputational consequences occur for you or your company.
What practical steps can people take now to learn more?
Adopt a “can do” mindset. Be confident. You can do this.
Practise! Modelling is a hands-on skill that you can only learn through practical experience by doing the following:
Review other models - To gain confidence, initially get hold of as many different models as you can and start to evaluate them for logic levels and flow. Where are the Inputs? Workings? Outputs? Analysis? Application? Can you track the underlying logical flow cell by cell through the model? (Note that it is very likely to spiral around; it’s unlikely to be linear.)
Use shortcut keys - One of my favourite keyboard shortcuts is Ctrl + [ (hold down Control and at the same time hit the left square bracket). I call this the “jump tool” because it will jump you to the first precedent in a formula. Often this is an off sheet reference and it really helps you to work your way backwards unpicking the logic of a model. You may also be able to use a toggle tool.
Start simply - Master a simple model first and work your way up. I am very happy to share an introductory level model that can be used if you are still on your L plates.
Enrol in a course- Another reason it’s hard to self-learn modelling is that when you’re stuck; you’re really stuck and it’s impossible to fake it (unless you hard code everything!) Modelling is a really hard skill to learn from a book or a video. Having an expert tutor available to guide you is invaluable, especially when you go wrong, because you need someone to diagnose the error and point out the required correction. Jennifer is associated with Macquarie Business School and I can recommend the 2-day short course Modelling Financial Statements run by James Hay or you can book a live conference finance tutorial session with me, Jennifer Harrison, through Kintell.com.
What are good places (links) to find out more on the topic?
I strongly recommend re-reading the other articles in this series which cover similar territory, e.g. Financial Model Development published on 16 January 2020 and User Friendliness in Financial Modelling published on 23 January 2020.
Hopefully readers can begin to see that whilst there might be variations on a theme – like driving a Volvo versus a Toyota - there is an underlying core of wisdom and functionality being explained by the authors that is not inconsistent.
If you are in Sydney, come along to the Sydney Financial Modelling CPA Discussion Group that you can find on Meetup.com. (You might bump into quite a few of the co-authors in this series 😉.
How important is this skill in the context of learning Financial Modelling?
It’s really helpful to be able to discern the underlying functional logic as the user of a model and it’s also necessary if you are going to edit someone else’s model.
It’s mandatory if you are setting up something from scratch.
If you can’t lay out your own workbook from scratch with confidence, you will be tempted to copy and paste someone else’s work. Please don’t do this!
It looks very tempting, but it almost always ends badly because of the unintended consequences of copying links, formulas with errors, VBA code and other people’s intellectual property just to name a few pitfalls … but that might have to be the topic of another blog!
How does all this disruption, AI and automation talk impact this topic?
Whilst there is technology that can certainly automate aspects of model building, understanding the basics and how to build a model from scratch with vanilla Excel is invaluable.
You learn a lot about the value drivers of a business when you build a model for it because you uncover connections and sensitivities.
Too often technology is used to build “black box” models and this denies the users the understanding of where the numbers come from. It also results in a dangerous knowledge gap when people use or edit models they don’t really understand. The reputational damage of doing this can be significant. If your business relies on numbers in financial models to make key business decisions, then you must be able to explain where they come from and why they are changing.
If you want to find out more and follow the rest of the article series be sure to download the Financial Modelling App
If you want to find more information on financial modelling and content visit the Model Citizn website.