“If you cannot explain it simply, you don’t understand it well enough.”

Albert Einstein

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.

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.

Conclusion

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.