Financial modeling is an essential part of every FP&A professional’s toolkit. However, building a robust and reliable one requires skill, experience, and attention to detail.

This post will discuss five common mistakes to avoid when building financial models. The below is not an extensive list, but thinking through it before you start creating your next model will help you improve your work and better support the decision-making process within your business.

Mistake #1: Overcomplicating the Model

One of the most common mistakes in financial modeling is overcomplicating the model. I can’t even count the number of times I’ve found myself guilty of this.

As a rule of thumb, financial models should be as simple as possible while capturing the essential information needed to make informed decisions. On the other hand, make a model too complicated, and it will be difficult to update, prone to errors, and will take you much more time to create in the first place.

Examples of Overcomplication

Here are a few examples of overcomplication that I see creep up in my models most often:

• Too many tabs: A model with too many tabs can be overwhelming and hard to navigate. Instead of creating a new tab for every detail, it’s better to consolidate information where possible. Often, when I start working on a new model, the file seems so empty, almost like it’s not good enough. In my desire to make it look more professional, I often split things into unnecessarily many tabs. And then, when I’m done and ready to review and update, I have too many tabs to track. Here comes the fun part, refactoring (e.g., taking the 10 sets of Income Statements, Balance Sheets, and Cash Flow Statements and converting them from 30 tabs to 10 tabs with a 3-statement model within😅).

• Excessive detail: Including too much detail in a model can make it difficult to understand and update. In my case, I usually struggle to keep revenue models concise and straightforward. Especially when forecasting the next 3-5 years for a fundraising model, it rarely makes sense to go into different sales channels, calculate churn per cohort, estimate ads conversion, etc. Sometimes it does, but most often than not, I end up having to scrap a massive part of my revenue modeling tab. It’s essential to focus on the key drivers of the business and avoid getting bogged down in unnecessary details.

• Complicated formulas: This is my favorite one. As you get more experience crafting financial models, you inevitably start competing with your last model or a colleague, friend, etc. One of the ways to ‘show off’ is using complicated formulas, which can make your model logic hard to understand and track, and your model a pain to update. Using simple formulas and breaking down complex calculations into smaller, more manageable parts is always better. But sometimes, the appeal of a complex nested formula is simply too much. 😁

Overcomplicating your financial models can lead to some severe issues. Such models are very time-consuming to create. Additionally, the more complicated a model is, the more prone to errors it becomes. Complex models also become more challenging to update as new information becomes available.

Tips for Simplifying a Model

To simplify a financial model, consider the following tips:

• Focus on critical drivers: This is probably the most important tip. Don’t try to incorporate every single assumption you can think of. Instead, identify the key drivers of the business and focus on those in the model. Remember, the best models do the job while being simple and focused.
• Use clear labeling and consistent formatting: A great model is one that people can follow without too much trouble. I strive to create consistently formatted tabs with descriptive labels for inputs, formulas, and outputs. I also use different colors for numeric inputs and links to other tabs. This makes my models easier to understand and navigate, especially when I have to update one 2 months later.
• Remove old data: Leaving old data as redundancy can be very tempting. However, piling on unused data can quickly become problematic for your financial models. Be diligent and regularly clean up your files. If you update the model with the Q4 payroll actuals, you don’t need to keep the Q1, Q2, and Q3 payroll plans. Apart from a cleaner appearance, removing old data has another significant advantage. If any formulas broke at some point and were still referencing old data, deleting these tabs would render such formulas as errors that you can fix and avoid presenting incorrect model outputs.

Mistake #2: Ignoring Data Quality

Whether you create your financial models in Excel or use a dedicated tool, data quality is critical for accurate financial modeling. Using poor-quality data can often result in incorrect analysis and a flawed decision-making process. To mitigate such risk, verifying data sources, standardizing data formatting, and automating data ingestion is crucial.

Examples of Poor Data Quality

Here are a few examples of poor data quality:

• Incomplete or inaccurate data: Often, we rush to start modeling and pay little attention to the source data. Building a robust financial model, especially within a new industry, can be so exciting that we may forget to ensure our source data is complete and correct. Remember, in many cases, we rely on other departments or employees of our customers to provide the data, and they may not always be as financially savvy as needed to provide complete and accurate data. And starting with the wrong data set will render all our modeling work worthless, as our model will result in flawed decision-making at best.

• Inconsistent formatting: We already discussed the importance of consistent formatting within our model files. However, it is also essential to ensure that source data is appropriately formatted (correctly parsed date fields, etc.). Having data with formatting all over the place can make it challenging to perform comparisons and calculations, costing us time and accuracy.
• Manual data entry: Humans are pretty great in many regards. Still, we excel at making stupid mistakes.😅As Forbes mentioned in an article back in the day, around 90% of spreadsheets contain some form of human error. Not all of those significantly impact the final output, but they can potentially lead to severe consequences for the business. In short, automate every aspect of data ingestion you can.

Using poor-quality data usually leads to incorrect analysis, which can result in flawed decision-making within the business. Additionally, fixing poor-quality data later can be extremely time-consuming, especially if it has already undergone numerous transformation steps required for your model.

Tips for Improving Data Quality

To improve data quality, consider the following tips:

• Verify data sources: Regardless of whether you are getting the data directly from a system/database or someone from another department (or a client’s team) is providing the data, verify its accuracy and reliability before spending time processing it within your models. You can thank me later. 😉
• Standardize data formatting: Talk to your IT/ERP support/client. Get them to provide data in a consistent, standardized format. That way, you can set up your ingestion once and save time on every update.
• Automate data entry: Now that you (hopefully) have all of your regular data imports coming preformatted consistently, you can think of ways to automate the data ingestion process to reduce the risk of errors. You can have a source tab where you keep the source data untouched and link to it from other tabs. That way, you can easily update data by repasting over the old one. It’s simple, but it works. However, if you want to build a fancier model, you can write custom VBA macros that take the source data and process it for you. And, once in a while, this is the only way to go, but be cautious so as not to run into Mistake #1.

Mistake #3: Failing to Test Assumptions

We need to set up some assumptions when building a financial model forecasting future performance. These are an essential part of the process that act as a set of pre-defined ‘master’ guides for our calculations (e.g., growth rate of revenue at 5% per year, cost of sales as 70% of revenue per year, etc.).

Testing assumptions using sensitivity analysis, scenarios, and benchmarking is essential. We set assumption by interviewing people within the entity, analyzing past performance, researching market trends, etc. However, we are often eager to start with the actual model and fail to test assumptions rigorously. Doing so can lead to inaccurate forecasting and subsequently a poor decision-making process.

Examples of Overlooked Assumptions

Here are my top examples of assumptions that FP&A professionals (myself included) often overlook:

• Revenue growth rates: One of the most impactful assumptions within any financial model is the assumed revenue growth rates we apply to revenue for the forecasted periods. If our model logic is insufficient, small revenue growth rate changes can have a considerable impact. It’s essential to test different growth rates to understand their effect on the model. If a small bump in revenue growth rates results in EBITDA being double what it was before, we probably need to rethink (or at least strictly review) our modeling logic. A model I was working on had such flawed logic that a 1% increase in the revenue growth rate compounded to EBITDA margins jumping from 17.5% to 90%+. 😅
• Expense levels: We often model expense line items as a certain percentage of revenue. For example, if COGS (Cost of Goods Sold) was historically 60% of gross revenue, it makes sense to model it at 60% going forward. However, if payroll was 20% of revenues last year, it may be unreasonable to calculate it as 20% going forward. As revenue grows, a significant portion of the payroll (admin employees, etc.) may remain the same. It is better to separate payroll as another model based on the positions needed to support the company’s growth in such cases. We may need salespeople to drive sales growth, but we would not hire an additional CEO every 2 years. Expense levels can have a significant impact on our financial model. It’s crucial to understand the industry and the company’s business model, so we can correctly identify fixed/variable costs and how they will grow as the business grows.
• Discount rates: Calculating some form of а present value of future cash flows is a standard part of most models. And we often pay little attention to the rate we use to discount the future cash flows to their present value. Considering the model user and their perspective on the discount rate is essential. Management may wish to use the WACC (Weighted Average Cost of Capital) rate, while a researcher may use an industry average. On the other hand, a VC may use their hurdle rate (the rate of return they want to achieve). It’s essential to test different scenarios to understand the impact of discount rates on our model.

Failing to test assumptions rigorously can have severe consequences, leading to inaccurate forecasting and poor decision-making. Additionally, failing to test assumptions can result in surprises down the road, which can be challenging to address.

Tips for Testing Assumptions

To test assumptions effectively, consider the following tips:

• Use sensitivity analysis: If you are using Excel, there’s a great tool that allows you to create sensitivity tables, showing how changing two assumptions can impact the final result you are modeling. Most sophisticated FP&A tools also have sensitivity analysis features. Use sensitivity analysis to test the impact of different assumptions on the model and ensure your model can ‘survive’ stress tests. This is a pretty good indicator that you did well with the model logic.
• Use scenarios: Whenever I work on models incorporating a more extensive set of assumptions, I always prepare different scenarios. In Excel, you can have all your assumptions in a separate tab and reference them in your other tabs. You can then easily add multiple assumptions tabs and use a dropdown to select which one to reference within your model. This gives you a robust scenario functionality which will be crucial in testing the impact of different assumptions on the overall financial model.
• Benchmark against industry data: This is pretty self-explanatory. If the company grew in sales at a CAGR (Compounded Annual Growth Rate) of 15% over the last 3 years, don’t directly assume that 15% is a reasonable growth rate for the future. Then you have to adjust your assumption for this period. It may turn out the whole market grew at 20% CAGR over this period, but this is projected to slow down to 5% for the next 2 years. Research and benchmark against market and industry data to ensure the assumption is realistic and reasonable. Otherwise, your model would become a dreaming exercise with little to no value for the decision-making process.

Mistake #4: Forgetting to Document

The most neglected part of creating a robust financial model is proper documentation. Easy-to-read and understand documentation is crucial for maintaining and updating financial models. Without adequate documentation, it can be challenging for external users (people who did not work on the model) to understand the inputs and assumptions, follow the logic and be able to work with your model down the line. Creating legends, adding descriptive comments, and including version control to ensure accurate and up-to-date documentation are very important, especially if you must revisit the model in a few months. This is where proper documentation can save you countless hours when you need to update your model. I am still baffled at how fast my mind forgets every model I work on. Pretty much every time that I open a file, I have the initial thought that I couldn’t have worked on it. No matter how straightforward everything seems while you are spending hours working on it, it always seems very alien a few months later.

Examples of Information to Document

Here are a few examples of information that should be documented:

• Data sources: The sales prices document is a CSV Martha from Sales sent you. But next year, when you need to update, Martha is no longer with the company, and her replacement has no idea which reports she exported from the ERP. For me, that’s happened too many times! 🤯Therefore, I always document the complete source of any data I use in a model to ensure I always know where data came from and how to get it.
• Assumptions: Same story as above – Mark from the business development team told you newly onboarded enterprise clients would grow at 25% next year, and you, being sure you will never forget, just wrote 25% in a cell and moved on with the much more fascinating work of building model logic. However, time will pass, and you will forget where the 25% came from. I had a bad experience when a VC asked me where a number came from. I blanked. The VC passed. Document the reasoning behind assumptions used in the model to ensure they are transparent and well-understood. And that no VC passes on a technicality!
• Model Logic: A financial model is a form of art (at least for me 😅). It often has ‘state-of-the-art’ logic that is easy to follow and understand. At least for you, it is. Whenever you present it to someone else, they usually feel lost in all the complex logic your brain gave birth to. And that’s completely fine! Building a robust financial model is a serious undertaking that, more often than not, requires coming up with complex model logic and flow. It would be best to document how data flows throughout the model and how you thought about more complex calculations.

Poor documentation leads to time-consuming updates and makes it a challenge whenever you need to update or amend your models. It can also lead to a significantly higher risk of errors and inaccuracies in the model. When you are preparing a model aimed at external users, poor documentation can also undermine the credibility of the model and the decision-making process within the company.

Tips for Effective Documentation

Here are a few tips for documenting a financial model effectively:

• Use comments and legends: Use comments to explain the purpose of inputs, formulas, and outputs in the model. I always have a last column with notes on various calculations and assumptions throughout my file. Additionally, when working on larger projects, I would have a separate doc file with a detailed written explanation of critical assumptions, model logic, model flow, etc. Setting this up takes a significant chunk of time, but it’s a massive time-saver. Another thing I am very strict about is the number colors. My models always have the input values colored in blue, black for calculations, and green for calculations using information from other tabs. Adding a quick legend for the colors and keeping the formatting consistent across the entire model helps users easily track and understand how everything works.
• Add diagrams: This is a simple but mighty one. Before opening Excel, create a flow chart of how everything will link within the model. It can be as simple as drawing 5 boxes on a piece of paper and showing which takes information from which others, or it can be much more complex (e.g., showing data sources, coloring different boxes/tabs differently based on their function, showing data flow, the logic of calculations, etc.). Having this in front of your eyes is a great way to ensure you are more focused when building your model. It also helps you think about future needs and ensure that your sales revenue forecasting tab is set up in a way that provides all the data you will later need to populate all the output tabs.

Conclusion

Whoa, that was a long post! You have my sincere thanks if you made it this far! 😎

If I have to put it all in a single paragraph, financial modeling is a crucial tool for making informed decisions within a business. However, building a robust and reliable financial model requires skill, experience, and attention to detail.

The good news is that avoiding some of the most common mistakes in financial modeling is pretty straightforward. You must keep your financial models as simple as possible, focus on data quality, always stress test your assumptions, and last but not least, document your work in a way that an external user can fully understand your model flow and logic.

Following these tips will significantly improve your modeling and help you make better-informed decisions within your business. And by avoiding inaccurate forecasting, you can help support effective decision-making within your company or a client’s business.