Please fill out excel sheet and show formulas step by step example thanks

4. A manufacturer wants to determine its WACC. Today, 1/1/2018, the firm issued 7,000 bonds that will mature in 1/1/2038 with $1,000 face value. These bonds will pay a 9% coupon rate semiannually and are currently selling for $950. The firm has 100,000 preferred shares of stock outstanding with a book value of $40, but currently selling for $50 per share. The most recent preferred and common dividends were $3.50 and $2.50 per share, respectively. The firm's EPS five years ago was $8.00 and it expects to increase its next dividend payment by the implied 5- year earnings per share growth rate. Flotation costs on debt and preferred equity are both 3%, but 7% in the case of common stocks. The common stock is selling today for $25 and the firm's tax rate and payout ratio are 25% and 25%, respectively. The firm has 200,000 shares of common stock outstanding with the same book value as that of its preferred stock. a) Calculate the book value and market value weights for each source of capital. b) Calculate the component costs of capital (i.e., debt, preferred equity, retained earnings, and new common equity). c) Determine the weighted average costs of capital using both the market and the book value weights Manufacturer Tax Rate Payout Ratio Settlement Date Maturity Date Frequency Face Value Coupon Rate Selling Price Flotation Costs on Debt and Preferred Preferred Dividend Preferred/Common Stock Book Value Preferred Price Last Common Dividend (D.) EPS 5 Years Ago Price on Common Share Flotation Costs on Common Equity 25% 25% 1/1/18 1/1/2038 2 $ 1,000 9.00% $950 3.00% $3.50 $40 $50 $2.50 $8.00 $25.00 7.00% Source Quantity Book Value Market Value BV Weights MV Weights Common Preferred Debt Totals Yield to Maturity After-tax Cost of Debt Cost of Preferred Equity Growth Rate Cost of Retained Earnings Cost of New Common Equity Weighted Average Cost of Capital Book Value Weights With Retained Earnings With New Common Equity Market Value Weights With Retained Earnings With New Common Equity 4. A manufacturer wants to determine its WACC. Today, 1/1/2018, the firm issued 7,000 bonds that will mature in 1/1/2038 with $1,000 face value. These bonds will pay a 9% coupon rate semiannually and are currently selling for $950. The firm has 100,000 preferred shares of stock outstanding with a book value of $40, but currently selling for $50 per share. The most recent preferred and common dividends were $3.50 and $2.50 per share, respectively. The firm's EPS five years ago was $8.00 and it expects to increase its next dividend payment by the implied 5- year earnings per share growth rate. Flotation costs on debt and preferred equity are both 3%, but 7% in the case of common stocks. The common stock is selling today for $25 and the firm's tax rate and payout ratio are 25% and 25%, respectively. The firm has 200,000 shares of common stock outstanding with the same book value as that of its preferred stock. a) Calculate the book value and market value weights for each source of capital. b) Calculate the component costs of capital (i.e., debt, preferred equity, retained earnings, and new common equity). c) Determine the weighted average costs of capital using both the market and the book value weights Manufacturer Tax Rate Payout Ratio Settlement Date Maturity Date Frequency Face Value Coupon Rate Selling Price Flotation Costs on Debt and Preferred Preferred Dividend Preferred/Common Stock Book Value Preferred Price Last Common Dividend (D.) EPS 5 Years Ago Price on Common Share Flotation Costs on Common Equity 25% 25% 1/1/18 1/1/2038 2 $ 1,000 9.00% $950 3.00% $3.50 $40 $50 $2.50 $8.00 $25.00 7.00% Source Quantity Book Value Market Value BV Weights MV Weights Common Preferred Debt Totals Yield to Maturity After-tax Cost of Debt Cost of Preferred Equity Growth Rate Cost of Retained Earnings Cost of New Common Equity Weighted Average Cost of Capital Book Value Weights With Retained Earnings With New Common Equity Market Value Weights With Retained Earnings With New Common Equity