Fall 2019 Weighted Average Cost of Capital Assignment I. Prepare an Excel spreadsheet that will: 1. Compute a firm's weighted average cost of capital. 2. Determine how much financing will come from the various component sources 3. Split common equity into internal and external components and adjust the weighted average cost of capital for various levels of internal equity availability II. You will need the following input cells: 1. Par value of bonds 2. Coupon rate paid by bonds 3. Time to maturity of bonds 4. Price of bonds 5. Preferred stock dividend (D) 6. Preferred stock price (PP) 7. Preferred stock flotation costs (Fp) 8. Current common stock dividend (Do) 9. Common stock price (Po) 10. Common stock growth rate (g) 11. Common stock flotation cost (F) 12. Tax rate (t) 13. Total amount of financing needed 14. Internal equity available 15. Capital structure (debt, preferred stock, common equity) Be sure to arrange and label your input cells so they are easily accessible and understandable. Highlight in yellow any cells in which a value must be entered. For example, the inputs for bonds may look something like this: Bonds Par Value Coupon Rate Time to Maturity (years) Price Yield to Maturity 1,000.00 5.50% 10 925.00 6.54% III. You will need output cells that compute the following: 1. Yield to maturity on bonds. 2. Dollar amount of debt financing needed. 3. Dollar amount of preferred stock financing needed. 4. Dollar amount of internal equity (retained earnings) used. 5. Dollar amount of new common stock financing needed. 6. After tax cost of debt. 7. Cost of preferred stock financing 8. Cost of internal equity financing 9. Cost of new common stock financing. Arrange and format these items appropriately. For example, you can include the calculation for yield to maturity with the bond input data, if you like (see above example) You will want to put the component costs into a separate section or area, and then show the dollar amount of financing from each component source in its own section. Ease of use and understanding is important. Make it easy to follow. Using your own formatting (borders, shading, etc...), an output section for component costs could look something like this: Component Costs Debt Preferred Stock Internal Equity External Equity 4.581% 8.152% 10.500% 11.000% IV. Using your output cells as references, create the following table to compute your weighted average cost of capital: Weighted Average Cost of Capital Weight Cost Debt 39.00% 4.58% 0.01787 Preferred Stock 16.00% 8.15% 0.01304 Internal Equity 10.00% 10.50% 0.01050 External Equity 35.00% 11.00% 0.03850 100.00% WACC7.991% Format the table however you want, but you should include the information shown above. Note that the weight is determined by dividing the dollar amount of financing from each component source (Section III, Numbers 2-5) by total amount of financing needed (Section II, Number 13). The costs should come directly from the output section for component costs shown above. 1. The input values (Section II) are up to you with the following exceptions: 1. The par value of bonds should be $1,000. 2. The coupon rate on your bonds should involve a fractional percentage (x.x%). 3. The internal equity available (Section II, Number 14) should not be enough to cover all common equity financing requirements. In other words, you WILL need to issue some new common stock. 4. Make the tax rate somewhere between 30%-35%. Fractional percentages are fine but not necessary it won't make it any more or less difficult). 5. Your capital structure should include all 3 of the components - debt, preferred stock, and common equity. Common equity will eventually be split into internal equity and new common stock, but that split will occur in the section where you compute the dollar amount of financing required from each source, the section where you show the individual component costs (shown above), and in your final WACC table (shown above). Fall 2019 Weighted Average Cost of Capital Assignment I. Prepare an Excel spreadsheet that will: 1. Compute a firm's weighted average cost of capital. 2. Determine how much financing will come from the various component sources 3. Split common equity into internal and external components and adjust the weighted average cost of capital for various levels of internal equity availability II. You will need the following input cells: 1. Par value of bonds 2. Coupon rate paid by bonds 3. Time to maturity of bonds 4. Price of bonds 5. Preferred stock dividend (D) 6. Preferred stock price (PP) 7. Preferred stock flotation costs (Fp) 8. Current common stock dividend (Do) 9. Common stock price (Po) 10. Common stock growth rate (g) 11. Common stock flotation cost (F) 12. Tax rate (t) 13. Total amount of financing needed 14. Internal equity available 15. Capital structure (debt, preferred stock, common equity) Be sure to arrange and label your input cells so they are easily accessible and understandable. Highlight in yellow any cells in which a value must be entered. For example, the inputs for bonds may look something like this: Bonds Par Value Coupon Rate Time to Maturity (years) Price Yield to Maturity 1,000.00 5.50% 10 925.00 6.54% III. You will need output cells that compute the following: 1. Yield to maturity on bonds. 2. Dollar amount of debt financing needed. 3. Dollar amount of preferred stock financing needed. 4. Dollar amount of internal equity (retained earnings) used. 5. Dollar amount of new common stock financing needed. 6. After tax cost of debt. 7. Cost of preferred stock financing 8. Cost of internal equity financing 9. Cost of new common stock financing. Arrange and format these items appropriately. For example, you can include the calculation for yield to maturity with the bond input data, if you like (see above example) You will want to put the component costs into a separate section or area, and then show the dollar amount of financing from each component source in its own section. Ease of use and understanding is important. Make it easy to follow. Using your own formatting (borders, shading, etc...), an output section for component costs could look something like this: Component Costs Debt Preferred Stock Internal Equity External Equity 4.581% 8.152% 10.500% 11.000% IV. Using your output cells as references, create the following table to compute your weighted average cost of capital: Weighted Average Cost of Capital Weight Cost Debt 39.00% 4.58% 0.01787 Preferred Stock 16.00% 8.15% 0.01304 Internal Equity 10.00% 10.50% 0.01050 External Equity 35.00% 11.00% 0.03850 100.00% WACC7.991% Format the table however you want, but you should include the information shown above. Note that the weight is determined by dividing the dollar amount of financing from each component source (Section III, Numbers 2-5) by total amount of financing needed (Section II, Number 13). The costs should come directly from the output section for component costs shown above. 1. The input values (Section II) are up to you with the following exceptions: 1. The par value of bonds should be $1,000. 2. The coupon rate on your bonds should involve a fractional percentage (x.x%). 3. The internal equity available (Section II, Number 14) should not be enough to cover all common equity financing requirements. In other words, you WILL need to issue some new common stock. 4. Make the tax rate somewhere between 30%-35%. Fractional percentages are fine but not necessary it won't make it any more or less difficult). 5. Your capital structure should include all 3 of the components - debt, preferred stock, and common equity. Common equity will eventually be split into internal equity and new common stock, but that split will occur in the section where you compute the dollar amount of financing required from each source, the section where you show the individual component costs (shown above), and in your final WACC table (shown above)