In this lesson, we're going to begin populating our model with formulas. This is a hands on lesson that will draw upon your Excel skills and your general financial knowledge. To prepare a full set of financial projections, including an income statement, balance sheet and cash flow statement requires you to know a little bit about accounting. You don't necessarily need to be a designated professional account. But understanding the principles of accrual accounting and debits and credits will help you tremendously. The number one reason is because it forces you to consider both sides of every transaction.

The income statement is just one side of a transaction. The other side is typically to the balance sheet, and some income transactions impact cash and others do not which is why we reconcile the income statement in the balance sheet with the statement of cash flows. I've worked with many many wonderful financial analysts I'm often asked by them how they can further develop their own professional skills and for them, I will often say that improved In your understanding of accounting will make them a better financial analyst. In the same but opposite way. I'll often tell the accounts that they need to spend more time focusing on the analysis side of financial statements to complement their ability to balance the debits and the credits. Preparing a set of financial projections requires both skill sets.

Let's begin with the projected income statement in this lesson. In lessons to come, we will continue with the projected balance sheet and the projected statement of cash flows. You can either use the Excel file you finished with at the end of the last lesson, or open up the start file provided with this lesson to follow along. Let's begin by working our way down the income statement and starting right at the top with the sales line, begin forecasting each of the balances for the years 2000 x three to 2000 x seven. So starting with 2000 x three sales can be calculated using our 2000 x three operating budget as a basis. And so we can use a simple reference formula Let's tie this into the budget tab.

For the years after this, a growth rate of 2% has been provided a couple of comments at this point. First, remember, our protocols resist the urge to put the 2% growth rate into the formula directly because we may want to play with this assumption separately when we do different scenarios or look at sensitivity. So down in our assumption area at the bottom of the your worksheet, let's set up a row for the sales growth. I've shaded my sales gray to highlight to myself where I have assumptions. I've used a blue font to indicate where hard codes that is the raw numeric data had been input. Now I can calculate my forecasted sales using the assumption and copying it across row 10.

The second point is that in our last lesson, we talked about breaking out our calculation of sales into volume and Price separately. When we have this information available, this is a much better way of calculating this number. A cleaner way of doing this is to set up a separate sub schedule either located at the bottom of our worksheet, or on a separate worksheet to calculate sales, then we can just link the calculated sales into the associated sales on row 10. In this case, we don't know if the 2% of growth pertains to either volume or price. So for the time being simply compounding sales will suffice for our purposes in this course. Next, let's look at the cost of sales.

We will once again reference to the 2003 budget worksheet provided to us to deal with column F. Note that we are separately breaking out depreciation. So take the total cost of sales less the 3.296 million per note four of the budget tab. Next, we have another assumption provided to us that our gross profit margin before depreciation will be 21%. In the years after 20 x three. In our high level model, this is fine in an operating model. Typically you'll have a number of these sub schedules separately calculating the fixed and variable components of manufacturing costs, which really make your model truly dynamic.

Similarly, we have been provided with an assumption for our selling general and administrative expense, along with a cost escalator. So reference by way of formula, the 20 x three number from the budget tab, then set up another assumption to reference in the escalator to the SGA and a formula for the years following. How are we doing? Is your spreadsheet progressing like this? depreciation and amortization almost always necessitate a sub schedule to reconcile it's tempting to short Cut the calculation by adding a complex formula in row 14 itself, but I almost guarantee it'll give you a headache later on. We were provided with a capital asset schedule for two comparative years and the budget year.

Why don't we just calculate the projected costs accumulated depreciation and depreciation balance for each of the capital assets factoring in our expected additions and disposals? Remember that we are told to assume $2 million of annual sustaining capital expenditures. So let's take a moment to add that to our list of assumptions. We can now go over to the worksheet entitled capital assets, remember to reference by way of Formula your column headers back to our assumptions. Now we can extend the calculations over the five years by copying the formulas in column f to the right and finally, We can reference in the building and equipment capex assumptions for each years on row 18 and 25, respectively. still with me.

At the bottom of the capital asset schedule, we can see the depreciation expense for each year, which can now be linked back to the projected income statement on row 14. In the real world, depending on the degree of refinement you want in your financial model, you will typically have two of these capital asset schedules, one for accounting purposes and another for tax purposes, as capital assets are commonly depreciated for tax purposes differently than they are for accounting purposes. This gives rise to deferred income taxes which is beyond the scope of this course but I am going to spend a moment raising your awareness. deferred income taxes boggles the mind of most senior analysts. It is often one of the most complex aspects of digital Helping a robust financial projection. The simplest assumption to make is that the accounting and the tax basis of income are similar enough that you can ignore the timing differences of paying income tax.

For simple businesses, particularly service businesses. This is probably a suitable assumption. However, if you're modeling a business where this assumption does not hold true, then a little more intelligent thought may need to be given. So for example, if you're building a wind farm, which qualifies for accelerated depreciation for tax purposes, the capital costs of the project are deductible for tax purposes in the first couple of years of the project. While the accounting depreciation extends over many decades of the project's life, you get a huge relief of taxes payable in the early years, only to pay more taxes on the back end of the project. The finance people will look at this accelerated tax deduction is a valuable source of positive cash flow in the early years and a lesyk Expensive liability at some later date in the future time value of money in all the accounts however, we'll look at this phenomena of receiving it now and paying it later as a zero sum game and adjust the financial statements without consideration of this time value of money.

So there's your Coles notes awareness version of deferred income taxes. Let's add in some subtotals, which you can copy from the adjacent cells to arrive at operating income. Next, we have interest expense, which I'm going to actually leave until the next lesson as this is dependent on the amount of debt we have outstanding, which we have yet to determine until we actually forecast the balance sheet. We can now copy across the subtotals for earnings before taxes across row 20. Now we can move on to the calculation of income taxes. In our simplistic model we are assuming that the accounting and tax basis of income are very similar.

So we will use the effective tax rate assumption provided To determine the income tax expense. In the real world, you may break this into two lines one for the current taxes payable as the cash taxes and the other for taxes payable at some point in the future, which we call deferred taxes. Notice how this gives us a decimal answer. This might be a good time to mention the some analysts like to avoid decimals as this has a tendency to create the $1 rounding errors. The way around this is to wrap your formulas using the round function of Excel. However, note that this is not a foolproof way of eliminating the $1 errors in all instances, so I leave it to personal preference.

So track the income taxes next to arrive at the projected net earnings on row 24. And then you can format the cells and copy them across rows 22 and 24. I like to put the statement of retained earnings just after the income statement to calculate the continuity of this account as it's important To tie together the income statement and the balance sheet. Now, continuity schedules are a wonderful way of breaking down accounts into their components so that you can understand what is included in each line and how changes year over year have been recorded in the financial statements. This continuity schedule takes the opening retained earnings adds in the current earnings and subtracts the dividends declared, which is another assumption to add to our list. Go ahead and complete the retained earnings statement and replicate what you see on the screen before you.

So there you have our projected income statement less interest expense, which we'll get back to In our next lesson. Before we go, let's add a few ratios to our projection ratios are handy for reviewing our work and developing key performance indicators that allow us to evaluate different sets of assumptions. Let's add a gross profit margin ratio. The 21% certainly looks conservative, right? relative to what has been achieved in the past two years. Next, let's calculate EBITDA, which is why I broke out interest depreciation and amortization separately from the other expenses.

Eva is the most commonly referenced and versatile financial indicator used by investment bankers, analysts and lenders at large. It's a proxy for funds generated from operations. Even emerging like operating merchant is often used to compare the operating profitability of different businesses. And finally, let's add net profit margin. This ratio indicates how much of each dollar of sales falls incrementally to the bottom line. These are all indicators of profitability, we might want to add a few indicators of return as well we might want to include a return on equity calculation, which is your net earnings divided by your common equity and retained earnings.

You may wish to do this using the year end balance or an average balance of the opening and closing amounts. We won't have a result of this indicator until the end of our next lesson. As we haven't projected the balance sheet yet, we might want to include a ratio that addresses financial leverage, such as the interest coverage ratio, interest coverage can be calculated as simply EBIT divided by the interest expense. We'll see the results of this formula once we get to forecasting interest expense, which speaking of that, again reminds me that we need to move on. In this lesson, we cover three important points. First of all, think about forecasting revenue and expenses using detail sub schedules and assumptions about the future.

Then reference those balances into your primary model calculations. Secondly, make your model dynamic by keeping those assumptions separate from your formulas. And thirdly, incorporate ratios into the projection to help you interpret and validate your projections. In our next lesson, we will project the balance sheet so until then,