PLEASE USE AND LIST FUNCTIONS IN EXCEL, THANKS!!!
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: 1 2 3 433.0 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 547.0 6.0% -366.5 180.5 -109.4 -9.0 621 -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 60 Shares outstanding (millions) Debt (millions) $120 Cash (millions) $40 Tax rate 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 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 4 $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.00 -$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.00 -$8.6 $9.0 -$9.91 -$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% -$384.8 $189.5 $7.0 -$7.5 -$9.00 -$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.31 $7.5 -$10.0 -$8.6 $9.00 -$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 1). 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 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 Teminal value (millions) Total cash flow (millions) $468.0 8.1 % $313.6 $154.4 $93.6 $ 7.0 $53.8 $21.5 $7.0 $7.7 $516.0 10.3% $345.7 $170.31 $103.2 $7.5 $59.6 $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 $574.3 5.0% $384.8 $189.5 $114.9 $9.5 $65.2 $26.1 $9.5 $10.4 $0.0 Enterprise value (millions) Stock price
