If you use Excel for financial modeling, you have probably had one (or more) of these things happen to you:
- You give your model to someone else (e.g. an investor) and they come back with many questions because they can’t understand the logic
- You feel stressed when you’re making changes because there is a lot of information to hold in your head
- You open a model you last used months/years ago, and you need a bunch of time to figure out what you were thinking
The tips below can help. When you read through the list, your reaction to each item will likely be one of:
- Yup, I already do this
- Yup, I know about this, and I do it when I have enough time
- I don’t see the point
I’ve designed and delivered training courses in accounting, financial modeling and Excel to engineers, product managers (and finance professionals!) at companies you’ve heard of. When people who’ve attended ask for help to improve a spreadsheet, or advice on how to add some feature to a complicated model, the answer almost always is to make the functioning of the model more obvious, logical and, most of all, transparent.
Apply the tips below to one of your models. It will make your life easier.
Don’t do this: Use
VLOOKUP to find the value in a particular column/row of a table
Do this instead: Use
MATCH to search in the lookup column, and
INDEX to pick out the value
Why: If you use
VLOOKUP, then you need to reference a whole table, and specify the column by it’s sequence. But if you insert a new column anywhere to the left of that column, the column number in your
VLOOKUP formula will be wrong, and your formula will return the wrong value. Using
INDEX and MATCH is better because you reference only the 2 relevant columns (not the whole table), which means they are more efficient to calculate, and that the formula doesn’t break when you add new columns.
Good example: https://exceljet.net/index-and-match
Don’t do this: Make all numbers positive. Use addition/substraction as appropriate.
Do this instead: Use both positive and negative numbers, e.g. revenues should be positive, and costs should be negative. When making totals or subtotals, use only addition (either
Why: Formulae with lots of
- symbols are hard to read. Showing all numbers as positive makes it hard to understand what’s going on, without inspecting the formulae that calculate subtotals or totals.
Don’t do this: Hard-code numbers in a formula (e.g.
Do this instead: Put any input (hard-coded number) into its own cell, e.g.
A1: Number of days in the year B1: 365 A10: Annual Revenue A11: Daily Revenue B11: B10/B1
Why: The model will be easier to understand, easier to audit, and easier to fix.
Don’t do this: Modify individual formulae by hand when copying them across a row, or down a column
Do this instead: Use the
$ symbol to fix references to a particular row or column
Why: If you have ‘interest rate’ in cell
B1 (among inputs at the top of your sheet), and you have a formula that references it:
When you copy that formula and paste it to the right, the pasted formula will be:
=C1*C11 (and C1 is probably empty!)
But what you probably wanted was:
=B1*C11 (i.e. apply the same interest rate, but to C11 instead of B11)
You could avoid this hassle in the first place if your first formula were instead:
Then, when you paste it one cell to the right, it will be:
The reason we fix both the column (
B) and the row (
1) is because we are referring to a specific global input, not a row or column of values. In other examples, you might just fix one or the other (using just a single
How: Select the cell reference within your formula, and press
F4. Press it again, and again, until the
$ signs are in the right places.
Don’t do this: Use complicated formulae (e.g. containing multiple
Do this instead: Split complicated formulae between separate cells or separate rows.
Why: Complicated formulae frequently hide complicated logic, e.g.
is harder to understand than two separate formula:
Rainfall per month:
Don’t do this: Use circular references to optimise a value
Do this instead: Don’t use circular reference unless you really really know what you’re doing. If you’re not sure if this applies to you, then don’t use circular references.
Why: There are very few cases in which circular references are the right way to solve a problem. In all other cases, circular references will make the output of your model hard to reason about, and will increase the chance that you make an error.
Don’t do this: Use named ranges and names
Do this instead: Don’t use names or named ranges.
Why: If a formula refers to a named cell, anyone looking at the model needs to use the ‘Trace Precedents’ command to find where that cell is, making the model harder to understand and audit.
Don’t do this: Use ‘plugs’, i.e. formulae for balancing items
Do this instead: Calculate every item directly when possible
Why: If you calculate an item as a balancing item, then:
- you can’t do an error check on your total, and
- no one can reason about the components of that figure.
One of the few cases when a balancing item is acceptable is when you’re building a financial model and each period has a ‘new debt/equity funding’ line. In this case, the funding requirement is a direct result of the other items in the column, so it’s OK.
Don’t do this: Leave all cells as black-on-white
Do this instead: Colour code any cell that contains inputs (hard-coded numbers), with either (i) yellow background, blue text, or (ii) orange background, grey font
How: Use the ‘Cell styles’ section in the ‘Home’ ribbon to use the pre-defined styles. You can customise these styles if you don’t like them. Or you can just change each cell’s background colour and font colour as you normally would (not recommended, as it’s hard to stay consistent).
Don’t do this: Display numbers with many digits of precision
Do this instead: Display numbers with a maximum of 4 significant figures (but usually 3)
Why: The 4th or 5th significant figure is usually insignificant (do you care about less than 0.1% difference?), and every additional number on the page makes your spreadsheet harder to read, and therefore less useful.
Don’t do this: Centre numbers
Do this instead: Leave Excel to align things automatically
Why: Numbers in a column are easier to compare when right-aligned. Text labels in a column are easier to scan when left-aligned. The default in Excel is to left-align text, and right-align numbers, so there’s usually no need to change anything
Don’t do this: Use the default number formatting in Excel
Do this instead: Signify negative financial values using brackets, and maybe also red font
Why: Brackets are larger and much more obvious than the dash that normally signifies numbers.
Use borders consistently:
- Single line at the top of a cell indicates that this cell is a subtotal
- Single line at top, and double line at the bottom, indicates this is a grand total
Don’t do this: Enter the same input (hard-coded value) in more than one cell
Do this instead: Enter any input (hard-coded value) only once in the whole workbook, and reference it using a formula anywhere else it is needed
Don’t do this: Reference values from another sheet in a calculation
Do this instead: Use formulae to link the values from the other sheet in a separate row/column in the current sheet, and then (in a separate row) write your formulae to reference this row of values on the current sheet.
Why: It is easier to audit a formula when you can see the number it depends on, without flipping between sheets
Don’t do this: Hide rows to make your spreadsheet easier to read
Do this instead: Don’t hide rows.
Why: People using your spreadsheet want to know how it works. If you hide rows, they cannot trust the calculations, because they cannot see them.
Don’t do this: Mix different types of values in a row (e.g. GAAP and non-GAAP)
Do this instead: Keep different types of values in different rows (e.g. one row for GAAP, and another for non-GAAP). Even if some columns don’t include values for some rows, that is better.
Why: People assume things in the same column or row are comparable. Mixing different types of things means that neither the column nor the row contain the same type of value.
Don’t do this: Arrange your sheets in the order in which you created them Do this instead:
- Arrange Assumptions sheets to the left of Calculations sheets
- Arrange Calculations sheets to the left of Output sheets
- If you have a cover sheet, obviously put that as the first (left-most) sheet
Don’t do this: Hope that everything adds up
Do this instead: Use error check rows to check that key identities hold (e.g. Capital - Assets - Liabilities = 0)
How: Make a formula that shows ‘ERROR’ if the identity doesn’t hold, but shows nothing if it does. Use a red font or red background for that cell.
Don’t use macros
Do this instead: Just use formulae instead.
Why: Macros make the logic of your spreadsheet harder to understand, both for others and for your future self.
Don’t do this: Make copies of sheets for scenarios Do this instead: Use the same input, calculation and output sheets for all scenarios, and use a single input cell to choose which scenario you want to run
Why: Having multiple copies of the ‘same’ sheet makes it hard to make changes to your model, as you need to update structure and formulae in different places. It also makes it hard to audit, as there are more sheets to audit.
How: Arrange your spreadsheet like this:
- Column A: Input labels
- Columns C, D, E: Input values for scenarios 1, 2, 3
In column B:
- An input cell at the top, that contains the current selected scenario #
- All other cells use an INDEX() function to choose the correct current input from the cells to the right
- All calculation sheets refer only to column B
Don’t do this: Store numbers in thousands or millions
Do this instead: Store numbers in units (i.e. enter 1MM as 1000000) and use number formatting to display them as 1MM or however you want
Why: If you store some numbers in thousands, and some in millions, and some as units, then your formulae will be complicated. For example, if your revenue is in billions, but your number of shares is in millions, then your EPS calculation will be something like X1/Y1*1000
Don’t do this: Enter column headings like ‘2018 FY’ as text
Do this instead: Ensure all years are entered as numbers, and use number formatting to add any extra text
Why: Excel can automatically increment numbers when a range of numbers is extended, but cannot do the same for text. This reduces the risk of error.
Don’t do this: Include empty columns to create space between columns
Do this instead: User proper alignment and column width
Why: The more cells a model has, the harder it is to audit and understand it properly. Adding blank columns doubles the number of cells, and anyone reading your model will need to verify that those columns are actually empty, and don’t have numbers that affect formulae.
Don’t do this: Send a spreadsheet to someone without making sure it prints properly
Do this instead: Set up each page to print how you want it to appear
Why: Some people print spreadsheets or convert them to PDF, to share with others. If someone tries to do that with something you send them, and it looks bad, it will just make them annoyed at you.
How: On each sheet, use the Page Layout tab:
- Margins (set to normal or narrow)
- Orientation (set to landscape)
- Size (check that it’s A4)
- Width (set to ‘1 page‘’)
- Height (set to ‘Automatic’ or a specific number of pages)
- Print Titles - set ‘Rows to repeat at top’ and, if applicable, header+footer (with name of file and name of sheet)
Don’t do this: Let people guess where numbers came from
Do this instead: Use comments to explain specific terms or the source of an input
Don’t do this: Leave worksheets named as Sheet1, Sheet2 etc.
Do this instead: As soon as you add a second sheet to a workbook, rename Sheet1 and Sheet2 to sensible names
Why: Make your stuff easy to use!