Question: Complete the Excel Table...Show Functions Please and Thanks! A B E F G H 1 K L 32 Market Value Weight 0.31500 0.19555 0.14796 S910

G H 1 K L 32 Market Value Weight 0.31500 0.19555 0.14796 Complete the Excel Table...Show Functions Please and Thanks!

A B E F G H 1 K L 32 Market Value Weight 0.31500 0.19555 0.14796 S910 43.100% Market Value 564,300 350,307 265,060 106,024 1264 125,453 156,297 223,964 273 064 126,398 126 398 213,416 254,044 153,956 156,511 330,325 - $214,404.000 $156,499.50 Ave 237,426 120 100,030 150,429 182,648 Su 243,873 126,125 75,086 - 100,761 182,698 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 30 58 3 59 60 61 62 63 64 65 1 0.07003 008725 0.08725 10000 0.12502 0.07056 0.11913 0.14181 0.19181 0.08594 0.08737 0.18439 W.2015 0.11968 W.1900 0.08736 w.vors 0.13254 0.05584 0.08397 0.10196 0.13614 . 0.07041 0.04191 0.05625 www 0.10199 215,540 0.12032 Maturity YTM 5/3/23 59.000% 5/4/43 280.400% 5/6/24 95.900% 5900 2009 2.802 5/6/44 2/4/7 2/9/22 10 2/9/95 2/9/25 116.400% 2/9/45 274.800% 5/13/22 32.900% 5/13/25 5/13/25 122.100% 5/13/45 279.700% 2/23/23 40.800% 2/23/36 Arco 2.344% 2/23/46 con 2.790% 2.1907 2/23/26 1.393% COCO 1.395% 8/4/26 co 144.000% 8/4/46 278.200% % 2/9/22 N/A 2/9/22 26.100% 2/9/24 84.000% 2/9/27 1.586% 2/9/47 2.780% 5/11/22 N/A H. 5/11/22 31.200% 5/11/24 91.300% *** 5/11/27 164.DD0% 6/20/27 156.400% 9/12/27 169.300% 9/12/47 1414 276.100% 9/12/22 12/24 49.2009 1/13/23 40.700% Wenn 1/13/25 41242 1.072% 11/13/27 1574 168.000% 0.00 11/13/47 277.300% 100 9/11/22 --- 22.400% 9/11/24 wa 94.200% 9/11/26 . 148.700% 9/11/29 192.700% 9/11/49 280.100% 5/11/23 44.000% 1000 wer 5/11/25 MOOR 118.200% 5/11/30 194.900% 5/11/50 272.700% 100 ---- 8/20/25 112.700% 8/20/30 197.700% ner 8/20/50 269.700% wa 8/20/60 282.300% 10 - 2/8/26 . 126.200% 2/8/28 173.400% 2/8/31 203.700% 2/8/41 253.900% 2/8/51 272.400% 2/8/61 286.400% 8/5/28 177.100% 8/5/31 204.000% 8/5/51 272.700% 8/5/61 285.900% Quoted Price Book Value 102.600 $5,500,000.00 116.769 $3,000,000.00 106.024 $2,500,000.00 127.322 $1,000,000.00 100.362 $1,250,000.00 104.198 $1,500,000.00 111.982 $2,000,000.00 101.118 $1,250,000.00 106.708 $2,000,000.00 127.022 $2,000,000.00 102.637 $1,500,000.00 125.209 18.20 3,250,000.00 $1,250,000.00 132.130 $2,500,000.00 107.202 ######### """ 104.333 $1,500,000.00 118.713 $2,000,000.00 100.030 $1,000,000.00 100.286 $1,500,000.00 104.370 $1,750,000.00 108.388 $2,250,000.00 126.125 $1,000,000.00 100.114 $750,000.00 100.761 $1.000.000,00 104.399 $1,750,000.00 107.770 $2,000,000.00 107.311 $1,000,000.00 106.371 $2,000,000.00 117.910 $1,000,000.00 101.153 $1,000,000.00 102.097 102.09 $750,000.00 www.ro 104.896 $1,500,000.00 107.174 $1,500,000.00 117.762 $1,250,000.00 101.179 $1,000,000.00 ar www. 102.297 $750,000.00 - www.cy 102.512 $2,000,000.00 101.909 $1,750,000.00 102.828 $1,500,000.00 100.453 $2,000,000.00 ... 99.806 sove $2,250,000.00 97.673 $1,750,000.00 98.482 $2,500,000.00 97.893 $1,250,000.00 94.189 $1,250,000.00 . 94.085 $1,250,000.00 93.592 $1,750,000.00 42 97.703 $2,500,000.00 96.868 $2,500,000.00 96.764 $2,750,000.00 EC 97.516 $1,500,000.00 98.522 $3,000,000.00 98.506 $1,750,000.00 97.665 $2,300,000.00 97.020 $1,000,000.00 99.450 $1,800,000.00 99.785 $1,400,000.00 $ Total market value = 66 Weighted cost of debt 18.585% 54.832% % 14.190% 2.551% 201201 10 15.602 10.156% % 24 25 34.356% 39104 2.321% 14.546% 39.665% % 3.506% 3.506% 0.205% 0.514% w 0.167% V.com 12.580% 12:30 36.872% or 0.000% 2.192% 8.564% 0.216% 0.196% 0.000% 1.755% 9.311% 19.732% .. 9.369% 20.100 18.173% 10.175 2.778% sro 1.740% 0.094% V.VN 15.076% 227.862% 1.265% 4.034% % 17.019% 19.184% 24.117% 3.701% Siro 14.817% 12 18.596% 37.479% MO 7.698% 1.0304 12.993% TOM 17.706% 25.810% 17.207% 23.441% row 30.258% 20.732% 44.944% 27.560% 22.207% 11.048% 27.250% 22.295% 137.687% 67 107,311 212,742 212.742 117.910 11:10 101.153 2012 76,573 157,344 DI 2000 160,761 1.472,025 101,179 - 76,723 - 205,024 178,341 154,242 150,680 224,564 170,928 246,205 122,366 SCH 117,736 117,606 163,786 244,258 242,170 ne od 266,101 146,274 ZOE ECC 295,566 172,386 224,630 97,020 179,010 139,699 1,791,405 68 69 70 71 72 73 74 75 76 77 78 79 0.05990 0.11876 0.11876 0.06582 0.00382 0.05647 WS 0.04274 0.04219 0.08783 V.Volos 0.08974 0.82172 0.05648 to 0.04283 co 0.11445 0.09955 www 0.08610 0.08411 0.12536 0.09542 0.13744 0.06831 0.06572 0.06565 0.09143 0.13635 0.13518 0.14854 DOLCE 0.08165 0,16499 0.09623 0.12539 0.05416 0.09993 0.07798 Cost of debt - 80 81 82 83 84 85 86 87 88 89 Tax rate: 35% Aftertax cost of debt: 89.05= H98(1-D91) $ Cost of equity: Market value of equity: Aftertax cost of debt: Market value of debt: F89 c. Finally, calculate the market value weights for debt and equity. What is the WACC for the company? Weight of debt: Weight of equity: WACC: WD*RD*(1-Tc)+We*re A B E F G H 1 K L 32 Market Value Weight 0.31500 0.19555 0.14796 S910 43.100% Market Value 564,300 350,307 265,060 106,024 1264 125,453 156,297 223,964 273 064 126,398 126 398 213,416 254,044 153,956 156,511 330,325 - $214,404.000 $156,499.50 Ave 237,426 120 100,030 150,429 182,648 Su 243,873 126,125 75,086 - 100,761 182,698 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 30 58 3 59 60 61 62 63 64 65 1 0.07003 008725 0.08725 10000 0.12502 0.07056 0.11913 0.14181 0.19181 0.08594 0.08737 0.18439 W.2015 0.11968 W.1900 0.08736 w.vors 0.13254 0.05584 0.08397 0.10196 0.13614 . 0.07041 0.04191 0.05625 www 0.10199 215,540 0.12032 Maturity YTM 5/3/23 59.000% 5/4/43 280.400% 5/6/24 95.900% 5900 2009 2.802 5/6/44 2/4/7 2/9/22 10 2/9/95 2/9/25 116.400% 2/9/45 274.800% 5/13/22 32.900% 5/13/25 5/13/25 122.100% 5/13/45 279.700% 2/23/23 40.800% 2/23/36 Arco 2.344% 2/23/46 con 2.790% 2.1907 2/23/26 1.393% COCO 1.395% 8/4/26 co 144.000% 8/4/46 278.200% % 2/9/22 N/A 2/9/22 26.100% 2/9/24 84.000% 2/9/27 1.586% 2/9/47 2.780% 5/11/22 N/A H. 5/11/22 31.200% 5/11/24 91.300% *** 5/11/27 164.DD0% 6/20/27 156.400% 9/12/27 169.300% 9/12/47 1414 276.100% 9/12/22 12/24 49.2009 1/13/23 40.700% Wenn 1/13/25 41242 1.072% 11/13/27 1574 168.000% 0.00 11/13/47 277.300% 100 9/11/22 --- 22.400% 9/11/24 wa 94.200% 9/11/26 . 148.700% 9/11/29 192.700% 9/11/49 280.100% 5/11/23 44.000% 1000 wer 5/11/25 MOOR 118.200% 5/11/30 194.900% 5/11/50 272.700% 100 ---- 8/20/25 112.700% 8/20/30 197.700% ner 8/20/50 269.700% wa 8/20/60 282.300% 10 - 2/8/26 . 126.200% 2/8/28 173.400% 2/8/31 203.700% 2/8/41 253.900% 2/8/51 272.400% 2/8/61 286.400% 8/5/28 177.100% 8/5/31 204.000% 8/5/51 272.700% 8/5/61 285.900% Quoted Price Book Value 102.600 $5,500,000.00 116.769 $3,000,000.00 106.024 $2,500,000.00 127.322 $1,000,000.00 100.362 $1,250,000.00 104.198 $1,500,000.00 111.982 $2,000,000.00 101.118 $1,250,000.00 106.708 $2,000,000.00 127.022 $2,000,000.00 102.637 $1,500,000.00 125.209 18.20 3,250,000.00 $1,250,000.00 132.130 $2,500,000.00 107.202 ######### """ 104.333 $1,500,000.00 118.713 $2,000,000.00 100.030 $1,000,000.00 100.286 $1,500,000.00 104.370 $1,750,000.00 108.388 $2,250,000.00 126.125 $1,000,000.00 100.114 $750,000.00 100.761 $1.000.000,00 104.399 $1,750,000.00 107.770 $2,000,000.00 107.311 $1,000,000.00 106.371 $2,000,000.00 117.910 $1,000,000.00 101.153 $1,000,000.00 102.097 102.09 $750,000.00 www.ro 104.896 $1,500,000.00 107.174 $1,500,000.00 117.762 $1,250,000.00 101.179 $1,000,000.00 ar www. 102.297 $750,000.00 - www.cy 102.512 $2,000,000.00 101.909 $1,750,000.00 102.828 $1,500,000.00 100.453 $2,000,000.00 ... 99.806 sove $2,250,000.00 97.673 $1,750,000.00 98.482 $2,500,000.00 97.893 $1,250,000.00 94.189 $1,250,000.00 . 94.085 $1,250,000.00 93.592 $1,750,000.00 42 97.703 $2,500,000.00 96.868 $2,500,000.00 96.764 $2,750,000.00 EC 97.516 $1,500,000.00 98.522 $3,000,000.00 98.506 $1,750,000.00 97.665 $2,300,000.00 97.020 $1,000,000.00 99.450 $1,800,000.00 99.785 $1,400,000.00 $ Total market value = 66 Weighted cost of debt 18.585% 54.832% % 14.190% 2.551% 201201 10 15.602 10.156% % 24 25 34.356% 39104 2.321% 14.546% 39.665% % 3.506% 3.506% 0.205% 0.514% w 0.167% V.com 12.580% 12:30 36.872% or 0.000% 2.192% 8.564% 0.216% 0.196% 0.000% 1.755% 9.311% 19.732% .. 9.369% 20.100 18.173% 10.175 2.778% sro 1.740% 0.094% V.VN 15.076% 227.862% 1.265% 4.034% % 17.019% 19.184% 24.117% 3.701% Siro 14.817% 12 18.596% 37.479% MO 7.698% 1.0304 12.993% TOM 17.706% 25.810% 17.207% 23.441% row 30.258% 20.732% 44.944% 27.560% 22.207% 11.048% 27.250% 22.295% 137.687% 67 107,311 212,742 212.742 117.910 11:10 101.153 2012 76,573 157,344 DI 2000 160,761 1.472,025 101,179 - 76,723 - 205,024 178,341 154,242 150,680 224,564 170,928 246,205 122,366 SCH 117,736 117,606 163,786 244,258 242,170 ne od 266,101 146,274 ZOE ECC 295,566 172,386 224,630 97,020 179,010 139,699 1,791,405 68 69 70 71 72 73 74 75 76 77 78 79 0.05990 0.11876 0.11876 0.06582 0.00382 0.05647 WS 0.04274 0.04219 0.08783 V.Volos 0.08974 0.82172 0.05648 to 0.04283 co 0.11445 0.09955 www 0.08610 0.08411 0.12536 0.09542 0.13744 0.06831 0.06572 0.06565 0.09143 0.13635 0.13518 0.14854 DOLCE 0.08165 0,16499 0.09623 0.12539 0.05416 0.09993 0.07798 Cost of debt - 80 81 82 83 84 85 86 87 88 89 Tax rate: 35% Aftertax cost of debt: 89.05= H98(1-D91) $ Cost of equity: Market value of equity: Aftertax cost of debt: Market value of debt: F89 c. Finally, calculate the market value weights for debt and equity. What is the WACC for the company? Weight of debt: Weight of equity: WACC: WD*RD*(1-Tc)+We*re

Step by Step Solution

There are 3 Steps involved in it

1 Expert Approved Answer

Step: 1 Unlock blur-text-image

Question Has Been Solved by an Expert!

Get step-by-step solutions from verified subject matter experts

Step: 2 Unlock

Step: 3 Unlock

Students Have Also Explored These Related Finance Questions!

The Chestnut Street Company plans to issue a bond semiannually on March 31st and September 30th. The Controller has asked you to calculate information about the bond assuming two different market...

please please help! I know this is a lot but my grade truly depends on this. THANK YOU In the context of this investigation, the 3005 invoices obtained by the CPA firm are our population of interest,...

Hi, can you prepare this assignment for me, please. it's a presentation about company Coca-Cola Amatil (Australia) base on 3 annual reports and complete all the task requirement...

The Chestnut Street Company plans to issue a bond semiannually on March 31st and September 30th. The Controller has asked you to calculate information about the bond assuming two different market...

Hi There, I need someone to help me with the attached finance worksheet. There are 3 problems in this excel file - one problem on each individual tab. I am attaching a couple of additional files as...

I am working on a homework problem and am stumped on part B. Kim hotels is interested in developing a new hotel in seoul. The company estimates that the hotel would require an initial investment of...

---- ALL ANSWERS MUST BE WRITTEN IN EXCEL FORMULAS. A1 face TABLE 2 B D E F G H I J K L M N O P 0 1 TABLE 2 Present Value of $1 2 3 periods 1.00% 1.50% 2.00% 2.50% 3.00% 3.50% 4.00% 4.50% 5.00% 5.50%...

AutoSave . OFF STA 3215 Deliverable 3 - Confidence Intervals Q Home Insert Draw Page Layout Formulas Data Review View Tell me Share Comments Calibri (Body) * 11 AA ab Wrap Text v General Insert v Ex...

M N A B C D E F G H J K L Munjal Showa Limited- Financials Munjal Showa Limited- Income Statement Munjal Showa Limited- Balance Sheet INR Mn except EPS -Y'11 FY'12 FY'13 FY'14 FY'15 INR Mn "Y'11...

7 8 9 3 4 You are a financial analyst for Greenback, Inc. a manufacturing firm, located in Kansas. Your boss, 5 the CFO of the company has asked you to create some spreadsheets and provide some...

The Legal Environment Dimension In determining if an insurer has a duty to defend an insured, should a court ask whether the insured had a reasonable expectation of coverage? Explain.

The Taylor series of function f(x) = ln(x) at a = 3 is C(x-3) n=0 Find the first few coefficients. Co In(3) C = 1/3 C2 = C3 || C4= Use your computations above to guess the form of the general term...

A ( n ) Blank _ _ _ _ _ _ is similar to a mutual fund but the assets in the fund duplicate a stock market index. Multiple choice question. S&L FDIC SEC ETF

2023 1040 form 1.Phillip and Claire are married and file a joint return. Phillip is self-employed as a real estate agent, and Claire is a flight attendant. Phillip and Claire have three dependent...