EXERCISE 7 – INCOME STATEMENTS
Bring up the spreadsheet entitled Exercise7-IncomeStatements-Spreadsheet. Make sure that you’re on the worksheet tab entitled Financials. This worksheet contains the income statements for a company from 2002 – 2009. What we want to do with this data is to try to project the results for 2010 – 2012. There are many ways of projecting income statements, but all of them begin with projections of sales (or revenues) for a company. The projected revenues for 2010 – 2012 are already presented in the spreadsheet. These are assumed to have been generated by the marketing department of the company given their experience in sales. What we are going to do is to use the regression techniques that we reviewed in Exercise 6 as a means of determining the relationships between costs and revenues for the company.
Let’s start by copying necessary data from the historic income statements to the worksheet entitled “Regressions”. First, copy the years from cells C4 through J4 on the Financials worksheet and then Paste Special – Values – Transpose to paste them in cell A2 of the Regressions worksheet. This is done by clicking on cell A2 where you want the column of data to start, followed by the drop-down arrow under Paste on the Home ribbon. Select Paste Special. Click the Values radio button and the Transpose box. Now click OK. Clicking the Values button is necessary in the event that the numbers we are copying are the result of a calculation, in which case we would get error messages since we would have lost the relationships in the copying process. In cell A1, type in “Year” as a header. The regression function of Excel will only work when the dependent and independent variables are in columns. In cell B1, type in “Revenues”. Now, copy the Total Sales for 2002 – 2009 from the Financials worksheet to cell C2 using the Paste Special – Values – Transpose function. Next, copy the Cost of Goods Sold from the Financials spreadsheet for years 2002 – 2009 (cells C13 – J13) and Paste Special – Values – Transpose the figures into cell C2 of the Regressions spreadsheet. Label the column as “COGS” in cell C1. Copy the Total Gen. & Adm. Expense from the Financials spreadsheet (cells C34 – J34) into cell D2 of the Regressions spreadsheet using Paste Special – Values – Transpose. Label cell D1 as “G&A”. These are the two general categories of expenses that we want to analyze. Before doing so, however, we need to make some adjustments to the General & Administrative expense totals.
Specifically, let’s back out the Bad Debt expense and the Depreciation expense from the Total G&A category. While we could easily leave the Bad Debt expense category as a part of the total, the fact that it is clearly a percentage of sales makes it a prime category to treat separately. Depreciation is an accounting expense rather than an actual cash expense incurred from operations. It is also based upon a (somewhat) arbitrary calculation rather than a true economic occurrence.
Copy the Bad Debt expense figures for 2002 – 2009 on the Financials worksheet (cells C18 – J18) to cell E2 of the Regressions spreadsheet and label the column as “Bad Debt” in cell E1. Now copy the Depreciation expenses for 2002 – 2009 from the income statement (cells C31 – J31) to cell F2 and label that column as “Depreciation” in cell F1. Now, type “Adj. G&A” into cell G1 to identify the Adjusted Gen. & Admin. Expenses that we will calculate. In cell G2, set the equation equal to the 2002 G&A figure and subtract the 2002 Bad Debt and Depreciation expenses. Your equation should look like this:
=D2-E2-F2
Copy G2 down to G3 – G9.
Now let’s perform a regression analysis using the Data Analysis – Regression routine. This time, the Sales will be the independent (X) variable and the Adjusted Total Expenses will be the dependent (Y) variable. Set the Input Y-variable to cells C2 through C9 and the Input X-variable to cells B2 through B9. Set the Output cell to A15 and click OK. Rename cell A15 as “Revenues vs. COGS”. Now change cell A31 from “Intercept” to “Fixed Costs” and cell A32 from “X Variable 1” to “Variable Costs”. If you look at the Adjusted R Square in B21 and the F-ratio in E26, you’ll see that the regression equation is very robust. Also, the t-stats show that the fixed costs of $7.9 million per year and the variable cost component of 27.4% of sales are both very significant. Now let’s use the regression to forecast our COGS on the income statements for 2010 – 2012. In cell K13, our equation should appear as
=Regressions!$B$31+Regressions!$B$32*Financials!K5
Note the use of the dollar signs on the regression coefficients to allow for copying the equation to cells L13 – M13. We have now projected the Cost of Goods Sold for the next three years.
Now let’s calculate a regression equation for the Adjusted G&A. Using the Regression routine, set the dependent (Y) variable equal to the range G2 – G9. You’ll notice that our independent (X) variable is still set for the Total Sales figures. Now set the Output Range to cell A35 and click OK. Relabel cell A35 as “Revenues vs. Adj. G&A” and change the “Intercept” text in cell A51 to “Fixed Cost” and the “X Variable 1” in cell A52 to “Variable Cost”. Again, you’ll notice that the regression equation and the coefficients are all highly significant. On the Financials spreadsheet, let’s just use row 33, currently the “Miscellaneous” expense row, to calculate our projected Adjusted G&A expenses for 2010 – 2012 since we don’t have a row for Adjusted G&A (although we could create one). In cell K33, your equation should be
=Regressions!$B$51+Regressions!$B$52*Financials!K5
Don’t forget the dollar signs!
You’ll notice that we skipped the Interest Income and Miscellaneous income lines of rows 8 and 9. Let’s assume that the future amounts will be the same as last year’s (2009). Set cell K8 equal to cell J8 and the set cell K9 equal to cell J9 and copy both cells to columns L and M.
Let’s create an equation to project the Bad Debt expense in row 18, since we deleted this in our calculation of Adjusted G&A, and base it on the average percentage of sales that Bad Debt has been in the past. There are many ways to calculate an average. We could calculate the average percentage of sales that Bad Debt has been for each year in the past and then take a straight average of the averages. Or we can calculate a sales weighted-average, which gives more weight to years with higher sales. There is a short-cut to calculating a sales weighted average. Rather than calculating the average for each year and then calculating a weight based on each year’s percentage of the total sales for all years, we can simply sum the Bad Debt for all of the years and then divide it by the sum of the Total Sales for all of the years. The result is the same.
Let’s use a weighted-average to project the future Bad Debt. The formula for cell K18 will be
=SUM($C$18:$J$18)/SUM($C$5:$J$5)*K5
Again, you’ll notice the dollar signs ($) are used to maintain the same average bad debt percentage when we copy the equation to columns L and M.
The next expense category that we need to consider is the Depreciation expense that we removed from the Total Gen. & Admin. Expenses. Let’s use a weighted average like we did for Bad Debt, but make the Depreciation a percentage of Net Fixed Assets rather than Total Sales. The equation in cell K31 should be
=SUM($C$31:$J$31)/SUM($C$64:$J$64)*K64
You’ll note that the result was a zero since we have not projected the Net Fixed Assets for 2010 (cell K64) yet. That will fill in when we get to the Balance Sheet projections. Copy cell K64 to columns L and M.
We can also project future interest expense similar to the manner in which we calculated Depreciation. In this case, however, let’s just use last year to estimate the interest rate since and apply it to future debt levels rather than going as far back as 2002. To project 2010’s interest expense, we’ll express 2009 Interest expense as a percentage of all 2009 interest-bearing debt (Revolving line of credit or “Revolver”, current portion of L-T debt, and L-T debt). The equation in cell K39 will appear as
=$J$39/($J$71+$J$72+$J$76)*(K71+K72+K76)
Again, the result is a zero since we have not forecasted our future debt levels yet.
Lastly, we need to project Income Taxes. Again, there are different ways we could do this. For instance, we could calculate a weighted average of past taxes as a percentage of past taxable income to get an “effective” tax rate, or we could just assume an average tax rate for income at our projected levels going forward since we don’t know the extent to which the accounting department can postpone or minimize our future tax liabilities. Let’s just assume that the future tax rate will be 40% (a combination of both federal and state taxes). Set the cells K43 – M43 equal to 40% of each year’s taxable income.
At this point, we’ve completed the modeling of the income statements for the next three years. In the next exercise, we’ll forecast the Balance Sheets in order to determine if we’ll need additional financing or if we’ll have surplus funding available.