Balance Sheet Not Balancing: Batman’s Guide to Fixing Your Balance Sheet

Balance Sheet Not Balancing Definition: A Balance Sheet goes “out of balance” when Total Assets does not equal Liabilities + Equity; you can follow the 4-step process outlined here to fix this problem methodically, but you should also consider simplifying and consolidating your financial model if you keep running into this issue.

Whenever we receive questions about the Balance Sheet not balancing in a financial model, the first thing that comes to mind is Batman: The Dark Knight.

Specifically, I think about the ending sequence, where Jim Gordon explains that Batman is “the hero Gotham deserves, but not the one it needs right now”:

The same could be said about explaining and fixing a broken Balance Sheet.

The answer you deserve is that if your Balance Sheet is out of balance, your model is overly complex and does not follow best practices.

We have a full guide to financial modeling best practices, so you should refer to that and revise your model accordingly, time permitting.

Most likely, your Balance Sheet and Cash Flow Statement are far too complex; by simplifying the BS down to ~5-6 items on each side, you’ll make it 10x easier to link and balance.

But then there’s the answer you need right now.

Let’s say it’s 3 AM, you’ve just finished your last Red Bull, and the coffee machine is broken.

You need to fix your broken model ASAP before you can go home and get a few hours of sleep.

In this case, there is a step-by-step process to fix a broken model, even if your Excel file is set up in an overly complicated or non-ideal way.

A summary of the steps follows. Below, we’ll go through an example of a broken 3-statement model and explain how to follow these steps to fix it.

• Step 1: Look at the Discrepancy Each Year and Note Any Patterns – Is the Balance Sheet out of balance by a constant amount? Does it vary? Does it change by the same amount each year? These differences can hint at the problems.
• Step 2: Review Each Line of the Balance Sheet – Check to make sure that each BS line item is either reflected on the CFS once and only once with the correct signs, or that it flows in from CFS line items with the correct signs. Highlight in yellow the properly-accounted-for items on both statements as you progress.
• Step 3: If You Finish the Balance Sheet, But It’s Still Not Balanced, Review the Cash Flow Statement – Focus on non-zero, unhighlighted line items because these are the ones that haven’t yet been accounted for on the BS. Also, check the Ending Cash numbers in the historical periods and the Net Change in Cash links.
• Step 4: If Nothing Else Works – If you still cannot find the problem after all of this, consider simplifying the model to reduce the number of line items and links. It takes time to consolidate, but it’s faster than going through this process multiple times.

Files & Resources:

• Balance Sheet Not Balancing – File with Errors (XL)
• Balance Sheet Not Balancing – Corrected File (XL)
• Balance Sheet Not Balancing – Step-by-Step Process to Fix (PDF)

Video Table of Contents:

• 0:00: Introduction
• 4:53: Step #1: Review the Discrepancies
• 7:00: Step #2: Do a Line-by-Line Balance Sheet Review
• 19:48: Step #3: Review the “Unconfirmed” CFS Line Items
• 24:17: Step #4: If Nothing Here Works…
• 24:45: Recap and Summary

What Makes a Balance Sheet Go Out of Balance?

The Balance Sheet in a 3-statement model can go out of balance for a range of reasons, but most problems fall into one of three categories:

1. Forgetting to Link and Reflect a BS or CFS Line Item on the Other Statement – This is very common when your statements are too complicated.
2. Double-Counting a Line Item on the Other Statement – For example, many people get confused about what goes in the “Change in Working Capital” section on the Cash Flow Statement and end up adding or subtracting items twice.
3. Linking to Items with the Incorrect Signs – On the Assets side, you always subtract CFS line items that flow in, and on the L&E side, you always add CFS line items that flow in.

Another common issue is that people sometimes link Balance Sheet line items, such as PP&E, directly to external schedules, such as a CapEx and Depreciation schedule, rather than lines that are directly on the Cash Flow Statement.

This is extremely poor modeling practice and should be avoided at all costs. Even if it’s 3 AM and you’re out of Red Bull, change the model to fix this.

One “different” problem is that historical statements are sometimes incorrect or inconsistent, which can result in the projections being off – even if the historical Balance Sheet balances (see below).

Step 1: Look at the Discrepancy Each Year and Note Any Patterns

At the bottom of the Balance Sheet projections, take Total Assets, subtract Total Liabilities & Equity, and note any patterns:

This is useful because these patterns can give you a hint at the problem(s):

• Constant Difference: If it’s a constant difference each year, such as +10 or –10, usually it means that you forgot to link an item in a single year, but it is linked correctly or $0 after that.
• The Difference Changes by a Constant Number – This usually means that an actual formula is wrong, but only in one specific period, such as in Year 1 of the forecast.
• Variable Difference with Variable Changes: If the discrepancy changes each year, and there’s no pattern to the changes, it means that one or more of the formulas is wrong throughout the forecast period (harder to track down).

Why We Don’t Normally Create “Difference Columns”

Some trainers suggest creating a “difference column” for the Balance Sheet to plot changes in each line item and compare these changes to the overall discrepancy.

This is a nice idea in theory, but it’s difficult to use in real life because fixing a broken Balance Sheet is rarely as simple as spotting a single forgotten line item.

In our experience, it’s usually a more complex issue or multiple errors, which makes this approach less useful.

Step 2: Review Each Line of the Balance Sheet

Start with the Assets side of the Balance Sheet and move to the Liabilities & Equity side.

SKIP any line items that stay constant in every single year, such as Goodwill in many models – they cannot be directly causing the Balance Sheet to go out of balance.

They may indirectly cause problems, but you can identify and address these issues in the next step of the process.

For each Balance Sheet item that changes over this period, check the following:

a) Is this change reflected somewhere on the CFS, as with many Working Capital line items? And if so, is the sign correct, such as taking Old Asset – New Asset and New Liability – Old Liability to calculate the changes on the Cash Flow Statement?

b) If not, does this line item correctly link to the matching CFS line items, with the correct signs? On the Assets side, you always subtract CFS lines, and on the L&E side, you always add CFS lines.

c) If one of these is true, mark the line items as “correct” by highlighting them in yellow on the BS and CFS.

If the line item is not correct because it’s missing a link, it uses the incorrect sign, or it’s double-counted, make the correction and then highlight the relevant lines as being correct.

d) If you get a “collision,” fix it – If you’re checking a Balance Sheet line item and you find something on the CFS that has already been linked to a previous Balance Sheet line, you have a double-counted item.

Fix this by removing one of these links and then highlighting the relevant lines.

To do this in Excel, the Ctrl + [ and Ctrl + ] shortcuts are very useful for selecting direct precedents and direct dependents.

We used the approach above to find an error with the Other Long-Term Assets:

Step 3: If You Haven’t Fixed the Balance Sheet Yet, Review the Cash Flow Statement

If you’ve finished the Balance Sheet but haven’t made it balance yet, move to the Cash Flow Statement.

Focus on non-zero, unhighlighted line items because these are the ones that haven’t yet been accounted for on the BS.

By definition, they must be responsible for the discrepancy.

You verify this by pressing the Ctrl + ] shortcut on each one to select direct dependents and see what it flows into, if anything.

If you find an item on the CFS that is not linked to anything on the BS, go back to the BS and link it to the most appropriate line.

If you can’t figure out what it matches, link it to Equity as the “catch-all” (a bit of a hack, but it works for last-minute fixes at 3 AM).

In this model, we found an issue with Deferred Taxes by using this approach:

After fixing both these mistakes, though, the Balance Sheet was still not balancing:

In cases like this, one common issue is that historical statements are not accurate due to inconsistent data and rounding errors.

It’s also worth checking the “Net Change in Cash” calculations in all periods, but for a small, constant difference like this, it’s more likely an issue with the historical statements.

One common problem is that the Cash at the bottom of the CFS may not match the Cash on the Balance Sheet:

This problem is tricky to locate because the historical Balance Sheet still balances – since the Cash there is hard-coded and not linked to the Cash Flow Statement.

But since the Cash number in the first projected period starts from a slightly different baseline Cash, the Balance Sheet will immediately go out of balance in Year 1.

Step 4: If This Still Doesn’t Work…

If you still cannot find the problem after following this entire process, consider consolidating and simplifying the model to reduce the number of line items.

Simply by reducing the line items on both statements, you often “fix” these issues because you end up deleting the errors.

Yes, it takes more time than reviewing the statements and looking for errors, but after a certain point, it’s faster to aim for the answer you deserve rather than the one you need.