8. Building Projections for the Balance Sheet

Overview

In this lesson, we complete our assumptions for the balance sheet, focusing on individual line items in non-current assets and liabilities.

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 build the future projections for the Balance Sheet.

  2. Projections for Current Assets (00:13)

    After creating assumptions, we can then use these assumptions to build future projections for each of these items in the Balance Sheet. This process can often involve consultation with supervisors or other knowledgeable people, as well as analysis of the company’s operations and trends in the industry.

    In MarkerCo’s Balance Sheet, we have two current assets: receivables and inventory. Receivables currently represent a low percentage of total revenue, so we project that this percentage will increase in future years. We project that inventory as a percentage of revenue will decline in future years, as MarkerCo moves more towards consulting services.

  3. Projections for Current Liabilities (01:26)

    MarkerCo has three current liabilities. As with current assets, we need to analyze the structure of the business, and consider future trends in the company and the industry to create future projections for these liabilities.

    First is trade payables. This is a high percentage of cost of sales, which may discourage suppliers from working with MarkerCo in the future. As a result, we project this percentage will reduce in future years towards the industry average.

    Second is accrued expenses. This amount is very high as a percentage of sales, general, and administrative costs. This is because MarkerCo uses a lot of contract staff for which they are invoiced every six months. We expect this is not likely to be sustainable, and project a decrease in accrued expenses in future years.

    Third is deferred revenue. Deferred revenue is common in consulting companies, so we project that this will increase as a percentage of revenue in future years.

  4. Projections for Non-Current Liabilities (04:43)

    MarkerCo’s Balance Sheet contains one current liability that we need to project. This is deferred tax liabilities. The value for this is very high as a percentage of the income tax expense. As a result, we forecast that this will reduce considerably over the coming years.

Transcript

Now that we have our assumptions created for the Balance Sheet, I'm going to build the projections in this lesson starting with trade and other receivables. As we've mentioned in the past, receivables for the company are extremely healthy and a very low percentage of total revenue. Our supervisor believes that this is unlikely to continue despite the growing prevalence of consulting as the main source of sales. He's asked us to increase by 3% each year for the next 5 years. So this will be 10.2%, 13.2%, 16.2%, 19.2% and of course 22.2%.

Now let's move on to inventory. Given that Macro Co.'s revenue is moving more towards consulting services with very little inventory, our supervisor believes that the company is due to reduce inventory as a percentage of cost of goods sold in the coming years. As a result, she advocates a reduction in this percentage of 2% each year. So this would be 38.1%, 36.1%, 34.1%, 32.1% and 30.1%.

Now we need to move on to our current liabilities starting with trade payables. As a percentage of the cost of sales, trade payables is quite high and Macro Co. is clearly not paying its suppliers within a very quick timeframe. While this is very good from a cash perspective, it's not ideal from a reputational perspective. If suppliers feel that Macro Co. will not pay in a reasonable manner, they may wish to leave and work for the company's competitors. Having taken some sandings in the market, we've found that a typical trade payables in the industry is around 35% of the cost of sales. We're going to assume that the trade payables drops 4% each year for the next 5 years to approach this average. So this would equal to 64.9%, 60.9%, 56%, 52%, And 48%.

The next liability in our projections are accrued expenses.

Accrued expenses are a huge percentage of the total sale, general and administrative costs and as a result, we may want to include R and D expense as part of this projection, because its highly likely that R and D also has some accrued expenses included in its figure. So I'm going to adjust this formula slightly, by jumping back into the formula with F2 and including D58 in my sum.

And this reduces the percentage slightly.

When we show this assumption to our supervisor, he is still taken aback by the percentage of expenses that still remain to be paid. As a consequence, he digs a little deep into the industry and finds out that Macro Co. uses a lot of contract stock for which they are invoiced every 6 months. This means that they often have incurred a lot of expenses at the end of the year for which they are yet to pay. Again, this points to the ability of Macro Co. to conserve cash by imposing onerous payment terms on its suppliers. Our supervisor believes that this value will have to come down further in the coming years and suggests reducing the percentage by 3% each year for the next 5 years. So I'll do this quickly.

And 60%.

Our next liability is deferred revenue, which is revenue paid upfront by our customers before we have delivered the service or good in question. This tends to happen a lot for consulting projects and as a result, deferred revenue should be a greater proportion of total revenue in the future years as consulting contributes more to the total revenue line. As a consequence, our supervisor agrees to increase deferred revenue by 1% each year.

Our last assumption relates to deferred tax liabilities. Again, this figure is very high compared to the income tax expense and our supervisor wants to assume that this figure will reduce considerably over the coming years to ultimately be under 100% of income tax expenses. As a result, we're going to reduce the deferred tax liabilities by 15% each year. So this is 154.1%, 139.1%, 124.1%, 109.1%, And lastly, 94.1%.

With our projected assumptions for the Balance Sheet now complete, in our ext lesson, we'll connect these assumptions back to our Balance Sheet as shown onscreen.