"Global Manufacturing has 2,550,000, shares of common stock outstanding. The current share price is 115.67. Global also has two semi-annual bond issues outstanding. The first bond issue has a face value of $4,547,200, a coupon rate of 8.21%, and sells for 108.30% of par. The second issue has a face value of $6,004,200, a coupon rate of 6.59%, and sells for 106.90% of par. The first issue matures in 12 years, the second in 24 years.

Suppose the most recent dividend was $0.56 and the constant dividend growth rate is 15.31%. Assume that the overall cost of debt is the weighted average of that implied by the two outstanding debt issues. Both bonds make semiannual payments. The tax rate is 20.00%. What is the companys WACC? (Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.)"

**P.S. Could you also include the formulas used to calculate each yellow cell please, would be greatly appreciated**

AutoSave OFF OF ABES RESU FINC 312 CASE STUDY DATA SHEET-(Fall 2021)(updated) Home - Insert Draw Page Layout Formulas Data Review View Analytic Solver Data Mining Tell me Share Comments Aral v 12 Insert = 2 ~ Al A X LE Custom Conditional Formatting Format e Table Cell Styles 27-O 4 Paste BIU 1 Y Ouv A - -D 2 Delete DE! Farmat Sort & Filler Find & Select Analyze Cala Sensitivity Y Reference Translator B37 . fx =B18*813 A e C D E F H 1 1 1 WEIGHTED AVERAGE COST OF CAPITAL (WACC) ANALYSIS 2 SIMILAR TO PROBLEM 14-13 3 Global Manufacturing has 2,550,000, shares of common stock outstanding. The current share price is $115.67. Global also has two semi-annual bond Issues outstanding. The first bond issue has a face value of $4,547,200, a coupon rate of 8.21%, and sells for 108.30% of par. The second issue has a face value of $6,004,200, a coupon rate of 6.59%, and sells for 106.90% of par. The first issue matures in 12 years, the second in 24 years. Suppose the most recent dividend was $0.56 and the constant dividend growth rate is 15.31%. Assume that the overall cost of debt is the weighted average of that implied by the two outstanding debt issues. Both bonds make semiannual payments. The tax rate ls 20.00%. What is the company's WACC? (Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.) 5 DATA INPUTS 11 First Bond Issue $4,547200 8.21% 108.3% 12 12 13 FACE Value Coupon rate=1 Band price (7 of PAR) | % Years lo Malurily # Times Paid per year = Tax rate=1 PAR Value 15 16 Second Bond Issue $6,004,200 6.59% 106.9% 24 2 SEM-ANNUAL BONDS 20% $1,000.00 Even though not stated in the problem, PAR value is always $1,000 2 17 20% $1,000.00 18 15 common Stack 22 23 26 28 Common shares outstanding = Share price (P) Dividend Today (D.) = Constant Growth Rate (g) - 2,550,000 $115.67 $0.56 15.31% 29 30 STEP #1: Calculate After-tax Cost of Bonds 31 First Bond Second Bond 32 22 Annual Coupon Payment PRO FORMA (WITHOUT CORRECTION) $82.10 PRO FORMA (WITH CORRECTIONS) $65.90 PRO FORMA (EFFECTS) 41.05 32.95 CAPITAL BUDGETING CALCULATIONS WACC + CAPITAL BUDGETING ANALYSIS E-- Ready + 100% AutoSave OFF OF ABES FINC 312 CASE STUDY DATA SHEET-(Fall 2021)(updated) Insert Draw Page Layout Formulas Home - Data Review View Analytic Solver Data Mining Tell me Share Comments Insert X LE Aral 12 ~ Al A Custom = = = , = = Conditional Formatting Format e Table Cell Styles YO 4 DX Delete Paste BIU * Y UvA A -D Sort & Filler Find & Select DE! Farmat Analyze Cala Sensitivity Reference Translator Y B37 . fx =B18*813 T D E F G H 1 1 STEP #1: Calculate After-tax Cost of Bonds 31 First Bond Second Bond 32 33 $82.10 $65.90 11.05 32.95 24 48 34 35 $77.83 $ $64.70 $1,069 32 Annual Coupon Payment Semi-annual period to maturity = Semi-annual interest payment Current Band Price PAR or Face Value = Type=1 SEMI-ANNUAL RATE ANNUAL RATE - YIELD TO MATURITY (YTM) After-tax Cost of Bonds (ro) = $1083 $4,547200 38 39 $6,004,200 O A Bonds 1919 pay interest payments at the end of each period 0 41 -0.74% 1.73% 42 -1.43% 0.00% 0.0000% 0.0000% 43 44 $0.00 STEP #2: Calculate the Cost Common Stock (DGM Approach) 50 53 Dividend One year From Now (D.) = 52 Dividend Yield Capital Gain Yield = 1 54 After-tax Cost of Common Stock (cs) = 0.00% 53 0.00% 0.0000 55 STEP #3: Calculate the Market Value 56 CURRENT MARKET VALUES GO Capital Components 61 $0 First Bond Second Bond 62 $0 64 Common Stock $0 GS Total Market Value = $0 PRO FORMA (WITHOUT CORRECTION) PRO FORMA (WITH CORRECTIONS) PRO FORMA (EFFECTS) WACC CAPITAL BUDGETING CALCULATIONS CAPITAL BUDGETING ANALYSIS + E ---+ 100% + % Ready AutoSave OFF OF ABES RESU FINC 312 CASE STUDY DATA SHEET-(Fall 2021)(updated) Insert Draw Page Layout Formulas Home - Data Review View Analytic Solver Data Mining Tell me Share Comments Aral v 12 Insert = y ~ Al A Custom X LG Conditional Formatting Format e Table Cell Styles 4 DX Deleev Paste BIU 1 Y CA A Sort & Filler = = Find & Select Sensitivity DE! Farmat Reference Translator Analyze Cala Y B37 + x . T fx =B18*813 A e C D E F H 1 1 W.DO TE 54 55 STEP #3: Calculate the Market Value 56 CURRENT MARKET VALUES 60 Capital Components First Bond so 61 62 Second Band $0 Common Stock 64 $0 Total Market Value $0 65 66 STEP #4: Calculate the weighted Average Cost Of Capital (WACC) 67 68 Long-Tern Capital Companeris After-Tax COST(%) Current Market Values (5) SO Current Market Value WEIGHT (%) 0.000% 69 First Bond Weighted Cost 0.00000% 0.00000% 0.00000% 0.0000% 0.0000% 0.0000% Second Bond 0.000% 73 $0 $0 74 Common Stock 0.000% 75 TOTAL $0 0.00% WEIGHTED AVERAGE COST OF CAPITAL (WACC) = 0.00% 76 77 78 79 80 81 92 13 PRO FORMA (WITHOUT CORRECTION) PRO FORMA (WITH CORRECTIONS) PRO FORMA (EFFECTS) WACC CAPITAL BUDGETING CALCULATIONS + CAPITAL BUDGETING ANALYSIS # 2 - Ready + 100% AutoSave OFF OF ABES RESU FINC 312 CASE STUDY DATA SHEET-(Fall 2021)(updated) Home - Insert Draw Page Layout Formulas Data Review View Analytic Solver Data Mining Tell me Share Comments Aral v 12 Insert = 2 ~ Al A X LE Custom Conditional Formatting Format e Table Cell Styles 27-O 4 Paste BIU 1 Y Ouv A - -D 2 Delete DE! Farmat Sort & Filler Find & Select Analyze Cala Sensitivity Y Reference Translator B37 . fx =B18*813 A e C D E F H 1 1 1 WEIGHTED AVERAGE COST OF CAPITAL (WACC) ANALYSIS 2 SIMILAR TO PROBLEM 14-13 3 Global Manufacturing has 2,550,000, shares of common stock outstanding. The current share price is $115.67. Global also has two semi-annual bond Issues outstanding. The first bond issue has a face value of $4,547,200, a coupon rate of 8.21%, and sells for 108.30% of par. The second issue has a face value of $6,004,200, a coupon rate of 6.59%, and sells for 106.90% of par. The first issue matures in 12 years, the second in 24 years. Suppose the most recent dividend was $0.56 and the constant dividend growth rate is 15.31%. Assume that the overall cost of debt is the weighted average of that implied by the two outstanding debt issues. Both bonds make semiannual payments. The tax rate ls 20.00%. What is the company's WACC? (Do not round intermediate calculations and enter your answer as a percent rounded to 2 decimal places, e.g., 32.16.) 5 DATA INPUTS 11 First Bond Issue $4,547200 8.21% 108.3% 12 12 13 FACE Value Coupon rate=1 Band price (7 of PAR) | % Years lo Malurily # Times Paid per year = Tax rate=1 PAR Value 15 16 Second Bond Issue $6,004,200 6.59% 106.9% 24 2 SEM-ANNUAL BONDS 20% $1,000.00 Even though not stated in the problem, PAR value is always $1,000 2 17 20% $1,000.00 18 15 common Stack 22 23 26 28 Common shares outstanding = Share price (P) Dividend Today (D.) = Constant Growth Rate (g) - 2,550,000 $115.67 $0.56 15.31% 29 30 STEP #1: Calculate After-tax Cost of Bonds 31 First Bond Second Bond 32 22 Annual Coupon Payment PRO FORMA (WITHOUT CORRECTION) $82.10 PRO FORMA (WITH CORRECTIONS) $65.90 PRO FORMA (EFFECTS) 41.05 32.95 CAPITAL BUDGETING CALCULATIONS WACC + CAPITAL BUDGETING ANALYSIS E-- Ready + 100% AutoSave OFF OF ABES FINC 312 CASE STUDY DATA SHEET-(Fall 2021)(updated) Insert Draw Page Layout Formulas Home - Data Review View Analytic Solver Data Mining Tell me Share Comments Insert X LE Aral 12 ~ Al A Custom = = = , = = Conditional Formatting Format e Table Cell Styles YO 4 DX Delete Paste BIU * Y UvA A -D Sort & Filler Find & Select DE! Farmat Analyze Cala Sensitivity Reference Translator Y B37 . fx =B18*813 T D E F G H 1 1 STEP #1: Calculate After-tax Cost of Bonds 31 First Bond Second Bond 32 33 $82.10 $65.90 11.05 32.95 24 48 34 35 $77.83 $ $64.70 $1,069 32 Annual Coupon Payment Semi-annual period to maturity = Semi-annual interest payment Current Band Price PAR or Face Value = Type=1 SEMI-ANNUAL RATE ANNUAL RATE - YIELD TO MATURITY (YTM) After-tax Cost of Bonds (ro) = $1083 $4,547200 38 39 $6,004,200 O A Bonds 1919 pay interest payments at the end of each period 0 41 -0.74% 1.73% 42 -1.43% 0.00% 0.0000% 0.0000% 43 44 $0.00 STEP #2: Calculate the Cost Common Stock (DGM Approach) 50 53 Dividend One year From Now (D.) = 52 Dividend Yield Capital Gain Yield = 1 54 After-tax Cost of Common Stock (cs) = 0.00% 53 0.00% 0.0000 55 STEP #3: Calculate the Market Value 56 CURRENT MARKET VALUES GO Capital Components 61 $0 First Bond Second Bond 62 $0 64 Common Stock $0 GS Total Market Value = $0 PRO FORMA (WITHOUT CORRECTION) PRO FORMA (WITH CORRECTIONS) PRO FORMA (EFFECTS) WACC CAPITAL BUDGETING CALCULATIONS CAPITAL BUDGETING ANALYSIS + E ---+ 100% + % Ready AutoSave OFF OF ABES RESU FINC 312 CASE STUDY DATA SHEET-(Fall 2021)(updated) Insert Draw Page Layout Formulas Home - Data Review View Analytic Solver Data Mining Tell me Share Comments Aral v 12 Insert = y ~ Al A Custom X LG Conditional Formatting Format e Table Cell Styles 4 DX Deleev Paste BIU 1 Y CA A Sort & Filler = = Find & Select Sensitivity DE! Farmat Reference Translator Analyze Cala Y B37 + x . T fx =B18*813 A e C D E F H 1 1 W.DO TE 54 55 STEP #3: Calculate the Market Value 56 CURRENT MARKET VALUES 60 Capital Components First Bond so 61 62 Second Band $0 Common Stock 64 $0 Total Market Value $0 65 66 STEP #4: Calculate the weighted Average Cost Of Capital (WACC) 67 68 Long-Tern Capital Companeris After-Tax COST(%) Current Market Values (5) SO Current Market Value WEIGHT (%) 0.000% 69 First Bond Weighted Cost 0.00000% 0.00000% 0.00000% 0.0000% 0.0000% 0.0000% Second Bond 0.000% 73 $0 $0 74 Common Stock 0.000% 75 TOTAL $0 0.00% WEIGHTED AVERAGE COST OF CAPITAL (WACC) = 0.00% 76 77 78 79 80 81 92 13 PRO FORMA (WITHOUT CORRECTION) PRO FORMA (WITH CORRECTIONS) PRO FORMA (EFFECTS) WACC CAPITAL BUDGETING CALCULATIONS + CAPITAL BUDGETING ANALYSIS # 2 - Ready + 100%