8. Projecting the Balance Sheet Part 2

Overview

In this lesson, we project the future assets and liabilities for TrackerTime, over the next 5 years.

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 create a projection for the Balance Sheet using our assumptions.

  2. Projections for Current Assets (00:21)

    Once we have created assumptions, we can create projections for the Balance Sheet. Trade and other receivables is assumed to be a percentage of revenue, so we calculate the projection for this figure by multiplying the percentage assumption by revenue from the Income Statement projection. Inventory is assumed to be a percentage of cost of sales, and can be projected in a similar way. Cash is not projected until we have completed the projected Statement of Cashflows.

  3. Projections for Non-Current Assets (01:21)

    In our non-current assets section, long-term investments and other non-current assets are both projected using a growth rate. To calculate the projected values, we multiply the value for the previous year by the assumed growth rate. The non-current assets section also includes Property, Plant and Equipment, but this cannot be projected until we have projected the Statement of Cashflows.

  4. Projections for Current Liabilities (02:26)

    In the current liabilities section, trade and other payables is assumed to be a percentage of cost of goods sold, so we calculate the projection by multiplying the percentage by cost of goods sold. Accrued expenses is assumed to be a percentage of SG&A, which is the sales and marketing expense plus the general and administrative expense. Finally, deferred revenue is assumed to be a percentage of revenue. In our model, short-term debt is assumed to be zero every year.

  5. Projections for Non-Current Liabilities (04:05)

    Our model contains two non-current liabilities. Other non-current liabilities is calculated using a growth rate, while deferred tax liabilities is assumed to be a percentage of the income tax expense. This calculation can cause an error if the income tax expense is zero. To deal with this, we calculate deferred tax liabilities using an ISERROR function inside an IF function. This returns a value of zero if the deferred tax liabilities calculation returns an error, and returns the correct amount for deferred tax liabilities otherwise.

Transcript

In the previous lesson, we created balance sheet assumptions that would help us project the absolute values in our balance sheet for 2017 to 2021.

We will now enter these values in our balance sheet projection in this lesson.

And we'll start with trade and other receivables.

Which is typically calculated as a percentage of revenue. So I'll scroll up find trade and other receivables and multiply by revenue.

And I'll copy across for the remaining cells.

Inventory is calculated as a percentage of cost of goods sold.

So I'll take the percentage and multiply by the figure for cost of goods sold.

And again I'll copy across.

I won't calculate cash and cash equivalents until the very end of my model. So instead, I'll simply sum the current values.

And then move on to non-current assets.

Again, property, plant, and equipment I don't calculate until my cashflow statement is complete. So I'll focus on longterm investments and other non-current assets first.

Both longterm investments and other non-current assets are based on a growth rate from the previous year. So I'll simply take the previous year and multiply by one plus the growth rate, which I can find in my assumptions.

Again I'll copy across and also copy down.

And again, I'll sum to find total non-current assets.

And to find total assets, albeit missing capex and cash, I'll add non-current to current.

Next, I'll look at current liabilities. Starting with trade and other payables.

So I'll scroll up to my assumptions and find trade and other payables which is multiplied by cost of goods sold.

And again I'll copy across.

Accrued expenses will have a percentage of 12 percent multiplied by SG&A, which is the sum of the following three values.

And again I'll copy across.

Next is referred revenues, which logically is a percentage of revenue.

And lastly we also have short-term debt.

Which again is going to be a percentage growth rate on the previous year.

And I'll copy these across for the remaining cells.

Before calculating total current liabilities.

And then moving on to non-current liabilities.

Both our non-current liabilities are taken as a growth rate. So I'll multiply by one plus the growth rate.

And again I'll copy across.

And I'll also copy across for the total assets which I didn't do earlier.

For deferred tax liabilities, we have a figure of zero historically, and it's calculated by taking a percentage of the income tax expense. Unfortunately, in some years, we're going to have an income tax expense of zero. And this can create an error because I'll have zero divided by zero. And when I do this in Excel, it creates a divide by zero error. And to avoid this error, I'm going to use an is error function, and to do this, I can simply write equals if.

And the logical test will be is error.

And the value will simply be the percentage divided by the income tax expense.

And if it is an error, I'll return zero. And if it's not an error, I'll return the assumption divided by the income tax expense.

Then I'll copy across for the remaining cells.

And I'll calculate total non-current liabilities by adding these two numbers together.

And of course, total liabilities is equal to non-current plus current.

And again I'll copy across.

In our balance sheet we still have equity remaining. And unfortunately, I'm going to need to make some assumptions in my statement of cashflows before wrapping up this balance sheet projection. I'll do my statement of cashflows projection in the next lesson.