9. Completing Balance Sheet Projections

Overview

In this lesson, we complete our balance sheet projections, creating future projections both long-term liabilities and shareholders' equity.

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 complete our pro forma balance sheet projections.

  2. Completing Current Assets Projections (00:15)

    Once we’ve created our future assumptions, it’s easy to create the projected Balance Sheet, which we also call a Pro Forma Balance Sheet. In the current assets section, receivables will be a percentage of revenue, so we calculate the receivables amount by multiplying the assumption percentage by revenue for each year of our projection. We project inventory will be a percentage of cost of sales, so we calculate the projected inventory amount by multiplying the assumption percentage by cost of sales.

    We can then calculate the total projection for current assets by adding the projection for each of the assets together.

  3. Completing Non-Current Assets Projections (02:07)

    In our balance sheet, we only need to create a projection for other non-current assets, which we do using a growth rate. To project non-current assets for a year, we take the previous years value and multiply by the growth rate.

    We can then calculate the total projected value for non-current assets by adding the projection for each asset. To project total assets we add current assets and non-current assets together.

  4. Completing Current Liabilities Projections (03:15)

    In our Balance Sheet, there are four current liabilities that we need to project. Three of these are assumed to be a percentage of a relevant operating metric. These are payables, accrued expenses, and deferred revenues. To calculate the projections for these, we multiply the assumption percentage by the relevant operating metric.

    The final current liability is short-term debt. We project this will be zero in the future as it has been zero in all previous years. After projecting each liability, we can calculate the total projection for current liabilities.

  5. Completing Non-Current Liabilities Projections (05:10)

    Our Balance Sheet contains two non-current liabilities. Other non-current liabilities is projected to be constant in future years. Deferred tax liabilities is assumed to be a percentage of the income tax expense. We therefore project future deferred tax liabilities by multiplying the assumption percentage by the income tax expense.

    We then calculate total noncurrent liabilities by summing each liability together, then calculate total liabilities by adding current and non-current liabilities. 

  6. Completing Equity Projections (06:02)

    The equity section of the Balance Sheet contains two items. The first is issued share capital. We assume this will remain constant in future as it has remained constant in the past. To project accumulated earnings, we add net profit to the previous year’s accumulated earnings.

    We can then calculate total equity by adding all the equity items together and calculate total liabilities and equity by adding total liabilities and total equity.

Transcript

Now that we have our balance sheet assumptions created, it's time to build our pro forma balance sheet billable, so I'll skip down by pressing the page down key. Let's start with trade and other receivables, which we know is going to be based on revenue. So we'll write a formula that multiplies our assumption by revenue, which is in cell H50, and I'll copy across for the remaining cells.

Next up is inventory. So again, we'll take our assumption and multiply it by revenue, which is H50, and as I copy across, you can see that our values seem extremely large, particularly given the fact that inventory should be falling as the hardware sales decrease as a percentage of the total, particularly as the hardware business is not due to grow substantially in 2016.

If I scroll up to my assumptions, I can see the mistake that I've made.

Inventory is actually calculated as a percentage of the cost of sales, not revenue. This is a pretty easy fix. So let's go back to my inventory formula, jump inside the cell, remove revenue, and, instead, scroll up to find the cost of sales.

Now, I'll copy this across for the remaining cells. These inventory figures make a lot more sense. I can now calculate total current assets, even though I won't be calculating cash and cash equivalents until later.

I'll write the sum formula, select all three cells, and again copy across for the remaining cells.

Now let's move onto noncurrent assets. I'm going to calculate PP and E later from the statement of cash flows, and I know that long-term investments is going to stay constant at 0.6 for the next five years. So now, let's take a look at other noncurrent assets, which we calculate by using a growth rate. So I'll select the previous value, multiply by one, plus my assumption, which is here, and I copy across for the remaining cells, and similarly, I can calculate total noncurrent assets, including the PP and E cell, which we'll fill in later.

I can also calculate total assets by adding non-current with current.

These numbers will obviously increase once I've added cash and PP and E to my formula.

Now let's move on to current liabilities, starting with trade and other payables. So again, I'll jump up to my assumptions, trade and other payables, and multiply it by the cost of sales, which is cell H52, which I can type, and I'll copy across for the remaining cells.

Accrued expenses has an assumption, and you might remember that we multiply accrued expenses by R and D and SG and A.

So I'll sum these values.

So OR and D, sales and market, and general and administrative.

Next is deferred revenues, which needless to say will be seen as a percentage of revenue in my assumptions.

So I'll multiply that by H50 and copy across.

For short-term debt, I'm going to assume that the company does not take on any additional short-term debt over the next five years in accordance with its previous policy of no short-term debt. I'll simply write zero in each cell.

I can now calculate total current liabilities by summing these values together.

Let's now move on to noncurrent liabilities. Already, we have other noncurrent liabilities calculated, and we assume that this is going to be constant for the next five years. Deferred tax liabilities has an assumption based on the income tax expense, so we'll calculate this quickly. Scrolling up, I'll find my assumption and multiply it by the income tax expense, and I'll copy across for the remaining cells. We can calculate total noncurrent liabilities by summing these values together.

For total liabilities, we add noncurrent to current, and to wrap up our balance sheet, we take a look at equity. I'm going to assume that the company does not issue any share capital over the coming years, particularly as it hasn't done so from the beginning of our historic data. So 3.2 for each year.

Accumulated earnings is simply the previous year's earnings plus net profit. So I'll take the previous year's earnings and add net profit, and I'll extend this formula out to 2020.

We can now calculate total equity by summing these values together and total liabilities and equity by adding total equity to total liabilities.

To finish our financial projections, in the next lesson, we'll look at the statement of cash flows and how it interacts with the cash and PP and E entries in our balance sheet.