I need you guys to answer this problem for me using excel. I also need this question answered ASAP! Thanks.

1 WACC 2ABDC Conslting Sevicescurently has the following captal structure Source Quantity Book Value Common 1,250,000 $ 25,000,000 Preferred 100,000 5,000,000 Debt 8,600 8,600,000 8 The bounds had been issued recently on April 1st,2018. 9 New debt would mature on the same date after 30 years, have a coupon rate of 7.5%, and would be sold for their par value of SI 10 The bonds pay interest semiannually, and flotation costs would be 2% of the selling price. 12 13 The preferred stock pays a 3 dividend annually and is currently valued at $66.00 per share. Flotation costs on preferred would be 4% of the price. 5 The common stock which can be bought ro $35 has experience a 6% annual growth rate in dividends and is expected to pay a 16 Ihe stock has a beta of 1.25, the risk-free rate is 3%, and the expected market risk premium is 6%. 17 18 In addition, the firm expects to generate $150,000 of retained earnings. Assume that ARDC's marginal tax rate is 30%. 19 IS dividend next year tation cos son new common equity would be 6 20 a. Set up a worksheet as framed on the worksheet of "Question 1 Solution" with all of the data from the problem in a well organized input area. Use Range Names for your computations (10pts) 21 b. Calculate the book-value weights for each source of capital. 22 c. Calcthe market value weights fro each source of capital. 23 d. Calculate the component costs of capital (i.e., debt, preferred equity, retained earnings, and new common equity). se YI function when finding the after-tax cost of debt 24 Use CAPM to tind the cost of retained earnings, and the constant growth model for new common equity. 25 e. calculate the weighted average costs of capital using both the market value and book value weights with retained earnings, and also new common equity. General Information Bonds Scttlement Datc Maturity Date 8 Coup Rate 9 Frequency 10 Face Value 11 Price Flotation Costs 13 Yield to Maturity r-tux Cost of Debt Preferred Equity l6 17 Dividend 18 Facc Value 19 Price 20 Flotation Costs 2l Cost of Prefcmed F 23 Common Kquity 24 idd D,) 25 irowth Rate 26 Pnce 27 Flotation Costs 28 Beta 2 Risk-lree Rale 30 Markct Risk Premium 31 Cost of Retained Eamings 32 Cost of New Cnin Equily Source Book Value Market Value BV Weights MV Weights CC wih Retained Eamings CC with New Common Equity 14 35 Common 36 elei red 37 Debt 38 olals 39 10 41 Book ValueWeights 2 With Retuined Eans 43With New Commonquity 44 Market Value Weights 5 With Retued Es 6 Wirh New Common Equity Weighted Average Cost of Capilal