FIN-320 Module 4 Excel Assignment PDF

Preview

Summary

Module 4 Excel Assignment for FIN-320, Principles of Finance....

Description

Delta Air Lines Inc. (NYS: DAL) Exchange rate used is that of the Year End reported date

As Reported Annual Balance Sheet

Ratios Report Date 12/31/2019 Currency USD Audit Status Not Available Consolidated Scale

Cash Short-term investments Restricted cash & short-term investments Accounts receivable, net Aircraft fuel, spare parts & supplies, net Prepaid expenses & other current assets Total current assets

Yes Thousands 280000 3546000 158000 1750000 1851000 621000 8206000

Flight equipment, at cost Ground property & equipment Equipment purchase deposits Total property & equipment, at cost Less accumulated depreciation & amortization Total property & equipment, net Operating lease right-of-use assets Operating lease right-of-use assets Goodwill

42537000 9443000 1674000 53654000 18659000 34995000 8737000 4091000

Intangibles, gross Less: accumulated amortization Intangibles, net of accumulated amortization Deferred tax asset Other assets Total other assets Total assets Current maturities of long-term debt & capital leases

2084000 645000 1237000 8057000 59995000 2861000

Accounts payable Accrued salaries & wages Air traffic liability Loyalty program liability Operating lease liabilities Operating lease liabilities Other accrued liabilities Total current liabilities

2062000 1541000 4808000 3193000 1708000 2138000 18311000

Secured credit facility Secured aircraft enhanced equipment trust certificates Secured equipment loans & other notes payable Secured special facility revenue bonds Other secured obligations Secured debt Senior notes Unsecured debt Total long-term debt

5192000 11933000 4727000 754000 22606000 1250000 1250000 23856000

Less: total unamortized debt discount Less current maturities Long-term debt, net of current maturities Long-term debt & finance leases, net of current maturities Long-term debt & capital leases, net of current maturities Pension & postretirement benefits Loyalty program liability Operating lease liabilities

-211000 2749000 20896000 21454000 6052000 5422000 7421000

Operating lease liabilities Other liabilities Total non-current liabilities Common stock Additional paid-in capital Pension & retiree medical & other postretirement benefits Unrealized gain (loss) on investments Income tax benefit (expense)

1453000 41802000 4000 3945000 -5238000 -2000 -1091000

Accumulated other comprehensive income (loss) Retained earnings (accumulated deficit) Total stockholders' equity (deficit)

-6331000 2264000 -118000

Cited Reference: Mergent Online. 20 Sep 2020. Delta Air Lines Inc (NYS: DAL). Retrieved September 20, 2020 from: https://www-mergentonlinecom.ezproxy.snhu.edu/companyfinancials.php?pagetype=asreported&compnumber=28 8&period=Annuals&dataarea=BS&range=3¤cy=AsRep&scale=AsRep&Submit=R efresh.

2019 Current Ratio Debt / Equity Ratio Free Cash Flow (000) Earnings per Share Price / earnings ratio Return on Equity Net profit margin Number of Shares Outstanding Closing Price Last Business Day in December

0.45 -155.18 -$453,000.00 $ 3.80 $ 15.38 -1428.81% 3.68% Stock Data 59,995,000 $ 58.48 $

Delta Air Lines Inc. (NYS: DAL) Exchange rate used is that of the Year End reported date

As Reported Annual Balance Sheet Report Date

12/31/2018

Currency USD Audit Status Not Available Consolidated Scale Cash Short-term investments Restricted cash & short-term investments Accounts receivable, net Aircraft fuel, spare parts & supplies, net Prepaid expenses & other current assets Total current assets Flight equipment, at cost Ground property & equipment Equipment purchase deposits Total property & equipment, at cost Less accumulated depreciation & amortization Total property & equipment, net Operating lease right-of-use assets Operating lease right-of-use assets Goodwill Intangibles, gross Less: accumulated amortization Intangibles, net of accumulated amortization Deferred tax asset Other assets Total other assets Total assets Current maturities of long-term debt & capital leases Accounts payable Accrued salaries & wages Air traffic liability Loyalty program liability Operating lease liabilities Operating lease liabilities Other accrued liabilities Total current liabilities Secured credit facility Secured aircraft enhanced equipment trust certificates Secured equipment loans & other notes payable Secured special facility revenue bonds Other secured obligations Secured debt Senior notes Unsecured debt Total long-term debt Less: total unamortized debt discount Less current maturities Long-term debt, net of current maturities Long-term debt & finance leases, net of current maturities Long-term debt & capital leases, net of current maturities Pension & postretirement benefits Loyalty program liability Operating lease liabilities Operating lease liabilities Other liabilities Total non-current liabilities

Yes Thousands 275000 4485000 154000 1706000 1522000 495000 8637000 41499000 8764000 1278000 51541000 17443000 34098000 9151000 4091000 2800000 663000 2137000 1145000 1321000 8694000 60580000 3294000 1773000 1427000 4339000 3267000 1654000 2342000 18096000 5245000 11648000 5060000 798000 22751000 1250000 1250000 24001000 -222000 3213000 20566000 21179000 6907000 5272000 7902000 1393000 42653000

Delta Air Lines Inc. (NYS: DAL) Exchange rate used is that of the Year End reported date

As Reported Annual Balance Sheet Report Date

12/31/2017

Currency USD Audit Status Not Qualified Consolidated Scale Cash Short-term investments Restricted cash & short-term investments Accounts receivable, net Aircraft fuel, spare parts & supplies, net Prepaid expenses & other current assets Total current assets Flight equipment, at cost Ground property & equipment Equipment purchase deposits Total property & equipment, at cost Less accumulated depreciation & amortization Total property & equipment, net Operating lease right-of-use assets Operating lease right-of-use assets Goodwill Intangibles, gross Less: accumulated amortization Intangibles, net of accumulated amortization Deferred tax asset Other assets Total other assets Total assets Current maturities of long-term debt & capital leases Accounts payable Accrued salaries & wages Air traffic liability Loyalty program liability Operating lease liabilities Operating lease liabilities Other accrued liabilities Total current liabilities Secured credit facility Secured aircraft enhanced equipment trust certificates Secured equipment loans & other notes payable Secured special facility revenue bonds Other secured obligations Secured debt Senior notes Unsecured debt Total long-term debt Less: total unamortized debt discount Less current maturities Long-term debt, net of current maturities Long-term debt & finance leases, net of current maturities Long-term debt & capital leases, net of current maturities Pension & postretirement benefits Loyalty program liability Operating lease liabilities Operating lease liabilities Other liabilities Total non-current liabilities

Yes Thousands 295000 4771000 318000 1752000 1359000 651000 9146000 40318000 8267000 1217000 49802000 15646000 34156000 4091000 2825000 622000 2203000 427000 1373000 8094000 51396000 2554000 1688000 1672000 3978000 2791000 2281000 14964000 4781000 11881000 5259000 857000 773000 1750000 25301000 -236000 2554000 22511000 7497000 2498000 32506000

Delta Air Lines Inc. (NYS: DAL) Exchange rate used is that of the Year End reported date

As Reported Annual Income Statement Report Date

12/31/2019

Currency USD Audit Status Not Available Consolidated Scale Mainline passenger Regional passenger

Yes Thousands -

Passenger Cargo revenue

42010000 863000

Other revenues Total operating revenues

2895000 45768000

Aircraft fuel & related taxes Salaries, wages & benefits

7526000 12609000

Regional expenses Maintenance, materials & repairs

7501000 2380000

Other rent & landing fees Aircraft rent

2055000 1326000

Selling expenses Depreciation & amortization

1602000 1982000

Special items, net Other operating expenses

635000 5087000

Total operating expenses Operating income (loss)

42703000 3065000

Interest income Interest expense, net of capitalized interest

127000 1095000

Other nonoperating income (expense), net Total nonoperating income (expense)

159000 -809000

Income (loss) from before income taxes

2256000

State & local current income tax provision (benefit) Current foreign income taxes

2000 8000

Current income tax expense (benefit) Federal deferred income tax provision (benefit)

10000 498000

State & local deferred income tax provision (benefit) Deferred income tax expense (benefit)

62000 560000

Income tax provision (benefit) Net income (loss) Weighted average shares outstanding-basic Weighted average shares outstanding-diluted Year end shares outstanding Net income (loss) per share-basic Net income (loss) per share-diluted Cash dividends declared per common share Number of average full-time equivalent employees Number of common stockholders

Cited Reference:

Mergent Online. 20 Sep 2020. Delta Air Lines Inc (NYS: DAL). Retrieved September 20 2020 from: https://www mergentonline

570000 1686000 443363 444269 428202.506 3.8 3.79 0.4 133700 12995

Delta Air Lines Inc. (NYS: DAL) Exchange rate used is that of the Year End reported date

As Reported Annual Income Statement Report Date

12/31/2018

Currency USD Audit Status Not Available Consolidated Scale Mainline passenger Regional passenger

Yes Thousands -

Passenger Cargo revenue

40676000 1013000

Other revenues Total operating revenues

2852000 44541000

Aircraft fuel & related taxes Salaries, wages & benefits

8053000 12251000

Regional expenses Maintenance, materials & repairs

7133000 2050000

Other rent & landing fees Aircraft rent

1900000 1264000

Selling expenses Depreciation & amortization

1520000 1839000

Special items, net Other operating expenses

787000 5088000

Total operating expenses Operating income (loss)

41885000 2656000

Interest income Interest expense, net of capitalized interest

118000 1056000

Other nonoperating income (expense), net Total nonoperating income (expense)

166000 -772000

Income (loss) from before income taxes

1884000

State & local current income tax provision (benefit) Current foreign income taxes

3000 29000

Current income tax expense (benefit) Federal deferred income tax provision (benefit)

32000 390000

State & local deferred income tax provision (benefit) Deferred income tax expense (benefit)

50000 440000

Income tax provision (benefit) Net income (loss) Weighted average shares outstanding-basic Weighted average shares outstanding-diluted Year end shares outstanding Net income (loss) per share-basic Net income (loss) per share-diluted Cash dividends declared per common share Number of average full-time equivalent employees Number of common stockholders

Cited Reference:

Mergent Online. 27 Sep 2020. Delta Air Lines Inc (NYS: DAL). Retrieved September 20 2020 from: https://www mergentonline

472000 1412000 464236 465660 460610.87 3.04 3.03 0.4 128900 13685

Delta Air Lines Inc. (NYS: DAL) Exchange rate used is that of the Year End reported date

As Reported Annual Income Statement Report Date

12/31/2017

Currency USD Audit Status Not Qualified Consolidated Scale Mainline passenger Regional passenger Passenger Cargo revenue

Yes Thousands 29238000 6895000 800000

Other revenues Total operating revenues

5274000 42207000

Aircraft fuel & related taxes Salaries, wages & benefits

6128000 11816000

Regional expenses Maintenance, materials & repairs

6546000 1959000

Other rent & landing fees Aircraft rent

1806000 1197000

Selling expenses Depreciation & amortization

1477000 1702000

Special items, net Other operating expenses

712000 4806000

Total operating expenses Operating income (loss)

38149000 4058000

Interest income Interest expense, net of capitalized interest

94000 1053000

Other nonoperating income (expense), net Total nonoperating income (expense)

-15000 -974000

Income (loss) from before income taxes

3084000

State & local current income tax provision (benefit) Current foreign income taxes

24000 -

Current income tax expense (benefit) Federal deferred income tax provision (benefit)

24000 1085000

State & local deferred income tax provision (benefit) Deferred income tax expense (benefit)

56000 1141000

Income tax provision (benefit) Net income (loss)

1165000 1919000

Weighted average shares outstanding-basic Weighted average shares outstanding-diluted Year end shares outstanding Net income (loss) per share-basic Net income (loss) per share-diluted Cash dividends declared per common share Number of average full-time equivalent employees Number of common stockholders

Cited Reference:

Mergent Online. 27 Sep 2020. Delta Air Lines Inc (NYS: DAL). Retrieved September 20 2020 from: https://www mergentonline

489164 491692 475507.887 3.92 3.9 0.4 126600 10921

Delta Air Lines Inc. (NYS: DAL) Exchange rate used is that of the Year End reported date

As Reported Annual Cash Flow Report Date Currency

12/31/2019 USD

Audit Status Not Available Consolidated Scale

Net income (loss) Depreciation & amortization Hedge derivative contracts Deferred income taxes Pension, postretirement & postemployment payments greater than expense Equity investment loss (earnings) Receivables Fuel inventory Hedge margin Prepaid expenses & other current assets Air traffic liability Loyalty program deferred revenue Frequent flyer deferred revenue Profit sharing Accounts payable & accrued liabilities Other operating activities, net Net cash flows from operating activities Property & equipment additions - flight equipment, including advance payments Property & equipment additions - ground property & equipment, including technology Purchase of equity investments Sale of equity investments Purchase of short-term investments Redemption of short-term investments Other investing activities, net Net cash flows from investing activities Payments on long-term debt & finance lease obligations Payments on long-term debt & capital lease obligations Repurchase of common stock Cash dividends Fuel card obligation Proceeds from short-term obligations Payments on hedge derivative contracts Proceeds from hedge derivative contracts Proceeds from long-term obligations Other financing activities, net Net cash flows from financing activities Net increase (decrease) in cash, cash equivalents & restricted cash Cash, cash equivalents & restricted cash at beginning of period Cash, cash equivalents & restricted cash at end of period Net increase (decrease) in cash & cash equivalents Cash & cash equivalents at beginning of period Cash & cash equivalents at end of period Cash paid for interest Cash & cash equivalents at beginning of period Cash & cash equivalents at end of period Interest paid Income taxes paid

Yes Thousands 1686000 2318000 -112000 376000 -178000 560000 94000 -62000 73000 -373000 327000 469000 76000 -1230000 -209000 3815000 -4268000 850000 -3184000 4144000 250000 -3000 54000 -86000 -2243000 3960000 -4190000 -61000 -1097000 -178000 -2000 -1568000 4000 286000 290000 1111000 8000

Delta Air Lines Inc. (NYS: DAL) Exchange rate used is that of the Year End reported date

As Reported Annual Cash Flow Report Date Currency

12/31/2018 USD

Audit Status Not Available Consolidated Scale

Net income (loss) Depreciation & amortization Hedge derivative contracts Deferred income taxes Pension, postretirement & postemployment payments greater than expense Equity investment loss (earnings) Receivables Fuel inventory Hedge margin Prepaid expenses & other current assets Air traffic liability Loyalty program deferred revenue Frequent flyer deferred revenue Profit sharing Accounts payable & accrued liabilities Other operating activities, net Net cash flows from operating activities Property & equipment additions - flight equipment, including advance payments Property & equipment additions - ground property & equipment, including technology Purchase of equity investments Sale of equity investments Purchase of short-term investments Redemption of short-term investments Other investing activities, net Net cash flows from investing activities Payments on long-term debt & finance lease obligations Payments on long-term debt & capital lease obligations Repurchase of common stock Cash dividends Fuel card obligation Proceeds from short-term obligations Payments on hedge derivative contracts Proceeds from hedge derivative contracts Proceeds from long-term obligations Other financing activities, net Net cash flows from financing activities Net increase (decrease) in cash, cash equivalents & restricted cash Cash, cash equivalents & restricted cash at beginning of period Cash, cash equivalents & restricted cash at end of period Net increase (decrease) in cash & cash equivalents Cash & cash equivalents at beginning of period Cash & cash equivalents at end of period Cash paid for interest Cash & cash equivalents at beginning of period Cash & cash equivalents at end of period Interest paid Income taxes paid

Yes Thousands 1412000 2159000 -92000 458000 -300000 440000 86000 -64000 222000 -390000 -147000 297000 -283000 -475000 210000 3533000 -3745000 1207000 -3412000 3705000 207000 72000 -7000 -1973000 2354000 -2941000 -59000 -837000 -186000 -3000 -1672000 -112000 398000 286000 1091000 18000

Delta Air Lines Inc. (NYS: DAL) Exchange rate used is that of the Year End reported date

As Reported Annual Cash Flow Report Date Currency

12/31/2017 USD

Audit Status Not Qualified Consolidated Scale

Net income (loss) Depreciation & amortization Hedge derivative contracts Deferred income taxes Pension, postretirement & postemployment payments greater than expense Equity investment loss (earnings) Receivables Fuel inventory Hedge margin Prepaid expenses & other current assets Air traffic liability Loyalty program deferred revenue Frequent flyer deferred revenue Profit sharing Accounts payable & accrued liabilities Other operating activities, net Net cash flows from operating activities Property & equipment additions - flight equipment, including advance payments Property & equipment additions - ground property & equipment, including technology Purchase of equity investments Sale of equity investments Purchase of short-term investments Redemption of short-term investments Other investing activities, net Net cash flows from investing activities Payments on long-term debt & finance lease obligations Payments on long-term debt & capital lease obligations Repurchase of common stock Cash dividends Fuel card obligation Proceeds from short-term obligations Payments on he...