It’s a simple sentence, but it carries a lot of truth. A lack of simplicity is often why models are so complicated that they are almost useless. And I’m not just talking about Excel workbooks full of hard-to-track VLOOKUPs, convoluted named ranges, and data tables. Better financial models start with better conceptualizations and more straightforward ways of representing the issues you are trying to analyze and solve.
Read ahead for 5 great tips on how to build better financial models in Excel!
Tip 1: Start with outlining the flow of your financial model
Before you even open Excel, spend some time outlining the flow of your model. What are you trying to accomplish? Who is it for, and how will it be used? Which data sources will be required?
It’s essential to set goals before you start building so that you and the people who use your model know what they can expect from it. For example, if all they’re looking for is a report on year-over-year sales, there’s no need to build complicated forecasting logic. By knowing upfront who will use the model and how they’ll use it, you can limit its scope and increase its usefulness.
Take some time to outline the logical flow of the financial model. This means thinking through how different sections of the financial model will interact.
Whenever I have to build a new financial model, I start by drawing a diagram on a whiteboard or paper that shows how all the pieces fit together. This process will help you visualize the structure of the model and make sure you don’t forget any components. It’s also helpful to have this structure in place as you build out your model because it will help keep your spreadsheet organized.
Here’s an example from a recent model I built. This outlines the Sales section of the model logic and shows how the relevant financials will flow through the model. By having this structure in place before I start building the components, I can group items and conceptualize the functionality of each sheet and how I can reuse them. For example, the US Sales and EU Sales tabs have the exact same structure, but different data sources and forward-looking assumptions.
Tip 2: Document your assumptions.
Create a summary sheet that lists your key inputs and provides a source for each model input or how it was calculated. For example, take an LBO model where you use an EBITDA multiple of 5.00x to calculate the value of a target company. It is helpful to provide a source for the multiple, e.g., “The target has similar business characteristics as ACME Inc., which was recently acquired at 5.50x EBITDA”. If you don’t have a specific source, you can also explain the logic behind the assumption (“We used 5.00x because the average transaction multiple in the industry is 4.00-7.00x.”).
If you are doing complex calculations, remember to write down explanations for the formulas. You may think you will remember what you did, but if you need to revisit the financial model in a few months or even weeks, it will be much harder to figure out the calculations if you don’t document them. Therefore, it’s a good idea to always think about documenting your model in a way that would allow someone with similar experience to understand the financial model logic and flow relatively easily.
Sign Up for our Newsletter
And Get a FREE Benchmark Analysis Template
Tip 3: Use consistent formatting
To make your Excel financial models easy to use, you should use consistent formatting. Anything you do to make the model more intuitive will save users time in the long run.
Using the same basic template for each worksheet (or “tab”) is a great idea. This may include setting up headers with titles, currencies, notes, etc., which are organized in the same way across the entire financial model. If, for example, you are modeling the sales performance of several entities, it makes sense to build a template for that when you model one of the entities and then reuse the same template for the others. This would speed up your work, and it will be easier to track the different pieces of the model, as they will look the same and follow the same calculation logic.
The workbook should also have a consistent theme across all worksheets. Set up a standard color scheme with different colors for constants/assumptions, links to other tabs or files, and calculations. That way, it will be visible at a glance what kind of information you’re looking at.
For example, I always use blue for assumptions (input numbers or constants), black for calculations, and green for links to other sheets or external files.
Additionally, when I have a cell with a formula that differs from the formula in the other cells in the row/column, I color it dark brown. In the image above, the prices in Mar and Dec were decreased manually to account for promotions.
Tip 4: Introduce validation checks to reduce errors
One way to ensure your financial model works well is by building in safeguards to minimize the possibility of error.
Regularly testing your model can help you discover and fix errors. One common technique is to do a “stress test,” which involves running extreme scenarios through the model to see if it can handle them. If you have a model for production costs, for example, trying a scenario where the cost of raw materials doubles can show you whether the model will break and what happens when it does. You can also perform sensitivity analysis on key assumptions to visualize how changes in them reflect on key outputs.
Another approach is to run multiple scenarios that are only slightly different, then compare the results. If you have a model for measuring sales, try running a projection for this year and next year with only a few minor changes to the assumptions (e.g., adjusting the predicted revenue growth by 1%). If the two sets of results differ too much, it could indicate something’s wrong with the model’s calculation logic.
Whenever I add validation checks, I’d calculate them as 1 for ‘OK’ and 0 for ‘Error.’ Then, with simple conditional formatting, I can transform these into icons that make the whole validation more visual and ensure it’s easier to spot problems.
Tip 5: Keep it Simple
Probably the best advice is to keep it simple. If you can do that and communicate your financial models effectively, you’re well on your way to creating clear and effective communication with the model users. If people struggle to understand your model, or if it seems confusing, you need to reevaluate how you built it.
I tend to often over-engineer modeling components that can be solved much more straightforwardly. Therefore, it’s a good idea to regularly revisit parts of the model you built a while ago and think about simplifying them further.
Keep your financial models as simple as possible, and you will also significantly reduce the potential for human errors.
There are many important things to consider when building models in Excel. The ones I shared are far from extensive but are the 5 that had the most impact on my modeling.
If there’s anything that I can recommend, it would be to experiment with your models and templates continuously. Of course, you can also keep an eye out for blogs like this one to learn about some techniques that you may not have considered before.
Remember that no one ever started at the top of their modeling game. You can only improve by practicing more and being critical of your work. Keep learning, and things will get better!
Please show your support by sharing this article with colleagues and friends.
Hi! I am a finance professional with 10+ years of experience in audit, controlling, reporting, financial analysis and modeling. I am excited to delve deep into specifics of various industries, where I can identify the best solutions for clients I work with.
In my spare time, I am into skiing, hiking and running. I am also active on Instagram and YouTube, where I try different ways to express my creative side.
FP&A ≠ Freakishly Painful & Annoying
Seriously though, we believe that company data should seamlessly translate into valuable insights. In real time. Without late nights, heartburn or exasperation…
The information and views set out in this publication are those of the author(s) and do not necessarily reflect the official opinion of Magnimetrics. Neither Magnimetrics nor any person acting on their behalf may be held responsible for the use which may be made of the information contained herein. The information in this article is for educational purposes only and should not be treated as professional advice. Magnimetrics and the author of this publication accept no responsibility for any damages or losses sustained in the result of using the information presented in the publication.