We will do some work with the Federal Express Corporation (FDX) in this case. You will calculate several things including the cost of equity, cost of debt and weighted average cost of capital. Please follow the instructions on the first tab of the spreadsheet. The due date is November 22nd, at 11:59pm. Let me know if you have any questions Here is a profile of the company FedEx Corporation is in the transportation sector of the S&P 500. The company provides transportation, e-commerce, and business services worldwide. Its FedEx Express segment offers shipping services for delivery of packages and freight. Its FedEx Ground segment offers business and residential money back guaranteed ground package delivery services and consolidates and delivers low weight and less time-sensitive business-to-consumer packages. Its FedEx Freight segment offers less-than-truckload and other freight delivery services. As of May 31, 2019. It operated approximately 28.000 vehicles and 373 service centers. Its FedEx Services sogment provides sales, marketing, Information technology, communications, customer service, technical support. billing and collection services, and other back-office support services. It also offers FedEx Mobile, a suite of solutions to track packages, create shipping labels, view account-specific rate quotes, and access drop-off location information; FedEx Office, a suite of printing and shipping management solutions, including digital printing, professional finishing, document creation, direct mail, signs and graphics, copying, computer rental, Wi-Fi, and corporate print solutions, and packing services, supplies, and boxes, as well as FedEx Express and FedEx Ground shipping services. Its Corporate, Other and Eliminations segment offers international trade services in customs brokerage, and Ocean and air freight forwarding services: Oros-border enablement and technology solutions, and e-commerce transportation solutions, Integrated supply chain management solutions, time-critical shipment services and critical inventory and service parts logistics, 3-D printing, and technology repair. It also provides international trade advisory services, including assistance with the customs-trade partnership against terrorism program; and publishes customs duty and tax information. It has a strategic alliance with Microsoft Corp. The company was founded by Frederick Smith in 1971 and is headquartered in Memphis, Tennessee. When Mr. Smith attended graduate school at Yale University, he wrote a paper that outlined the overnight delivery service. D1 A D Excel Case #2 - Federal Express Corp. (FDX) 2 3 Total points available 20 4 5 The Aswer Schedule shows the points per question / answer 6 points available for each answer are all or nothing - must be correct answer AND in correct format 17 Rounding of your answers should take place when you get to the final step, do not round prior to that 8 Please refer to the other worksheets in this Excel file to get the data you need 9 You need to show your work somewhere in this Excel file to get credit when calculations are involved 10 if you just provide the correct answers and do not show your work when calculations are necessary, you will not earn full credit 11 On the answer schedule, you can use cell references OR manually input your answers (as long as you did the actual calculations in Excel) 12 For example: show the growth rate for equity on the worksheet that contains the data you use for the calculation D Excel Case #2 - Federal Express Corp. Answer Schedule Format Points available Remarks billion 0.50 1.75 0.50 0.50 0.50 use 05/31/19 data provided on Data tabuse value in BILLIONS use dividend growth for most recent five years as I have segregated on the Data tab use 05/31/19 data provided on Datatab use the most recent year on the Data tab use 05/31/19 data provided on Duta tab Market value of equity (market cap Growth rate for equity Market price of stock (pet shate) Annual dividend 9 Deta of stock 10 Risk tree rate 11 Market rate of return 12 DGM cost of equity (stock 13 CAPM cost of equity (stock 14 Average cost of equity 15 Market value of de 1.10 9.90 currency, 3 decimal places percentage, 2 decimal places Currency, 2 decimal places Currency, 2 decimal places number, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places Currency, 3 decimal places 125 125 1.00 0.50 Ibition simple average of two methods use bond schedule for DS/31/19 - use value in LUONS use average for most recent five calendar yasthere are two acceptable ways to calculate this that result in two lightly different answers use band schedule for 05/31/19-e value in BILLIONS use bond schedule for 05/31/19-value in SILLIONS une value in BILLIONS 1.00 3.50 1.25 050 16 Average tax rate percentage, 2 decimal places 17 Pretax cost of debt bonds) percentage, 2 decimal places 18 After tax cost of det (bonda percentage, 2 decimal places 10 Total market value of equity and det billion currency, 1 decimal places 10 Weight for et number, a decimal place 21 Weight for dett number, & decimal places 12 Werdverage cost of capital percent, 2 decimal places 21 24 Sve your fee with this name format Case 2 Fistinitilastame section 25 Example Excel Chie 2 Milwinnow 1.25 1.25 3.50 27 Total points 20.00 2 11 - EL Poste 3 18 B = Format F1 1 Corp FOX BALANCE SHEET 2 calands in Mey. USD in ons 2019-05 2018 Os 2017-05 2016-05 2015-09 2,319 9116 553 3265 RARI -3.969 1590 514 3.534 7252 496 1763 5.719 498 SOG 255 101 1,050 110 1020 13,341 546 12,420 707 11.99 59.511 179,00 6.34 42.864 (21.37 1.10 55.121 26,967) 5,973 480 3,382 38,888 52,330 50.626 124,545) 7.154 529 2,260 35.924 LESS 47,018 (22,734) 6,747 1. DON 2.036 14075 46,054 4004 41317 54.403 24,138 3709 4 Durres 5 ash and cash valents cevable 7 ore & peerred Income ad persies 10 current sets 11 on-cum 12 res property, plant and it 13 cm Depreciation 14. 15 mg 16 other long-term 17-es 20 talets 19 20 21 current 22 ortamdebt 23 counts payable 24 payable 25 rued 26 Other current 27 a current 28 current abs 23 ongorm debt 30 Deerde bi 31 red leti 12 wees 33ions and other benefits 4 other long term 35 ore ST 29 2.44 964 2.030 356 3,278 1,385 9,013 1.342 2.977 334 4041 933 9427 22 2,752 201 4,080 781 7,918 3820 304 1.DOS 2.066 378 1011 502 5.957 7,249 1. 1120 181 16,617 2.821 1.899 99 5,095 1,102 27,633 16,646 15263 2.367 1.784 121 2.187 1 DBS 23.287 12.914 14309 2AUS 1494 137 4407 1,049 24561 12.AT 13.30 1.567 1,314 155 6,227 1,171 24,272 32280 329 16115 22,078 37 - Manga on stock 40 tona pod TE 3,231 245 42 wystad med other comprensivo 46 Hedden' 45 cholery 12 3.05 20,339 07:382) (415) 16.073 48.552 3.117 242 07878 (578 1941 52,330 32 2.892 103 17-3431 (16) 174 1.064 2.785 1600 4897 172 1493 1855) 17,757 54403 49 CO INCOME STATEMENT so way.Un 2018-as 2011-05 2011-os 2015 2011-05 15 19 10365 52 53 Cup 54 rous press 52354 17.30 18 11. 439 65 410 43,764 15.685 1145 4524 9.387 3.00 41.14 1UIS 1,199 74 36 305 10.14 2,10 7.561 16 141 401 5,707 250 3,077 337 4.37 57 SITOA Herende perting income Grupos 61 cm 62 compense SET 4.556 2.04 SS SIE SSH 5,714 1142 472 4054 105 2.999 932 1.527 479 14 79 59 Instructions AntwerSchedule Financials Bond Sched + B C D E F G FED EX Corp. yahoo finance data 1 05/31/19 (year end) 2 3 4 Current stock price $ 154.28 5 Market Cap (Value) $ 49.061 billion 6 Beta 1.42 7 PE Ratio 74.89 8 EPS $ 2.06 Su 9 Annual dividend $ 2.60 10 #of shares outstanding 318 million 11 12 13 current price $ 154.28 14 outstanding shares 318,000,000 15 market value $ 49,061,040,000 16 or 17 $ 49.061 billion 18 19 20 21 Note: FedEx paid out more in dividends in the most recent year end 22 than it actually earned. Companies can do this if they have sufficient 23 retained earnings. 24 25 26 27 Date 06/21/19 03/08/19 12/07/18 09/07/18 06/22/18 03/09/18 12/08/17 09/08/17 06/20/17 03/09/17 12/08/16 09/08/16 06/14/16 03/10/16 12/10/15 09/08/15 06/16/15 03/09/15 12/10/14 09/08/14 Quarterly Dividends 0.6500 0.6500 0.6500 0.6500 0.6500 0.5500 0.5500 0.5500 0.5500 0.4800 0.4800 0.4800 0.4800 0.4200 0.4200 0.4200 0.4200 0.3800 0.3800 0.3800 Fed Ex Corp. outstanding bonds as of 05/31/19 Face amounts are in millions Name Current Price Coupon % Market Value $ (MI) Weight Weighted YTM yo Senior Unsecured Debt 7 Senior Unsecured Debt Senior Unsecured Debt s Senior Unsecured Debt 10. Senior Unsecuted Debt 11 Senior Unsecured Debt 12 Senior Unsecured Debt 11 Senior Unsecured Debt 14 Senior Unsecured Debt 15 Senior Unsecured Debt 16 Senior Unsecured Debt 17 Senior Unsecured Debt 16 Senior Unaecumd Debt 19 Senior Unsecurd Deb 20 Sanier Uhsecured Debt 21 Senior Unsecured Debt 22 Senior Unseed Debt 23 Senior Unsecuted Det 24 Senior Unsecured Debt 25 Senior Unsecured Debt 25 Senior Unsecured Debt Foreign 27 Senior Linsecured Debt Foreign 28 Senior Unsecured Debt Foreign) 29 Senior Unsecured Debt Foreign 30 Other Debt and Captai Lentes 31 Totals 32 Maturity Year 2020 2022 2025 2024 2025 2025 2027 2028 2020 2034 2035 2049 2044 2045 2046 2047 2048 2049 2065 2098 2020 2022 2023 2027 Veron Face Amount $(MI) 405.00 500.00 770.00 750.00 740.00 810.00 490,00 555.00 395.00 505.00 580.00 1.215.00 795.00 820.00 2.955.00 930.00 1.345.00 835.00 325.00 225.00 565.00 715.00 930.00 1.730.00 6670 19,950.20 or 19.950 billion 95.8000 100,0000 97.0000 99.5000 93.5000 92.0000 90 3000 88.7500 100.7500 977000 85.4000 81.0000 93 3000 78.0000 83 2500 79.0000 73 2000 100 4000 75.0000 103 8000 99.8800 100.0000 89.6000 50.4000 100,0000 2.300% 3.400% 2.650% 4.000 3.200% 3.250% 3 300% 3400% 4.200% 4.900 3.000% 4.000% 5.100% 4. 100% 4,850 4.400% 4.050% 4.950% 4 500% 7.600 1.450% 2.300% 1.800% 1,900% 2.200% Yield to Maturity % 3.3556% 3:40005 3.4434% 4.1094% 4.4302% 4.5851% 4.7502% 4.9526% 4.1080% 5.1203% 5.2557 $.42215 5.8001% 5.7350 5.8954% 5.9489% 6,0110% 4.8240% 6.1342% 7.3202% 1.5898% 2 3000% 4.6238% 41610% 2.1991 14 Pre-tax Cost of Debel 35 37 38 39 40 Adjustment for tax rate After tax Cost of Debt 43 We will do some work with the Federal Express Corporation (FDX) in this case. You will calculate several things including the cost of equity, cost of debt and weighted average cost of capital. Please follow the instructions on the first tab of the spreadsheet. The due date is November 22nd, at 11:59pm. Let me know if you have any questions Here is a profile of the company FedEx Corporation is in the transportation sector of the S&P 500. The company provides transportation, e-commerce, and business services worldwide. Its FedEx Express segment offers shipping services for delivery of packages and freight. Its FedEx Ground segment offers business and residential money back guaranteed ground package delivery services and consolidates and delivers low weight and less time-sensitive business-to-consumer packages. Its FedEx Freight segment offers less-than-truckload and other freight delivery services. As of May 31, 2019. It operated approximately 28.000 vehicles and 373 service centers. Its FedEx Services sogment provides sales, marketing, Information technology, communications, customer service, technical support. billing and collection services, and other back-office support services. It also offers FedEx Mobile, a suite of solutions to track packages, create shipping labels, view account-specific rate quotes, and access drop-off location information; FedEx Office, a suite of printing and shipping management solutions, including digital printing, professional finishing, document creation, direct mail, signs and graphics, copying, computer rental, Wi-Fi, and corporate print solutions, and packing services, supplies, and boxes, as well as FedEx Express and FedEx Ground shipping services. Its Corporate, Other and Eliminations segment offers international trade services in customs brokerage, and Ocean and air freight forwarding services: Oros-border enablement and technology solutions, and e-commerce transportation solutions, Integrated supply chain management solutions, time-critical shipment services and critical inventory and service parts logistics, 3-D printing, and technology repair. It also provides international trade advisory services, including assistance with the customs-trade partnership against terrorism program; and publishes customs duty and tax information. It has a strategic alliance with Microsoft Corp. The company was founded by Frederick Smith in 1971 and is headquartered in Memphis, Tennessee. When Mr. Smith attended graduate school at Yale University, he wrote a paper that outlined the overnight delivery service. D1 A D Excel Case #2 - Federal Express Corp. (FDX) 2 3 Total points available 20 4 5 The Aswer Schedule shows the points per question / answer 6 points available for each answer are all or nothing - must be correct answer AND in correct format 17 Rounding of your answers should take place when you get to the final step, do not round prior to that 8 Please refer to the other worksheets in this Excel file to get the data you need 9 You need to show your work somewhere in this Excel file to get credit when calculations are involved 10 if you just provide the correct answers and do not show your work when calculations are necessary, you will not earn full credit 11 On the answer schedule, you can use cell references OR manually input your answers (as long as you did the actual calculations in Excel) 12 For example: show the growth rate for equity on the worksheet that contains the data you use for the calculation D Excel Case #2 - Federal Express Corp. Answer Schedule Format Points available Remarks billion 0.50 1.75 0.50 0.50 0.50 use 05/31/19 data provided on Data tabuse value in BILLIONS use dividend growth for most recent five years as I have segregated on the Data tab use 05/31/19 data provided on Datatab use the most recent year on the Data tab use 05/31/19 data provided on Duta tab Market value of equity (market cap Growth rate for equity Market price of stock (pet shate) Annual dividend 9 Deta of stock 10 Risk tree rate 11 Market rate of return 12 DGM cost of equity (stock 13 CAPM cost of equity (stock 14 Average cost of equity 15 Market value of de 1.10 9.90 currency, 3 decimal places percentage, 2 decimal places Currency, 2 decimal places Currency, 2 decimal places number, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places Currency, 3 decimal places 125 125 1.00 0.50 Ibition simple average of two methods use bond schedule for DS/31/19 - use value in LUONS use average for most recent five calendar yasthere are two acceptable ways to calculate this that result in two lightly different answers use band schedule for 05/31/19-e value in BILLIONS use bond schedule for 05/31/19-value in SILLIONS une value in BILLIONS 1.00 3.50 1.25 050 16 Average tax rate percentage, 2 decimal places 17 Pretax cost of debt bonds) percentage, 2 decimal places 18 After tax cost of det (bonda percentage, 2 decimal places 10 Total market value of equity and det billion currency, 1 decimal places 10 Weight for et number, a decimal place 21 Weight for dett number, & decimal places 12 Werdverage cost of capital percent, 2 decimal places 21 24 Sve your fee with this name format Case 2 Fistinitilastame section 25 Example Excel Chie 2 Milwinnow 1.25 1.25 3.50 27 Total points 20.00 2 11 - EL Poste 3 18 B = Format F1 1 Corp FOX BALANCE SHEET 2 calands in Mey. USD in ons 2019-05 2018 Os 2017-05 2016-05 2015-09 2,319 9116 553 3265 RARI -3.969 1590 514 3.534 7252 496 1763 5.719 498 SOG 255 101 1,050 110 1020 13,341 546 12,420 707 11.99 59.511 179,00 6.34 42.864 (21.37 1.10 55.121 26,967) 5,973 480 3,382 38,888 52,330 50.626 124,545) 7.154 529 2,260 35.924 LESS 47,018 (22,734) 6,747 1. DON 2.036 14075 46,054 4004 41317 54.403 24,138 3709 4 Durres 5 ash and cash valents cevable 7 ore & peerred Income ad persies 10 current sets 11 on-cum 12 res property, plant and it 13 cm Depreciation 14. 15 mg 16 other long-term 17-es 20 talets 19 20 21 current 22 ortamdebt 23 counts payable 24 payable 25 rued 26 Other current 27 a current 28 current abs 23 ongorm debt 30 Deerde bi 31 red leti 12 wees 33ions and other benefits 4 other long term 35 ore ST 29 2.44 964 2.030 356 3,278 1,385 9,013 1.342 2.977 334 4041 933 9427 22 2,752 201 4,080 781 7,918 3820 304 1.DOS 2.066 378 1011 502 5.957 7,249 1. 1120 181 16,617 2.821 1.899 99 5,095 1,102 27,633 16,646 15263 2.367 1.784 121 2.187 1 DBS 23.287 12.914 14309 2AUS 1494 137 4407 1,049 24561 12.AT 13.30 1.567 1,314 155 6,227 1,171 24,272 32280 329 16115 22,078 37 - Manga on stock 40 tona pod TE 3,231 245 42 wystad med other comprensivo 46 Hedden' 45 cholery 12 3.05 20,339 07:382) (415) 16.073 48.552 3.117 242 07878 (578 1941 52,330 32 2.892 103 17-3431 (16) 174 1.064 2.785 1600 4897 172 1493 1855) 17,757 54403 49 CO INCOME STATEMENT so way.Un 2018-as 2011-05 2011-os 2015 2011-05 15 19 10365 52 53 Cup 54 rous press 52354 17.30 18 11. 439 65 410 43,764 15.685 1145 4524 9.387 3.00 41.14 1UIS 1,199 74 36 305 10.14 2,10 7.561 16 141 401 5,707 250 3,077 337 4.37 57 SITOA Herende perting income Grupos 61 cm 62 compense SET 4.556 2.04 SS SIE SSH 5,714 1142 472 4054 105 2.999 932 1.527 479 14 79 59 Instructions AntwerSchedule Financials Bond Sched + B C D E F G FED EX Corp. yahoo finance data 1 05/31/19 (year end) 2 3 4 Current stock price $ 154.28 5 Market Cap (Value) $ 49.061 billion 6 Beta 1.42 7 PE Ratio 74.89 8 EPS $ 2.06 Su 9 Annual dividend $ 2.60 10 #of shares outstanding 318 million 11 12 13 current price $ 154.28 14 outstanding shares 318,000,000 15 market value $ 49,061,040,000 16 or 17 $ 49.061 billion 18 19 20 21 Note: FedEx paid out more in dividends in the most recent year end 22 than it actually earned. Companies can do this if they have sufficient 23 retained earnings. 24 25 26 27 Date 06/21/19 03/08/19 12/07/18 09/07/18 06/22/18 03/09/18 12/08/17 09/08/17 06/20/17 03/09/17 12/08/16 09/08/16 06/14/16 03/10/16 12/10/15 09/08/15 06/16/15 03/09/15 12/10/14 09/08/14 Quarterly Dividends 0.6500 0.6500 0.6500 0.6500 0.6500 0.5500 0.5500 0.5500 0.5500 0.4800 0.4800 0.4800 0.4800 0.4200 0.4200 0.4200 0.4200 0.3800 0.3800 0.3800 Fed Ex Corp. outstanding bonds as of 05/31/19 Face amounts are in millions Name Current Price Coupon % Market Value $ (MI) Weight Weighted YTM yo Senior Unsecured Debt 7 Senior Unsecured Debt Senior Unsecured Debt s Senior Unsecured Debt 10. Senior Unsecuted Debt 11 Senior Unsecured Debt 12 Senior Unsecured Debt 11 Senior Unsecured Debt 14 Senior Unsecured Debt 15 Senior Unsecured Debt 16 Senior Unsecured Debt 17 Senior Unsecured Debt 16 Senior Unaecumd Debt 19 Senior Unsecurd Deb 20 Sanier Uhsecured Debt 21 Senior Unsecured Debt 22 Senior Unseed Debt 23 Senior Unsecuted Det 24 Senior Unsecured Debt 25 Senior Unsecured Debt 25 Senior Unsecured Debt Foreign 27 Senior Linsecured Debt Foreign 28 Senior Unsecured Debt Foreign) 29 Senior Unsecured Debt Foreign 30 Other Debt and Captai Lentes 31 Totals 32 Maturity Year 2020 2022 2025 2024 2025 2025 2027 2028 2020 2034 2035 2049 2044 2045 2046 2047 2048 2049 2065 2098 2020 2022 2023 2027 Veron Face Amount $(MI) 405.00 500.00 770.00 750.00 740.00 810.00 490,00 555.00 395.00 505.00 580.00 1.215.00 795.00 820.00 2.955.00 930.00 1.345.00 835.00 325.00 225.00 565.00 715.00 930.00 1.730.00 6670 19,950.20 or 19.950 billion 95.8000 100,0000 97.0000 99.5000 93.5000 92.0000 90 3000 88.7500 100.7500 977000 85.4000 81.0000 93 3000 78.0000 83 2500 79.0000 73 2000 100 4000 75.0000 103 8000 99.8800 100.0000 89.6000 50.4000 100,0000 2.300% 3.400% 2.650% 4.000 3.200% 3.250% 3 300% 3400% 4.200% 4.900 3.000% 4.000% 5.100% 4. 100% 4,850 4.400% 4.050% 4.950% 4 500% 7.600 1.450% 2.300% 1.800% 1,900% 2.200% Yield to Maturity % 3.3556% 3:40005 3.4434% 4.1094% 4.4302% 4.5851% 4.7502% 4.9526% 4.1080% 5.1203% 5.2557 $.42215 5.8001% 5.7350 5.8954% 5.9489% 6,0110% 4.8240% 6.1342% 7.3202% 1.5898% 2 3000% 4.6238% 41610% 2.1991 14 Pre-tax Cost of Debel 35 37 38 39 40 Adjustment for tax rate After tax Cost of Debt 43