What are Financial Modeling Best Practices (part 2)?
Modeling "best practices" simply means building your model in such a way that is organized, understandable, and (hopefully) free from error.
Picking up from last week, today's post is part 2 of the most impactful best practices I've learned over my career.
Assumptions Sheet
To be honest, I go back-and-forth on this one.
Some people are die-hard "Assumption Sheeters" and need to have all model assumptions on one page.
And I get that, to an extent. However, sometimes the assumptions are either too nuanced for a one-pager or I want to see the changes play out live.
For example, in the image above, I have a handful of input cells (note the formatting) that sit inside the monthly model.
I like this method because when I change the input cell, I can immediately see the impact play out in real-time. On an Assumptions sheet, I have to trust that the input I changed actually linked to the place I thought it would, so I find myself constantly flipping back-and-forth to double-check.
However, it's still nice to see all the assumptions in one place, so what I'll typically do is actually link these to an "Assumptions Output"-type tab, like this:
You can see there aren't any Assumption inputs on this page, just outputs from my model.
I think this one comes down to personal preference, but nonetheless make sure there's a way someone can quickly understand all the underlying assumptions in your model.
Formula Consistency
Take a look at the image on the right. You've probably seen something like that before.
What I'll see a lot is:
- 3 monthly columns, then
- A quarterly column, then
- More monthly columns, until
- There's an annual column
When you first start modeling, I get that this layout "makes sense": you want to see a quick summary of your results.
However, this modeling practice will ultimately hurt you.
As you get more advanced and start using formulas like SUMIFS, INDEX/MATCH, or XLOOKUP, you want consistency in your columns so that you can easily drag lookups around your file without having to skip columns.
Assuming a date-based model, the correct layout is this:
- All the monthly columns next to each other, then
- (New section) your columns summarized by quarter, then
- (New section) your columns summarized by year
The image on the left is a decent example. See how all the monthly columns are together, and then the annual columns come at the end? That's the correct way to do it.
I'll talk through how you can do this next.
Date Tagging
Look at the red dotted box in the image above.
Every column represents one month.
Above those months I have "tags."
In other words, I'm "tagging" the Year and Quarter of each month as well.
That way, when I get to summarizing by Quarter or by Year, I can copy my entire column with its exact format, and then use the SUMIFS function to aggregate the data.
These tags make your model extremely flexible when building summaries, and detach you from the worry about needing to make a print-friendly model.
The model can just be the model, hundreds of rows and columns. Whatever it may be.
These "tags" allow you to condense that information into print-friendly summaries on other tabs.
Placeholder Row
This might sound dumb, but I can't tell you how many times I see this mistake.
Look at the image on the left.
We want our models to be "pretty" right? So we always list our data and then put the total in the very next row.
And this hurts us. Every time.
It's not if, but when, you will add a new row.
Look what happened when I added "Customer 5" to the image on the left: my SUM formula doesn't capture the new data.
So I'm left having to make two updates:
- Adding the new data in the first place (Customer 5)
- Updating my SUM formula
This sets me up for error because at some point I will forget the SUM portion of the model.
Instead... use a placeholder row (image on the right).
This blank row isn't as pretty, I get it, but it will make your model so much more secure.
Now, every time you need to add new data, this blank placeholder row ensures the SUM formula "comes with you" to capture the new entry.
See how "Customer 5" is already captured in the Total Revenue without me having to make any other changes? This is because of the blank placeholder row.
Sounds dumb, I know, but it's a game changer. And I can't tell you how many times I see companies make this mistake in their modeling.
I will use it for rows and for columns, always leaving one extra space for my formulas in case I have to insert something new.
To Summarize
- Assumptions: Have one place where all your assumptions are visible
- Consistency: Group all column types together, don't mix and match
- Placeholder Row: Add an extra space to help reduce silly errors in the file
Of course there are always more best practices, but I feel like mastering these core concepts from this week and last week will quickly put you ahead of the pack.
That's it for today. See you next time.
—Chris
Before you go...
It's one thing to read in an email, but another to see it on video. Join over 2,000 students, many of whom have secured top-tier finance positions in FP&A and Private Equity, by checking out my Financial Modeling Courses .
"⭐⭐⭐⭐⭐...this resource has been an absolute game-changer."
Gain the confidence to be on top of your game and check them out today .