October 17, 2023
Adam Hoeksema
Since there isn’t a specific “right” and “wrong” way to build a financial model, a lot of it comes down to preference; however, I do think there are some best practices when it comes to financial modeling in Excel. We have the opportunity to see dozens of different financial models every month at ProjectionHub, and often the models that we look at aren’t great, but it is hard to put a finger on exactly what the financial models are lacking, so I wanted to put together this list of general best practices when building a financial model.
The Model Should Meet the Objective:
Objective: Understand the purpose of the model. Is it for investment valuation, forecasting, budgeting, or another purpose?
Scope: Decide on the time frame, the level of detail, and the key outputs you need. Based on the objective you might only need 12 months or you could need 10+ years of modeling.
Simplicity is Key:
- Make the model as simple as possible but as complex as necessary.
- Avoid overly complicated formulas and structures.
Sometimes it is better to just show your work and show each step in the model so that it is easier to follow instead of having a more elegant, but very complex equation that is doing many steps or calculations.
Consistent Structure:
- Organize your Excel sheets logically (e.g., 'Inputs', 'Assumptions', 'Calculations', 'Outputs').
- Use a consistent color coding scheme (e.g. our projection templates use - blue for inputs, black for calculations, and green for outputs).
I often have trouble following client’s financial models because they don’t follow a logical structure.
Use Named Ranges:
- Naming cells or ranges can make formulas easier to read and understand.
Just like this blog post has headings for each section, it can be really helpful to have a section header for each section of a financial model.
Avoid Hardcoding:
- Do not input numbers directly into formulas. Instead, use input cells or assumptions sections.
- If you must hardcode, make sure to highlight or color-code these cells to make them easily identifiable.
I have learned this lesson the hard way, one of the most common customer support questions we get is from an assumption for the number of weeks in a month that we have hard coded in an equation. If you must hard code, you should at least leave a comment or note in the cell to describe the hard coded number.
Clear Assumptions:
- List all assumptions in a dedicated section.
- Ensure assumptions are realistic and based on solid research or historical data where possible.
If you can, it is good to leave notes about where you found your assumptions so that the user can understand that your assumptions are research based, if you don’t leave a note about your source, the user is going to assume you just pulled something from thin air.
Error Checks:
- Build in error-check cells to identify when something is wrong. For instance, a check to ensure balance sheets balance.
- Use Excel’s auditing tools to trace precedents and dependents.
One of the most common errors I see with a financial model from a client is that their balance sheet does not balance. It is quite difficult to balance a balance sheet in a financial model, especially if you aren’t an accountant by trade. It is just easy to throw your balance sheet out of balance as you change assumptions, so it is important to add in a diagnostic or at least make sure to check that your balance sheet balances before you send it off to stakeholders.
Resource: Here is a free balance sheet template if you need help getting your balance sheet to balance.
Documentation:
- Comment on complex formulas or decisions to make it easier for others (or yourself later on) to understand the model.
- Consider a dedicated 'notes' or 'documentation' section.
Scenario & Sensitivity Analysis:
- You can use data tables, goal seek, scenario manager, and other Excel tools to test various scenarios and sensitivities.
- Honestly, for most Excel users I just suggest that you save two copies of your financial model to compare different scenarios so that you don’t have to learn the more complex Excel features.
Protection & Integrity of the Model:
- Protect sheets and cells that shouldn't be modified to maintain the integrity of the model.
- Use data validation to ensure only permissible values are entered.
This is another tip that I could debate because I get really frustrated when a financial model is not fully unlocked so that I can make changes to it if I want to. At the same time, I do spend a lot of customer service time fixing a client’s file because they broke something since we provide our templates as fully unlocked files.
Again, since there aren’t specific rules about financial modeling like there is Generally Accepted Accounting Principles (GAAP) for accounting, these are more guidelines and opinions than they are rules, but I think if you follow these best practices, your financial model will be more clear and effective for the end user.
If you need any help with your financial model don’t hesitate to reach out, we would love to help!
