Excel best practices for financial models

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.

Formulae

Use INDEX and MATCH instead of VLOOKUP

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

Positive and Negative numbers

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 + or SUM).

Why: Formulae with lots of + and - 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.

Separate inputs from formulae

Don’t do this: Hard-code numbers in a formula (e.g. =A13/365)

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.

Use $ to fix row/column within formula

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:

=B1*B11

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:

$B$1*B11

Then, when you paste it one cell to the right, it will be:

$B$1*C11

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 $ symbol).

How: Select the cell reference within your formula, and press F4. Press it again, and again, until the $ signs are in the right places.

Simplify formulae

Don’t do this: Use complicated formulae (e.g. containing multiple IF/OR/AND functions)

Do this instead: Split complicated formulae between separate cells or separate rows.

Why: Complicated formulae frequently hide complicated logic, e.g.

=IF(AND(MONTH(A1)>5,MONTH(A1)<9),C4,D4)

is harder to understand than two separate formula:

Is summer?

=AND(MONTH(A1)>5,MONTH(A1)<9)

Rainfall per month:

=IF(B2,C4,D4)

Circular References

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.

Named cells and Named Ranges

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.

Balancing items

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.

Formatting

Colour coding

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).

Number precision

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.

Left/right alignment

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

Signifying negatives

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.

Borders / Underlining

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

Model Structure

Single input only

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

Calculations across sheets

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

Hidden rows/columns

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.

Multi-purpose columns/rows

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.

Arrange sheets in a logical order

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

Check for errors

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.

Macros

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.

Scenarios

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

Numbers

Billions

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

Good example: https://www.ablebits.com/office-addins-blog/2016/07/07/custom-excel-number-format/

Enter numbers as numbers

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.

Presentation

Empty columns

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.

Printing

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)

Comments

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

Sheet names

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!