6. Creating Balance Sheet Assumptions Part 1

Overview

We begin building our balance sheet projections by creating input assumptions for 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 create historic assumptions for the Current Assets and Current Liabilities.

  2. Overview of Balance Sheet Assumptions (00:19)

    In our model, we place Balance Sheet Assumptions below the Income Statement assumptions. For current assets and current liabilities, we assume each value will be a percentage of a relevant operating metric, such as revenue or cost of goods sold.

  3. Historic Assumptions for Current Assets (00:57)

    Our balance sheet contains two current assets: trade and other receivables, and inventory. We will project receivables as a percentage of revenue, as we would expect receivables to rise if we had a large increase in revenue. Therefore, we calculate the historical assumption for receivables by dividing receivables by revenue for each historic year in our financial statements.

    Inventory relates directly to the cost of goods sold. For example, if the cost of goods sold rises, we will probably need to increase inventory to avoid running out of stock. As a result, we will project inventory as a percentage of cost of goods sold. We therefore calculate this percentage for historical years in our statements.

  4. Historic Assumptions for Current Liabilities (02:28)

    Next, we want to calculate historical assumptions for current liabilities. MarkerCo has three current liabilities. First is payables, which is forecast as a percentage of cost of goods sold. We calculate the appropriate percentage for historical years.

    Second is accrued expenses. This is generally dependent on operating expenses, excluding depreciation. As a result, we project it as a percentage of sales, general, and administrative expenses. Therefore, we calculate this percentage for historical years.

    Third is deferred revenue. This is closely correlated with total revenue. As a result, we project deferred revenue as a percentage of total revenue, and calculate this percentage for historical years.

  5. Assumptions for Debt and Cash (04:36)

    Our assumptions do not consider two common current assets. The first of these is short-term debt. In this case, we are assuming MarkerCo will not take on any debt in the next few years, so we don’t need to create a projection for short-term debt.

    The second is cash and cash equivalents. We do not create a projection for cash in the Balance Sheet. Instead, we calculate the projected cash balance when projecting the Statement of Cash Flows, and link this projection to the Balance Sheet.

Transcript

With our income projection statement now complete, let's move on to the Balance Sheet. In this lesson, I'm just going to focus on the assumptions that would inform our projections for current assets and current liabilities. And as you can see on our spreadsheet, I've included a new panel called Balance Sheet Assumptions to account for these values. Similar to the Income Statement projections, most lines in the Balance Sheet will be projected off a percentage of revenue, cost of goods sold or some other operating metric. These values normally drive the amount of receivables, payables and inventory that a company will hold on the Balance Sheet. For example, if Macro Co experiences a huge increase in revenue during the year, it's likely that we will have higher deferred revenue that year as well and indeed potentially higher receivables. So let's start filling in our assumptions for the historic time periods starting with trade and other receivables. As you may remember, trade and other receivables is the amount of cash that the company is owed by its customers and is typically projected as a percentage of revenue. So here, I'll write "Equals", skip down to Receivables...

And divide by Revenue.

And I'll copy across for the remaining cells.

Next up is inventory. Inventory is the amount of stock that we have bought but not yet sold. It relates directly to the cost of goods sold as typically projected as a percentage of this amount. So again, I'll select Inventory...

And divide by the cost of goods sold.

And I'll copy across for the remaining cells and fix the formatting by pressing Ctrl + I.

If we have higher costs of goods sold in a year, it's likely that we will also have higher inventory as well to make sure that we don't run out of stock. If our costs of goods sold falls, on the other hand, then we're likely to have less inventory because we don't need to keep our storehouses filled to the same extent. Now let's move on to the current liabilities with trade and other payables. This is also based off cost of goods sold or cost of sales.

So I'll write "Equals"...

Select Payables, which is a bit further down and divide... With the costs of goods sold. And I'll copy across for the remaining cells. Next up after payables are accrued expenses. Accrued expenses are expenses which have been incurred but for which no payment has been made. Accrued expenses are typically dependent on operating expenses not including depreciation, which you might remember is a non-cash expense. So what we'll do is calculate accrued expenses based on the sales, general and administrative expense. We could include R and D as well in this calculation, but I think sales, marketing, general and administrative is probably a better projection for accrued expenses. So I'll select Accrued Expenses...

Down on my Balance Sheet...

And divide by...

The sum...

Of Sales and Marketing...

And General and Administrative.

And I'll copy across for the remaining cells.

Lastly, I have deferred revenue. Deferred revenue are revenues for which cash has been received but the service has not yet been delivered. Deferred revenue is obviously very much affected by the total revenue and that's what we'll base our projection on. So I'll select Deferred Revenue...

And divide by Revenue.

Again, copying across for the remaining cells.

If we now skip down to the Balance Sheet, we can see that we are missing two lines: Cash and Cash Equivalents and Short-term Debt. For Short-term debt, I'm going to assume that the company does not take on any short-term debt in the next 5 years in keeping with it's current policy. So no projection assumption is needed here. This brings us, lastly, to the Cash and Cash Equivalents balance. When building projections, we don't actually project the cash balance directly, in the Balance Sheet. Instead, we calculate the cash balance in the statement of cash flows and then write a formula to link this value back to the Balance Sheet. So the projection for cash and cash equivalents is normally the last value we'll do when building our model. In the next lesson, we'll finish our assumptions for the non-current assets and liabilities.