Please help answer these questions using excel functions described in the rubric on the second photo.

D G H M N a. Covan has 8 million shares outstanding, $3 million in excess cash, and it has no debt. If its cost of capital is 12%, what should its stock price be? b. Covan reinvests all its FCF and has no plans to add debt or change its cash holdings. If you plan to sell Covan at the beginning of year 2, what should you expect its price to be? c. Assume you bought Covan stock at the beginning of year 1. What is your expected return from holding Covan stock until year 2? 2 Year Free Cash Flow (million) 3 4 10 $ 12 s 13 S 4% Growth 4% Growth rate Number of periods until steady growth a. Covan has 8 million shares outstanding, $3 million in excess cash, and it has no debt. If its cost of capital is 12%, what should its stock price be? S Number of shares (million) Excess cash (million) Debt (million) Cost of capital 8 3 $0.00 12% 2 24 Cash flow in year 5 (million) Value in year 4 (million) Enterprise value (million) Vo Value of equity (million) Price per share S S S S $ 14.56 14.00 152.31 155.31 19.41 25 26 27 b. Covan reinvests all its FCF and has no plans to add debt or change its cash holdings. If you plan to sell Covan 29 at the beginning of vear what should vou expect its price to be 27 b. Covan reinvests all its FCF and has no plans to add debt or change its cash holdings. If you plan to sell Covan at the beginning of year 2, what should you expect its price to be? 28 29 After-sale number of periods left until staedy growth 30 31 32 Enterprise value (million) Vo Value of equity (million) Price per share $ $ $ 160.59 163.59 20.45 34 35 c. Assume you bought Covan stock at the beginning of year 1. What is your expected return from holding Covan stock until year 2? 36 37 38 39 Holding period retum 5.33% 40 43 44 4 45 41 Requirements 42 1 In cell D22, by using cell references, calculate the cash flow in year 5 (1 pt.). 2 In cell D23, by using cell references, calculate the value of the company in year 4 (1 pt.). 3 In cell D24, by using the NPV function and cell references, calculate the enterprise value of the company. Note: Use cell D13 as the number of periods to discount the value you calculated in cell D23. (1 pt.). In cell D25, by using cell references, calculate the value of the equity. Note: Even though the value of "Debt" is zero, include cell D19 in your expression to calculate the value of equity. (1 pt.). 5 In cell D26, by using cell references, calculate the price per share (1 pr.). In cell D32, by using the NPV function and cell references, calculate the enterprise value of the company in one year. Note: Use cell D30 as the number of periods to discount the value you calculated in cell D23. (1 pt.). 7 In cell D33, by using cell references, calculate the value of the equity in one year. Note: Even though the value of "Debt" is zero, include cell D19 in your expression to calculate the value of equity. (1 pt.). 8 In cell D34, by using cell references, calculate the price per share in one year (1 pr.). In cell D38, by using cell references, calculate the holding period retum (1 pr.). 45 6 47 48 49 50 9 51 D G H M N a. Covan has 8 million shares outstanding, $3 million in excess cash, and it has no debt. If its cost of capital is 12%, what should its stock price be? b. Covan reinvests all its FCF and has no plans to add debt or change its cash holdings. If you plan to sell Covan at the beginning of year 2, what should you expect its price to be? c. Assume you bought Covan stock at the beginning of year 1. What is your expected return from holding Covan stock until year 2? 2 Year Free Cash Flow (million) 3 4 10 $ 12 s 13 S 4% Growth 4% Growth rate Number of periods until steady growth a. Covan has 8 million shares outstanding, $3 million in excess cash, and it has no debt. If its cost of capital is 12%, what should its stock price be? S Number of shares (million) Excess cash (million) Debt (million) Cost of capital 8 3 $0.00 12% 2 24 Cash flow in year 5 (million) Value in year 4 (million) Enterprise value (million) Vo Value of equity (million) Price per share S S S S $ 14.56 14.00 152.31 155.31 19.41 25 26 27 b. Covan reinvests all its FCF and has no plans to add debt or change its cash holdings. If you plan to sell Covan 29 at the beginning of vear what should vou expect its price to be 27 b. Covan reinvests all its FCF and has no plans to add debt or change its cash holdings. If you plan to sell Covan at the beginning of year 2, what should you expect its price to be? 28 29 After-sale number of periods left until staedy growth 30 31 32 Enterprise value (million) Vo Value of equity (million) Price per share $ $ $ 160.59 163.59 20.45 34 35 c. Assume you bought Covan stock at the beginning of year 1. What is your expected return from holding Covan stock until year 2? 36 37 38 39 Holding period retum 5.33% 40 43 44 4 45 41 Requirements 42 1 In cell D22, by using cell references, calculate the cash flow in year 5 (1 pt.). 2 In cell D23, by using cell references, calculate the value of the company in year 4 (1 pt.). 3 In cell D24, by using the NPV function and cell references, calculate the enterprise value of the company. Note: Use cell D13 as the number of periods to discount the value you calculated in cell D23. (1 pt.). In cell D25, by using cell references, calculate the value of the equity. Note: Even though the value of "Debt" is zero, include cell D19 in your expression to calculate the value of equity. (1 pt.). 5 In cell D26, by using cell references, calculate the price per share (1 pr.). In cell D32, by using the NPV function and cell references, calculate the enterprise value of the company in one year. Note: Use cell D30 as the number of periods to discount the value you calculated in cell D23. (1 pt.). 7 In cell D33, by using cell references, calculate the value of the equity in one year. Note: Even though the value of "Debt" is zero, include cell D19 in your expression to calculate the value of equity. (1 pt.). 8 In cell D34, by using cell references, calculate the price per share in one year (1 pr.). In cell D38, by using cell references, calculate the holding period retum (1 pr.). 45 6 47 48 49 50 9 51