Compute the WACC for all three car companies in the excel file using the same methodology as the attached disney example. Then the company's WACC will be the average of those 3. I uploaded an example of how the WACC needs to be calculated (specific instructions given by my professor). Can you find the average WACC of all 3 car companies using the methodology in the attached example (disney). My project is due in a couple of hours, please help me out. It would be greatly appreciated!

1. Yield for 10-year Treasury bonds: 2.181% 2. Disney's Market Capitalization: Disney's Enterprise Value: Disney's Cash: Disney's equity beta: 170,420 million 178,760 million 4,400 1.63 3. Disney's cost of debt: 3.917% maturity is Mar. 1, 2032 5. Total market value of Disney's debt: 6. Weight of debt: Weight of equity: 6.773% 93.227% 7. Disney's cost of equity: 10.331% 8. Disney's effective cost of debt: 2.546% 9. Disney's WACC: 9.804% 10. Net debt: Weight of debt: Weight of equity: 7,981 4.474% 95.526% Disney's WACC: 9.983% 12,381 million Check the next worksheet THIS DATA IS LITTLE OUTDATED. PLEASE UPDATE THIS LIST. Issuer Name Bond Symbol Callable Coupon CAPITAL CITIES ABC INCDIS.GB 8.75 DISNEY WALT CO Yes 7.55 DIS.GC DISNEY WALT CO NEW DIS.HL 7 DISNEY WALT CO NEW DIS.IK Yes 1.35 DISNEY WALT CO NEW DIS.IL Yes 2.75 DISNEY WALT CO NEW DIS.IM Yes 4.375 DISNEY WALT CO NEW DIS3819067 Yes 1.125 DISNEY WALT CO NEW DIS3819069 Yes 2.55 DISNEY WALT CO NEW DIS.HY Yes 5.625 DISNEY WALT CO NEW DIS.IJ Yes 3.75 DISNEY WALT CO NEW DIS.HV 5.875 DISNEY WALT CO NEW DIS.IO Yes 4.125 DISNEY WALT CO NEW DIS.IN Yes 0.875 DISNEY WALT CO NEW DIS3937503 Yes 0.45 DISNEY WALT CO NEW DIS3937504 Yes 1.1 DISNEY WALT CO NEW DIS3937505 Yes 2.35 DISNEY WALT CO NEW DIS3937521 Yes 3.7 DISNEY WALT CO NEW DIS.IB Yes 6 DISNEY WALT CO NEW DIS3968745 No 0.231 DISNEY WALT CO NEW DIS.IA 6.2 DISNEY WALT CO NEW DIS.II Yes 5.5 DISNEY WALT CO NEW DIS.GV Yes DISNEY WALT CO NEW DIS.GP Yes Maturity 08/15/2021 07/15/2093 01/03/2032 08/16/2016 08/16/2021 08/16/2041 02/15/2017 02/15/2022 09/15/2016 06/01/2021 12/15/2017 12/01/2041 12/01/2014 12/01/2015 12/01/2017 12/01/2022 12/01/2042 07/17/2017 02/11/2015 06/20/2014 03/15/2019 04/01/2039 08/15/2057 Moody A1 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 A2 Ratings S&P A A A A A A A A A A A A A A A A A A A A A A A Fitch A A A A A A A A A A A A A A A A A A A A A A A Price Last Sale Yield 135.274 123.469 133.365 101.465 99.547 100.701 101.186 96.5 112.403 106.618 114.25 95.421 100.526 99.925 99.259 93.434 89.682 115.547 100.033 101.965 116.15 97 99.3 3.436 4.723 4.321 0.765 2.817 4.331 0.729 3.045 0.819 2.746 2.036 4.412 0.243 0.491 1.299 3.21 4.33 1.397 1.117 2.149 4 5 Amount OutstandingMarket Value of Issue $97,495,000 $131,885,386 $201,169,000 $248,381,353 $25,000,000 $33,341,250 $1,000,000,000 $1,014,650,000 $750,000,000 $746,602,500 $350,000,000 $352,453,500 $1,000,000,000 $1,011,860,000 $400,000,000 $386,000,000 $750,000,000 $843,022,500 $500,000,000 $533,090,000 $300,000,000 $342,750,000 $600,000,000 $572,526,000 $1,000,000,000 $1,005,260,000 $500,000,000 $499,625,000 $1,000,000,000 $992,590,000 $1,000,000,000 $934,340,000 $500,000,000 $448,410,000 $350,000,000 $404,414,500 $800,000,000 $800,264,000 $450,000,000 $458,842,500 $500,000,000 $580,750,000 $25,000,000 $24,250,000 $16,305,000 $16,190,865 total market value of Disney debt: $12,381,499,354 e Computing WACC Step-by-step instructions Hands-on practice Period Ending Total Revenue Cost of Revenue 31-Dec-12 31-Dec-13 31-Dec-14 133,559,000 146,917,000 144,077,000 116,154,000 128,055,000 126,215,000 Gross Profit 17,405,000 18,862,000 17,862,000 Operating Expenses Research Development Selling General and Administrative Non Recurring Others Total Operating Expenses - 11,529,000 13,176,000 14,117,000 77,000 208,000 305,000 - Operating Income or Loss - - 5,799,000 5,478,000 3,440,000 Income from Continuing Operations Total Other Income/Expenses Net Earnings Before Interest And Taxes Interest Expense Income Before Tax Income Tax Expense Minority Interest 1,964,000 8,351,000 713,000 7,638,000 2,026,000 1,000 1,322,000 7,869,000 829,000 7,040,000 -135,000 7,000 424,000 5,139,000 797,000 4,342,000 1,156,000 1,000 Net Income From Continuing Ops 6,201,000 8,251,000 4,462,000 Non-recurring Events Discontinued Operations Extraordinary Items Effect Of Accounting Changes Other Items Net Income Preferred Stock And Other Adjustments Net Income Applicable To Common Shares - - 5,613,000 - - 7,182,000 - 5,613,000 3,187,000 - 7,182,000 3,187,000 Growth Rate 8% 9% Period Ending Total Revenue Cost of Revenue 31-Dec-12 31-Dec-13 31-Dec-14 152,256,000 155,427,000 155,929,000 141,443,000 137,373,000 142,121,000 Gross Profit 10,813,000 18,054,000 13,808,000 Operating Expenses Research Development Selling General and Administrative Non Recurring Others Total Operating Expenses - 14,031,000 12,382,000 12,158,000 27,145,000 541,000 120,000 - Operating Income or Loss Non-recurring Events Discontinued Operations Extraordinary Items Effect Of Accounting Changes Other Items 5,131,000 1,530,000 595,000 -28,206,000 489,000 -28,695,000 -34,831,000 52,000 851,000 7,792,000 334,000 7,458,000 2,127,000 15,000 1,025,000 4,649,000 403,000 4,246,000 228,000 -69,000 7,500,000 Net Income From Continuing Ops Net Income Applicable To Common Shares - -30,363,000 Income from Continuing Operations Total Other Income/Expenses Net Earnings Before Interest And Taxes Interest Expense Income Before Tax Income Tax Expense Minority Interest Net Income Preferred Stock And Other Adjustments - 6,944,000 6,245,000 - - 6,188,000 - - 5,346,000 - 4,859,000 3,949,000 - 3,770,000 2,804,000 Growth Rate 2% 1% Period Ending Total Revenue Cost of Revenue 31-Mar-13 31-Mar-14 31-Mar-15 234,289,000 249,472,000 227,096,000 197,940,000 201,982,000 182,128,000 Gross Profit 36,349,000 47,490,000 44,968,000 Operating Expenses Research Development Selling General and Administrative Non Recurring Others Total Operating Expenses - 22,323,000 25,233,000 22,033,000 - Operating Income or Loss - - 14,026,000 22,257,000 22,936,000 Income from Continuing Operations Total Other Income/Expenses Net Earnings Before Interest And Taxes Interest Expense Income Before Tax Income Tax Expense Minority Interest 1,123,000 1,637,000 1,377,000 15,149,000 23,894,000 24,313,000 244,000 191,000 191,000 14,905,000 23,703,000 24,122,000 5,858,000 7,456,000 7,450,000 -1,288,000 -1,636,000 -1,122,000 Net Income From Continuing Ops 10,217,000 17,703,000 18,122,000 Non-recurring Events Discontinued Operations Extraordinary Items Effect Of Accounting Changes Other Items Net Income Preferred Stock And Other Adjustments Net Income Applicable To Common Shares - - - 10,217,000 17,703,000 18,122,000 10,217,000 17,703,000 18,122,000 Growth Rate -2% Growth -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% -2% Growth Rates Best: Normal: Worst: 8% 2% -2% Income Statement Sales COGS Gross Profit 2015 $ $ $ 40,000 18,200 21,800 SGA Depreciation Fixed Expense Expenses $ $ $ $ 4,000 3,000 4,000 11,000 EBIT Taxes Net Income $ $ $ 10,800 4,320 6,480 Balance Sheet Current Assets PPE Total Assets Accounts Payable Notes Payable AC Expenses Bonds Payable Common Stock Paidin Surplus Retained Earnings Total L+SE 2015 $ $ $ $ $ $ $ $ $ $ $ 100,000 70,000 170,000 40,000 10,000 10,000 40,000 40,000 20,000 10,000 170,000 Tax Rate 40% 2014 Retain 42287000 ned earnings Best Case Income Statement Sales COGS Gross Profit 2016 $ $ $ 43,227 19,668 23,559 SGA Depreciation Fixed Expense Expenses $ $ $ $ 4,323 3,000 4,000 11,323 EBIT Taxes Net Income $ $ $ 12,236 4,894 7,342 Payout Ratio 10% EBIT %: 28% Tax Rate 40% Balance Sheet Current Assets PPE Total Assets 2016 $ $ $ 108,069 75,648 183,716 Accounts Payable Notes Payable AC Expenses Bonds Payable Common Stock Paidin Surplus Retained Earnings Total L+SE $ $ $ $ $ $ $ $ 43,227 10,807 40,000 53,075 20,000 16,608 183,716 Add. Funds Needed $ 13,075 Normal Case Income Statement Sales COGS Gross Profit 2016 $ $ $ 40,962 18,638 22,324 SGA Depreciation Fixed Expense Expenses $ $ $ $ 4,096 3,000 4,000 11,096 EBIT Taxes Net Income $ $ $ 11,228 4,491 6,737 Payout Ratio 10% EBIT %: 27% Tax Rate 40% Balance Sheet Current Assets PPE Total Assets 2016 $ $ $ 102,406 71,684 174,090 Accounts Payable Notes Payable AC Expenses Bonds Payable Common Stock Paidin Surplus Retained Earnings Total L+SE $ $ $ $ $ $ $ $ 40,962 10,241 40,000 46,150 20,000 16,737 174,090 Add. Funds Needed $ 6,150 Worst Case Income Statement Sales COGS Gross Profit 2016 $ $ $ 39,004 17,747 21,257 SGA Depreciation Fixed Expense Expenses $ $ $ $ 3,900 3,000 4,000 10,900 EBIT Taxes Net Income $ $ $ 10,357 4,143 6,214 Payout Ratio 10% EBIT %: 27% Tax Rate 40% Balance Sheet Current Assets PPE Total Assets 2016 $ $ $ Accounts Payable Notes Payable AC Expenses Bonds Payable Common Stock Paidin Surplus Retained Earnings Total L+SE $ $ $ $ $ $ $ $ 39,004 9,751 40,000 41,421 20,000 15,592.77 165,769 Add. Funds Needed $ 1,421 97,511 68,258 165,769 (All Numbers in $Million) Sales Growth of Sales EBIT = 28% of sales Tax = 40% of EBIT Net Income Increase in Sales 10% of Increase in sales Step 1 (Compute FCF) FCF= NI - All Adjustments $ $ $ $ 11,200 $ 4,480 $ 7,342 $ $ $ 2016 43,227 8% 12,104 4,841 7,262 3,227 323 $ $ $ $ $ $ 2017 46,283 7% 12,959 5,184 7,776 3,056 306 $ $ $ $ $ $ 2018 49,092 6% 13,746 5,498 8,247 2,809 281 $ $ $ $ $ $ 2019 50,564 3% 14,158 5,663 8,495 1,473 147 $ $ $ $ $ $ 2020 52,081 3% 14,583 5,833 8,750 1,517 152 $ $ $ $ $ $ $ Step 2 (Compute V0) Present Value of FCF Terminal Value Present Value of Terminal Value WACC V0 = Sum of all PVs Cash Debt Number of Shares Step 3 (Compute Share Price) P0 2015 40,000 $ 6,939 $ 7,470 $ 7,967 $ 8,348 $ 8,598 $ $ 6,309 $ 6,174 $ 5,985 $ 5,701 $ 5,339 $ $ $ 10% $ $ $ 108,062 3,300 1,200 893.6 $ 123.28 73,556 2021 53,644 3% 15,020 6,008 9,012 1,562 156 8,856 $ 9,122 4,999 130,308