Problem 9-19 Can you show the answers in excel and how they were calulated

"Heavy Metal Corporation is expected to generate the following free cash flows over the next five years:"

Year 1 2 3 4 5 FCF (million) $53 $68 $78 $75 $82

After then, the free cash flows are expected to grow at the industry average of 4% per year. Using the discounted free cash flow model and a weighted average cost of capital of 14%:

a. Estimate the enterprise value of Heavy Metal.

b. If Heavy Metal has no excess cash, debt of $300 million, and 40 million shares outstanding, estimate its share price.

Cost of capital 14.00%

Long-run growth rate 4.00%

Year 1 2 3 4 5 FCF (million) $53.00 $68.00 $78.00 $75.00 $82.00

Terminal value (million) __________(Answer)

Total cash flow (million) $53.00 $68.00 $78.00

a. Estimate the enterprise value of Heavy Metal. Enterprise value (million)

b. If Heavy Metal has no excess cash, debt of $300 million, and 40 million shares outstanding, estimate its share price.

Debt (million) $300.00

Number of shares (million) 40

Equity value (million) _________(Answer)

Stock price _________(Answer)

Requirements .

In cell G19, by using cell references, calculate the terminal value of the company at the end of year 4. . In cell G20, by using cell references, calculate the total cash flows for year 4. . In cell D24, calculate the enterprise value of the company by using the function NPV. . In cell D31, by using cell references, calculate the equity value of the company. . In cell D32, by using cell references, calculate the stock price.