Show computation of WACC in excel please.
(15 Points) Your company, Portage Bay Enterprise, is considering a major expansion program. Before proceeding with the expansion, your team must estimate its cost of capital. Suppose you are an assistant to Anna Wang, the financial vice president. Your first task is to estimate Portage Bay's cost of capital. Wang has provided you with the following data and instruction. a. Portage Bay's common stock is currently selling for $85.00 per share. The stock beta is 0.85. Wang didn't provide you with risk-free data and direct you to check the most recent 10-year Treasury bond rate yield. Similarly, she didn't provide you with a market return value and want you to use the annual S&P500 index growth rate over the period of 1/2/2019-12/31/2021 as a proxy for expected market return. b. The current price of Portage Bay's $100 face value, 7.5% coupon, semiannual payment, noncallable bonds with 12 years remaining to maturity is $95.2. Portage Bay does not use short-term interest-bearing debt on a permanent basis. c. The current price of the firm's 12% annual dividend payout ratio, $100.00 par value, quarterly dividend, perpetual preferred stock is $118.50. d. The firm's tax rate is 25%. e. Portage Bay's target capital structure is 35% debt, 10% preferred stock, and 55% common equity. Use the informatio provided above to calculate the company's weighted average cost of capital. (15 Points) Your company, Portage Bay Enterprise, is considering a major expansion program. Before proceeding with the expansion, your team must estimate its cost of capital. Suppose you are an assistant to Anna Wang, the financial vice president. Your first task is to estimate Portage Bay's cost of capital. Wang has provided you with the following data and instruction. a. Portage Bay's common stock is currently selling for $85.00 per share. The stock beta is 0.85. Wang didn't provide you with risk-free data and direct you to check the most recent 10-year Treasury bond rate yield. Similarly, she didn't provide you with a market return value and want you to use the annual S&P500 index growth rate over the period of 1/2/2019-12/31/2021 as a proxy for expected market return. b. The current price of Portage Bay's $100 face value, 7.5% coupon, semiannual payment, noncallable bonds with 12 years remaining to maturity is $95.2. Portage Bay does not use short-term interest-bearing debt on a permanent basis. c. The current price of the firm's 12% annual dividend payout ratio, $100.00 par value, quarterly dividend, perpetual preferred stock is $118.50. d. The firm's tax rate is 25%. e. Portage Bay's target capital structure is 35% debt, 10% preferred stock, and 55% common equity. Use the informatio provided above to calculate the company's weighted average cost of capital
