Problem 9-21 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. Sora Industries has 60 million outstanding shares, $120 million in debt, $40 million in cash, and the following projected free cash flow for the next four years: 3 433.0 547.0 6.0% -366.5 Year Earnings and FCF Forecast ($ millions) Sales Growth versus Prior Year Cost of Goods Sold Gross Profit Selling, General, and Administrative Depreciation EBIT Less: Income Tax at 40% Plus: Depreciation Less: Capital Expenditures Less: Increase in NWC Free Cash Flow 468.0 8.1% -313.6 154.4 -93.6 -7.0 53.8 -21.5 7.0 -7.7 -6.3 25.3 516.0 10.3% -345.7 170.3 -103.2 -7.5 59.6 -23.8 7.5 -10.0 -8.6 24.6 180.5 -109.4 -9.0 62.1 -24.8 9.0 -9.9 -5.6 30.8 574.3 5.0% -384.8 189.5 -114.9 -9.5 65.2 -26.1 9.5 -10.4 -4.9 33.3 a. Suppose Sora's revenue and free cash flow are expected to grow at a 5% rate beyond year 4. If Sora's weighted average cost of capital is 10%, what is the value of Sora's stock based on this information? b. Sora's cost of goods sold was assumed to be 67% of sales. If its cost of goods sold is actually 70% of sales, how would the estimate of the stock's value change? c. Let's return to the assumptions of part (a) and suppose Sora can maintain its cost of goods sold at 67% of sales. However, now suppose Sora reduces its selling, general, and administrative expenses from 20% of sales to 16% of sales. What stock price would you estimate now? (Assume no other expenses, except taxes, are affected.) d. Sora's net working capital needs were estimated to be 18% of sales (which is their current level in year 0). If Sora can reduce this requirement to 12% of sales starting in year 1, but all other assumptions remain as in part (a), what stock price do you estimate for Sora? (Hint: This change will have the largest impact on Sora's free cash flow in year 1.) Shares outstanding (millions) Debt (millions) Cash (millions) Tax rate 60 $120 $40 40% a. Suppose Sora's revenue and free cash flow are expected to grow at a 5% rate beyond year 4. If Sora's weighted average cost of capital is 10%, what is the value of Sora's stock based on this information? Long-run growth rate Cost of capital 5% 10% 0 1 2 3 4 Year Terminal value (millions) Total cash flow (millions) $0.0 $25.3 $24.6 $30.8 Enterprise value (millions) Stock price b. Sora's cost of goods sold was assumed to be 67% of sales. If its cost of goods sold is actually 70% of sales, how would the estimate of the stock's value change? Assumed COGS/sales Actual COGS/sales 67% 70% 0 1 2 3 $433.0 $468.0 8.1% $516.0 10.3% $547.0 6.0% $574.3 5.0% -$93.6 -$7.0 -$103.2 -$7.5 -$109.4 -$9.0 -$114.9 -$9.5 Year Earnings and FCF Forecast (millions) Sales Growth vs. Prior Year Cost of Goods Sold Gross Profit Selling, General, and Administrative Depreciation EBIT Less: Income Tax at 40% Plus: Depreciation Less: Capital Expenditures Less: Increase in NWC Free Cash Flow Terminal value (millions) Total cash flow (millions) $7.0 -$7.7 -$6.3 $7.5 -$10.0 -$8.6 $9.0 -$9.9 $5.6 $9.5 -$10.4 -$4.9 $0.0 Enterprise value (millions) Stock price c. Let's return to the assumptions of part (a) and suppose Sora can maintain its cost of goods sold at 67% of sales. However, now suppose Sora reduces its selling, general, and administrative expenses from 20% of sales to 16% of sales. What stock price would you estimate now? (Assume no other expenses, except taxes, are affected.) Assumed SG&A expenses/sales Actual SG&A expenses/sales 20% 16% 0 1 2 3 4 $433.0 $468.0 8.1% $313.6 $154.4 $516.0 10.3% -$345.7 $170.3 $547.0 6.0% -$366.5 $180.5 $574.3 5.0% -S384.8 $189.5 -$7.0 -$7.5 $9.0 -$9.5 Year Earnings and FCF Forecast (millions) Sales Growth vs. Prior Year Cost of Goods Sold Gross Profit Selling, General, and Administrative Depreciation EBIT Less: Income Tax at 40% Plus: Depreciation Less: Capital Expenditures Less: Increase in NWC Free Cash Flow Terminal value (millions) Total cash flow (millions) $7.0 -$7.7 -$6.3 $7.5 -$10.01 -$8.6 $9.0 -$9.9 -$5.6 $9.5 -$10.4 -$4.9 $0.0 Enterprise value (millions) Stock price d. Sora's net working capital needs were estimated to be 18% of sales (which is their current level in year 0). If Sora can reduce this requirement to 12% of sales starting in year 1, but all other assumptions remain as in part (a), what stock price do you estimate for Sora? (Hint: This change will have the largest impact on Sora's free cash flow in year 1.) Assumed NWC/sales Actual NWC/sales 18% 12% Initial value for NWC (millions) 0 1 2 3 4 $433.0 $574.3 Year Earnings and FCF Forecast (millions) Sales Growth vs. Prior Year Cost of Goods Sold Gross Profit Selling, General, and Administrative Depreciation EBIT Less: Income Tax at 40% Plus: Depreciation Less: Capital Expenditures Less: Increase in NWC Free Cash Flow Terminal value (millions) Total cash flow (millions) $468.0 8.1% -$313.6 $154.4 -$93.6 -$7.0 S53.8 -$21.5 $7.0 -$7.7 $516.0 10.3% -$345.7 $170.3 $103.2 -$7.5 $59.61 -$23.8 $7.5 -$10.0 $547.0 6.0% -$366.5 $180.5 -$109.4 -$9.0 $62.1 -$24.8 $9.0 -$9.9 5.0% $384.8 $189.5 $114.9 -$9.5 S65.2 -$26.1 $9.5 -$10.4 $0.0 Enterprise value (millions) Stock price Requirements 1. Start Excel - completed. 2. In cell D17, by using cell references and the function PV, calculate the net present value of the project (1 pt.). 3. In cells D21 and F21, select either reliable or not reliable to use of the IRR rule for this project and how the cash flows are located (2 pt.). 4. Prepare cells D25:D26 to use the Solver Add-in to determine the minimum percent change in the annual expenses of the defender that will result in a delay of its replacement by the challenger. In cell D25, calculate the net present value of the project using cells D9:D12 and the internal rate of return specified in cell D26 (1 pt.) 5. Use the Solver Add-in to find the internal rate of return (1 pt.). Using Solver Add-in Select the Solver in the Analyze group of the Data tab of the Ribbon (note you should add this Add-in in case you do not have it in the Data tab already). In the Set Objective field, choose cell D25 that corresponds to the net present value calculated using the internal rate of return. Select the Value of option below and enter O in the field. Choose cell D26 in the By Changing Variable Cells field. In the Solver Parameters dialog box, under Subject to the Constraints, click Add. Choose cell D26 in the Cell Reference box. In the relationship drop-down menu choose ">=. Enter 0.01 in the Constraint box. Click OK. Leave the GRG Nonlinear solving method. Click Solve. In the popup window, select the Keep Solver Solution option. Do not check any additional boxes and click OK. As the result, you will see the non-zero internal rate of return in cell D26. The value for the net present value will update automatically. 6. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed