Sign in or start a free trial to avail of this feature.
2. Structuring Projections in Excel
Structuring financial projections in Excel is easier than you would think, particularly with this short, helpful lesson.
Structuring Projections in Excel
- Financial projections are quite easy to structure in Excel
1. We decide on a set of assumptions for the income statement
2. We decide on a set of assumptions for the balance sheet and cashflow statement
3. We use historic information to inform our future assumptions
4. We use the future assumptions to create financial statement projections
Initial Income Statement Assumptions
- Revenue will be based on a growth rate assumption
- To begin, we can assume all expense projections are based on a percentage of revenue
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.