Please show work in a spreadsheet and please show spreadsheet formulas as well. Please make answers clear. Thank you.
You are on your way to an important budget meeting. In the elevator, you review the project valuation analysis you had your summer associate prepare for one of the projects to be discussed: B. Looking over the spreadsheet, you realize that while all of the cash flow estimates are correct, your associate used the flow-to-equity valuation method and discounted the cash flows using the company's equity cost of capital of 11%. However, the project's incremental leverage is very different from the company's historical debt-equity ratio of 0.20: For this project, the company will instead borrow $80 million upfront and repay $20 million in year 2, $20 million in year 3, and $40 million in year 4. Thus, the project's equity cost of capital is likely to be higher than the firm's, not constant over timeinvalidating your associate's calculation. Clearly, the FTE approach is not the best way to analyze this project. Fortunately, you have your calculator with you, and with any luck you can use a better method before the meeting starts. a. What is the present value of the interest tax shield associated with this project? b. What are the free cash flows of the project? c. What is the best estimate of the project's value from the information given? ($ million) EBIT Interest (5%) Earnings Before Tax Taxes (39%) Depreciation Cap Ex Additions to NWC Net New Debt FCFE NPV at 11% Equity Cost of Capital 1 9.8 - 4.0 5.8 -2.3 25.0 2 10.1 - 4.0 6.1 -2.4 25.0 3 10.4 - 3.0 7.4 -2.9 25.0 10.5 - 2.0 8.5 - 3.3 25.0 20.0 - 100.0 - 20.0 80.0 -40.0 6.4 0.0 - 20.0 8.7 - 20.0 9.5 -40.0 10.2 28.5 a. What is the present value of the interest tax shield associated with this project? The present value of the interest tax shield associated with this project is $million. (Round to one decimal place.) b. What are the free cash flows of the project? The free cash flows of the project are: (Round all answers to one decimal place.) 1 2 3 4 FCF ($ million) C. What is the best estimate of the project's value from the information given? The best estimate of the project's value from the information given will be $ million. (Round to one decimal place.)
