Need formulas shown for excel Please

EYE fx 100% Q- Search in Sheet Abs IP N o Q R TI A Home Layout Tables Charts SmartArt Formulas Data Review Developer Sort & Filter Analysis External Data Sources Tools Group & Outline re Text to Columns Show Detail TO Sort Filter Pivot Table What if Solver Refresh Text Remove Database Consolidate HTML FileMaker Validate Hide Detail Duplicates Group Ungroup L23 fx A B D E G H 1 K M 1 Given the data below: 2 DO NOT MODIFY 3 1) Calculate the book-value weights for each source of capital. Remember, BV does not consider float cost. 2 pts 1 O Book Value Weights 4 2 o Market Value Weights 5 3 2) Calculate the market value weights for each source of capital. 2 pts o Costs of Capital (6 answers) 6 4 O WACC (2 answers) 7 0 8 3) Calculate the costs of capital for Common Stock, Preferred Stock and Debt using both market value and book-value. 2 pts 9 10 4) Calculate the weighted Average cost of Capital using both market-value and book-value weights. Remember, BV does not consider float cost. 4 pts 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 $ 5.00 Dividend 0 $ 1.43 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% 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 IF Statements-Extra Credit VLOOKUP & Cond Format Exra Credit Two-Input Table Regression WACC Cost-Benefit Analysis Solver Hyperlinks Balance Sheet + Normal View Sumo 200 RRRR BBB EYE fx 100% Q- Search in Sheet Abs IP N o Q R TI A Home Layout Tables Charts SmartArt Formulas Data Review Developer Sort & Filter Analysis External Data Sources Tools Group & Outline re Text to Columns Show Detail TO Sort Filter Pivot Table What if Solver Refresh Text Remove Database Consolidate HTML FileMaker Validate Hide Detail Duplicates Group Ungroup L23 fx A B D E G H 1 K M 1 Given the data below: 2 DO NOT MODIFY 3 1) Calculate the book-value weights for each source of capital. Remember, BV does not consider float cost. 2 pts 1 O Book Value Weights 4 2 o Market Value Weights 5 3 2) Calculate the market value weights for each source of capital. 2 pts o Costs of Capital (6 answers) 6 4 O WACC (2 answers) 7 0 8 3) Calculate the costs of capital for Common Stock, Preferred Stock and Debt using both market value and book-value. 2 pts 9 10 4) Calculate the weighted Average cost of Capital using both market-value and book-value weights. Remember, BV does not consider float cost. 4 pts 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 $ 5.00 Dividend 0 $ 1.43 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% 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 IF Statements-Extra Credit VLOOKUP & Cond Format Exra Credit Two-Input Table Regression WACC Cost-Benefit Analysis Solver Hyperlinks Balance Sheet + Normal View Sumo 200 RRRR BBB