4. Building Projections for the Income Statement

Overview

We are now ready to build our income statement projections, with some help from our knowledgeable supervisor!

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 Income Statement projections for Operating Expenses, EBIT, and Net Profit.

  2. Completing Income Statement Projections (00:18)

    In our model, we assume that all operating expenses are a percentage of revenue. Therefore, we project each of the operating expenses by multiplying revenue by the assumption percentage. After projecting expenses, we can project EBIT by subtracting operating expenses from gross profit.

    We then need to project financing costs and income taxes. In our case, we assume no financing costs, as MarkerCo has no debt, and does not intend to take on new debt. As a result, profit before income tax is the same as EBIT. To project income taxes, we need to make an assumption about the effective tax rate. In our case, we assume this will stay constant. We can then calculate the projected income tax payment for each year. After doing this, we can project net profit by subtracting the income tax expense from profit before income tax.

  3. Modifying the Projections (02:26)

    After creating a financial statement projection, it’s a good idea to sense check the numbers in the projection. In particular, you should sense check the assumptions used to build the statement. A well-designed Excel model should allow you to modify the assumptions and immediately see an updated projection.

    In our case, we consult with a supervisor, and decide to reduce the revenue growth rate for consulting. We also want to make adjustments to the way depreciation is calculated.

Transcript

In the previous lesson we created projections for revenue, cost of goods sold, and gross profit. Let's not complete our income statement projections for operating expenses, EBIT, and ultimately net profit. I'll start at the top with the R&D expense. Our assumption for all operating expenses is that the future projection should be based on a percentage of revenue. This simplified view helps us to create our projections quickly. So for R&D, I'll take the assumption and multiply it by revenue, anchoring this cell by rule. And then copy across with control R. And down for the remaining operating expenses with control D.

I can now calculate total operating expense projections by summing these values.

To calculate EBIT, I'll take gross profit and subtract operating expenses.

Again copying across for the remaining cells. For net financing costs, I'm going to assume that the company does not take on any new debt for the next 5 years, based on it's existing policy of having no loans on the balance sheet. So I'll write in zero for each year.

This means that my profit before income tax will be the same as EBIT. I'll still calculate the formula however, it should be EBIT minus net financing costs. And I'll copy across for the remaining cells. Now I must calculate my income tax expense. I'm going to assume that the effective tax rate stays at 12.5 percent.

So I'll copy this across, and multiply my profit before income tax by my effective tax rate for each year.

- And calculate net profit by simply take profit before income tax, and subtract the income tax expense.

Now that we have our income statement projections complete, it's worth sense checking our numbers with our supervisor who wants to see the projections. After reviewing the projections, he has some feedback on the assumptions. So let's skip to the top of the page and see what that feedback is. Firstly, he's very skeptical of the consulting growth rate, which is still very aggressive for the next five years. Particularly as the company has acquired a good chunk of the local market share already. He suggests decreasing the growth rate by 4 percent per year starting in 2016. Here's where a good model structure comes in use. We can respond to his request in a matter of seconds by simply reducing our growth rate in these cells. And the whole income statement updates automatically. So let's do so. Dropping 2016 by four percent we get 34.5 percent, 30.5 percent, 26.5 percent, 22.5 percent, and 18.5 percent.

Our supervisor is fine with our assumption for hardware growth rate, and with our initial assumptions for expenses as a starting point in the model. This is apart from depreciation which for Marco Co, is not remotely dependent on revenue, instead he says look at the future capital expenditure projections for the company, and base your depreciation projections from these numbers. I'll show you how to do this in the next lesson.