3. Creating Income Statement Assumptions

Overview

To build projections, we first need to identify assumptions for each line item. Let's start in this lesson with the income statement.

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 future assumptions for the Income Statement.

  2. Projecting Revenue Growth (00:35)

    At the top of the Income Statement assumptions, we make projections for future revenue growth. In our statement, we assume revenue will grow by a certain percentage each year.

    The company we are making projections for, MarkerCo, has two divisions, hardware and consulting. We project revenue growth for each division separately. The consulting division has seen large revenue growth in recent years, so we project a constant, but high, revenue growth rate for this division for each year of our projections. The hardware division has seen little growth recently, so we predict the revenue growth rate will decline slightly each year.

    After determining these growth rates, we can project the revenue amount for each future year. We do this by multiplying the previous year’s revenue by the projected growth rate. We can calculate total projected revenue by adding the projected revenue for each division.

  3. Assumptions for Cost of Sales and Expenses (02:20)

    After projecting future revenue. we create future assumptions for the cost of sales and operating expenses. In our case, we assume each of these figures will be a percentage of revenue. Using our knowledge of the business and previous financial statements, we can determine whether this percentage will rise or fall each year of the projection. For example, in our case, we have been told MarkerCo plans to increase spending on sales and marketing. As a result, we assume this expense will increase as a percentage of revenue for each year of our projection. 

    At this early stage of the process, we shouldn’t worry too much about the accuracy of the assumptions, as they can be changed later. We just want to make sure they are reasonably sensible at this stage.

  4. Projecting Gross Profit (04:19)

    After creating assumptions, we can calculate the projected values for cost of sales and operating expenses. We do this by multiplying the projected revenue figure by the projected percentage from our assumptions.

    Once we project cost of sales and operating expenses, we can calculate the projected gross profit and gross margin, using the usual formula for these values.

Transcript

In the previous lesson we created a table for our Income Statement assumptions basing our projections of expenses as a percentage of revenue. Needless to say, these projections can be made much more detailed. For example, we could split marketing costs between new and existing customers, or split revenue between volume and price for the different business units. However, for the sake of simplicity, given that this is our first model, we are going to assume that expenses are a percentage of revenue. Let's start by looking at the consulting and hardware growth rates. The consulting growth rate is increasing year on year, and in 2015 hit 38.5%.

I'm going to assume that for the next five years the growth rate stays constant. So I'll apply this value out to 2020. While the company might actually grow faster, this figure of 38.5% is a very high growth rate as it stands. And the company will do well to maintain this growth rate right through to 2020. It's clear that the hardware business will decline in the coming years, given the anemic growth rate experienced from 2013 to 2015.

So for the next five years I'm going to assume that the hardware growth rate declines by .4% each year. So 0.9%, 0.5%, 0.1%, -0.3%, and -0.7%.

From these values I can now project my revenue from continuing operations for the next five years. Now I'll do this below. To calculate consulting revenue in 2016, I now take the 2015 value and multiply it by one plus the growth rate for 2016.

And I copy across for the remaining sales to find consulting revenue for each year to 2020. And I can repeat this for hardware by pressing Ctrl + D to copy down the formula. I now calculate revenue from continuing operations by summing these values together. And again I will copy across for the remaining sets. Now let's move back to our assumptions and focus on cost of sales. In 2015 the cost of sales is calculated at 38.7% of revenue.

Given how margins have improved from 2012 to 2015, I'm going to assume that margins are going to continue to improve by .7% for each of the next five years.

So this will be 38%, 37.3%, 36.6%, 35.9%, and 34.2%.

For R and D expense I'm going to assume that this stays constant as a percentage of revenue. So 3% across the board.

For sales and marketing I'm going to assume an increase each year by 0.5% based on comments I've heard from the CEO on their focus of expanding revenue by increasing sales and marketing expense. So this value will be 9.8%, 10.3%, 10.8%, 11.3%, and 11.8%.

I'm also going to assume that the company gets general and administrative costs under control and keeps them constant at 12.7%. So I'll copy across for the remaining years. And finally depreciation. Given that the hardware business is in decline, I'm going to assume that depreciation will drop over the coming the years by say, 0.1% per year.

And there we have our Income Statement assumptions. At the beginning I wouldn't worry too much about getting the accuracy of these assumptions correct. What we really need are some reasonably realistic values that allow us to build out our model, and these assumptions can be updated later after some additional analysis. With the assumptions now in place, let's put in our expense projections on the Income Statement. To project cost of sales or cost of goods sold I simply take the revenue for that year and multiply it by my assumption, which is cost of sales as a percentage of revenue. I then copy across for the remaining sales. This will allow me to calculate gross profit, so I'll simply take revenue and subtract cost of goods sold.

I copy across for the remaining sales and do the same for gross margin.

In the next lesson, we'll finish off our Income Statement projection all the way down as far as net profit.