A G H 1 J L M N 0 P R S Requirements 1 3 4 5 6 7 8 B D E F Sora Industries has 60 million outstanding shares, S120 million in debt, 540 million in cash and the following projected free cash flow for the next four years: Year 0 1 2 3 4 Earnings and FCF Forecast (5 millions) Sales 433.0 468.0 516.0 547.0 574.3 Growth versus Prior Year 8.79 10.3% 6.0% 5.09 Cost of Goods Sold -313.6 -345.7 -366.5 -384.8 Gross Profit 154.4 170.3 180.5 1895 Selling, General, and Administrative -93.6 -103.2 -109.4 -114.9 Depreciation -7.0 -7.5 -9.0 -9.5 EBIT 53.8 59.6 62.1 65.2 Less: Income Tax at 40% -21.5 -23.8 -24.8 -26.1 Plus: Depreciation 7.0 7.5 9.0 9.5 Less: Capital Expenditures -7.7 -10.0 -9.9 -10.4 Less: Increase in NWC -6.3 -8.6 -5.6 4.9 Free Cash Flow 24.6 30.8 33.3 9 10 11 2. In cell H36, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 3. In cell H37, by using cell references, calculate the total cash flow for year 4 (1 pt.). 4. In cell D39, by using cell references and the function NPV. calculate the enterprise value of the company under baseline information (1 pt.). 5. In cell D40, by using cell references, calculate the price per share under baseline information (1 pt.). 6. In cells E51:H51, by using cell references, calculate the cost of goods sold for years 1:4, respectively (4 pt.). Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 7. In cells E52:H52, by using cell references, calculate the gross profit for years 1:4, respectively (4 pt.). 8. In cells E55:H55, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.). 9. In cells E56:H56, by using cell references, calculate the income tax for years 1:4, respectively (4 pt.). Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 10. In cells E60:H60, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). 11. In cell H61, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 12. In cells E62:H62, by using cell references, calculate the total cash flow for years 1:4, respectively (4 pt.). 13. In cell D64. by using cell references and the function NPV, calculate the enterprise value of the company under new information (1) (1 pt.). 14. In cell D65, by using cell references, calculate the price per share under new information (1) (1 pt.). 15. In cells E78:H78, by using cell references, calculate the selling, general, and administrative expenses for years 1:4, respectively (4 pt.). 12 13 14 15 16 25.3 17 18 19 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 ). 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.) Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 16. In cells E80:H80, by using cell references, calculate the EBIT for years 1:4. respectively (4 pt.). 20 17. In cells E81:H81, by using cell references, calculate the income tax for years 1:4, respectively (4 pt.). 21 22 23 24 25 Shares outstanding (millions) 60 Debt (millions) S120 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? 26 27 Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 18. In cells E85:H85, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). 19. In cell H86, by using cell references, calculate the al value of the company in year 4 (1 pt.). 20. In cells E87:H87, by using cell references, calculate uie total cash flow for years 1:4, respectively (4 pt.). 21. In cell D89, by using cell references and the function NPV, calculate the enterprise value of the company under new information (2) (1 pt.). 22. In cell D90, by using cell references, calculate the price per share under new information (2) (1 pt.). 23. In cell D97, by using cell references, calculate the initial value for the net working capital under new information (3) (1 pt.). 24. In cells El11:H111, by using cell references, calculate the increase in net working capital for years 1:4, respectively (4 pt.). 25. In cells E112:H112, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). 26. In cell H113, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 27. In cells E114:H114, by using cell references, calculate the total cash flow for years 1:4. respectively (4 pt.). 28. In cell D116, by using cell references and the function NPV, calculate the enterprise value of the company under new information (3) (1 pt.). 29. In cell D117, by using cell references, calculate the price per share under new information (3) (1 pt.). 28 29 Long-run growth rate Cost of capital 5% 10% 30 31 0 1 2 3 32 33 Year Terminal value (millions) Total cash flow (millions) 34 $0.01 25.3 $24.6 $30.8 A B H 36 37 39 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 679 Actual COGS/sales 70% 40 41 42 43 14 0 1 2 3 4 S433.0 45 46 47 48 $468.0 8.1% $516.01 10.3% $547.0 6.0% $574.3 5.0% 19 $93.6 $7.0 $103.2 $7.5 -$109.4 $9.0 $114.9 $9.5 50 51 52 53 54 55 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 -S6.3 $7.50 $10.0 $8.6 $9.0 -$9.9 $5.61 $9.5 $10.4 -S4.9 56 57 58 59 60 81 S0.0 62 03 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 20% Actual SG&A expenses/sales 16% 65 66 57 69 0 1 3 S433.0 70 71 72 73 74 75 76 77 78 S468.0 8.1% $313.6 $154.41 $516.0 10.3% $345.7 S170.3 $547.0 6.0% $366.51 $180.5 $574.3 5.0% $384.8 $189.5 $7.0 $7.51 -$9.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 Terminal value (millions) Total cash flow (millions) -$9.5 79 80 81 $7.0 $7.7 $6.3 $7.5 $10.0 $8.6 $9.0 $9.9 $5.6 $9.5 $10.4 -S4.9 83 S0.0 or A G H 1 J L M N 0 P R S Requirements 1 3 4 5 6 7 8 B D E F Sora Industries has 60 million outstanding shares, S120 million in debt, 540 million in cash and the following projected free cash flow for the next four years: Year 0 1 2 3 4 Earnings and FCF Forecast (5 millions) Sales 433.0 468.0 516.0 547.0 574.3 Growth versus Prior Year 8.79 10.3% 6.0% 5.09 Cost of Goods Sold -313.6 -345.7 -366.5 -384.8 Gross Profit 154.4 170.3 180.5 1895 Selling, General, and Administrative -93.6 -103.2 -109.4 -114.9 Depreciation -7.0 -7.5 -9.0 -9.5 EBIT 53.8 59.6 62.1 65.2 Less: Income Tax at 40% -21.5 -23.8 -24.8 -26.1 Plus: Depreciation 7.0 7.5 9.0 9.5 Less: Capital Expenditures -7.7 -10.0 -9.9 -10.4 Less: Increase in NWC -6.3 -8.6 -5.6 4.9 Free Cash Flow 24.6 30.8 33.3 9 10 11 2. In cell H36, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 3. In cell H37, by using cell references, calculate the total cash flow for year 4 (1 pt.). 4. In cell D39, by using cell references and the function NPV. calculate the enterprise value of the company under baseline information (1 pt.). 5. In cell D40, by using cell references, calculate the price per share under baseline information (1 pt.). 6. In cells E51:H51, by using cell references, calculate the cost of goods sold for years 1:4, respectively (4 pt.). Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 7. In cells E52:H52, by using cell references, calculate the gross profit for years 1:4, respectively (4 pt.). 8. In cells E55:H55, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.). 9. In cells E56:H56, by using cell references, calculate the income tax for years 1:4, respectively (4 pt.). Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 10. In cells E60:H60, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). 11. In cell H61, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 12. In cells E62:H62, by using cell references, calculate the total cash flow for years 1:4, respectively (4 pt.). 13. In cell D64. by using cell references and the function NPV, calculate the enterprise value of the company under new information (1) (1 pt.). 14. In cell D65, by using cell references, calculate the price per share under new information (1) (1 pt.). 15. In cells E78:H78, by using cell references, calculate the selling, general, and administrative expenses for years 1:4, respectively (4 pt.). 12 13 14 15 16 25.3 17 18 19 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 ). 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.) Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 16. In cells E80:H80, by using cell references, calculate the EBIT for years 1:4. respectively (4 pt.). 20 17. In cells E81:H81, by using cell references, calculate the income tax for years 1:4, respectively (4 pt.). 21 22 23 24 25 Shares outstanding (millions) 60 Debt (millions) S120 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? 26 27 Note: The outputs of the expression or function you typed in these cells are expected as negative numbers. 18. In cells E85:H85, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). 19. In cell H86, by using cell references, calculate the al value of the company in year 4 (1 pt.). 20. In cells E87:H87, by using cell references, calculate uie total cash flow for years 1:4, respectively (4 pt.). 21. In cell D89, by using cell references and the function NPV, calculate the enterprise value of the company under new information (2) (1 pt.). 22. In cell D90, by using cell references, calculate the price per share under new information (2) (1 pt.). 23. In cell D97, by using cell references, calculate the initial value for the net working capital under new information (3) (1 pt.). 24. In cells El11:H111, by using cell references, calculate the increase in net working capital for years 1:4, respectively (4 pt.). 25. In cells E112:H112, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). 26. In cell H113, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 27. In cells E114:H114, by using cell references, calculate the total cash flow for years 1:4. respectively (4 pt.). 28. In cell D116, by using cell references and the function NPV, calculate the enterprise value of the company under new information (3) (1 pt.). 29. In cell D117, by using cell references, calculate the price per share under new information (3) (1 pt.). 28 29 Long-run growth rate Cost of capital 5% 10% 30 31 0 1 2 3 32 33 Year Terminal value (millions) Total cash flow (millions) 34 $0.01 25.3 $24.6 $30.8 A B H 36 37 39 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 679 Actual COGS/sales 70% 40 41 42 43 14 0 1 2 3 4 S433.0 45 46 47 48 $468.0 8.1% $516.01 10.3% $547.0 6.0% $574.3 5.0% 19 $93.6 $7.0 $103.2 $7.5 -$109.4 $9.0 $114.9 $9.5 50 51 52 53 54 55 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 -S6.3 $7.50 $10.0 $8.6 $9.0 -$9.9 $5.61 $9.5 $10.4 -S4.9 56 57 58 59 60 81 S0.0 62 03 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 20% Actual SG&A expenses/sales 16% 65 66 57 69 0 1 3 S433.0 70 71 72 73 74 75 76 77 78 S468.0 8.1% $313.6 $154.41 $516.0 10.3% $345.7 S170.3 $547.0 6.0% $366.51 $180.5 $574.3 5.0% $384.8 $189.5 $7.0 $7.51 -$9.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 Terminal value (millions) Total cash flow (millions) -$9.5 79 80 81 $7.0 $7.7 $6.3 $7.5 $10.0 $8.6 $9.0 $9.9 $5.6 $9.5 $10.4 -S4.9 83 S0.0 or
