How to build a financial model
Like thousands of other analysts, accountants, bankers, students and other professionals, you’ve identified that you want to build financial models. It’s a smart move. Whether you are seeking a position or in a job that calls for relevant skills, financial modelling is seen increasingly as core.
At F1F9, we have been working with people like you to share our expertise. And this is the place to find the basics: the core design and construction skills and techniques you will need to build financial models that mark you out as a true professional.
10 things to remember
Keen to cut to the chase? Check out the essentials of what we want to share with you in under 4 minutes.
Adopt a financial modelling standard and stick to it
Some financial modellers will tell you that standards constrain creativity. Be suspicious of such statements.
Adopting a standard is a critical first step. And there are a few to choose from.
At F1F9, we have been working with the FAST Standard for years. We like it because once you master it you become a super-fast ninja modeller.
We also like it because it makes our financial models extremely easy to review. That’s because FAST eliminates the 3 major causes of poorly designed models:
- Lengthy formulas that are difficult to understand;
- Formulas that refer to cells far away from the calculation; and
- Links to links (known as “daisy chains”). Daisy chains are a major source of #REF errors in financial models.
Adopt the FAST Standard and stick to it
Developed over many years, FAST is a shared modelling language based on a series of trademark characteristics that promote transparency, efficiency of implementation, ease of navigation and maintenance.
The FAST Standard is maintained and developed by the FAST Standard Organisation – a not-for-profit institution.
Financial models must be built under short lead times, free from important errors, easily understood and changed without difficulty. A FAST model is:
- Flexible – easily adapted when new information is made available
- Appropriate – the level of detail is matched to the business assumptions and commercial logic
- Structured – model integrity is preserved through consistency in design and construction
- Transparent – calculations are simple and clear
Get into the right frame of mind
When you build a financial model, you are building it for someone else. This is the most important single change in mindset you need.
Too many financial models that we see are difficult to read, difficult to understand, difficult to change and just plain difficult to use. That’s because they are built with little regard for anyone other than the modeller.
Your model should be transparent – which means it is easy to navigate and easy to review.
The good news is that it’s not hard to create models that are readable and usable, but the techniques are also not obvious.
The way you make a model useful and readable is to keep it simple. It’s important to understand that the complexity of the transaction you model and the complexity of the model itself are two different things.
The businesses and projects that you’re modelling are financially and commercially complex. Done properly, the model is a valuable tool to help us make sense of that complexity. If you build a model that is complex and poorly structured, it can’t do its job of helping you to understand more about the reality.
Get good at typing / wean yourself off the mouse
Getting good at a motor skill like typing, even if it’s typing in Excel, requires you to be disciplined enough to do things the same way each time.
It sounds simple. But being consistent and applying rigorous discipline to your modelling isn’t easy to do.
There are some things you can do, though, to make some quick progress. The first is to be disciplined about using the keyboard rather than the mouse. If you focus on learning Excel keystrokes, you’ll simply be faster at the job and make fewer mistakes.
Your keys on the keyboard are always in the same place and over time you’ll forget about operating the machine and focus on what it is that you want to do. This will only happen if you do things the same way each time so they become habitual.
That’s the bit that takes time and practice and that’s not easy.
Work with columns consistently
A column in a worksheet should have a specific purpose. And every purpose should have its own column.
And a column on one worksheet should have the same purpose as the same column on every other worksheet in the model.
As a minimum, you should have dedicated columns for the following:
- Row labels (rows are also known as “line items”)
- Constants (a number – either hardcoded or calculated by the model – that’s independent of any timing logic)
- Unit labels
- Row totals
- Horizontal timelines (our “time-based columns”)
Write formulas so they can be read easily
Put spaces in between the arguments of your formulas. Why? It makes formulas easier for others to read.
If you are writing a time-based formula, then design a single formula in the first time-based column that can be copied across.
Keep formulas short. Consider the rule of thumb: make your formula no longer than your thumb. It’s a useful rough benchmark in a world where lots of modellers think long formulas mean that they are being sophisticated and clever.
Leonardo Da Vinci: “Simplicity is the ultimate sophistication“. It’s hard to design a model that’s simple for others to understand.
Include row totals for line items
Row totals are for presentation only. They should be on the left where you can see them. It astonishes us to see lots of models with row totals on the right of the time-based data – completely out of view.
Avoid using row totals to drive other calculations. If they are for presentation only we don’t want other parts of the model relying on them being there.
Use them for flows only, for example revenue flowing into the business or expenses flowing out of the business. Don’t use them to add up balances (where the row total makes no sense).
Make sure your formulas are consistent across the columns
When writing formulas to produce a series of data across a timeline, start in the first column of the timeline and copy all the way across. Avoid:
- Inconsistent formulas; and
- Missing out sections and then hacking something in at different points across the timeline.
It’s important to have assurance that a formula in the first column of the timeline is the same all the way across. If we ensure formula consistency, when somebody looks at your model they only need check that first column.
In a FAST model, only two columns matter: the constant column and the first column of a timeline series.
Use quick charts to step back from the detail
Throwing up a quick chart of line items is something that we recommend you do regularly. It’s going to give you a much stronger sense of what’s going on in your model.
Once you’ve highlighted the data you want to review, try:
- F11: to throw up a quick chart; or
- Alt + F1: to see the chart as an object on your worksheet
Charting is great for spotting trends in the data. When you hit your F11 quick chart, you want to be particularly on the look-out for discontinuities in the data. Look out for:
- spikes in the data
- changes in slope
- a sudden step-up or step-down
Make sure you have commercial explanations for those discontinuities.
Include heading columns to add signposts
F1F9 models are set up with a minimum of three heading columns. In our template model you will find those in columns A to C.
There are three reasons to include heading columns:
- Visual: headings act as important signposts. The indentation of headings indicates main section headings, sub-section headings, and sub-sub section headings;
- Navigation: jump from section to section using the headings – and the short-cut keys Ctrl + up arrow (to move up) and Ctrl + down arrow (to move down);
-
Ease of formatting: by highlighting the whole column you can change the formatting of every cell in that column – all at once.
Mark inputs clearly
Would having inputs scattered around the model be useful for users? No. When a model is finished, you’d expect to see the inputs on their own dedicated worksheets that have been properly organised and structured.
When we’re in the process of building a model, however, it can be useful to have the calculations and the inputs next to each other so that we can easily change the inputs to test the calculations. This saves us paging back and forth from the input sheet to the calculation sheets as we’re building our model.
But don’t start modelling by focusing on the inputs.
Start by focusing on the structure of outputs and work backwards from there. That way it is easy to identify only the inputs you need (based on the modelling you have just completed).
We recommend standard formatting for inputs. It always used to be the case that blue font was the standard formatting convention for inputs. This, however, is not appropriate for input marking for the simple reason that if you have no inputs in a cell, there’s nothing to show that it’s an input cell at all.
So we recommend a light yellow background shade and black font.
Format your financial model consistently
We have two objectives in formatting our models:
- Make the formats we use consistent between modellers and between models; and
- Make the process of applying formatting easy, so that we can do it as we go along and don’t have to come back to it later.
This is best achieved through additional keyboard shortcuts not available by default in Excel.
Download the F1F9 keyboard macros and take a bit of time to get things set up. We suggest that you play around with the keystrokes (the PDF guide is linked below) to get used to what they do within your models.
Work with calculation blocks
Most modellers think of a calculation as being a formula. Modellers following the FAST Standard will build calculations in blocks. The calculation block is a core element of the FAST approach to modelling.
Calculation blocks aid readability because everything required for the calculation – including labels, units, row totals and data – is close by.
In a calculation block, each ingredient is positioned next to the calculation itself. The formula then refers to the ingredients.
In order to display the ingredients, we create links.
Calculation block rules:
- one calculation per block;
- the calculation is the last item in the block;
- the formula should only refer to ingredients in the calculation block.
Navigate through your model using links
Ingredients in a calculation block are called up as links. Links may be used for navigation. In any column of a link that we have created, we can hop to the source of that link using Ctrl + [ .
Using Ctrl + [ on a link will take you back to the source of any given link even if it’s worksheets away. To return from where we jumped, use F5 + Enter.
So calculation blocks and their links have two more benefits:
- You have the precedents to the calculation right next to the calculation itself (avoiding all the hunting around the model when we want to understand what the calculation is doing); and
- When we do want to understand what’s going on with a given ingredient and where it’s coming from, we can get ourselves straight to the source.
Avoid daisy chains / links to links
Links to links, known as daisy chains, should be avoided in your models for two main reasons:
- From a navigational perspective, if you’re using Ctrl + [ to navigate using links, the return routine that you access with F5 + Enter only remembers one position. If you had to follow a chain of links to get back to a source, you would only be able to return to the last link in the chain – not very useful; and
- A more serious problem occurs when you try to delete sections from your model. A deleted section with daisy chains will most likely generate #REF errors elsewhere in the model.
Apply row anchoring when establishing links to calculations
A cell reference (“A1”) may be row anchored using the dollar sign: “A$1”. This means that the cell reference, when copied and pasted, will continue to point to row number one.
On calling up ingredients for a calculation block, we recommend:
- Linking to the label;
- Row anchoring the link (best achieved by hitting the F4 key twice); and
- Copying the link across the columns.
If you always do that, your link will itself be available to copy and paste when you need a duplicate of that link.
Copy calculation blocks to improve your productivity
It’s easy to copy calculation blocks and then replace ingredients as required. For example, if you’ve spent some time modelling Tranche A of debt and Tranche B has the same structure, you can just copy the calculation blocks for Tranche A and replace the ingredients to set up Tranche B.
You can then replace the ingredients simply by overwriting the existing links with new links.
Remember to change the label on the calculation.
Use minimal anchoring in your cell references
For calculation blocks to be available to copy and paste, they need a minimum of anchoring in the calculation’s formula. You want to avoid row anchoring in your formula so a copied block will refer to the new ingredients in the new block.
Constants should be column-anchored only (tap the F4 key three times).
Use placeholders to mark temporary code
As a financial modeller, you don’t always have all the information you need for a given section that you are working on. So it’s important to be able to mark temporary solutions.
Placeholders denote work that you are going to back to. They mark work-in-progress.
It’s important that you distinguish placeholders from finished work. We recommend:
- Visual marking: mark the whole line item in bright yellow;
- Square brackets: put square brackets around the label (this is a trick we have borrowed from lawyers when drafting contracts; and
- Adding a comment (using the keyboard shortcut: Shift + F2)
Apply these markings to all temporary line items and you will be confident that you know what is left to be done.
Answer the question “when?” with flags
For every line item that we model, we should be able to answer:
- How much?
- When?
Separate the “when” logic from the “how much”. Do that with a dedicated Time sheet and flags.
A flag is a data series consisting of “0” and “1”. A number multiplied by a flag will either return “0” or the number itself. So flags can be used to show numbers only when the timing is right.
Mark imported links and exported calculations
The complexity of the average financial model goes beyond what any one person can keep track of in their head.
So when we collaborate with others, we want to make it easy for them to understand our models: it helps if we put as much intelligence as possible into the model itself. Showing clearly how each of the worksheets in the model is interacting with other worksheets contributes to that intelligence.
Links imported from other worksheets can be copied anywhere in the model, but same worksheet links can only be copied and pasted on the same sheet. This is because links imported from other worksheets have a worksheet reference, while same worksheet links don’t.
Marking calculations linked to other worksheets is important because:
- they tend to be significant line items on the sheet and the immediate visual identification of that is useful; and
- deletion of these line items is going to cause #REF errors elsewhere in the model that should be managed proactively.
Mark imported links in blue font; mark exported calculations in red font.
Model balances using a standard structure
There are two basic types of line item in our model:
- flow – an amount of value passing in and out of the business within a period; and
- balance – the amount of value present at a point in time
All balances have the same three essential properties:
- upward flow – the flow that makes the balance go up
- download flow – the flow that makes the balance go down
- initial balance – at a point in time
Since all balances have a common structure, we can use the same modelling structure for all of them. This standard structure is called a “corkscrew“.
Keep a template worksheet handy in your model
Never introduce a blank worksheet to your model. Instead, keep a formatted template worksheet close by.
The template worksheet has two purposes:
- Storing template code such as the balance corkscrew. That’s for you to copy and paste when you need it; and
- Being a formatted worksheet that can be copied and pasted when you require new sheets in your model
Download our example model
If you haven’t already, download our example model to see how we construct models using all the guidance above.