2. Structuring Projections in Excel

Overview

Structuring financial projections in Excel is easier than you would think, particularly with this short, helpful lesson.

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 Income Statement in Excel.

  2. Structuring the Excel Workbook (00:19)

    To create financial projections, we first create a set of assumptions for each financial statement individually. We then combine these assumptions with historical financial statements to create the projections. 

    When we create financial statement projections, we usually project the Income Statement first, followed by the Balance Sheet, followed by the Statement of Cashflows. As a result, we organise the financial statements in this order in Excel. We keep all three statements on a single sheet, as this makes it easier to move between statements quickly.

  3. Income Statement Assumptions (01:05)

    Assumptions for the Income Statement are placed above the statement in our Excel model. These assumptions detail how each item in the Income Statement is expected to grow over the coming years. We can use any method we want to create these assumptions. For example, we assume revenue will grow by a specified percentage each year. We also assume each expense in the Income Statement will be a specified percentage of revenue in future years.

  4. Historic Income Statement Assumptions (02:20)

    Before creating assumptions for future years, we calculate the values for our assumptions in historic years, using the values in the Income Statement for previous years. This information will help us to create our assumptions for future years.

    In our data set, we calculate the annual revenue growth rates for each of the company’s two divisions, and we calculate the percentage of revenue for each operating expense. We perform these calculations for each year of historical data in our Income Statement.

Transcript

In our other modelling courses you may have seen influence diagrams like this that I use to structure models. Influence diagrams are great when we need to build a new model from scratch with relatively little context or historic modeling to build from. Financial projections, on the other hand, are a lot easier. We've a set of assumptions for the Income Statement, Balance Sheet, and Statement of Cashflows that we project for the next five years. We then combine these assumptions with historical financial statements to create our financial projections. When building a model like this in Excel, I like to keep all three financial statements on the same sheet, as I spend quite a lot of time jumping between the three statements. The order in which we build projections tends to be Income Statement, followed by Balance Sheet, followed by Statement of Cashflows and therefore, I arrange my model in Excel the same way with Income Statement at the top, followed by Balance Sheet, followed by the Statement of Cashflows. Above the financial statements, I include my assumptions and off camera, I'll now add my assumptions for the Income Statement. For my Income Statement assumptions, you'll see that I've included growth rates for both business units, figures for cost of sales or cost of goods sold and the individual operating expenses, all as a percentage of revenue. You might remember from the previous course the common size Income Statement where we divide each line by the annual revenue. Here, we're going to use a similar calculation to help us build our Income Statement projections for the company's various expenses. These assumptions will help determine the actual dollar figures that appear in our Income Statement projections. The method of calculating these assumptions can be as simple or as complicated as you want. For example, if you had access to the internal numbers of the business, research and development expense projections could be broken down into equipment expenditure, salaries, contractors, or even split by product that's being developed by the research team. However to keep things very simple and as our first model, I'm going to use an assumption based on expenses as a percentage of revenue to calculate my future projections. Before we create our future assumptions, let's first enter the historical assumptions from our existing Income Statement at the top of the page and I'll start with consulting growth rate. So I'll simply take the 2013 consulting revenue, subtract the 2012 consulting revenue, and divide by the 2012 consulting revenue.

I'll copy across for the next two years and I'll copy down for the hardware growth rate also. For cost of sales, we can enter 2012 as well. I'll simply take the cost of goods sold and divide by revenue from continuing operations. Copy across for the remaining cells.

Next, I want to calculate research and development divided by revenue, so I'll take research and development and divide by revenue and I'll anchor the cell by row and copy across and copy down for all of my expenses.

As you can see, the historical metrics that we have in our Income Statement assumptions will inform the future assumptions we project for 2016 and 2020.

I'll do this in the next lesson.