WACC Case Study

ABC is about to begin its annual capital budgeting process, and the cost of capital is an essential input for capital budgeting decisions. Your team plans to use an Excel model both to illustrate the discussion and also to help the directors learn more about Excel. The team plans to discuss the costs of different types of capital, the process of calculating the weighted average cost of capital (WACC), the appropriate weights, how the WACC is used in the capital budgeting process, and how the corporate WACC is adjusted to reflect the risks of different projects. Assume that you must prepare for the session. You must discuss the issues listed above, plus any others that you think are relevant for this session. For purposes of this case, assume that your analysis is conducted today.

Table 1. Data Used in the Analysis

1. Bond Data. Two dollar-denominated bonds are currently outstanding. Bond A has a 7.00 percent coupon, pays interest semi-annually, sells for $900.00, matures in 20 years. Bond B has a 10.00 percent semi-annual coupon, sells for $1125.00 also matures in 20 years. ABCs federal-plus-state tax rate is 35 percent. Assume that the analysis is conducted today. New bonds carrying the prevailing rate could be sold to institutional investors, and no bond flotation cost would be involved.
2. Preferred Stock Data. ABC has one issued of preferred stock outstanding, a perpetual and non-callable preferred that pays a $7.00 annual dividend, has a $100 par value, and currently sells for $110 per share. Investment bankers have indicated that ABC could sell additional shares with a dividend rate that would provide the same market yield, but such a sale would incur a flotation cost of 6.0%.
3. Common Equity Cost Data
   1. CAPM ABCs estimated beta coefficient is 1.0. The risk-free rate is 3.0%, and the market risk premium is estimated to be 6.0%.
   2. DCF ABCs stock sells for $21 per share. The company last paid a dividend D(0) of $1.00, which is expected to grow 5.0% indefinitely.

1. Based on the data, what is a reasonable estimate of the companys cost of debt for use in the WACC calculation? Hint: Take an average of both bonds using the relevant rate. Recall that we need to estimate a company's long term cost of borrowing (say 10-20 year cost of borrowing)
2. ABC also raises capital with preferred stock. If ABC finances with preferred stock, what would its cost be for the WACC calculation?
3. Based on the Table 1 data, what is the cost of equity per the CAPM?
4. Based on the data, what is a reasonable estimate of ABCs market implied cost of equity?
5. Given data on the two costs of equity estimating techniques, what is your estimate of ABCs cost of common equity, assuming all equity is raised as retained earnings?
6. ABCs target capital structure calls for 20% debt, 5% preferred stock, and 75% common equity, all taken at market value. Assuming that new capital is raised in these percentages and that all common equity is raised as retained earnings, what is ABCs WACC?

Please show excel so i can learn to do it myself in excel!