TRM Consulting Services currently has the following capital structure:Debt is represented by 15-year original maturity bonds, issued five years ago, with a coupon rate of 8%, and are currently selling for $965. The bonds pay interest semiannually. The preferred stock pays a $5 dividend annually and is currently valued at $60 per share. Flotation costs on debt and preferred equity are negligible and can be ignored, but they will be 8% of the selling price for common stock. The common stock, which can be bought for $32.00, has experienced a 5% annual growth rate in dividends and is expected to pay a $1.50 dividend next year. In addition, the firm expects to have $150,000 of retained earnings. Assume that TRM”s marginal tax rate is 35%. a. Set up a worksheet with all of the data from the problem in a well- organized input area. b. Calculate the book-value weights for each source of capital. c. Calculate the market-value weights for each source of capital. d. Calculate the component costs of capital (i.e., debt, preferred equity, retained earnings, and new common equity). e. Calculate the weighted average costs of capital using both the market- value and book-value weights.note: (please provide the answer with the excel sheet )Book Values of Sources of CapitalSourceBook ValueQuantityCommon Stock$8,500,000350,000Preferred Stock$500,00010,000Debt$4,000,0004,000

TRM Consulting Services currently has the following capital structure:Debt is represented by 15-year original maturity bonds, issued five years ago, with a coupon rate of 8%, and are currently selling for $965. The bonds pay interest semiannually. The preferred stock pays a $5 dividend annually and is currently valued at $60 per share. Flotation costs on debt and preferred equity are negligible and can be ignored, but they will be 8% of the selling price for common stock. The common stock, which can be bought for $32.00, has experienced a 5% annual growth rate in dividends and is expected to pay a $1.50 dividend next year. In addition, the firm expects to have $150,000 of retained earnings. Assume that TRM”s marginal tax rate is 35%. a. Set up a worksheet with all of the data from the problem in a well- organized input area. b. Calculate the book-value weights for each source of capital. c. Calculate the market-value weights for each source of capital. d. Calculate the component costs of capital (i.e., debt, preferred equity, retained earnings, and new common equity). e. Calculate the weighted average costs of capital using both the market- value and book-value weights.note: (please provide the answer with the excel sheet )Book Values of Sources of CapitalSourceBook ValueQuantityCommon Stock$8,500,000350,000Preferred Stock$500,00010,000Debt$4,000,0004,000

TRM Consulting Services currently has the following capital structure:Debt is represented by 15-year original maturity bonds, issued five years ago, with a coupon rate of 8%, and are currently selling for $965. The bonds pay interest semiannually. The preferred stock pays a $5 dividend annually and is currently valued at $60 per share. Flotation costs on debt and preferred equity are negligible and can be ignored, but they will be 8% of the selling price for common stock. The common stock, which can be bought for $32.00, has experienced a 5% annual growth rate in dividends and is expected to pay a $1.50 dividend next year. In addition, the firm expects to have $150,000 of retained earnings. Assume that TRM”s marginal tax rate is 35%. a. Set up a worksheet with all of the data from the problem in a well- organized input area. b. Calculate the book-value weights for each source of capital. c. Calculate the market-value weights for each source of capital. d. Calculate the component costs of capital (i.e., debt, preferred equity, retained earnings, and new common equity). e. Calculate the weighted average costs of capital using both the market- value and book-value weights.note: (please provide the answer with the excel sheet )Book Values of Sources of CapitalSourceBook ValueQuantityCommon Stock$8,500,000350,000Preferred Stock$500,00010,000Debt$4,000,0004,000