`

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.
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.).
22	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.
28	In cell D116, by using cell references, calculate the enterprise value of the company under new information (3) (1 pt.).
	Note: Refer to cells E114:H114 in your calculations for enterprise value.
	In cell D117, by using cell references, calculate the price per share under new information (3) (1 pt.).

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: 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.) 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 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.) 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? Assumed COGS/sales Actual COGS/sales 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: 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.) 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 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.) 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? Assumed COGS/sales Actual COGS/sales