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 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? 15 In cell range E82:H82, by using cell references, add back the depreciation for years 1:4, respectively (4 pt.). In cell range E83:H83, by using cell references, calculate the capital expenditure for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E83:H83 are expected as negative numbers. 16 In cell range E84:H84, by using cell references, calculate the increase in net working capital for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D94 as the assumed NWC at 18% of sales. In cell range E85: H85, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). Note: Refer only to the cells provided in the table for part c in your calculations for the free cash flows. 17 In cell H86, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 18 In cell range D87:H87, by using cell references, calculate the total cash flow for years 0:4, respectively (5 pt.). Note: Refer only to the cells provided in the table for part c in your calculations. 19 In cell D89, by using cell references, calculate the enterprise value of the company under new information (2) (1 pt.). Note: Refer to cells E87:H87 in your calculations for enterprise value. In cell D90, by using cell references, calculate the price per share under new information (2) (1 pt.). 20 In cell D97, by using cell references, calculate the initial value for the net working capital under new information (3) (1 pt.). 21 In cell range E103:H103, by using cell references, calculate the cost of goods sold for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D44 as the assumed COGS at 67% of sales. In cell range E104:H104, by using cell references, calculate the gross profit for years 1:4, respectively (4 pt.). In cell range E105:H105, by using cell references, calculate the selling, general, and administrative expenses for years 1:4, respectively (4 pt.). In cell range E106:H106, by using cell references, calculate the depreciation for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D69 as the assumed SG\&A expenses at 20% of sales. 23 In cell range E107:H107, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.). In cell range E108: H108, by using cell references, calculate the income tax at 25% for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E108: H108 are expected as negative numbers. 24 In cell range E109:H109, by using cell references, add back the depreciation for years 1:4, respectively (4 pt.). In cell range E110:H110, by using cell references, calculate the capital expenditure for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E110:H110 are expected as negative numbers. 25 In cell range E111:H111, by using cell references, calculate the increase in net working capital for years 1:4, respectively (4 pt.). In cell range E112:H112, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). Note: Refer only to the cells provided in the table for part d in your calculations for the free cash flows. 26 In cell H113, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 27 In cell range D114:H114, by using cell references, calculate the total cash flow for years 0:4, respectively (5 pt.). Note: Refer only to the cells provided in the table for part 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.) quirements 1 In cell H36, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 2 In cell range D37:H37, by using cell references, calculate the total cash flow for years 0:4, respectively (5 pt.). 3 In cell D39, by using cell references, calculate the enterprise value of the company under baseline information (1 pt.). Note: Refer to cells E37:H37 in your calculations for enterprise value. In cell D40, by using cell references, calculate the price per share under baseline information (1 pt.). 4 In cell range E51:H51, by using cell references, calculate the cost of goods sold for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in these cells are expected as negative numbers. In cell range E52:H52, by using cell references, calculate the gross profit for years 1:4, respectively (4 pt.). 5 In cell range E53:H53, by using cell references, calculate the selling, general, and administrative expenses for years 1:4, respectively (4 pt.). In cell range E54:H54, by using cell references, calculate the depreciation for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D69 as the assumed SG\&A expenses at 20% of sales. 6 In cell range E55:H55, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.). In cell range E56:H56, by using cell references, calculate the income tax at 25% for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E56:H56 are expected as negative numbers. 7 In cell range E57:H57, by using cell references, add back the depreciation for years 1:4, respectively (4 pt.). In cell range E58:H58, by using cell references, calculate the capital expenditure for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E58:H58 are expected as negative numbers. 8 In cell range E59:H59, by using cell references, calculate the increase in net working capital for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D94 as the assumed NWC at 18% of sales. In cell range E60:H60, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). Note: Refer only to the cells provided in the table for part b in your calculations for the free cash flows. 9 In cell H61, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 10 In cell range D62:H62, by using cell references, calculate the total cash flow for years 0:4, respectively (5 pt.). Note: Refer only to the cells provided in the table for part b in your calculations. 11 In cell D64, by using cell references, calculate the enterprise value of the company under new information (1) (1 pt.). In cell D65, by using cell references, calculate the price per share under new information (1) (1 pt.). Note: Refer to cells E62:H62 in your calculations for enterprise value. 12 In cell range E76:H76, by using cell references, calculate the cost of goods sold for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D44 as the assumed COGS at 67% of sales. In cell range E77:H77, by using cell references, calculate the gross profit for years 1:4, respectively (4 pt.). 13 In cell range E78:H78, by using cell references, calculate the selling, general, and administrative expenses for years 1:4, respectively (4 pt.). In cell range E79:H79, by using cell references, calculate the depreciation for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in these cells are expected as negative numbers. 14 In cell range E80:H80, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.). In cell range E81: H81, by using cell references, calculate the income tax at 25% for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E81:H81 are expected as negative numbers. 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 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? 15 In cell range E82:H82, by using cell references, add back the depreciation for years 1:4, respectively (4 pt.). In cell range E83:H83, by using cell references, calculate the capital expenditure for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E83:H83 are expected as negative numbers. 16 In cell range E84:H84, by using cell references, calculate the increase in net working capital for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D94 as the assumed NWC at 18% of sales. In cell range E85: H85, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). Note: Refer only to the cells provided in the table for part c in your calculations for the free cash flows. 17 In cell H86, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 18 In cell range D87:H87, by using cell references, calculate the total cash flow for years 0:4, respectively (5 pt.). Note: Refer only to the cells provided in the table for part c in your calculations. 19 In cell D89, by using cell references, calculate the enterprise value of the company under new information (2) (1 pt.). Note: Refer to cells E87:H87 in your calculations for enterprise value. In cell D90, by using cell references, calculate the price per share under new information (2) (1 pt.). 20 In cell D97, by using cell references, calculate the initial value for the net working capital under new information (3) (1 pt.). 21 In cell range E103:H103, by using cell references, calculate the cost of goods sold for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D44 as the assumed COGS at 67% of sales. In cell range E104:H104, by using cell references, calculate the gross profit for years 1:4, respectively (4 pt.). In cell range E105:H105, by using cell references, calculate the selling, general, and administrative expenses for years 1:4, respectively (4 pt.). In cell range E106:H106, by using cell references, calculate the depreciation for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D69 as the assumed SG\&A expenses at 20% of sales. 23 In cell range E107:H107, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.). In cell range E108: H108, by using cell references, calculate the income tax at 25% for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E108: H108 are expected as negative numbers. 24 In cell range E109:H109, by using cell references, add back the depreciation for years 1:4, respectively (4 pt.). In cell range E110:H110, by using cell references, calculate the capital expenditure for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E110:H110 are expected as negative numbers. 25 In cell range E111:H111, by using cell references, calculate the increase in net working capital for years 1:4, respectively (4 pt.). In cell range E112:H112, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). Note: Refer only to the cells provided in the table for part d in your calculations for the free cash flows. 26 In cell H113, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 27 In cell range D114:H114, by using cell references, calculate the total cash flow for years 0:4, respectively (5 pt.). Note: Refer only to the cells provided in the table for part 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.) quirements 1 In cell H36, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 2 In cell range D37:H37, by using cell references, calculate the total cash flow for years 0:4, respectively (5 pt.). 3 In cell D39, by using cell references, calculate the enterprise value of the company under baseline information (1 pt.). Note: Refer to cells E37:H37 in your calculations for enterprise value. In cell D40, by using cell references, calculate the price per share under baseline information (1 pt.). 4 In cell range E51:H51, by using cell references, calculate the cost of goods sold for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in these cells are expected as negative numbers. In cell range E52:H52, by using cell references, calculate the gross profit for years 1:4, respectively (4 pt.). 5 In cell range E53:H53, by using cell references, calculate the selling, general, and administrative expenses for years 1:4, respectively (4 pt.). In cell range E54:H54, by using cell references, calculate the depreciation for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D69 as the assumed SG\&A expenses at 20% of sales. 6 In cell range E55:H55, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.). In cell range E56:H56, by using cell references, calculate the income tax at 25% for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E56:H56 are expected as negative numbers. 7 In cell range E57:H57, by using cell references, add back the depreciation for years 1:4, respectively (4 pt.). In cell range E58:H58, by using cell references, calculate the capital expenditure for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E58:H58 are expected as negative numbers. 8 In cell range E59:H59, by using cell references, calculate the increase in net working capital for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D94 as the assumed NWC at 18% of sales. In cell range E60:H60, by using cell references, calculate the free cash flow for years 1:4, respectively (4 pt.). Note: Refer only to the cells provided in the table for part b in your calculations for the free cash flows. 9 In cell H61, by using cell references, calculate the terminal value of the company in year 4 (1 pt.). 10 In cell range D62:H62, by using cell references, calculate the total cash flow for years 0:4, respectively (5 pt.). Note: Refer only to the cells provided in the table for part b in your calculations. 11 In cell D64, by using cell references, calculate the enterprise value of the company under new information (1) (1 pt.). In cell D65, by using cell references, calculate the price per share under new information (1) (1 pt.). Note: Refer to cells E62:H62 in your calculations for enterprise value. 12 In cell range E76:H76, by using cell references, calculate the cost of goods sold for years 1:4, respectively (4 pt.). Note: (1) The outputs of the expressions you typed in these cells are expected as negative numbers. (2) Use cell D44 as the assumed COGS at 67% of sales. In cell range E77:H77, by using cell references, calculate the gross profit for years 1:4, respectively (4 pt.). 13 In cell range E78:H78, by using cell references, calculate the selling, general, and administrative expenses for years 1:4, respectively (4 pt.). In cell range E79:H79, by using cell references, calculate the depreciation for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in these cells are expected as negative numbers. 14 In cell range E80:H80, by using cell references, calculate the EBIT for years 1:4, respectively (4 pt.). In cell range E81: H81, by using cell references, calculate the income tax at 25% for years 1:4, respectively (4 pt.). Note: The outputs of the expressions you typed in cell range E81:H81 are expected as negative numbers
