You are evaluating a project that requires an investment of $90 today and provides a single cash flow of $115 for sure one year from now. You decide to use 100% debt financing, that is, you will borrow $90. The risk-free rate is 5% and the tax rate is 25%. Assume that the investment is fully depreciated at the end of the year, so without leverage you would owe taxes on the difference between the project cash flow and the investment. 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. a. Calculate the NPV of this investment opportunity using the APV method. b. Using your answer to part (a), calculate the WACC of the project. c. Verify that you get the same answer using the WACC method to calculate NPV. d. Finally, show that flow-to-equity also correctly gives the NPV of this investment opportunity. a. Calculate the NPV of this investment opportunity using the APV method. b. Using your answer to part (a), calculate the WACC of the project. c. Verify that you get the same answer using the WACC method to calculate NPV. VL NPV d. Finally, show that flow-to-equity also correctly gives the NPV of this investment opportunity. 1. Start Excel - completed. 2. To calculate the project's NPV using the APV method, you need to calculate the unlevered and levered values of the project. In cell D18, by using cell references, calculate the tax basis at year end (1 pt.). 3. In cell D19, by using cell references, calculate the taxes owed (1 pt.). 4. In cell D20, by using cell references, calculate the free cash flow at year end (1 pt.). 5. In cell D21, by using cell references and the function PV, calculate the present value of the free cash flow (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the value of the free cash flow from Step 4 in your calculations. 6. In cell D22, by using cell references, calculate the interest expense (1 pt.). 7. In cell D23, by using cell references, calculate the interest tax shield (1 pt.). 8. In cell D24, by using cell references and the function PV, calculate the present value of the interest tax shield (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the value of the interest tax shield flow from Step 7 in your calculations. 9. In cell D25, by using cell references, calculate the project's levered value (1 pt.). Note: Refer to the present values of the free cash flow and interest tax shield from Steps 5 and 8 in your calculations. 10. In cell D26, by using cell references, calculate the net present value using the APV method (1 pt.). Note: Refer to the project's levered value from Step 10 in your calculations. 11. To calculate the WACC of the project using the answer from part (a), you will use the project-based WACC formula: r_wacc =rUnlevered (Debt/Value) Marginal tax rate * r Debt. In cell D30, by using cell references, calculate the debt-to-value ratio (1 pt.). Note: Refer to the project's levered value from Step 10 in your calculations. 12. In cell D31, by using cell references, calculate the WACC (1 pt.). 13. To verify that you get the same answer using the WACC method to calculate the NPV, first, calculate the project's levered value. In cell D35, by using cell references and the function PV, calculate the project's levered value (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the values of the free cash flow and WACC from Steps 4 and 12 in your calculations. 14. In cell D36, by using cell references, calculate the NPV (1 pt.). Note: Refer to the project's levered value from Step 13 in your calculations. 15. To show that the flow-to-equity approach also correctly gives the NPV of this investment opportunity, you need to calculate the free cash flow-to-equity after calculating the after-tax interest expense. In cell D40, by using cell references, calculate the after-tax interest expense (1 pt.). 16. In cell D41, by using cell references, calculate the free cash flow-to-equity ( 1 pt.). 17. To calculate the NPV of this investment opportunity, you need to calculate the present value of the free cash flow-toequity. In cell D42, by using cell references and the function PV, calculate the NPV (1 pt.). Note: Refer to the value of the free cash flow-to-equity from Step 16 in your calculations. 18. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed. You are evaluating a project that requires an investment of $90 today and provides a single cash flow of $115 for sure one year from now. You decide to use 100% debt financing, that is, you will borrow $90. The risk-free rate is 5% and the tax rate is 25%. Assume that the investment is fully depreciated at the end of the year, so without leverage you would owe taxes on the difference between the project cash flow and the investment. 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. a. Calculate the NPV of this investment opportunity using the APV method. b. Using your answer to part (a), calculate the WACC of the project. c. Verify that you get the same answer using the WACC method to calculate NPV. d. Finally, show that flow-to-equity also correctly gives the NPV of this investment opportunity. a. Calculate the NPV of this investment opportunity using the APV method. b. Using your answer to part (a), calculate the WACC of the project. c. Verify that you get the same answer using the WACC method to calculate NPV. VL NPV d. Finally, show that flow-to-equity also correctly gives the NPV of this investment opportunity. 1. Start Excel - completed. 2. To calculate the project's NPV using the APV method, you need to calculate the unlevered and levered values of the project. In cell D18, by using cell references, calculate the tax basis at year end (1 pt.). 3. In cell D19, by using cell references, calculate the taxes owed (1 pt.). 4. In cell D20, by using cell references, calculate the free cash flow at year end (1 pt.). 5. In cell D21, by using cell references and the function PV, calculate the present value of the free cash flow (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the value of the free cash flow from Step 4 in your calculations. 6. In cell D22, by using cell references, calculate the interest expense (1 pt.). 7. In cell D23, by using cell references, calculate the interest tax shield (1 pt.). 8. In cell D24, by using cell references and the function PV, calculate the present value of the interest tax shield (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the value of the interest tax shield flow from Step 7 in your calculations. 9. In cell D25, by using cell references, calculate the project's levered value (1 pt.). Note: Refer to the present values of the free cash flow and interest tax shield from Steps 5 and 8 in your calculations. 10. In cell D26, by using cell references, calculate the net present value using the APV method (1 pt.). Note: Refer to the project's levered value from Step 10 in your calculations. 11. To calculate the WACC of the project using the answer from part (a), you will use the project-based WACC formula: r_wacc =rUnlevered (Debt/Value) Marginal tax rate * r Debt. In cell D30, by using cell references, calculate the debt-to-value ratio (1 pt.). Note: Refer to the project's levered value from Step 10 in your calculations. 12. In cell D31, by using cell references, calculate the WACC (1 pt.). 13. To verify that you get the same answer using the WACC method to calculate the NPV, first, calculate the project's levered value. In cell D35, by using cell references and the function PV, calculate the project's levered value (1 pt.). Notes: 1. The output of the expression or function you typed in this cell is expected as a positive number. 2. Refer to the values of the free cash flow and WACC from Steps 4 and 12 in your calculations. 14. In cell D36, by using cell references, calculate the NPV (1 pt.). Note: Refer to the project's levered value from Step 13 in your calculations. 15. To show that the flow-to-equity approach also correctly gives the NPV of this investment opportunity, you need to calculate the free cash flow-to-equity after calculating the after-tax interest expense. In cell D40, by using cell references, calculate the after-tax interest expense (1 pt.). 16. In cell D41, by using cell references, calculate the free cash flow-to-equity ( 1 pt.). 17. To calculate the NPV of this investment opportunity, you need to calculate the present value of the free cash flow-toequity. In cell D42, by using cell references and the function PV, calculate the NPV (1 pt.). Note: Refer to the value of the free cash flow-to-equity from Step 16 in your calculations. 18. Save the workbook. Close the workbook and then exit Excel. Submit the workbook as directed