Unformatted Attachment Preview
Excel Case #2 - Federal Express Corp. (FDX)
Total points available
50
The Aswer Schedule shows the points per question / answer
Points available for each answer are all or nothing - must be correct answer AND in correct format
Rounding of your answers should take place when you get to the final step, do not round prior to that
Please refer to the other worksheets in this Excel file to get the data you need
You need to show your work somewhere in this Excel file to get credit when calculations are involved
If you just provide the correct answers and do not show your work when calculations are necessary, you will not earn full credit
On the answer schedule, you can use cell references OR manually input your answers (as long as you did the actual calculations in Excel)
For example: show the growth rate for equity on the worksheet that contains the data you use for the calculation
Save your file with this name format: FDX Excel Case2 firstinitiallastname
Example: FDX Excel Case 2 mgwinn
Submit on Blackboard - do not email directly to Professor Gwinn
(due date and time is November 18th, 11:30pm)
I will only grade your last submission (3 maximum submissions)
Late submissions are subject to a 10 point penalty
1
Excel Case #2 - Federal Express Corp.
Answer Schedule
Market value of equity (market cap)
Growth rate for equity
Market price of stock (per share)
Annual dividend
Beta of stock
Risk free rate
Market rate of return
DGM cost of equity (stock)
CAPM cost of equity (stock)
Average cost of equity
Market value of debt
Average tax rate
Pre tax cost of debt (bonds)
After tax cost of debt (bonds)
Total market value of equity and debt
Weight for equity
Weight for debt
Weighted average cost of capital
Format
billion
2.60%
10.30%
billion
billion
Points available
currency, 2 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, 2 decimal places
1
3
1
1
1
percentage, 2 decimal places
percentage, 2 decimal places
percentage, 2 decimal places
currency, 2 decimal places
number, 4 decimal places
number, 4 decimal places
percentage, 2 decimal places
3
8
3
1
3
3
8
3
3
2
1
Save your file with this name format: FDX Excel Case2 firstinitiallastname
Example: FDX Excel Case 2 mgwinn
5
Total points
50
Remarks
use 05/31/18 data provided on Data tab - use value in BILLIONS
use dividend growth for most recent five years as I have segregated on the Data tab
use 05/31/18 data provided on Data tab
use the most recent year on the Data tab
use 05/31/18 data provided on Data tab
simple average of two methods
use bond schedule for 05/31/18 - use value in BILLIONS
use average for most recent five calendar years = there are two acceptable ways to calculate this
that result in two slightly different answers
use bond schedule for 05/31/18
use bond schedule for 05/31/18
use value in BILLIONS
Submit on Blackboard - do not email directly to Professor Gwinn
(due date and time is November 18th, 11:30pm)
I will only grade your last submission (3 maximum submissions)
Late submissions are subject to a 10 point penalty
2
Fed Ex Corp (FDX) BALANCE SHEET
Fiscal year ends in May. USD in millions.
Assets
Current assets
Cash and cash equivalents
Receivables
Inventories
Deferred income taxes
Prepaid expenses
Total current assets
Non-current assets
Gross property, plant and equipment
Accumulated Depreciation
Goodwill
Intangible assets
Other long-term assets
Total non-current assets
Total assets
2018-05
2017-05
2016-05
2015-05
2014-05
3,265
8,481
525
3,969
7,599
514
3,534
7,252
496
1,070
13,341
546
12,628
707
11,989
3,763
5,719
498
606
355
10,941
2,908
5,460
463
522
330
9,683
55,121
(26,967)
6,973
480
3,382
38,989
52,330
50,626
(24,645)
7,154
529
2,260
35,924
48,552
47,018
(22,734)
6,747
1,008
2,036
34,075
46,064
42,864
(21,989)
3,810
40,691
(21,141)
2,790
1,443
26,128
37,069
1,047
23,387
33,070
Liabilities
Current liabilities
Short-term debt
Accounts payable
Taxes payable
Accrued liabilities
Other current liabilities
Total current liabilities
Non-current liabilities
Long-term debt
Deferred taxes liabilities
Accrued liabilities
Deferred revenues
Pensions and other benefits
Other long-term liabilities
Total non-current liabilities
Total liabilities
1,342
2,977
334
4,041
933
9,627
22
2,752
283
4,080
781
7,918
29
2,944
311
3,920
804
8,008
19
2,066
328
3,037
507
5,957
1
1,971
339
2,567
434
5,312
15,243
2,867
1,784
121
2,187
1,085
23,287
32,914
14,909
2,485
1,494
137
4,487
1,049
24,561
32,479
13,838
1,567
1,314
155
6,227
1,171
24,272
32,280
7,249
1,747
1,120
181
4,893
929
16,119
22,076
4,736
2,114
1,038
206
3,484
903
12,481
17,793
Stockholders' equity
Common stock
Additional paid-in capital
Retained earnings
Treasury stock
Accumulated other comprehensive income
Total stockholders' equity
Total liabilities and stockholders' equity
32
3,117
24,823
(7,978)
(578)
19,416
52,330
32
3,005
20,833
(7,382)
(415)
16,073
48,552
32
2,892
18,371
(7,342)
(169)
13,784
46,064
32
2,786
16,900
(4,897)
172
14,993
37,069
32
2,643
20,429
(4,133)
(3,694)
15,277
33,070
Fed Ex Corp (FDX) INCOME STATEMENT
Fiscal year ends in May. USD in millions.
2018-05
2017-05
2016-05
2015-05
2014-05
65,450
49,360
16,090
10
6,674
9,406
2,756
6,650
897
5,753
1,175
4,578
60,319
46,554
13,765
24
5,655
8,086
3,049
5,037
458
4,579
1,586
2,993
50,365
38,539
11,826
1,498
4,621
5,707
2,630
3,077
337
2,740
922
1,818
47,453
36,805
10,648
2,190
4,085
4,373
2,330
2,043
516
1,527
479
1,048
45,567
36,294
9,273
Revenue (Sales)
Cost of revenue (Cost of goods sold)
Gross profit (Gross margin)
Sales, general and administrative
Other operating expenses
EBITDA
Deprec and amort
Operating income (EBIT)
Interest Expense
Taxable income
Income tax expense
Net income
3,337
5,936
2,590
3,346
157
3,189
1,096
2,093
3
FED Ex Corp.
yahoo finance data
05/31/18 (year end)
Current stock price
Market Cap (Value)
Beta
PE Ratio
EPS
Annual dividend
# of shares outstanding
current price
outstanding shares
market value
$
$
$
$
$
$
$
249.12
66.520 billion
1.51
14.59
17.08
2.30
267 million
249.12
267,000,000
66,515,040,000
or
66.520 billion
Date
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
06/17/14
03/07/14
12/10/13
09/06/13
4
Quarterly
Dividends
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
0.3800
0.3200
0.3200
0.3200
Fed Ex Corp. outstanding bonds as of 05/31/18
Face amounts are in millions.
Name
Maturity Year
Face Amount $(Mil)
Current Price
Senior Unsecured Debt
Senior Unsecured Debt
2019
2020
750.00
400.00
Senior Unsecured Debt
Senior Unsecured Debt
Senior Unsecured Debt
Senior Unsecured Debt
Senior Unsecured Debt
2023
2024
2025
2026
2027
745.00
745.00
695.00
745.00
445.00
Senior Unsecured Debt
2028
495.00
Senior Unsecured Debt
2034
495.00
90.3000
88.7500
97.7000
Senior Unsecured Debt
2035
495.00
Senior Unsecured Debt
2043
985.00
Senior Unsecured Debt
Senior Unsecured Debt
2044
2045
740.00
640.00
Senior Unsecured Debt
Senior Unsecured Debt
2046
2047
2,460.00
735.00
Senior Unsecured Debt
2048
985.00
Senior Unsecured Debt
Senior Unsecured Debt
2065
2098
245.00
235.00
Senior Unsecured Debt (Foreign)
Senior Unsecured Debt (Foreign)
2019
2020
585.00
580.00
Senior Unsecured Debt (Foreign)
2023
870.00
Senior Unsecured Debt (Foreign)
Other Debt and Capital Leases
2027
various
1,440.00
75.00
Totals
103.5000
98.8000
97.0000
99.5000
93.5000
92.0000
Coupon % Yield to Maturity %
8.000%
2.300%
2.650%
4.000%
3.200%
3.250%
3.300%
5.2390%
2.8744%
3.2955%
4.0932%
4.2728%
4.4383%
Market Value $ (Mil)
Weight
Weighted YTM %
$769.68
$395.60
0.05
0.03
4.98%
2.73%
$723.16
$741.37
$650.70
$686.46
$402.55
0.05
0.05
0.04
0.05
0.03
3.13%
3.89%
4.06%
4.22%
4.39%
$440.19
4.6193%
4.8216%
5.1111%
0.03
4.58%
4.900%
$483.77
0.03
4.85%
85.4000
3.900%
5.1997%
$423.54
0.03
4.94%
81.0000
93.3000
4.000%
5.3900%
5.5908%
$799.35
0.05
5.12%
$690.83
$500.24
0.05
0.03
5.31%
5.42%
$2,050.89
$581.70
0.14
0.04
5.58%
5.62%
$722.72
0.05
5.68%
$183.94
$243.95
0.01
0.02
5.82%
6.95%
$585.00
$572.22
0.04
0.04
1.90%
1.12%
$781.54
0.05
$1,162.28
$75.00
0.08
0.01
4.03%
2.61%
14,666.67
or
14.667
billion
1.00
100.00%
78.0000
83.2500
79.0000
73.2000
75.0000
103.8000
100.0000
98.6000
89.6000
80.4000
100.0000
3.400%
5.100%
4.100%
4.650%
4.400%
4.050%
4.500%
7.600%
2.000%
0.500%
1.000%
1.625%
2.750%
5.7045%
5.8746%
5.9222%
5.9804%
6.1262%
7.3202%
1.9995%
1.1822%
3.2351%
4.2482%
2.7486%
1.053
Pre-tax Cost of Debt
Adjustment for tax rate
After tax Cost of Debt
5
3.07%
coupon
meturity yield
face value
Year(No of periods)
1
2
5
6
7
8
9
10
16
17
25
26
27
28
29
30
47
80
1
2
5
9
coupon rate maturity Yield
8.000%
5.2390%
2.300%
2.8744%
2.650%
3.2955%
4.000%
4.0932%
3.200%
4.2728%
3.250%
4.4383%
3.300%
4.6193%
3.400%
4.8216%
4.900%
5.1111%
3.900%
5.1997%
4.000%
5.3900%
5.100%
5.5908%
4.100%
5.7045%
4.650%
5.8746%
4.400%
5.9222%
4.050%
5.9804%
4.500%
6.1262%
7.600%
7.3202%
2.000%
1.9995%
0.500%
1.1822%
1.000%
3.2351%
1.625%
4.2482%
2.750%
2.7486%
105.2874%
face value coupon payments Maturity value
750.00
$60.0000
$769.68
400.00
$9.2000
$395.60
745.00
$19.7425
$723.16
745.00
$29.8000
$741.37
695.00
$22.2400
$650.70
745.00
$24.2125
$686.46
445.00
$14.6850
$402.55
495.00
$16.8300
$440.19
495.00
$24.2550
$483.77
495.00
$19.3050
$423.54
985.00
$39.4000
$799.35
740.00
$37.7400
$690.83
640.00
$26.2400
$500.24
2,460.00
$114.3900
$2,050.89
735.00
$32.3400
$581.70
985.00
$39.8925
$722.72
245.00
$11.0250
$183.94
235.00
$17.8600
$243.95
585.00
$11.7000
$585.00
580.00
$2.9000
$572.22
870.00
$8.7000
$781.54
1,440.00
$23.4000
$1,162.28
75.00
$2.0625
$75.00
$14,666.67
6
weight
5.25%
2.70%
4.93%
5.05%
4.44%
4.68%
2.74%
3.00%
3.30%
2.89%
5.45%
4.71%
3.41%
13.98%
3.97%
4.93%
1.25%
1.66%
3.99%
3.90%
5.33%
7.92%
0.51%
100.00%
7