7. Projecting the Balance Sheet Part 1

Overview

In this lesson, we enter a series of assumptions for each line-item in the balance sheet, that will form the basis of our balance sheet projection.

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 assumptions for the future Balance Sheet values.

  2. Historical Values for Assumptions (00:10)

    We will project each item in the Balance Sheet using assumptions. These assumptions are either annual growth rates, or percentages of some item in the Income Statement. We can calculate historic values for these assumptions using the historic Balance Sheet and Income Statement For example, trade and other receivables is assumed to be a percentage of revenue. We can calculate historic values for this percentage by dividing trade and other receivables in the Balance Sheet by revenue in the Income Statement.

  3. Future Assumptions for Current Assets (01:18)

    As a startup grows, they often tend to target larger customers. These larger customers can be slower to settle their accounts than smaller customers. For this reason, we assume trade and other receivables will increase as a percentage of revenue in future years. 

    Inventory will grow as a company grows, but this should be in line with cost of sales. We assume inventory will stay constant as a percentage of cost of sales in future years.

  4. Future Assumptions for Current Liabilities (02:09)

    As a company grows, it may gain the ability to seek improved payment terms from suppliers. As a result, we assume trade and other payables will be higher in future years than historically. Accrued expenses follow a similar logic, so we assume a higher value for accrued expenses as a percentage of SG&A than historical values.

    Deferred revenue is common in software companies, where customers pay for a subscription before receiving the service. Deferred revenue can grow when a company targets very large customers. These customers may sometimes pay up to three years in advance. As a result, we assume steady future growth in deferred revenue.

    The best way to create an assumption for short-term debt is to consult with the company’s executives to determine their future debt strategy. In our case, the CEO tells us the company has no plans to take on any debt, so we assume short-term debt will be zero in future years.

  5. Non-Current Assets and Liabilities (03:50)

    Assumptions for non-current assets and non-current liabilities can often be determined by consulting with company executives. In our case, we are told the company will have no change in long-term investments, non-current liabilities, and deferred tax liabilities in future years. We assume that other non-current assets will grow slightly in the future.

  6. Understanding Foreign Exchange Effects (04:39)

    Foreign exchange effects occur when a company trades in markets that use different currencies. This is particularly common for technology companies, which can sell their product globally from an early stage. Predicting the size of these effects is obviously difficult, so we assume that foreign exchange effects will be constant as a percentage of revenue in our model. The percentage we use is the most recent value from the historical accounts.

Transcript

With their income statement now complete, it's time to create our balance sheet and cash flow projections. In the balance sheet assumption section of this model, I have a series of assumptions either based on the previous year's growth rate or as a percentage of revenue, SG&A, cost of goods sold, or even income tax expense.

In this lesson, I'm going to simply complete this section of the model.

To calculate the historic values of these assumptions, I can simply reference the relevant values in my balance sheet and income statement. For example, I can calculate trade and other receivables divided by revenue by going to the balance sheet, finding trade and other receivables, and then dividing by revenue.

And then I can copy across for the remaining cells.

As I've done these calculations previously, and they're pretty straightforward, I'm going to leave it for you as an exercise to complete the remainder of these historical calculations.

Instead, I'm going to focus on the projections.

Let's start at the top with trade and other receivables.

I'm going to assume that trade and other receivables grows to 17% in 2017, and by an additional 0.5% each year thereafter.

And the reasoning behind this is quite straightforward.

The larger B2B accounts that TrackerTime wants to target will typically take longer to pay than the current customer base of S&Bs and consumers and this will likely drag accounts receivable a little higher. Inventories on the other hand are not forecasted to grow faster than revenue and I'm going to keep these at 7% each year for the five years I'm projecting.

For trade and other payables, TrackerTime CEO is hoping that she can improve payment terms slightly with some of her suppliers. Pushing accounts payable up to 17.5% of cost of goods sold for the duration of the five year projection. So I'll simply enter 17.5% and copy across for the remaining cells.

For accrued expenses, I'm expecting a small bump for the same reason to maybe 12% of SG&A. So I'll enter 12% and copy across.

Deferred revenue occurs when we receive cash upfront from a customer before we provide the service or deliver the good. It can be especially prevalent in software as a service businesses like TrackerTime. When customers pay annually upfront before actually availing of a service. As I mentioned previously, B2B customers tend to take longer to pay, but in certain cases, very large B2B customers will often pay up to three years in advance which will increase deferred revenue markedly. So I'm going to assume that deferred revenue will increase as a percentage of revenue overtime by 1% each year.

And finally, in our current liability section, I have short-term debt.

TrackerTime's CEO has assured me that no short-term debt will be taken out over the next five years so I'll assume that this stays at zero.

I'm also going to assume that long-term investments stays at zero as well, again, with TrackerTime's CEO's blessing.

And this also applies to other non-current liabilities and deferred tax liabilities. So I'll simply enter zero in both of these cases.

The two remaining assumptions we have left are other non-current assets and foreign exchange effects as a percentage of revenue. Other non-current assets won't have a big impact on our model but I'm going to assume that these have a marginally higher growth rate of 1% each year for the next five years.

Foreign exchange effects are an item on the model that you may not have seen before and essentially are present due to the fact that TrackerTime sells into many different markets that do not trade in TrackerTime's local currency. Foreign exchange effects tend to have a bigger impact for technology startups than for more traditional companies because a huge percentage of a technology startup's revenue can be from international markets even at a very early stage in development. Although it's hard to predict currency fluctuation movements, I've decided to assume foreign exchange effects at 1.5% each year to 2021.

And again, I'll copy across.

With our balance sheet assumptions now complete, we can project the actual values in our balance sheet below and I'll do this in the next lesson.