12. Keeping the Model Balanced

Overview

Each change to your projections should impact the balance sheet in two ways to keep it balanced. This is particularly helpful when debugging errors.

To explore more Kubicle data literacy subjects, please refer to our full library.

Summary

  1. Lesson Goal (00:04)

    The goal of this lesson is to learn how changes to the model affect the Balance Sheet.

  2. Creating a Balance Sheet Check (00:11)

    The Balance Sheet should always stay balanced when we make changes to the model. In other words, total assets should always equal total liabilities and equity. 

    We can ensure this is the case by creating a balance sheet check. This is a row at the top of the Balance Sheet that subtracts total liabilities and equity from total assets. This row should equal zero at all times. If it does not equal zero, then our financial statements contain an error that we need to fix.

  3. Adjusting the Financial Statements (00:55)

    When we adjust financial statements, for example by editing the assumptions, it should affect the Balance Sheet twice, and both effects should balance each other out. Understanding the impact of adjusting your assumptions makes it easier to understand and debug financial models.

    For example, if we reduce the inventory assumption, then we’ll find that our inventory reduces, but our cash balance increases. This keeps the Balance Sheet balanced. As another example, if we increase our deferred revenue assumption, then deferred revenue (which is a current liability) increases and cash (which is a current asset) increases as well. This keeps the Balance Sheet balanced at all times.

  4. Formatting the Workbook (03:34)

    After we’ve completed our financial model, it’s a good idea to make some formatting changes to it. These changes make it easier for a new person to come along and understand the model, even if they didn’t create it.

    Our model contains constants in the cells that refer to previous years, and formulas in the cells that refer to future projections. Formatting these differently makes it easier to understand the model. We can select all the constants in the model by selecting all the cells in the Financial Statements, then opening the Go To dialog box, with F5. If we select Special, then Constants, this selects all the constants in the model. We can then apply whatever formatting we want to these constants, such as changing the text color.

Transcript

Now that we have our financial projections complete, let's examine how the model works in a bit more detail. At the heart of any financial model is the Balance Sheet. The Balance Sheet must stay balanced regardless of the changes that we make to the model. When I build a model, I often create a balancing check, which is just the difference between total assets and total liabilities and equity. At all times, the figures for the Balance Sheet check should be zero. So I create it by writing a formula that equals total assets minus total liabilities and equity.

I copy this across for the remaining cells.

Whenever we change an assumption or an input, it should affect the Balance Sheet twice so as to keep both sides of the Balance Sheet balanced. For example, if we acquire a new piece of equipment, PP and E will go up, but the cash balance will go down, thereby maintaining the balance. When making edits to your assumptions in the model, it's always worth asking what two different impacts this assumption change will have on the Balance Sheet? Currently on the Balance Sheet, for 2016, we have cash and cash equivalents of 79.3 and an inventory of 7.3, giving us total current assets of 91.7. I'm going to change my inventory assumption. So, skipping up to the top, I'll reduce inventory to 30% of cost of sales for 2016.

And now, if I scroll down, thankfully, my Balance Sheet check is still working, cash and cash equivalents have bumped 1.6 million, and inventory has been reduced by 1.6 million.

And, in this way, the Balance Sheet stays balanced. And I press control zed to change back my assumption.

Let's now take another example of deferred revenue. If I boost my deferred revenue to, say, 50% of revenue, and scroll down to my Balance Sheet, again, the Balance Sheet check is still in place. My cash has jumped to 86.4, and my deferred revenues have jumped to 25.1.

So, increasing deferred revenues increases the current asset of cash and increases the current liability of deferred revenues.

If you can understand the underlying impact on the Balance Sheet for every change that is made in your assumptions, like I am doing now, then interpreting and debugging financial models will come very easy to you.

Using this completed model, I'd encourage you to experiment with changing some of the assumptions and to understand how each of these changes affect the Balance Sheet in two different ways. Before we wrap up this lesson, we have some formatting to do which will help a colleague who has not built the financial model but would like to use it. Our financial statements contain a combination of constants and formulas. Let's format these differently to help a new user understand what we've calculated. To start, I'll simply select all of the cells related to the Income Statement, Balance Sheet and, of course, a statement of cash flows.

I'll then press F5, which opens the Go To dialogue box and I'll hit the Special option. And this allows me to select constants.

I'll then press OK, and, as you can see, this command selects all of the constant values in my model.

I can now format these differently, say, by changing the text color to blue.

Maybe a darker blue than this.

And, as you can see, this makes our model a little easier to understand, although we may need to do some additional formatting to make it entirely consistent.

In the next course on financial statements, we're going to look at these projections in a bit more detail and use them to place a valuation on MarkerCo for a would-be buyer of this business.