What is the best practice for financial modeling in excel?
"Financial modeling sits at the core of the finance industry, providing insights into future financial performance," is what some d-bag AI definition would tell you.
What we're really doing is building a tool to make an estimate about a company's future performance.
Excel, with its powerful features and flexibility, stands out as the preferred tool for financial modeling (though I'm seeing a lot of Google Sheets users these days too).
So whether you're in FP&A, consulting, corporate finance, or private equity, mastering Excel financial modeling is a critical skill for career longevity.
But with so many approaches and techniques available, what is the best practice for financial modeling in excel?
I'll talk through a bunch of them.
Understanding Financial Modeling
At its core, financial modeling involves creating a summary of a company's expenses and earnings in the form of a spreadsheet (or FP&A software, what have you), which can be used to analyze the impact of a future event or decision.
I used to think models were some overly cryptic creation that only the best and brightest had access to. That's not the reality.
You're simply building a projection (estimate/guess) of what you think a company might do in the future.
Excel: The Swiss Army Knife for Financial Modeling
Excel is to a financial modeler what a palette is to a painter: essential. (-Robert Frost, probably)
And despite what all the haters say, Excel is still the go-to spreadsheet program. Far beyond Google Sheets or some other "Excel killer" program.
But to truly leverage Excel, you need to understand more than just the basic features; you need to adopt best practices that ensure models are not only accurate but also transparent and user-friendly.
It's not good enough to just be "proficient with Excel" like it says on your resume. You have to know how to use the tool to make business projections (and ultimately decisions).
1. Start with a Clear Structure
Keep it Simple and Structured: Begin with a clear layout. Separate your inputs, calculations, and outputs.
Use distinct sections, and make sure your model flows logically from assumptions to conclusions.
I like to divide things into:
- Raw Data
- Support Schedules
- Financial Model (usually three statement)
- Summaries
- Error Checking / Admin
I will also use "subject divider tabs" to make it very clear what part of the model you're in.
And yes, sometimes I'll even use emojis at the beginning of tab names. Sometimes a visual indicator is much more powerful than just the words, like this:
- Raw Data
- 📈Raw Data
Which one catches your eye more? Exactly. Just don't go crazy. Keep the visuals consistent. It's not an art project, it's a model.
2. Maintain Transparency and Traceability
Document Your Assumptions: Every model is built on assumptions.
Clearly documenting these assumptions not only provides transparency but also makes it easier to update the model as new information becomes available.
Also, contrary to popular opinion of having an "Input Page," I'm actually not a huge fan of these. Rather, I prefer to place the assumptions inside the model wherever it makes the most sense (gasp).
But, I will always make sure to have an "Assumptions Summary" page, where I at least link my assumptions to. So, it's more of an output page than an input page.
Here's an example:
3. Focus on Flexibility
Build for the Future: Models should be built to accommodate changes easily.
Assume that every month you're going to have to "roll" this thing forward, which means updating the model with the latest actual results.
Make this easy on yourself by doing a few things:
- "Mapping" your actuals into buckets, then using SUMIFS or SUMPRODUCT to populate your three statement model with summarized figures (here's a video I made on that):
- Create a named range of "Last_Updated" (or something like that) on your Admin/Control Panel tab so that you can easily change the date there and let that flow through the rest of the model.
- Focus on the 80/20 of the model. A lot of the detail we think we need is just noise. Make your model about the most impactful parts of the business, and everything else can just be a basic assumption. Far too often people over-complicate and build structures they don't need. This ultimately just bogs down the file.
4. Ensure Accuracy
Double-Check Your Work: A minor error can significantly impact your model's output.
You need need need an "Error Checking / Admin" tab (like I mentioned at the beginning).
Did you know about 90% of all models contain errors? That sucks. Like, really bad.
You need a way to check for these things:
- Does my balance sheet balance?
- Does my statement of cash flows match the balance sheet?
- Does my Net Income from QuickBooks tie to my three statement model?
- And anything else you can think of. This stuff is important.
Btw, someone always lights me up about the 90%. Google it if you want. 90% isn't really the point. So what if it's 80%, or 70%, or shoot, even 30%? That's still high. So make sure you're checking your models.
Here's an example:
5. Adopt Consistency
Standardize Your Approach: Use consistent formatting, naming conventions, and formula approaches throughout your model.
This not only helps in understanding the model but also in maintaining it.
I really like to put a "formula guide" at the beginning of the file that shows exactly what everything looks like, what it means, and what it does. I just find it's helpful.
Here's an example of one I use:
6. Use Visuals to Communicate
Not to totally go against the grain on this one, but I think charts are only so helpful. Keep that sh*t simple: line charts, bar charts, and that's about it.
Heat maps and other wild visuals seem cool but they're not overly helpful.
Think about the essence of Financial Modeling: we're using data about the past to make estimates about the future.
That means, all we really need to know is: what did we think would happen vs. what actually happened? This can be communicated with simple dashboards and a BVA or "budget-vs-actuals" analysis.
Here's an example:
That's a nice fancy image of a fairly basic dashboard that I used on most of my PE deals and still use today. It tells me:
- LTM or Last Twelve Months
- Current Month
- Year To Date
- Outlook (for rest of the year)
This gives you multiple views on the same line item. I don't see a graph anywhere, do you?
7. Embrace Best Practices for Data Management
Data Validation and Protection: Use Excel's data validation features to ensure that inputs are within reasonable ranges. Protecting sheets and cells can prevent accidental changes to formulas and constants.
Honestly, data validation is one of the most unsung heroes in all of financial modeling. Instead of putting something ridiculous like "ONLY ENTER DATE IN THIS CELL," you can just select "data validation" and ensure only the correct type of data is entered.
That's the whole point. You can also add instructions and error notifications. It's a beautiful thing, and I will always use it on a client model that's going to have a lot of hands inside the file.
Other things that are helpful is just organizing your raw data into table format wherever you can. This layout is very friendly for SUMIFS, SUMPRODUCT, INDEX/MATCH, or even XLOOKUP for all the Gen Z'ers out there.
Don't make pretty boxes or separate data sets with columns. Once giant table is best.
8. Continuous Learning and Improvement
Stay Updated: Excel is constantly evolving, with new features and functions being added. Stay abreast of these updates and continuously seek to improve your modeling skills.
One the newer developments I love are dynamic arrays. These things are just awesome, and they're especially applicable for a model that has very frequent updates (like daily or weekly).
Get comfortable with FILTER, SORT, and UNIQUE. There are a ton of others but these three are great to start with.
Conclusion: What is the best practice for financial modeling in excel
Financial modeling in Excel is both an art and a science, requiring technical skills and a creative approach to problem-solving.
By following these best practices, you can build robust, reliable, and easy-to-understand financial models. Don't expect to get good at this overnight, though. You need to practice.
I've been doing this for years and I'm still always improving. Btw, the best way to improve your skills is to simply build a model of your own life. That's the way I learned early on and I still find it's the best teacher (since there's no learning curve about your own life).
My one takeaway here would be to keep things simple. Honestly, your boss doesn't give two sh*ts whether you used VLOOKUP, INDEX/MATCH, or XLOOKUP. Your boss just wants to know the answer. What do we do next?
Keep practicing. Focus on the 80/20, and go deep on Excel. This is still the best financial modeling program you can learn.
If you want to go from amateur to pro, then definitely check out my Financial Modeling courses. Featured by the Wharton Online PE Cert. Program, Wall Street Prep, and LinkedIn Learning. Thanks for reading, hope to see you there.