PLEASE SHOW HOW TO SOLVE USING EXCEL, PLEASE INCLUDE ALL FORMULAS USING FOR EXAMPLE: =PV(C42,C41.C40,0,0)

K11 fx - A B E F H 1 Given the data below: 2 3 1) Calculate the book-value weights for each source of capital. Remember, BV does not consider float cost. 4 pts 4 5 2) Calculate the market value weights for each source of capital. 4 pts 6 7 3) Calculate the costs of capital for Common Stock, Preferred Stock and Debt using both market value and book-value. 6 pts 8 9 4) Calculate the Weighted Average Cost of Capital using both market value and book-value weights. Remember, BV does not consider float cost. 6 pts 10 11 12 13 14 15 Source of Capital Book Value BV Weights BV WACC Units Market Value / Share Market Value MV Weights MV WACC 16 Common Stock $ 6,500,000 350,000 $ 35 17 Preferred Stock $ 375,000 7,500 $ 60 18 Debt $ 4,000,000 4,000 $ 965 19 Total 20 21 Additional Bond Data Additional Preferred Data Additional Common Data 22 Tax Rate 35% Dividend $ Dividend o $ 23 Coupon Rate 8% Growth Rate 5% 24 Face Value $ 1,000 25 Maturity 10 26 Float Cost 1.00% Float Cost 2.00% Float Cost 5.00% 5.00 1.43