The 13 Financial Modeling Errors to Avoid If You're a Pro

How many times have you used algebra to solve a circular reference? +++ Read to the end for a giveaway to hone your spreadsheet skills!

TL;DR?

If you prefer listening over reading, tune into NotebookLM's Audio Overview for an engaging and convenient experience. It’s the perfect way to absorb the key insights of my article while on the go—whether you're commuting, working out, or relaxing.

Financial Modeling in Times of COVID-19 Uncertainty

Projections about the spread of the Coronavirus and its long-term implications on the global economy are uncertain. In other words, it is a nightmare for those of us working in the area of FP&A (financial planning and analysis).

The current economic impacts are of unprecedented scale, and they push the limits of many existing financial models to the point that the accuracy and reliability of their outputs suffer.

A virus is not a suitable input variable for financial forecasts. Hence the revision of existing financial models must take into consideration multiple changes in business drivers – all with significant impact on income statement, balance sheet and cash flows.

Financial models are built with some basic assumptions of input ranges in mind. For example, the relation of certain COGS items to revenues may hold true for a variation of revenues by, let’s say 20% up or down.

But what happens when revenues abruptly decline by over 80% or even 90%, for example in particularly hard-hit industries such as aviation? At the same time, the situation evolves quickly. As one of my colleagues said: “Another day, another sensitivity analysis. Another week, another scenario calculation.”

Particularly in times like these, every analyst hopes – more than ever – that their financial models are robust and free of substantial errors. The unfortunate hard truth is: Many models aren’t. But, if there’s a shadow, there must be light: Now is the time to retreat to your home office, block out all distractions and give your financial model a complete overhaul.

There are a few Spreadsheet Errors, How To and Best Practice lists out there. And yet, I still encounter these mistakes again and again when reviewing financial models.

Hence I have compiled what I would like to see as the ultimate list of financial modeling errors (and how to avoid them). Trust me, in my journey as a financial analyst I have previously made many of these mistakes myself.

Fatal Financial Modeling Mistakes

"The reliability of a spreadsheet is essentially the accuracy of the data that it produces, and is compromised by the errors found in approximately 94% of spreadsheets."

(Ruth McKeever)

Over the years, I have built and reviewed dozens of financial models. As a result, I gained detailed knowledge of how they work. My exposure to this field is quite extensive. In my early career, I helped develop a financial modeling support tool at EY. I also teach financial modeling to MBA students and financial practitioners.

The process of building a financial model in Excel is a complex one. It can be frustrating if the model continues throwing errors. Especially when you spend much time setting up the formulas and conditions.

To avoid typical mistakes, a good financial analyst follows certain procedures and discipline. It is important to understand possible errors you are likely to make. This article will explain major mistakes made with financial modeling and how to avoid them.

What is a Financial Model

"All models are wrong, but some are useful."

(George Box)

A financial model is an abstract presentation of a financial problem. You can use the model to solve business and economic problems.

For example, you could create and present a 5-year financial plan to equity investors. Or a 12-months budget to a board of directors. A short-term cash model for a bank to secure an overdraft extension is a financial model as well. The way you create and present the model is important. It could mean the difference between a signed deal and no deal.

It is difficult to overestimate the importance of correct financial models. In 2005, the FSA even stated that “Spreadsheets are integral to the function and operation of the global financial system”.

Given the importance of financial models, understanding the potential errors is critical. You must understand the common mistakes people commit and how to overcome them. When using Excel to develop a financial model, adhere to guidelines minimizing spreadsheet risks. Also, your goal in creating financial models should be to achieve consistency in the team or company.

With this in mind, the financial modeling mistakes to avoid if you're a pro are:

1. Failure to Plan

When creating a financial model, most tend to overlook its impact. They fail to think through how the model will work. They don’t think what its users, applications, and scope will be. Others do not consider the kind of information and reports needed to develop the model. Failure to make considerations can over-complicate a model. Possible issues include providing unneeded information or missing out on critical components.

To paint a mental picture of how this works, think about building a house without planning. If you do not have a plan beforehand, you will likely encounter unforeseen problems. The entire project may end up being a failure. Similarly, your financial model will be disconnected from the operations of the business if you spend inadequate time preparing and developing it.

To avoid committing this error:

• Identify the financial problem the model will solve
• Identify the end-users of the model and how they should use the model
• Identify the needed inputs, processes, and outputs of the model
• Keep inputs in one place for quicker and easier access during the development stage
• Plan your model structure adequately

You will have to constantly monitor whether the underlying assumptions of your plan continue to hold true throughout the modeling process. As my friend Vincenzo Melpignano rightfully points out, situations change, and hence, the model-building analyst should always work together closely with the business resp. client during the entire project.

2. Lack of a Logical Structure

Most financial models have several sheets, and you must logically organize them. Haphazardly arranged sheets can be difficult for the users to navigate between. They make it difficult determining which part you wrote in one sheet and which you presented in another.

To overcome this weakness, identify the factors affecting the model and elaborate on different sheets. Present the content in a way that users can transition between sheets with ease. Make sure the flow within each sheet is natural, from left to right and from top to the bottom.

Your model should have three basic components:

Assumptions → Calculations → Output

Following a logical structure has several advantages. First, it offers a consistent and reliable architecture. This allows users to determine which areas they should work in and which the computer works in. Second, it mitigates errors by storing all assumptions in one place. This way, it is less likely to forget to remove old assumptions in earlier analyses.

3. Building Overly Complex Models

An informed model is good for your business since it provides more information. But the more assumptions you make, the less realistic your decisions are. It is difficult to assume anything far into the future. Making accurate assumptions of dozens of different factors takes longer and may not yield desired results. In many cases using ten to fifteen key assumptions gives more accurate results.

When building a financial model, reduce implicit assumptions to a minimum. You may build some implicit assumptions into the model. But explicit ones will require input fields and formulas for more flexibility. Your goal should be to find a balance between the model’s flexibility and complexity. In particular, if you are an entrepreneur, you should spend some time to evaluate the level of complexity your financial model may require.

4. Excessive Use of Complex Formulas

Long, nested formulas are difficult and time-consuming to follow through. A good financial model should be easy to follow and interpret. The rule of thumb is that most formulas should not exceed half the length of the formula bar.

The longer a formula is, the more likely you will make a mistake. To avoid this, simplify and reduce the number of steps in a formula. Choose simple and doable steps to check and pinpoint any errors present. Excel’s functionality provides many ways of simplifying financial models.

The complexity of a formula starts with how it looks. If your reader perceives the formula to be complex, the model is complex. First impressions count. The first 30 seconds are enough for a reader to give positive or negative feedback for the model.

Resist the temptation to showing off your skills by creating complicated formulas when using Excel. Strive to make it simple for your readers. If it is impossible to create a simple formula, look for ways to break it down into multiple cells.

When using Excel, you should:

• Use flags
• Avoid nested IFs
• Simplify IF statements using alternative functions
• Consider logics like MAX, MIN, OR, AND
• Consider functions such as HLOOKUP, VLOOKUP, INDEX, OFFSET (the latter with care!)

5. Formula Errors

Yes, that's a thing! Errors related to formulas are the easiest to make. They may also be the hardest to find. For example, it is easy to miss a piece of data when preparing the formula. This could be because the formula did not copy across right. Or it could be that a certain range of information is incomplete.

Whichever the reason, formula errors contribute to the inaccuracy of your financial model. Because of this, you must be careful when writing or changing a formula in an existing financial model. Take extra care when inserting columns or rows near the edge of ranges or where the references in the cell are fixed.

6. Using Hard Values Instead of Formulas

This is a classic, yet repeated error. The general rule is to never hard code. If you must, make sure you insert a comment against the cell with an explanation and where the number has come from. The biggest problem with hard values is that they make the formula more complex. You may remember the value when creating the model but forget something about it when you come back.

According to financial modeling expert Karl Gezer, there are many pitfalls of hard coding inputs. Besides a reader finding it challenging to follow the model's logic, the assumptions become hidden. The task becomes even harder if the document is printed or otherwise presented. Keeping inputs visible in the margins, or even better as a separate Inputs/Variables worksheet, enhances the flow of reasoning throughout the model. The temptation to save time by hard coding formulas demonstrates a lack of coding discipline, something which is fundamental to reliability and transparency. It is also the rookie mistake of inexperienced analysts. You may not know where the data is coming from, and it may be impossible to see the financial impact of changes in the assumption.

The only time you should use hard values is when dealing with underlying inputs and assumptions that drive elements of the model, such as costs and revenues. It is good practice to clearly and consistently formatting these cells as 'Inputs'. Doing so will immediately increase a reader's confidence in the reliability of the data..

7. Excessive Use of Names and Range Names

Many modelers try to reduce the complexity of their formulas by using names and range names. But there is always some form of a trade-off when using Excel. When you name a cell, the trade-off is when you no longer know its location without checking on the name manager. Excel will retain these names, unless you are proactively deleting them. This means you could end up building a formula containing dozens of phantom names. And this could make your model confusing.

But the use of names and name ranges can create even more problems. First, it is time-consuming and may delay the development of the model. Second, you will run out of alternatives fast and end up using redundant and ambiguous names. Third, the model will become more difficult to review because of trace precedents.

8. Hiding Rows and Columns

Obvious as it seems, hiding rows and columns is another fatal error. In a model, you will have rows with data and calculations unwanted in the final version or presentation. You may be tempted to hide them to achieve a cleaner presentation. The problem is you may miss crucial data or make an inaccurate presentation. It may also result in incorrect calculations and inconsistent copy/pasting of formulas. Rather than hiding rows, group them and do it sparingly.

9. Inconsistent Formats

The formats in a model should be consistent both when using and presenting it. The sheets will have the same year-by-year, quarter-by-quarter, or month-by-month profile. You should make sure that the column headings are identical across the sheets.

Choose the same fonts, bordering, descriptions, labels, and style where possible. This is important for consistency. If the rows and columns are not clearly described, you might confuse the reader.

Formatting each sheet in Excel differently is a big mistake. It makes the model look irregular and increases chances of an error. When preparing the sheets, format in a clear and consistent manner. Consistency builds a high level of comfort for the reader. And reader will be more comfortable referring the model to a friend if they understand it.

A good financial modeler will minimize the number of unique formulas in a model by using the same formula across a row. This will make the model easier to change, less prone to errors, and simpler to test and review.

10. Not Fully Integrating Balance Sheet and Cash Flow Calculations

I assume that you are beyond the level where you still make fundamental accounting mistakes such as thinking that profits equal cash-flow. You know that the balance sheet and cash flow statements are always important in a financial model. But you may still think that you can leave them out for calculating isolated business cases or building early start-up models. But they play a crucial role in the process.

Failure to integrate balance sheet and cash flow calculations means that you are overlooking your stock turnover, creditor days, debtor days and more. Not modeling these correctly means difficulty forecasting the cash flow position of the business with confidence.

11. Lack of Systematic Naming Convention for File Versions

The naming conventions for your file versions should be clear. In particular, the alphabetic and numeric order of your version names should match the chronological order of your file. Make sure the format you use is consistent. And avoid using name additions like “latest” or “final.” There is no need to include the date in the file name.

12. Not Adding an Executive Summary

Adding an executive summary makes the model more understandable. It allows the reader to understand the key assumptions, drivers, and other factors immediately. The summary provides a snapshot of the model. It allows the reader to decipher the key findings and the underlying logic quickly. On your part, the financial summary will help you determine the focus for presenting your financials.

The executive summary is where you outline the considered key drivers and assumptions. You should include various accounts like profit and loss, balance sheet, and cash flow in the form of a graph.

Your goal should be to make the life of the model’s user easier. Present the information in a way that allows users to get the level of information they want with ease. Consider including a table of contents, instructions, explanations, and guidance. These make it easier for the reader to use the model.

13. Lack of Error and Sanity Checks

It is easy to set up automatic error checks and checking your model using the audit function in Excel. Of equal importance are the sanity checks. These can be applied to aspects like average revenue per salesperson. They help answer questions like, can you really double your sales by adding an extra salesperson to your team of two?

When working on financial models, check for errors at regular intervals. The Excel auditing function will help you assess the entire model.

Sanity checks will point out fundamental errors in the model. Suppose your model shows adding a salesperson to an existing group of two would triple sales. Such an outcome looks outrageous prima facie. The sanity checks will help verify it.

Similarly, it would not make sense when your projected revenue per employee is $400,000, while the industry norm is $150,000. And the sanity checks will prevent these errors.

Since financial models are usually complex, testing is critical. Even though a test may not prove the model is error-proof, it can show if there are errors in the model. Don't just check the model yourself but get someone else to have a thorough look at it. Some important tips for conducting checks on the model include:

• Understand the purpose of your model
• Utilize the auditing functions of Excel’s formula
• Use pen and paper to outline the structure of the model; break down the complicated formulas
• Review the model’s code in detail, from line to line and sheet to sheet
• Change the model inputs and see what happens. You can use testable values like 0, 1, 100, etc.

Who should do the model testing? Ideally not the same person that built the model in the first place.

Financial modeling expert Dr. Achim Korten even suggests that all financial models that are used as a basis for strategic decisions should undergo an independent third-party review: “A thorough model review requires checking every single formula and cell-by-cell recalculation is time-consuming. If this happens internally among colleagues, objectivity may be at risk. Adding usual time pressure an internal check is quite prone to errors.”

He also rightfully states that by applying a systematic approach to testing and the use of special testing tools, an external consultant can identify model errors with a much higher probability.

Additionally, the result of the review is documented in writing and made available to decision-makers and potential investors, who rely on the model to make their decisions.

Summary

Building a financial model with Excel is a complex task. With so many considerations to make, you will likely make certain errors.

Among the most common and fatal errors include failure to plan, lack of structure, use of unnecessarily complex formulas, using inconsistent formulas or formats, and more. Look out for these to create both a useful and practical model to your users.

What are your thoughts about these errors? Are there any significant errors that I have missed? Leave a short comment and I will share with you an Excel-file with a handful of spreadsheet brain teasers I use when teaching my financial modeling classes. I am sure you will enjoy these little exercises. And if they give you a new modeling idea or two, then the file's job is done.

Special challenge: So far, only around 10% of my students were able to solve a typical circular reference problem using algebra in class. Even though I teach some very bright students and practitioners, most people tend to fall into the trap of forgetting math’s basics when working in Excel. Does this happen to you too?