Part 1: Projecting Free Cash Flows
Open the file HW3.xls. You will notice that there are five worksheets, or tabs, in the file. In this part of the homework, you will not use the 'Proj. of Financials & Valuation' tab. The actual historical financial statements for Van Leer are in the rightmost tab, labeled 'Actual'. These financial statements are identical to those in the lecture notes for the class.
a. Go to the 'Historical Analysis' tab, compute the historical free cash flows and calculate the historical ratios. All the cells that need to be computed are highlighted in yellow. You should compare your results to the calculations in the class notes to make sure that you have not made an error somewhere.
b. Next, go to the 'Inputs' tab. In the yellow cells, fill in the projected values for the various ratios one year ahead using the numbers in the class notes. Be careful not to enter anything in other cells.
c. Open the tab labeled 'Projection of FCFF' and use the projected ratios from the 'Inputs' tab to build formulas that complete the one year ahead projections of the operating accounts that are needed to forecast the free cash flow to the firm (rows 6 through 22). All the cells that need to be computed are highlighted in yellow. For example, in cell D6, sales next year should be computed as sales last year (cell C6) times one plus the projected growth rate from the 'Inputs' tab. You should compare your results to the calculations in the class notes to make sure that you have not made an error somewhere.
d. Finally, using the operating forecasts you have obtained in c. above, estimate the components of FCFF in rows 25 through 33 by entering the appropriate formulas into the cells. Again, the cells that need to be computed are highlighted in yellow and you should compare your results to the calculations in the class notes to make sure that you have not made an error somewhere.
Part 2: Projecting Financial Statements
In this part of the assignment, you will complete the one-year ahead financial statement projections and use the plug approach to balance the projected balance sheet. Our focus therefore now turns to the tab 'Proj. of Financials and Valuation'.
a. Go to the 'Inputs' tab and enter the projection for the dividend and debt ratios and interest rates (rows 24 through 28, column C, the tan colored cells). Use the class notes to find these inputs (10% for the one-year ahead dividend growth rate, etc).
b. Go to the tab 'Proj. of Financials and Valuation'. Start with the income statement in rows 4 through 15. In column C, enter the results from the operating income projections that you already completed in the 'Projection of FCFF' tab of the spreadsheet (for net sales, cost of goods sold, etc) to start projecting the income statement. In cells C9 and C10, estimate the projections based in the amounts of short term debt and short term investments in place at the beginning of the projected year, i.e., the end of the most recent year. Project dividends in cell C14 using the dividend growth rate from a. above and compute additions to retained earnings in cell C15 as Net income minus Dividends.
Then turn to the projected balance sheet in rows 19 through 36. Start by entering the results from the operating account projections that you already completed in the 'Projection of FCFF' tab of the spreadsheet (for cash, inventory, etc) but leave cells C20 and C29 blank for now (because short term debt and short term investments are your plugs). Long term debt in cell C31 is projected using the ratio of long term debt to net PPE from cell C25 in the 'Inputs' tab. For, common stock, assume that there will be no new stock issues, so the amount for next year should equal last year's amount. Retained earnings is projected as last year's retained earnings plus additions to retained earnings from cell C15. Remember to leave cells C20 and C29 blank for now, as those are your plugs.
c. Create a new entry in cell C39 in the row labeled 'Specified assets'. The formula in cell C39 should be the sun of the operating asset accounts, cash plus inventory plus accounts receivable + net PPE. This will give you your total assets ignoring short term investments.
d. Create another new entry in cell C40 in the row labeled 'Specified liabilities' It will contain the sum: accounts payable plus accrued expenses plus long term debt plus total common equity. The resulting number will change after we make some further changes below.
e. In cell C41 calculate the difference between Specified assets and Specified liabilities, namely C39-C40. The row is labeled 'Asset-liability gap' and this number represents the magnitude of the plug we need to balance the balance sheet.
f. Enter another formula in cell C42 in the row labeled 'Discrepancy' to calculate the difference between the total assets (cell C25) and total liabilities plus equity (cell C36).
g. Now for the last step, we will determine the plug that makes the balance sheet balance. Remember that we are using short term debt and short term investments as the plug. To operationalize this, we use the asset-liability gap calculation in cell C41. If the gap is positive, set short-term debt equal to the amount of the gap, i.e., enter "=IF(C41>0,C41,0)" in cell C29. If the gap is negative, set short-term investments equal to minus the amount of the gap, i.e., enter "=IF(C41<0,-C41,0)" in cell C20. If you did everything correctly, you should get the same results that we got in the class notes: you should get zero for short term investments and 40.2 for short term debt.
Part 3: Multiyear Projections
In this final part of the assignment, you will extend the one-year ahead FCFF projections out into the future and complete the valuation of the company, as we did in the notes.
a. In the 'Inputs' tab, go to columns D through I and fill in the data (blue cells) using the numbers from the class notes.
b. Now open the 'Projection of FCFF' tab. Complete the projections of the future operating entries for the future income statements (blue cells in rows 6 through 10). Just copy the formulas from column D into columns E through J (I suggest using copy, paste special, formulas, to preserve the formatting).
c. Similarly, fill out the balance sheet and free cash flow sections of the 'Projection of FCFF' tab (rows 13 through 33). All the cells that need to be computed are highlighted in blue. You should compare your results to the calculations in the class notes to make sure that you have not made an error somewhere.
d. Now go to the 'Proj. of Financials and Valuation' tab and complete the financial statement projections in a similar fashion (rows 4 through 42, blue cells). This will give you a full set of pro forma projections.
e. All that remains is to discount the projected FCFFs to value the firm and the equity. In row 45 you should enter formulas that copy the FCFF projections that were obtained in the tab 'Projections of FCFF'. For example, cell C45 should have the formula "='Projection of FCFF'!D32". Enter the WACC (11.5% from the notes) in cell B47. Rows 49 and 50 then compute the present values of the cash flows. The formulas are already entered so no changes are needed but you should satisfy yourself that they are correct.
f. Cell B52 should be set equal to the value of short term investments (cell B20) and the value of the firm in B53 is the value of operations (cell B50) plus the value of short term investments (cell B52). Cell B54 should be set equal to B29 plus B31. There are 10 million shares outstanding. The estimated value per share you obtain should be $52.38, the value we obtained in the class notes. If you get a different amount, you should go back and check your work against the numbers in the class notes for the intermediate steps.