Excel Case 2

gurzef2h
timer Asked: Dec 3rd, 2018

Question Description

In Excel Case #2, we are going to calculate the weighted average cost of capital for the Federal Express Corporation. Here is a profile of the company:

FedEx Corporation provides transportation, e-commerce, and business services worldwide. The company's FedEx Express segment offers shipping services for delivery of packages and freight. Its FedEx Ground segment provides business and residential money-back guaranteed ground package delivery services; and consolidates and delivers low-weight and less time-sensitive business-to-consumer packages. The company's FedEx Freight segment offers less-than-truckload and freight delivery services. As of May 31, 2018, it operated approximately 27,000 vehicles and 370 service centers. The company's FedEx Services segment provides sales, marketing, information technology, communications, customer, technical support, billing and collection, 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 for copying and digital printing, professional finishing, document creation, direct mail, signs and graphics, computer rentals, Wi-Fi, and corporate print solutions; and packing services, supplies, and boxes, as well as FedEx Express and FedEx Ground shipping services. The company's Corporate, Other and Eliminations segment offers international trade services in customs brokerage, and ocean and air freight forwarding services; cross-border enablement and technology solutions, and e-commerce transportation solutions; integrated supply chain management solutions; time-critical shipment services; critical inventory and service parts logistics, 3-D printing, and technology repair. This segment also provides international trade advisory services, including assistance with the customs-trade partnership against terrorism program; and publishes customs duty and tax information. FedEx Corporation was founded in 1971 and is headquartered in Memphis, Tennessee.

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
User generated content is uploaded by users for the purposes of learning and should be used following Studypool's honor code & terms of service.

This question has not been answered.

Create a free account to get help with this and any other question!

Similar Content

Related Tags

Brown University





1271 Tutors

California Institute of Technology




2131 Tutors

Carnegie Mellon University




982 Tutors

Columbia University





1256 Tutors

Dartmouth University





2113 Tutors

Emory University





2279 Tutors

Harvard University





599 Tutors

Massachusetts Institute of Technology



2319 Tutors

New York University





1645 Tutors

Notre Dam University





1911 Tutors

Oklahoma University





2122 Tutors

Pennsylvania State University





932 Tutors

Princeton University





1211 Tutors

Stanford University





983 Tutors

University of California





1282 Tutors

Oxford University





123 Tutors

Yale University





2325 Tutors