Balancing the balance sheet in a three-statement model can be a frustrating experience. This lesson and the associated video will walk through the most common errors made and demonstrate how to fix them quickly.

A model becomes unbalanced when the accounting equation is unbalanced. This is why the accounting equation should always be included as a “check” in every three-statement model. In the image visible below, you will see the check highlighted in yellow. The objective is to have a value of zero for every accounting period. Anything other than a zero suggests an error. 

The challenge is that these errors can be difficult to identify, especially as models grow in detail. Consequently, it helps to have a process that can quickly identify the source of these errors. Fortunately, most errors are derivative of three common mistakes.

FIRST ERROR:

You may recall from the series titled Introduction to Financial Statements, that every change on the balance sheet must be reflected on the cash flow statement in order to maintain the accounting equation. If a change on the balance sheet is not recorded on your cash flow statement, your model will not balance. 

It follows that if a line item visible on the balance sheet is missing from the cash flow statement, your model will not balance.

So, let's look at an example, and recall that when we first built this model, we did not properly project prepaid expenses. Instead, this line item was straight-lined through the projected period. If we update this line item, and project it as a percentage of COGS, it unbalances the model (see image below).

For the purpose of this exercise, let’s also pretend that we don’t know which line item is creating the error. To identify the row creating the imbalance, first add a new column to calculate the difference between the value of each line item on the balance sheet in the year that the imbalance occurs and the previous year. 

Note: You always start with the first year in which the imbalance occurs. If the check in the model above had a value of zero in period 20X3, then the column titled “Difference” would calculate differences between the periods 20X4 and 20X3 (vs. 20X3 and 20X2).

With this column created, the second step is to compare these values to the values in the associated period on the cash flow statement. In the image below you will note that the first two values are quickly identified (see long red arrows), but that the third value of 552 is missing from the cash flow statement.

This is sufficient to confirm that the Prepaid Expenses line item is creating an error in the model. The fact that the model is unbalanced by this same amount also confirms that it is mostly likely the only error in the model for that accounting period.

Now that we have identified the source of the error, we can easily fix it. First add a row to the cash flow statement for Prepaid Expenses, and then project this line item in the same manner that all current assets are projected on the cash flow statement.  

This will immediately result in a balanced model, and now you will see that the value of 552 is visible in both the column titled “Difference” and on the cash flow statement.