C D F Excel Case #2 - Federal Express Corp. Format Remarks Answer Schedule Points available Billion 0:50 17 050 use 05/31/19 data provided on Data tabus value in BLLONS use dividend growth for most recent five years as I have segregated on the Data tab use 05/31/19 data provided on Data tab use the most recent year on the Duta tab use 05/31/19 data provided on Data tab os 0.50 Market value of equity Imarket cap Growth rate for equity Market price of stock per share) Annual dividend Beta of stod Risk free rate Market rate of retum 2 DGM cost of equity (stock) CAPM cost of equity (stock) 4 Average cost of equity S Market value of debt 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 1.25 1.25 100 050 bon simple average of two methods use bond schedule for 05/31/19-e value in BILLIONS use average for most recent five calendar years there are two acceptable ways to calculate this that result in two lightly diferent answers use bond schedule for 05/31/10-ute value in BILLIONS use bond schedule for 05/11/19-se value in SILLIONS use value in LIONS 100 3.50 125 16 Average tax rate 7 Pretax cost of debt bonds) El After tax cost of debt bonds) Total market value of wity and debt 20 Weight for et 21 Weight for det 32 Weighted average cost of capital bio percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places currency, 3 decimal places number, & decimal places number, 4 decimal places percentage, 2 decimal places 0.50 125 1.25 3.50 24 Suv your le with this name format Excel Case 2 Finstintillastame section 25 Example: Excel Case 2 Mwinnow 26 21 Total points 21 20.00 30 Submit on Blackboard - do not email directly to Professor Gwinn due date and time is November 22nd, 11:58pm) I will only grade your last mission (2 maimum submissions) 3 te sutrisslons are subject to a point penalty 15 40 41 F1 fi D E G H 2019.05 2018-0 2017-05 2016-05 2015.05 2,319 9,116 553 3,265 8,481 3.969 7,599 514 3.534 7,252 496 3.763 5,719 495 606 525 SSE 1 Pedex Corp (FO) BALANCE SHEET Palyarends in May. USD in million 3 Assets 4 Current assets 5 Cash and cash equivalents 6 Receivables 7 Inventories & Deferred income 9 Prepaid expenses 10 Totalcunent sets 11 Nen-current assets 12 Grous property, plant and equipment 13 Accumulated Depreciation 14 Good 15 target 16 Other long termasse 17 Total non-cometsets 109 13.06 1,070 13,341 546 12.628 707 11.989 10.945 59,511 129,082) 6,884 42,864 (21,989) 3,810 55.121 (26,967) 6,973 480 3,382 38,989 52,330 50,626 (24,6451 7,154 529 2,260 35,924 40,552 47018 (22,734) 6.747 1.000 2015 34,075 46,054 4004 41.317 54,400 1,443 25,128 17.069 964 3.030 356 3,278 1,385 9,018 1,342 2,977 334 4,041 933 1.677 22 2.752 283 4,000 781 7,918 29 2.944 311 3.920 304 1.Doa 2,066 323 303 507 5,957 19 20 Les 22 Current les 22 Short term debt 23 Accounts payable 34 Tues payable 25 Accrued liabile 26 Omer current abilities 27 Total current liabilities 20 Non-current oblities 23 Long-term det 30 Deferred the 31 Accrued abilities 32 Oferred revenues 33 Pen we other benefits Mother long mabilis 35 Total Babies 16 Total 16.617 2.821 1899 99 5,095 1,102 27,633 36,546 15 241 2.367 1.784 121 2.187 1,085 23,283 32,914 14,909 2485 1.494 137 4487 1,049 24,561 32.479 13,838 1,567 1314 155 6222 1,171 24,272 32.280 7,249 1,747 1.120 151 4.89 925 16.119 22.075 LE 32 38 Shockden'egull 39 Commons 40 Additional pidin capital 41 und warnings 1 Tung Mark 41 Arund the comprehensive income 45 Toother equity 12 3.231 24641 19,289) (565) 17,757 54.403 3117 24823 17.978) 15701 19416 52.310 12 30s 20833 173021 (415) 14,073 43532 32 2892 18,371 17-3421 (169) 13,784 46,064 16.900 14.97 14,99 7 48 4 Corp) INCOME STATEMENT 50 dywends in Musson 51 1013-05 2016-05 2015-05 47453 36,505 SB Crocs S4 Groep its ET 65.450 49.750 11.40 1.641 14 10.6 1 587 11030 45 1,33 2,150 4.035 10.365 15.53 11,0 1,49 401 9,707 2,830 3.027 2017-05 60319 49.504 148 1.399 5.65 NOI 2,90 4560 512 4,054 102 2. INTE ELET DEEZ 30 Other operating 57 A 3D 5. Opringcomm 60 1 l H 62 m income 3.09 ASS 115 140 558 1,714 14 4573 7.740 922 1,010 2.04 516 1,527 479 1048 B1 A B C D E F G s FED EX Corp. yahoo finance data 1 05/31/19 (year end) 2 3 4 Current stock price 5 Market Cap (value) 6 Beta 7 PE Ratio 8 EPS 9 Annual dividend 10 # of shares outstanding 11 12 13 current price 14 outstanding shares 15 market value 16 17 18 19 154.28 49.061 billion 1.42 74.89 2.06 2.60 318 million $ 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 $ $ 154.28 318,000,000 49,061,040,000 or 49.061 billion $ 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 D Fed Ex Corp. outstanding bonds as of 05/31/19 Face amounts are in millions. Maturity Name Face Amount $(MI) 405.00 Current Price Coupon % Yield to Maturity % Year 2020 Market Values (MI) Weight Weighted YTM % 500.00 5 Senior Unsecured Debt 7 Senior Unsecured Debt 8 Senior Unsecured Debt 9 Senior Unsecured Debt 10 Senior Linsecured Debt 11 Senior Unsecured Debt 12 Senior Unsecured Debt 13 Senior Unsecured Debt 14 Senior Unsecured Debt 15 Senior Unsecured Debt 16 Senior Unsecured Debt 17 Senior Unsecured Debt 18 Senior Unised Debt 19 Senior Unsecured Debt 20 Senior Unsecured Debt 21 Senior Unsecured Debt 22 Senior insecured Debt 23 Senior Unsecured Debt 24 Senior Unsecured Dett 25 Senior Unsecured Debt 26 Senior Uncured Debt Foreign 27 Senior Unnacred Debt Foreign) 28 Senior Unsecured Debt Foreign 29 Senior Unsecured Debt Foreign 30 Other Debt and Captain 31 Totals 32 33 2022 2023 2024 2025 2026 2027 2028 2020 2034 2035 2043 2044 2045 2046 2047 2048 2049 2065 2008 2020 2022 2023 2027 Various 770.00 750.00 74000 81000 490.00 555.00 390.00 506.00 580.00 1.215.00 795.00 820.00 2.955.00 230,00 1.345.00 835.00 325.00 225.00 566.00 715.00 930.00 1.730.00 65.20 19,950.20 or 19.950 billion & BODO 100.0000 97 0000 99.5000 03.5000 92.0000 90.3000 B8.7500 100.7500 97.7000 85.4000 81.0000 83.3000 78.0000 63 2500 790000 73 2000 100.4000 750000 103.8000 99.500 100.0000 89.6000 80,4000 100.0000 2300% 3.400% 2.650% 4.000N 3 200% 3.250% 3,300% 3.400 4 2009 4900% 3.900% 4.000% 5.100% 4.100% 4.650% 4.400% 4.050 4.950% 4500% 7.600 1450% 2.300 1.800 1.900 2.2003 3.3556% 3.4000 34434% 41094% 443024 4585 4.7592% 49520% 411080N 5.1203% 5.2557% 5.4221% 5.60015 5.73595 5.89345 5.9469 6.0110% 4 9240% 6.1342 7.32025 1.58985 2.3000 4.82389 4.8610% 2.1991 Pre-tax cost of Debt 35 36 37 18 39 40 Adjustment for tax rate After tax Cost of Debt 42 44 C D F Excel Case #2 - Federal Express Corp. Format Remarks Answer Schedule Points available Billion 0:50 17 050 use 05/31/19 data provided on Data tabus value in BLLONS use dividend growth for most recent five years as I have segregated on the Data tab use 05/31/19 data provided on Data tab use the most recent year on the Duta tab use 05/31/19 data provided on Data tab os 0.50 Market value of equity Imarket cap Growth rate for equity Market price of stock per share) Annual dividend Beta of stod Risk free rate Market rate of retum 2 DGM cost of equity (stock) CAPM cost of equity (stock) 4 Average cost of equity S Market value of debt 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 1.25 1.25 100 050 bon simple average of two methods use bond schedule for 05/31/19-e value in BILLIONS use average for most recent five calendar years there are two acceptable ways to calculate this that result in two lightly diferent answers use bond schedule for 05/31/10-ute value in BILLIONS use bond schedule for 05/11/19-se value in SILLIONS use value in LIONS 100 3.50 125 16 Average tax rate 7 Pretax cost of debt bonds) El After tax cost of debt bonds) Total market value of wity and debt 20 Weight for et 21 Weight for det 32 Weighted average cost of capital bio percentage, 2 decimal places percentage, 2 decimal places percentage, 2 decimal places currency, 3 decimal places number, & decimal places number, 4 decimal places percentage, 2 decimal places 0.50 125 1.25 3.50 24 Suv your le with this name format Excel Case 2 Finstintillastame section 25 Example: Excel Case 2 Mwinnow 26 21 Total points 21 20.00 30 Submit on Blackboard - do not email directly to Professor Gwinn due date and time is November 22nd, 11:58pm) I will only grade your last mission (2 maimum submissions) 3 te sutrisslons are subject to a point penalty 15 40 41 F1 fi D E G H 2019.05 2018-0 2017-05 2016-05 2015.05 2,319 9,116 553 3,265 8,481 3.969 7,599 514 3.534 7,252 496 3.763 5,719 495 606 525 SSE 1 Pedex Corp (FO) BALANCE SHEET Palyarends in May. USD in million 3 Assets 4 Current assets 5 Cash and cash equivalents 6 Receivables 7 Inventories & Deferred income 9 Prepaid expenses 10 Totalcunent sets 11 Nen-current assets 12 Grous property, plant and equipment 13 Accumulated Depreciation 14 Good 15 target 16 Other long termasse 17 Total non-cometsets 109 13.06 1,070 13,341 546 12.628 707 11.989 10.945 59,511 129,082) 6,884 42,864 (21,989) 3,810 55.121 (26,967) 6,973 480 3,382 38,989 52,330 50,626 (24,6451 7,154 529 2,260 35,924 40,552 47018 (22,734) 6.747 1.000 2015 34,075 46,054 4004 41.317 54,400 1,443 25,128 17.069 964 3.030 356 3,278 1,385 9,018 1,342 2,977 334 4,041 933 1.677 22 2.752 283 4,000 781 7,918 29 2.944 311 3.920 304 1.Doa 2,066 323 303 507 5,957 19 20 Les 22 Current les 22 Short term debt 23 Accounts payable 34 Tues payable 25 Accrued liabile 26 Omer current abilities 27 Total current liabilities 20 Non-current oblities 23 Long-term det 30 Deferred the 31 Accrued abilities 32 Oferred revenues 33 Pen we other benefits Mother long mabilis 35 Total Babies 16 Total 16.617 2.821 1899 99 5,095 1,102 27,633 36,546 15 241 2.367 1.784 121 2.187 1,085 23,283 32,914 14,909 2485 1.494 137 4487 1,049 24,561 32.479 13,838 1,567 1314 155 6222 1,171 24,272 32.280 7,249 1,747 1.120 151 4.89 925 16.119 22.075 LE 32 38 Shockden'egull 39 Commons 40 Additional pidin capital 41 und warnings 1 Tung Mark 41 Arund the comprehensive income 45 Toother equity 12 3.231 24641 19,289) (565) 17,757 54.403 3117 24823 17.978) 15701 19416 52.310 12 30s 20833 173021 (415) 14,073 43532 32 2892 18,371 17-3421 (169) 13,784 46,064 16.900 14.97 14,99 7 48 4 Corp) INCOME STATEMENT 50 dywends in Musson 51 1013-05 2016-05 2015-05 47453 36,505 SB Crocs S4 Groep its ET 65.450 49.750 11.40 1.641 14 10.6 1 587 11030 45 1,33 2,150 4.035 10.365 15.53 11,0 1,49 401 9,707 2,830 3.027 2017-05 60319 49.504 148 1.399 5.65 NOI 2,90 4560 512 4,054 102 2. INTE ELET DEEZ 30 Other operating 57 A 3D 5. Opringcomm 60 1 l H 62 m income 3.09 ASS 115 140 558 1,714 14 4573 7.740 922 1,010 2.04 516 1,527 479 1048 B1 A B C D E F G s FED EX Corp. yahoo finance data 1 05/31/19 (year end) 2 3 4 Current stock price 5 Market Cap (value) 6 Beta 7 PE Ratio 8 EPS 9 Annual dividend 10 # of shares outstanding 11 12 13 current price 14 outstanding shares 15 market value 16 17 18 19 154.28 49.061 billion 1.42 74.89 2.06 2.60 318 million $ 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 $ $ 154.28 318,000,000 49,061,040,000 or 49.061 billion $ 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 D Fed Ex Corp. outstanding bonds as of 05/31/19 Face amounts are in millions. Maturity Name Face Amount $(MI) 405.00 Current Price Coupon % Yield to Maturity % Year 2020 Market Values (MI) Weight Weighted YTM % 500.00 5 Senior Unsecured Debt 7 Senior Unsecured Debt 8 Senior Unsecured Debt 9 Senior Unsecured Debt 10 Senior Linsecured Debt 11 Senior Unsecured Debt 12 Senior Unsecured Debt 13 Senior Unsecured Debt 14 Senior Unsecured Debt 15 Senior Unsecured Debt 16 Senior Unsecured Debt 17 Senior Unsecured Debt 18 Senior Unised Debt 19 Senior Unsecured Debt 20 Senior Unsecured Debt 21 Senior Unsecured Debt 22 Senior insecured Debt 23 Senior Unsecured Debt 24 Senior Unsecured Dett 25 Senior Unsecured Debt 26 Senior Uncured Debt Foreign 27 Senior Unnacred Debt Foreign) 28 Senior Unsecured Debt Foreign 29 Senior Unsecured Debt Foreign 30 Other Debt and Captain 31 Totals 32 33 2022 2023 2024 2025 2026 2027 2028 2020 2034 2035 2043 2044 2045 2046 2047 2048 2049 2065 2008 2020 2022 2023 2027 Various 770.00 750.00 74000 81000 490.00 555.00 390.00 506.00 580.00 1.215.00 795.00 820.00 2.955.00 230,00 1.345.00 835.00 325.00 225.00 566.00 715.00 930.00 1.730.00 65.20 19,950.20 or 19.950 billion & BODO 100.0000 97 0000 99.5000 03.5000 92.0000 90.3000 B8.7500 100.7500 97.7000 85.4000 81.0000 83.3000 78.0000 63 2500 790000 73 2000 100.4000 750000 103.8000 99.500 100.0000 89.6000 80,4000 100.0000 2300% 3.400% 2.650% 4.000N 3 200% 3.250% 3,300% 3.400 4 2009 4900% 3.900% 4.000% 5.100% 4.100% 4.650% 4.400% 4.050 4.950% 4500% 7.600 1450% 2.300 1.800 1.900 2.2003 3.3556% 3.4000 34434% 41094% 443024 4585 4.7592% 49520% 411080N 5.1203% 5.2557% 5.4221% 5.60015 5.73595 5.89345 5.9469 6.0110% 4 9240% 6.1342 7.32025 1.58985 2.3000 4.82389 4.8610% 2.1991 Pre-tax cost of Debt 35 36 37 18 39 40 Adjustment for tax rate After tax Cost of Debt 42 44