The Top Financial Modeling Mistakes: How to Avoid Balance Sheet Black Holes, Fake Projections, and Nonsensical Numbers

Top Financial Modeling Mistakes: The top financial modeling mistakes include an over-reliance on templates or a company’s financial statements, no step-by-step process for building and verifying the model, and entering assumptions that go against basic economics or financial reality. You can avoid these problems by following simple guidelines and getting practice building models for companies you follow.

The top financial modeling mistakes include an over-reliance on templates or a company’s financial statements, no step-by-step process for building and verifying the model, and entering assumptions that go against basic economics or financial reality. You can avoid these problems by following simple guidelines and getting practice building models for companies you follow.

“Financial modeling mistakes” are related to financial modeling best practices, but they’re not quite the same.

The “best practices” article covered points about how to set up models, how to follow proper linking and color-coding conventions, and so on.

All that is fine, but even if you follow every single guideline there, you might still produce a poor or unrealistic model.

These “best practices” are mostly about logistics, while the mistakes here are more about strategy and tactics:

To illustrate these issues and recommend solutions, this article will explain the 3 biggest mistakes we see with models created by students and early-career professionals.

Of course, a 2-hour, on-site 3-statement modeling test has a different set of pitfalls than an internal budget created for your company’s finance department that takes 1 month to finish.

This tutorial focuses on the shorter, simpler models often given in on-site case studies by banks and investment firms.

For each mistake, we’ll provide guidelines and fixes and give examples of exceptions and edge cases where the guidelines may not apply.

Files & Resources:

• Monster Beverage – Poor vs. Ideal Financial Statement Import (XL)
• Monster Beverage – Unrealistic vs. Realistic Assumptions (XL)
• Top Financial Modeling Mistakes – Presentation Slides (PDF)
• Monster Beverage – Annual Report (PDF) | Financial Statements (PDF) | Investor Presentation (PDF)

Video Table of Contents:

• 0:00: Introduction
• 1:24: Short Summary
• 4:30: Part 1: Over-Reliance on Templates and Company Statements
• 8:00: Part 2: No Clear Modeling Process or Guidelines
• 12:25: Part 3: Indefensible Assumptions
• 14:46: Recap and Summary

The Top Financial Modeling Mistakes: An Over-Reliance on Templates and Company-Provided Financial Statements

Some people label this one “building overly complicated” models, but the exact issue is slightly different.

The problem is not necessarily that you’re making the model overly complicated, but that you’re being inflexible.

For example, many students would look at Monster Beverages’ financial statements, do a direct import via Excel image recognition or copy/paste, and start forecasting the Balance Sheet based on the exact layout in the statements (L&E section shown below):

But this is not ideal because there are too many line items, and most are insignificant for the forecasts.

We recommend consolidating most of the Balance Sheet so that you’re left with 5 Liabilities and a single line for Equity:

In some cases, you might need to make the statements more complicated if you need more detail around certain items!

For example, it’s often helpful to split Revenue into different components, such as sales by product line, to better understand the key trends and drivers.

You always need to read the company’s financial statements, learn about its business, and pick the most important line items to project.

Some of these guidelines were discussed in the coverage of financial modeling best practices, but here’s a summary:

• Balance Sheet: Aim for ~5 items on each side of the Balance Sheet, with up to 10 for more complex companies. Consolidate aggressively (combine short-term and long-term versions of items, all components of Common Shareholders’ Equity, etc.).
• Cash Flow Statement: Similar; aim for ~5 or fewer items in each section, focusing on lines that apply to all companies, such as Capital Expenditures in CFI and Stock Issuances/Repurchases, Dividends, and Debt Issuances/Repayments in CFF.
• 10% Rule: If a line represents less than 10% of a larger category, such as Revenue or Cash Flow from Operations, you should probably consolidate it with other items and not worry about separate forecasts.

The main exceptions occur when you’re working with more complex companies or ones that operate across multiple industries.

For these conglomerate-type companies, it might not be possible to simplify the financial statements to this extent.

The Top Financial Modeling Mistakes: No Clear Modeling Process or Guidelines

Another common, related mistake is that students often start building financial models without a clear process or guidelines in mind.

They jump around randomly from document to document and forecast items based on whatever looks “interesting” or useful.

As a simple example, let’s say that you search Monster’s filings to figure out how you’ll forecast Revenue.

On page 90 of the 10-K, you find this split of the company’s sales by region and product line:

So, should you use this regional and product split in your forecasts?

No!

There are two main problems here:

1. Lack of Unit-Level Data – The company does not disclose units sold or average selling prices by region, so all you can do here is use simple growth rates.
2. Importance – Energy Drinks account for over 90% of sales, and the U.S. and Canada are far more important than the other regions, so don’t spend time on the other products or geographies.

A much better approach is to use the “Case Sales” and Average Net Sales per Case as key drivers, perhaps with Market Size and Market Share assumptions if they’re easy to find:

You can avoid these issues by using a process checklist and simple guidelines, such as:

1) Gather Data – If you’re starting from a blank sheet or entering the historical numbers yourself, get the company’s most recent annual and interim reports and investor presentation.

2) Enter the Historical Statements – Simplify and consolidate as much as possible, following the guidelines above (aim for ~5 items on each side of the Balance Sheet, with a max of 10).

3) Set Up the Revenue and Expense Drivers – Aim for 2 – 3 key drivers beyond basic percentage growth rates, if possible. If not, split the company’s Revenue into a few categories and forecast different growth rates for each one. Expenses should ideally be linked to “units,” such as employees, # stores, or production capacity, but if this is not possible, simple margin % assumptions are acceptable.

Example Revenue Drivers: Market Share * Market Size, Units Sold * Price per Unit, Capacity * Utilization Rate * Price per Unit, Locations * Sales per Location, Assets * Yield or Assets * Interest Rate on Assets.

4) Forecast the Financial Statement – Always start with the main Revenue and Expense lines on the Income Statement and then forecast the Working Capital lines on the Balance Sheet. Move to the Cash Flow Statement, link in the Change in Working Capital section from the Balance Sheet, and forecast the remaining CFS lines, such as CapEx. Then, link the Balance Sheet to the Cash Flow Statement (e.g., link Net PP&E to CapEx and Depreciation), and calculate the Interest Income/Expense on the Income Statement as the final step.

5) Check the Model – Make sure that the Balance Sheet balances, that Revenue growth generally declines over time, and that margins remain in stable ranges. Cash should always stay positive and above any minimum, and the company’s Free Cash Flow / Revenue should stay in a similar range over time.

The main exceptions here are for companies in highly specialized industries, such as commercial banking.

There, you change the order and start with the Balance Sheet, including forecasts for items such as Gross Loans and Deposits.

Also, in some industries, such as Exploration & Production within oil & gas, the projection methods are more complex because you need to forecast a company’s drilling activities first.

The Top Financial Modeling Mistakes: Using Unrealistic or Indefensible Assumptions

To illustrate this one, here are a few examples of unrealistic assumptions taken from the “incorrect” version of the Monster model:

The main problems here are:

1. Revenue Growth Should Generally Decline Over Time – Outside of major new spending initiatives, increased CapEx, or acquisitions, markets and products tend to grow at slower rates as their absolute sales volumes increase.
2. Growth and Margin Trade-Offs – There should always be a trade-off between growth and margins. If the company wants to grow more quickly, it should have to spend more to fund that growth, not less. Growth rates and margins very rarely increase at the same time.
3. CapEx and D&A – If a company is growing at even a modest clip, CapEx should exceed D&A because its asset base must increase to support that growth. CapEx and D&A should not be equal because that indicates the company is operating in “maintenance mode” and not growing its productive capacity at all.
4. Change in Working Capital – This should follow historical trends, so that if it was negative when the company’s sales grew, it should remain negative in the future. The trends here are questionable because the Change in WC becomes more positive as the company’s sales growth accelerates, which is normally “too good to be true.”

There are some exceptions to these rules, mostly for specialized industries.

For example, commodity-based businesses like steel producers cannot control their prices, so if the price of the underlying commodity increases significantly, revenue growth and margins might increase at the same time.

Also, if a company is declining or only expects to operate for a fixed amount of time, it won’t necessarily follow the rules above.

For example, a single-product biopharmaceutical company with an expected 20-year useful life might see increasing revenue growth and margins in the early part of its lifecycle but declines in both in later years as its sales fall.

The Top Financial Modeling Mistakes: Smaller, More Specific Issues

Besides the points discussed above, there are a few smaller, more specific issues that come up in many 3-statement and LBO models:

Cash and Borrowing

In all but the very simplest models, you should build in the ability for the company to issue new Debt so that it stays above its Minimum Cash (or at least keeps its Cash balance positive).

The formulation here is simple:

Preliminary Cash = Beginning Cash + CFO + CFI + Non-Debt Lines within CFF

If this Preliminary Cash is above the Minimum Cash, do nothing or assume modest Debt Repayment.

If not, assume additional borrowing to bring the company up to its Minimum Cash.

LTM, Stub Period, and Mid-Year Complexity

We’ve covered concepts like the Last Twelve Months, the stub period, the mid-year convention, and quarterly models before, and the bottom line is that you should avoid these in modeling tests unless they specifically require them.

All these issues complicate models and add to the time required without necessarily getting you “extra points” in interviews.

So, use annual periods and the company’s most recent annual report for standalone models and valuations. And assume that any deal closes at the end of the company’s next fiscal year.

Links and Separate Schedules for Balance Sheet and Cash Flow Statement Lines

It’s easiest to illustrate this one with a simple example.

Let’s say that you are forecasting Net PP&E for a company based on a full CapEx and Depreciation schedule.

Your schedule breaks out CapEx by segment, forecasts Depreciation for the CapEx spent in each year, and calculates the Net PP&E at the bottom:

This is fine, but you should NOT link the Net PP&E on the Balance Sheet directly to the number in this schedule.

Link the Net PP&E to the CapEx and Depreciation lines on the Cash Flow Statement, which can come from this schedule.

You always want to be able to properly audit a financial model, which means that there must be corresponding links on the CFS for each BS line, and links on the BS for each CFS line.

If one item is linked only to a separate schedule, you are asking for trouble if the model ever changes.

The Repayment Order in Debt Schedules

There are many different types of Debt Schedules, and in some cases, you can set them up without necessarily caring about the “repayment order.”

For example, if a company has 5 Senior Note tranches (all the same seniority) or a single Term Loan, this point doesn’t matter.

In LBO models, however, the repayment order is quite important because the Revolver should always come first, followed by Term Loan A, Term Loan B, Senior Notes, and then everything below that:

In each level, the optional repayments or cash flow sweep should be limited based on the available cash flows minus everything that has been repaid optionally above this tranche.

All mandatory or “scheduled” repayments should also take precedence over optional  ones.

If you do not set up the tranches sequentially, it is very easy to make a mistake with the repayments.

Unnecessary Schedules

Finally, be careful about adding or completing unnecessary schedules in models.

For example, something like the purchase price allocation schedule may be useful or even required in a merger model, but you can easily skip it in an LBO model.

This is because most of the items created in the PPA process, such as Goodwill and Other Intangibles, do not make an impact on the PE firm’s cash returns in the deal.

Similar principles apply to concepts like the treatment of financing fees and original issue discount.

Yes, the accounting is interesting to learn, but none of these items affect your investment decisions.

Focus on the key drivers that affect the company’s revenue, margins, and cash flows; everything else is noise.