Excel Case #2 - United Parcel Service, Inc. (UPS) D1 Total points available 20 1 U 1 The Answer Schedule shows the points per question / answer 5 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 B 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 o If you just provide the correct answers and do not show your work when calculations are necessary, you will not earn full credit 1 On the answer schedule, you can use cell references OR manually input your answers (as long as you did the actual calculations in Excel) 2 For example: show the growth rate for equity on the worksheet that contains the data you use for the calculation 4 Save your file with this name format: Excel Case2 firstinitiallastname sectionnumber 5 Example: Excel Case 2 jdoe 01w 7 Submit on Blackboard - do not email directly to Professor Gwinn 8 (due date and time is April 19th, 11:30pm) 9 I will only grade your last submission (3 maximum submissions) o Late submissions are subject to a 4 point penalty Nm 0 0 0 1 Excel Case #2 - United Parcel Service 3 Answer Schedule Format Points available Remarks 1 billion 0.50 1.75 0.50 0.50 0.50 use 12/31/19 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 12/31/19 data provided on Data tab use the most recent year on the Data tab use 12/31/19 data provided on Data tab 5 Market value of equity (market cap) 6 Growth rate for equity 7 Market price of stock (per share) 8 Annual dividend 9 Beta of stock O Risk free rate 11 Market rate of return 12 DGM cost of equity (stock) 13 CAPM cost of equity (stock) 14 Average cost of equity 25 Market value of debt 1.40% 9.80% 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.25 1.25 1.00 0.50 billion simple average of two methods use bond schedule for 12/31/19 - 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 12/31/19 - use value in BILLIONS use bond schedule for 12//31/19 use value in BILLIONS 1.00 3.50 1.25 16 Average tax rate 27 Pre tax cost of debt (bonds) 18 After tax cost of debt (bonds) 19 Total market value of equity and debt 20 Weight for equity 21 Weight for debt 22 Weighted average cost of capital billion 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 0.50 1.25 1.25 3.50 25 Total points 20.00 28 Submit on Blackboard - do not email directly to Professor Gwinn 29 (due date and time is April 19th, 11:30pm) 30 I will only grade your last submission (3 maximum submissions) 31 Late submissions are subject to a 10 point penalty 2019-12 2018-12 2017-12 2016-12 2015-12 4,069 5,035 9,898 421 10,346 5,741 9,934 394 1,034 17,103 404 729 United Parcel Service (UPS) BALANCE SHEET Fiscal year ends in December. USD in millions. Assets Current assets Cash and cash equivalents Receivables Inventories Other current assets 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 4,567 7.695 342 1,245 13,849 4,726 7,134 308 1,040 13,208 956 16,210 15,548 58,804 (28,322) 3,813 2,167 4,292 40,754 57,857 54,488 (27,912) 2.811 2,076 1,344 33,807 50,017 48,726 (26,60B) 3,872 1.964 1,901 29,855 45,403 43,674 (24,874) 3,757 1,758 2,213 26,528 40,377 41,918 (23,566) 3,419 1,549 1,783 25,103 38,311 2.665 3,960 2,972 3,420 538 5,555 2,552 3,348 15,413 140 5,188 3,047 3,047 14,087 3,872 2.521 2,304 12,708 3,635 46 3,042 2,317 2,690 11,730 2,587 2.253 2,838 10,696 Liabilities Current liabilities Short-term debt Capital leases Accounts payable Accrued liabilities Other current liabilities Total current liabilities Non-current liabilities Long-term debt Capital leases Deferred taxes liabilities Pensions and other benefits Minority interest Other long-term liabilities Total non-current liabilities Total liabilities 19,829 449 21,818 2,391 1,632 10,601 10,887 429 115 10,638 19,537 394 1,619 8,347 16 2,995 32,908 46,995 757 7,061 30 3,569 31,695 44,403 11,993 401 112 12,694 24 3,018 28,242 39,972 2,719 39,177 54,590 3,055 25,145 35,841 99 Stockholders' equity Common stock Additional Paid in Capital Retained earnings Treasury stock Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity 9 150 9,105 (26) (5,971) 3,267 57,857 8,006 (32) (4,961) 3,022 50,017 5,858 (37) (4,830) 1,000 45,403 4,879 (45) (4,438) 405 40,377 6,001 (51) (3,489) 2,470 38,311 United Parcel Service (UPS) INCOME STATEMENT Fiscal year ends in December. USD in millions. 2019-12 2018-12 2017-12 2016-12 2015-12 74,094 59,510 14,584 5,919 8,665 Revenue (Sales) Cost of revenue (Cost of goods sold) Gross profit (Gross margin) Operating expenses EBITDA Deprec and amort Operating income (EBIT) Interest Expense Taxable income Income tax expense Net income 2,360 71,861 59,372 12,489 3,658 8,831 2,207 6,624 605 6,019 1,228 4,791 65,872 53,304 12,568 2,685 9,883 2,282 7,601 453 7,148 2,238 4,910 60,906 50,816 10,090 2,349 7,741 2,224 5,517 381 5,136 1,705 3,431 58,363 46,059 12,304 2,537 9,767 2,084 7,683 341 7,342 2,498 4,844 6,305 653 5,652 1,212 4,440 A C D United Parcel Service. yahoo finance data 12/31/19 (year end) Annual Annual Growth Dividends Rates Average Growth Rate 1 6 4 Current stock price 5 Market Cap (Value) Beta 7 PE Ratio 8 EPS 9 Annual dividend 10 #of shares outstanding Bets Quarterly Dividends 0.96 0.96 0.96 0.96 0.91 0.91 0.91 0.91 0.83 0.83 $ 117.06 $ 100.480 billion 0.95 22.77 5.14 $ 3.84 858.36 million Date 11/07/19 08/08/19 05/09/19 02/15/19 11/16/18 08/17/18 05/18/18 02/16/18 11/10/17 08/10/17 05/11/17 02/16/17 11/09/16 08/12/16 05/12/16 02/18/16 11/12/15 08/13/15 05/14/15 02/19/15 11 0.83 $ 13 current price 14 outstanding shares 15 market value 16 117.06 858,360,000 100,479,621,600 $ or 100.480 billion 0.83 0.78 0.78 0.78 0.78 0.73 0.73 0.73 0.73 UPS outstanding bonds as of 12/31/19 Face amounts are in millions. Name Maturity Year Current Price Coupon % Yield to Maturity % Weighted YTM Weight Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Debentures Debentures Pound Sterling Notes Pound Sterling Notes Euro Senior Notes Canadian Senior Notes Floating Rate Notes Other Debt and Capital Leases Totals 2021 2021 2022 2022 2023 2024 2024 2026 2027 2029 2029 2038 2040 2042 2046 2047 2049 2049 2020 2030 2031 2050 2030 2024 2020 various Face Amount $(Mil) 1,500.00 700.00 1.000.00 600.00 1,000.00 500.00 400.00 500.00 1,000.00 750.00 400.00 1,500.00 500.00 375.00 500.00 1.150.00 750.00 700.00 424.00 276.00 87.00 597.00 3,246.00 573.00 2.291.00 4,069.00 25,388.00 or 25.388 billion 101.60 99.86 100.30 99.67 99.50 99.40 99.50 99.60 99.20 99.33 99.25 98.87 98.00 98.13 98.20 98.78 98.93 98.29 100.47 101.81 98.85 94.81 99.48 99.65 99.30 99.78 3.125% 2.050% 2.450% 2.350% 2.500% 2.800% 2.200% 2.400% 3.050% 3.400% 2.500% 6.200% 4.875% 3.625% 3.400% 3.750% 4.250% 3.400% 8.375% 8.375% 5.500% 5.125% 2.565% 2.125% 2.350% 4.250% 2.09 16% 2.1473% 2.3012% 2.4884% 2.6468% 2.9357% 2.3080% 2.4670% 3.1739% 3.4860% 2.5856% 6.3028% 5.0325% 3.7468% 3.5048% 3.8213% 4.3143% 3.4927% 2.6112% 8.1154% 5.6372% 5.4764% 2.6201% 2.2096% 3.0645% 4.8897% Market Value $ (Mil) 1,524.00 699.00 1,003.00 598.00 995.00 497.00 398.00 498.00 992.00 745.00 397.00 1,483.00 490.00 368.00 491.00 1,136.00 742.00 688.00 426.00 281.00 86.00 566.00 3,229.00 571.00 2.275.00 4,060.00 25238.00 or 25.238 billion Pre-tax Cost of Debt Aritment for tax rate 12/31/19 01/31/29 3.40% 99.3300 1,000.00 5 divide by 10 3.4864% maturity 07/31/21 Settlement date 06/30/21 Maturity date 01/31/22 Annual coupon rate 06/30/22 Coupons per year 07/31/23 Bond price (% of par) 09/30/24 Face value 11/30/24 06/30/26 03/31/27 01/31/29 12/31/29 Yield to maturity 11/30/38 06/30/40 12/30/42 06/30/46 09/30/47 06/30/49 11/30/49 01/31/20 06/30/30 06/30/31 07/31/50 12/31/30 04/30/24 12/30/20 04/30/20 1,524.0000 699.0000 1,003.0000 598.0000 995.0000 497.0000 398.0000 498.0000 992.0000 745.0000 397.0000 1,483.0000 490.0000 368.0000 491.0000 1,136.0000 742.0000 688.0000 426.0000 281.0000 86.0000 566.0000 3,229.0000 571.0000 2,275.0000 4,060.0000 25238.00 101.60 99.86 100.30 99.67 99.50 99.40 99.50 99.60 99.20 99.33 99.25 98.87 98.00 98.13 98.20 98.78 98.93 98.29 100.47 101.81 98.85 94.81 99.48 99.65 99.30 99.78 two different tax rates are acceptable Excel Case #2 - United Parcel Service, Inc. (UPS) D1 Total points available 20 1 U 1 The Answer Schedule shows the points per question / answer 5 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 B 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 o If you just provide the correct answers and do not show your work when calculations are necessary, you will not earn full credit 1 On the answer schedule, you can use cell references OR manually input your answers (as long as you did the actual calculations in Excel) 2 For example: show the growth rate for equity on the worksheet that contains the data you use for the calculation 4 Save your file with this name format: Excel Case2 firstinitiallastname sectionnumber 5 Example: Excel Case 2 jdoe 01w 7 Submit on Blackboard - do not email directly to Professor Gwinn 8 (due date and time is April 19th, 11:30pm) 9 I will only grade your last submission (3 maximum submissions) o Late submissions are subject to a 4 point penalty Nm 0 0 0 1 Excel Case #2 - United Parcel Service 3 Answer Schedule Format Points available Remarks 1 billion 0.50 1.75 0.50 0.50 0.50 use 12/31/19 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 12/31/19 data provided on Data tab use the most recent year on the Data tab use 12/31/19 data provided on Data tab 5 Market value of equity (market cap) 6 Growth rate for equity 7 Market price of stock (per share) 8 Annual dividend 9 Beta of stock O Risk free rate 11 Market rate of return 12 DGM cost of equity (stock) 13 CAPM cost of equity (stock) 14 Average cost of equity 25 Market value of debt 1.40% 9.80% 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.25 1.25 1.00 0.50 billion simple average of two methods use bond schedule for 12/31/19 - 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 12/31/19 - use value in BILLIONS use bond schedule for 12//31/19 use value in BILLIONS 1.00 3.50 1.25 16 Average tax rate 27 Pre tax cost of debt (bonds) 18 After tax cost of debt (bonds) 19 Total market value of equity and debt 20 Weight for equity 21 Weight for debt 22 Weighted average cost of capital billion 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 0.50 1.25 1.25 3.50 25 Total points 20.00 28 Submit on Blackboard - do not email directly to Professor Gwinn 29 (due date and time is April 19th, 11:30pm) 30 I will only grade your last submission (3 maximum submissions) 31 Late submissions are subject to a 10 point penalty 2019-12 2018-12 2017-12 2016-12 2015-12 4,069 5,035 9,898 421 10,346 5,741 9,934 394 1,034 17,103 404 729 United Parcel Service (UPS) BALANCE SHEET Fiscal year ends in December. USD in millions. Assets Current assets Cash and cash equivalents Receivables Inventories Other current assets 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 4,567 7.695 342 1,245 13,849 4,726 7,134 308 1,040 13,208 956 16,210 15,548 58,804 (28,322) 3,813 2,167 4,292 40,754 57,857 54,488 (27,912) 2.811 2,076 1,344 33,807 50,017 48,726 (26,60B) 3,872 1.964 1,901 29,855 45,403 43,674 (24,874) 3,757 1,758 2,213 26,528 40,377 41,918 (23,566) 3,419 1,549 1,783 25,103 38,311 2.665 3,960 2,972 3,420 538 5,555 2,552 3,348 15,413 140 5,188 3,047 3,047 14,087 3,872 2.521 2,304 12,708 3,635 46 3,042 2,317 2,690 11,730 2,587 2.253 2,838 10,696 Liabilities Current liabilities Short-term debt Capital leases Accounts payable Accrued liabilities Other current liabilities Total current liabilities Non-current liabilities Long-term debt Capital leases Deferred taxes liabilities Pensions and other benefits Minority interest Other long-term liabilities Total non-current liabilities Total liabilities 19,829 449 21,818 2,391 1,632 10,601 10,887 429 115 10,638 19,537 394 1,619 8,347 16 2,995 32,908 46,995 757 7,061 30 3,569 31,695 44,403 11,993 401 112 12,694 24 3,018 28,242 39,972 2,719 39,177 54,590 3,055 25,145 35,841 99 Stockholders' equity Common stock Additional Paid in Capital Retained earnings Treasury stock Accumulated other comprehensive income Total stockholders' equity Total liabilities and stockholders' equity 9 150 9,105 (26) (5,971) 3,267 57,857 8,006 (32) (4,961) 3,022 50,017 5,858 (37) (4,830) 1,000 45,403 4,879 (45) (4,438) 405 40,377 6,001 (51) (3,489) 2,470 38,311 United Parcel Service (UPS) INCOME STATEMENT Fiscal year ends in December. USD in millions. 2019-12 2018-12 2017-12 2016-12 2015-12 74,094 59,510 14,584 5,919 8,665 Revenue (Sales) Cost of revenue (Cost of goods sold) Gross profit (Gross margin) Operating expenses EBITDA Deprec and amort Operating income (EBIT) Interest Expense Taxable income Income tax expense Net income 2,360 71,861 59,372 12,489 3,658 8,831 2,207 6,624 605 6,019 1,228 4,791 65,872 53,304 12,568 2,685 9,883 2,282 7,601 453 7,148 2,238 4,910 60,906 50,816 10,090 2,349 7,741 2,224 5,517 381 5,136 1,705 3,431 58,363 46,059 12,304 2,537 9,767 2,084 7,683 341 7,342 2,498 4,844 6,305 653 5,652 1,212 4,440 A C D United Parcel Service. yahoo finance data 12/31/19 (year end) Annual Annual Growth Dividends Rates Average Growth Rate 1 6 4 Current stock price 5 Market Cap (Value) Beta 7 PE Ratio 8 EPS 9 Annual dividend 10 #of shares outstanding Bets Quarterly Dividends 0.96 0.96 0.96 0.96 0.91 0.91 0.91 0.91 0.83 0.83 $ 117.06 $ 100.480 billion 0.95 22.77 5.14 $ 3.84 858.36 million Date 11/07/19 08/08/19 05/09/19 02/15/19 11/16/18 08/17/18 05/18/18 02/16/18 11/10/17 08/10/17 05/11/17 02/16/17 11/09/16 08/12/16 05/12/16 02/18/16 11/12/15 08/13/15 05/14/15 02/19/15 11 0.83 $ 13 current price 14 outstanding shares 15 market value 16 117.06 858,360,000 100,479,621,600 $ or 100.480 billion 0.83 0.78 0.78 0.78 0.78 0.73 0.73 0.73 0.73 UPS outstanding bonds as of 12/31/19 Face amounts are in millions. Name Maturity Year Current Price Coupon % Yield to Maturity % Weighted YTM Weight Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Senior Notes Debentures Debentures Pound Sterling Notes Pound Sterling Notes Euro Senior Notes Canadian Senior Notes Floating Rate Notes Other Debt and Capital Leases Totals 2021 2021 2022 2022 2023 2024 2024 2026 2027 2029 2029 2038 2040 2042 2046 2047 2049 2049 2020 2030 2031 2050 2030 2024 2020 various Face Amount $(Mil) 1,500.00 700.00 1.000.00 600.00 1,000.00 500.00 400.00 500.00 1,000.00 750.00 400.00 1,500.00 500.00 375.00 500.00 1.150.00 750.00 700.00 424.00 276.00 87.00 597.00 3,246.00 573.00 2.291.00 4,069.00 25,388.00 or 25.388 billion 101.60 99.86 100.30 99.67 99.50 99.40 99.50 99.60 99.20 99.33 99.25 98.87 98.00 98.13 98.20 98.78 98.93 98.29 100.47 101.81 98.85 94.81 99.48 99.65 99.30 99.78 3.125% 2.050% 2.450% 2.350% 2.500% 2.800% 2.200% 2.400% 3.050% 3.400% 2.500% 6.200% 4.875% 3.625% 3.400% 3.750% 4.250% 3.400% 8.375% 8.375% 5.500% 5.125% 2.565% 2.125% 2.350% 4.250% 2.09 16% 2.1473% 2.3012% 2.4884% 2.6468% 2.9357% 2.3080% 2.4670% 3.1739% 3.4860% 2.5856% 6.3028% 5.0325% 3.7468% 3.5048% 3.8213% 4.3143% 3.4927% 2.6112% 8.1154% 5.6372% 5.4764% 2.6201% 2.2096% 3.0645% 4.8897% Market Value $ (Mil) 1,524.00 699.00 1,003.00 598.00 995.00 497.00 398.00 498.00 992.00 745.00 397.00 1,483.00 490.00 368.00 491.00 1,136.00 742.00 688.00 426.00 281.00 86.00 566.00 3,229.00 571.00 2.275.00 4,060.00 25238.00 or 25.238 billion Pre-tax Cost of Debt Aritment for tax rate 12/31/19 01/31/29 3.40% 99.3300 1,000.00 5 divide by 10 3.4864% maturity 07/31/21 Settlement date 06/30/21 Maturity date 01/31/22 Annual coupon rate 06/30/22 Coupons per year 07/31/23 Bond price (% of par) 09/30/24 Face value 11/30/24 06/30/26 03/31/27 01/31/29 12/31/29 Yield to maturity 11/30/38 06/30/40 12/30/42 06/30/46 09/30/47 06/30/49 11/30/49 01/31/20 06/30/30 06/30/31 07/31/50 12/31/30 04/30/24 12/30/20 04/30/20 1,524.0000 699.0000 1,003.0000 598.0000 995.0000 497.0000 398.0000 498.0000 992.0000 745.0000 397.0000 1,483.0000 490.0000 368.0000 491.0000 1,136.0000 742.0000 688.0000 426.0000 281.0000 86.0000 566.0000 3,229.0000 571.0000 2,275.0000 4,060.0000 25238.00 101.60 99.86 100.30 99.67 99.50 99.40 99.50 99.60 99.20 99.33 99.25 98.87 98.00 98.13 98.20 98.78 98.93 98.29 100.47 101.81 98.85 94.81 99.48 99.65 99.30 99.78 two different tax rates are acceptable