E H D B Problem 8-9 Elmdale Enterprises is deciding whether to expand its production facilities. Although long-term cash flows are difficult to estimate, management has projected the cash flows below for the first two years (in millions of dollars). Complete the steps below using cell references to given data or previous calculations. In some cases, a simple cell reference is all you need. To copy/paste a formula across a row or down a column, an absolute cell reference or a mixed cell reference may be preferred. If a specific Excel function is to be used, the directions will specify the use of that function. Do not type in numerical data into a cell or function. Instead, make a reference to the cell in which the data is found. Make your computations only in the blue cells highlighted below. In all cases, unless otherwise directed, use the earliest appearance of the data in your formulas, usually the Given Data section. 4 5 6 7 8 Year 2 S160 S60 9 a. What are the incremental earnings for this project for years 1 and 2? b. What are the free cash flows for this project for the first two years? Year 1 Revenues $125 Costs of goods sold and operating expenses other S40 than depreciation Depreciation S25 Increase in net working capital S5 Capital expenditures Marginal corporate tax rate 35% 10 11 12 S36 S8 S40 35% 13 S30 14 a. What are the incremental earnings for this project for years 1 and 2? 15 16 17 18 19 Incremental Earnings Forecast (million) Year Sale 8.9 Year 1 Year 2 H A B 18 19 Year 1 Year 2 20 Incremental Earnings Forecast (million) Year Sales Less: Costs of goods sold and operating expenses other than depreciation Less: Depreciation EBIT Less: Income tax at 35% Unlevered Net Income 21 22 23 24 25 26 27 28 29 30 Year 2 b. What are the free cash flows for this project for the first two years? Free Cash Flows (million) Year Year 1 Plus: Depreciation Less: Capital Expenditures Less: Increases in NWC Free Cash Flow 31 32 33 34 35 39 36 37 Requirements 38 1. Start Excel - completed 2. In cells F20:620, by using cell references, calculate the sales for years 1:2, respectively (2 p.) 3. In cells F21:21, by using cell references, calculate the costs of goods sold and operating expenses for years 1:2, respectively (2 pt.). Note: The outputs of the expression or function you typed in those cells are expected as negative numbers. 4. In cells F22:622, by using cell references, calculate the depreciation for years 1:2, respectively (2 pt.) Note: The depreciation for years 1:2 should be entered as negative numbers, 5. In cells F23:23. by using cell references, calculate the EBIT for years 1:2. respectively (2 pt.). 40 8-9 F20 fx G H B c D 40 42 42 43 44 45 36 37 Requirements 38 1. Start Excel - completed 39 2. In cells F20:20, by using cell references, calculate the sales for years 1:2, respectively (2 pt.). 3. In cells F21:921, by using cell references, calculate the costs of goods sold and operating expenses for years 1:2, respectively (2 pt.) Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 4. In cells F22:G22, by using cell references, calculate the depreciation for years 1:2, respectively (2 pt.) Note: The depreciation for years 1:2 should be entered as negative numbers, 5. In cells F23:G23, by using cell references, calculate the EBIT for years 1:2, respectively (2 pt.). 6. In cells F24:G24, by using cell references, calculate the income tax for years 1:2, respectively (2 pt.). Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 7. In cells F25:625, by using cell references, calculate the unlevered net income for years 1:2, respectively (2 p.). 8. In cells F31:G31, by using cell references, add back depreciation for years 1:2, respectively (2 pt.). Note: The outputs of the expression or function you typed in these cells are expected as positive numbers. 9. In cells F32:G32, by using cell references, subtract capital expenditures for years 1:2, respectively (2 pt.). Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 10. In cells F33:33, by using cell references, subtract the increase in net working capital for years 1:2, respectively (2 p.) Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. In cells F34:G34, by using cell references, calculate the free cash flows for years 1:2, respectively (pl.). 12. Save the workbook. Close the workbook and then exit Excel Submit the workbook as directed, 46 67 49 50 51 52 53 11 F 54 55 56 8.9
