Best Practice Formula Construction for Financial Modelling
21 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 moving into consulting in 2018, Jennifer was a corporate and investment banker. She started in the business as a graduate with Westpac in 1990. Whilst she was taught how to use the spreadsheet software, she was never taught how to model from first principles. They are two very different things, hence this series of articles.
Jennifer learned how to model on the job, patching together knowledge model by model.
In 2005, Jennifer started a side hustle as a freelance trainer working on London City and Wall Street bootcamps for graduate investment bankers. She got to see how they trained their graduates in financial modelling and they spent a lot of time on the basics, including best practice principles.
Now she is sure most of those grads thought that the best practice principles session was B.O.R.I.N.G … but they exist for a reason. They help you make decisions about matters such as formula construction.
Without the fundamental foundations of best practice principles things quickly get off the rails when it comes to building financial models.
Topic and context – Top Ten Tips for formula construction
One of the well-known financial modelling standards has a great acronym that is often used to benchmark models.
There is an article later in the series covering the financial modelling standards (in more detail), however for the purpose of this article we will use the FAST acronym (developed by F1F9).
Flexible, Appropriate, Structured, Transparent
Back to our formula construction tips which we feel are aligned to FAST.
Please note, this is purely Jennifer’s and Lance’s combined views and not those of F1F9.
1. No hard codes in formulas (Flexible)
This is a cardinal sin that most spreadsheeters commit without understanding why financial modellers think long and hard to write a formula that avoids it.
Everything must be driven from Inputs.
The Input can be a hard code. Then it is referenced (hit = and go and grab it) into the formula that produces the Output. If the number needs to change, you revise the Input and everything else just flows.
Even constants should be Inputs by which we mean things that don’t change like 100 cents in the dollar, 12 months in a year, or 365 days in a year (which actually does change for a leap year although this is often not going to make a material difference to Outputs). These must also be Inputs.
Even if it takes a while to find the right formula, it will save a huge amount of time later, for example, for scenario analysis like data tables. A hardcoding in a formula renders it completely inFlexible.
Plus you make the model incredibly time consuming to edit and make changes if you put hard codes inside formulas because you have to edit every individual formula to make the change.
The only exception to this rule is that the binary digits (0 and 1) may be used in a formula but this is usually only for the purposes of a toggle switch or a conditional logical statement, rather than the binary digits being an Input.
2. Consistent sign convention (Appropriate)
Choose a sign convention that is appropriate and stick to it.
Accountants have debits and credits. Modellers have positives (+) and negatives (-).
For the Balance Sheet, most modellers would present everything as a positive. But there are some folks in the highly leveraged end of town working on big infrastructure project finance models who will show Liabilities and Equity as negatives.
In this way, when the Balance Sheet is in balance, the sum of the entire column of Assets, Liabilities and Equity will be nil.
On the Income Statement, it’s six of one and half a dozen of the other whether you have Expenses as negatives or not.
However, on the Cash Flow Statement, you must have cash outflows as negatives.
What’s important is that the logic is easy to follow. Inconsistency is very confusing and leads to mistakes. Consistency is the golden rule.
You may set up Inputs as negatives to achieve the desired sign convention in the Outputs. This is Jennifer’s preference but some folks find that the Inputs become too busy and confusing with minus signs or brackets all over the place. In this case, you would have to add the negative sign convention when you code the formula. Back in the day, this was done by putting *-1 at the end of a formula. For example, =Inputs!E7*IS!E6*-1
The modern preference is to put a minus at the start of the formula, for example,
=-Inputs!E7*IS!E6. It’s less work, being up front it’s more visible (Transparent) and it’s easier to change (Flexible).
So this tip is not so much a question of whether an expense item is shown as a negative and your profit formulas are sums, or whether they are positive and the formula to calculate profit performs the subtraction, but what would the end user of the model expect and find most comfortable to work with? Plus avoid inconsistency which can cause an inAppropriate outcome.
When working with someone else’s model, follow their sign convention. Don’t re-work it to your preference; that’s rude and a waste of your time. Like driving on the left or right hand side of the road, you cannot force your preference on all the other road users, so you stick with what everyone else does!
3. Master-servant principle aka the rule against daisy chaining (Structure)
Always reference the master, not the servant.
Always refer back to the original reference, not a secondary reference, unless it becomes hugely onerous to do so.
In this way there is only one step between a formula and its precedents. This speeds up the calculations and the checking and auditing. If there are multiple steps between a formula and the cell that holds the master reference for the calculation, there is lots of empty referencing or “daisy chaining” and it drives experienced modellers mad!
For example: Cell J97 reads =F73. But Cell F73 reads =E44. But Cell E44 reads = B9 which is where the Date is entered! B9 is the master. All of those servant cells should reference the master, not each other.
4. Absolute and relative references – the dollar signs $$ (Appropriate)
Excel calculates in terms of the grid. It knows the relative position of a formula in the mix of Column letters and Row numbers (R1C1 reference).
If you copy and paste a formula from one cell to another cell, Excel will update that formula to the new location relative from the old location but making a relative change to the grid references of all the precedents that are in that formula.
This saves a lot of time and enables most models – driven from Inputs – to be built much more quickly by coding formulas for the first time period and then copying to the right (ideally using Ctrl+R).
Now this only works if the Inputs have also been laid out across the same time horizon, otherwise the formula will be referencing blanks and will either return 0.0 or an error.
Excel’s default is to treat all cell references as relative when the copy paste command is activated.
So a formula that references A1, when copied one row down and one column across, will now reference B2. If copied again one row down and one column across, the new formula will display a reference to C3. And so on.
(Note that the cut and paste command is different. The cut and paste command pastes exactly what was cut without updating the grid references.)
If you do have to work with an Input that is unique, in order to have the flexibility to copy that formula to a new location, you must make it an absolute reference. This anchors or fixes the grid references and they will not be changed relative to the new location.
A reference to $A$1 always remains as $A$1 when the formula containing this precedent is copied and pasted to a new location.
You achieve this grid anchoring with $ dollar sign and hence this is often known as “dollarizing” a cell reference which is a precedent in a formula. You can manually add the $ when coding or editing or use F4.
There are four possibilities in total and Excel gives them to you in this order when you repeatedly hit F4 to toggle through the choices: a) $ for the Column and $ for the Row b) $ for the Row only c) $ for the Column only and d) No $.
The $ must come immediately before the grid reference that you wish to anchor, so you may need to use the mouse or the arrow keys to achieve the precise location required for the placement of the $.
For example, if I want to anchor the column but not the row, =$C4. If I was to anchor the row but not the column, =C$4. If I want to anchor both, =$C$4, or neither =C4.
If it’s a cross-sheet (or off-sheet) reference, the $ come after the name of the sheet and still immediately before the grid letter or number.
=Workings!$G$43
As an alternative, you can Name the cell reference that you need to anchor and use the Name in the formula instead of the cell reference with the $$. Naming a cell has the same effect as dollarizing it when you use it in a formula.
For example, if cell C4 was an exchange rate, I could name it “FX” and then just code the name into the formula when multiplying or dividing by the exchange rate. This can also save time when the Input is on another sheet.
5. One row, one formula – no break in syntax (Structure and Flexible)
The syntax of the formula should be the same across the entire row for all time periods in that row. So you code a flexible and copyable formula once for the first period, and simply copy to the right (ideally using Ctrl + R) for all other periods.
Ideally the time series does not change in the same row which will allow the formula to be consistent. Excel does have an in-built default to check for this in “background settings” which you can find in File Options Formulas. This can detect “formulas inconsistent with other formulas in the region” and if detected small green triangles (the default colour) will appear in the top left corner of the cells involved. (If the cell has a Comment, this appears as a small red triangle in the top right corner of the cell.)
Too often there is a total annual column (for the 12 month periods to the left) in the middle of the monthly time series. Following this annual total column is the next year’s monthly periods and again another total annual column on the end.
This structure makes it very difficult (without creating really complex formulas to deal with the inconsistent time periods at the top) to keep the formula consistent.
Changing a formula in the same row will render it inFlexible as you trying to pull aggregate data from the worksheet you need to make sure you not reference the total columns not to mention the high degree of error risk.
6. The Rule of Thumb – avoid long formulas (Transparency)
Don’t code a formula longer than your thumb!
Now given that the human thumb is not a standard length, occasionally this is called the rule of two-thirds e.g. if the formula takes up more than 2/3 of the formula bar, then it is too long.
It should only be exceptional circumstances when you cannot break it down into intermediate steps using Workings.
If you have to create longer formulas then make sure you create space inside the formula to make it easy for people to understand the formula. E.g. Use white space and brackets (see below).
This tip is a bit like your high school maths teacher telling you not just to write down the answer but to SHOW ALL WORKINGS.
Imagine if the Income Statement was just one row for Net Profit After Tax (NPAT). It could be done, right? You could just do one big long formula to calculate the Revenues and then subtract all the Expenses. Whilst the answer might be correct, this misses the point.
All those lines in between need to be seen (Transparent) because they are important for informational and decision making purposes. As an example it would be a lot of extra work to calculate the gross and operating margins if you simply did the NPAT calculation on 1 row.
In our experience, big long complicated formulas rarely save you time in the long run and they introduce a high risk of error.
Rows are free in Excel … and mistakes are expensive. 😒
If calculations are laid out really clearly, line by line, you have Transparency and it’s much more likely you will get it right the first time. If you do get it wrong, it’s much easier to spot errors yourself, or for an associate to spot, before it goes to the boss or the client!
Can you see the mistakes in this formula?
=-IF(‘Debt Schedule’!E9<0.05,0,IF(('Cash Flow Statement'!E18+'Cash Flow Statement'!E25+'Cash Flow Statement'!E28+'Cash Flow Statement'! E29+'Cash Flow Statement'!E30+'Cash Flow Statement'!E32)<0,0, IF(('Cash Flow Statement'!E18+'Cash Flow Statement'!E25+'Cash Flow Statement'!E28+'Cash Flow Statement'!E29+'Cash Flow Statement'!E30+'Cash Flow Statement'!E32+'Cash Flow Statement'!E40-2000)<'Debt Schedule'!E9, ('Cash Flow Statement'!E18+'Cash Flow Statement'!E25+'Cash Flow Statement'!E28+'Cash Flow Statement'!E29+'Cash Flow Statement'!E30+'Cash Flow Statement‘! E32+'Cash Flow Statement'!E40-2000),'Debt Schedule'!E9)))
Whenever it’s possible, you must break down big long formulas into shorter ones.
7. White space, brackets and soft returns if formulas need to be longer (Transparent)
As mentioned in the thumb rule above, there are some instances where certain calculations just have too many conditions attached to them and this would be best suited attached to the formula which extends beyond the norm.
Creating white space or even additional lines in the formula would be useful.
=(1+Inputs!E6)*IS!D6
= (1 + Inputs!E6) * IS!D6
To create and additional row in the formula bar (like a soft return in word processing) you simply type Alt + Enter
If in doubt, or if just helps you to think by keeping like with like, you can put extra brackets into formulas, even if they are not mathematically necessary.
These visual improvements do not impact on the mathematical calculations.
You can see an example of this being applied below where you want to break out a mixed cell (contains text and formulas) with an IF statement into its component parts.
If the formula goes beyond one row, you will need to re-size the formula bar using the arrows over to the right of the box or drag the bottom down with your mouse.
8. Pick the Input first (Structure)
Make sure that the Input (or assumption) is referenced at the start of the formula so that you can leverage some trace precedent tricks.
In native Excel, this facilitates the use of one of Jennifer’s favourite keyboard shortcuts is Ctrl + [ (i.e. hold down the Control key and at the same time hit the left square bracket key).
Jennifer calls this the “jump tool” because it will jump you to the first precedent in a formula. But then it resets. It’s a one off.
So you need to choose carefully the first precedent that you reference into the formula.
It’s most useful if this is an off-sheet reference because this keyboard shortcut really helps you to check your work and to get to know an unfamiliar model. Jump quickly and cleanly to check the precedent; then hit F5 and Enter and Excel will take you back to exactly where you just came from. Now you can continue checking. Smooth.
Lance prefers to use a formula traverse tool which is included in the Modano Excel add-in to do the same job but also has a number of other useful functions for showing the formula logic graphically and hover over parts of formulas to see the answer without even jumping to it.
9. Edit in the formula bar (Transparent)
Another little tip and it helps with transparency is unticking the “Allow editing directly in cells” default.
When you want to edit the formula, hit F2 and you will now be active in the formula bar, not the sheet. If you need to reference something in the sheet to make the edit, hit F2 again. When finished editing hit Enter.
While you are in the formula bar you can double click directly on a precedent cell reference and Excel will jump you to the cell reference. Pretty neat!
It also means you edit the formula in the actual formula bar and you always know where to look on the screen. Additionally, you maintain the visibility and understand the context of the other cell ranges around the one you are editing.
If you have a large sheet of calculations and you need to zoom into a particular cell to edit, you lose perspective of the other content around you by having to occupy space in the cell for formula editing.
With large formulas (which you now know you shouldn’t really have …) you get an explosion of formula on the screen which obliterates all else. Totally unTransparent.
You need to change this from the default in the Excel options if it’s set to checked. Go to File Options, Advanced and uncheck. Here is a screen shot:
10. Choose in-built Excel functions with care (Transparent)
For example, should you choose an IF or a MAX to determine the intrinsic value of an option which can be positive or zero but never negative?
The beauty of an IF statement is that the logic is totally Transparent in the code whereas it can be confusing using a MIN or MAX.
=IF(C7>0,C7,0) rather than =MAX(C7,0)
In reality you are trying to limit the result from not going negative or too small and almost feels like there should be a MIN rather than MAX.
Lance uses the MAX or MIN formula question to test Excel knowledge and application when he is looking to hire staff, and many people go through the multiple-choice question and get this answer the wrong way around.
IF statements are very powerful and more Transparent.
They are well understood by most modellers. If you use more arcane formulas, like MAX, you rely on the user of the model understanding the Excel function not the business logic.
Many in-built functions are really not very user-friendly.
Some are downright clunky (like =PV) and may contain hidden traps (like =NPV which does not actually produce the “Net” Present Value!).
Whilst the Function AutoComplete tool in Excel does provide some assistance, there can still be a number of square bracketed [0] items and other quirks in those in-built formulas.
Which IRR formula you use can also be dangerous and understanding the differences between IRR, XIRR and MIRR is critical and the results can vary wildly if not applied correctly.
Understand also that IRR is only a theoretical calculation and often the reinvestment of cash flows is not well known.
If in doubt, either code the math or break it down into baby steps.
If you do use the in-built formula, then you must take the time to find out properly how the formula works. F1 help is good for this.
If you had to teach this topic in a class to school kids what key tips would you given them to focus on?
Learning proper formula construction is a bit like learning grammar and times tables.
Boring in the short-term but empowering in the long-term if applied well and consistently.
If you cannot construct a formula according to best practice (speak the bestest Engrish of busyness) then don’t try preach to people about a vision which isn’t built on a solid foundation.
What practical steps can people take now to learn more?
Firstly, research the well-known financial modelling standards which were mentioned in the previous article in the series on “Financial Modelling Friendliness” and understand those concepts.
Hold on with a bated breath for the article on standards which will be co-authored by the one and only Kenny Whitelaw Jones.
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 look at the formulas. Practise using F2 and change your settings, so you are editing in the formula bar.
Start simply - Master a simple model first and work your way up. Jennifer is happy to share an introductory level model that can be used if you are still on your L plates.
Find a tutor or mentor - Modelling is a really hard skill to learn from a book or a video. Having an expert tutor or mentor 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.
A tip from Jennifer when choosing tutors: “Beware of tutors claiming to teach “modelling” who in fact just teach Excel functions. Excel is a tool. Modelling is a skill. Modelling requires a lot more knowledge than just Excel.”
If you would like bespoke tutoring, or expert coaching, you can book a live conference tutorial session with Jennifer through Kintell.com.
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 and to follow some of the tips mentioned in this article above.
If you can’t code from scratch with confidence, you will be tempted to copy and paste someone else’s work. Please don’t do this!
Firstly, you lose out on the opportunity to learn anything. Simply copying someone’s hard work and claiming it as yours is really never going to get you far in life. You will get caught out.
Success comes from hard work.
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 model auditing is becoming more automated and, in some cases, more advanced forms of predictive modelling using AI/ML are tempting, we still need to communicate and understand the decisions and logic that sit behind the answer that spits out at the other end.
Model audits (that include line by line logic checking) are still necessary in many circumstances like syndicated loans, IPOs, project finance and other transactions when big $’s and risk management relies on the financial model.
The cost of a model audit can range from $30,000 up to $300,000 (sometimes even more) and clients notice the difference!
The standard way to price an audit is on a per unique formula basis.
Following the above tips, especially “One row, one formula” will make the job of the auditor much easier and cut down the cost of the service which will be valued by your client or your boss.
The model auditor will get a lot of comfort if they see some form of best practice being followed in the development of the model.
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.