Description
Follow instructions and all material is included
Unformatted Attachment Preview
Dec. 31, 2017
Dec. 31, 2016
Current ratio (times)
Quick ratio (times)
Average payment period (days)
0.42
0.27
183.41
0.49
0.35
181.43
Total asset turnover (times)
Fixed asset turnover (times)
Average collection period (days)
Inventory turnover (times)
0.77
1.55
19.65
6.26
0.77
1.63
18.80
9.89
Total debt to total assets
Equity multiplier (times)
Interest coverage (times)
73.90%
3.83
15.44
76.03%
4.17
17.92
Operating profit margin
Net profit margin
Operating return on assets
Return on total assets
Return on equity
14.82%
8.67%
11.47%
6.71%
25.72%
17.54%
11.03%
13.56%
8.53%
35.59%
LIQUIDITY RATIOS
ASSET MANAGEMENT RATIOS
FINANCIAL LEVERAGE RATIOS
PROFITABILITY RATIOS
Dec. 31, 2015
Dec. 31, 2014
0.52
0.31
151.15
0.54
0.33
0.77
1.77
19.36
17.27
0.75
1.84
20.77
21.85 Fuel Only
79.58%
4.90
16.22
83.68%
6.13
3.39
19.17%
11.12%
14.68%
8.52%
41.71%
5.47%
1.63%
4.08%
1.22%
7.48%
Consolidated Balance Sheets – USD ($) $ in Millions
Current Assets:
Cash and cash equivalents
Short-term investments
Accounts receivable, net of an allowance for uncollectible accounts of $12 and $15 at December
31, 2017 and 2016, respectively
Hedge margin receivable
Fuel inventory
Expendable parts and supplies inventories, net of an allowance for obsolescence of $113 and $110
at December 31, 2017 and 2016, respectively
Hedge derivatives asset
Prepaid expenses and other
Total current assets
Property and Equipment, Net:
Property and equipment, net of accumulated depreciation and amortization of $14,097 and
$12,456 at December 31, 2017 and 2016, respectively
Other Assets:
Goodwill
Identifiable intangibles, net of accumulated amortization of $845 and $828 at December 31, 2017
and 2016, respectively
Deferred income taxes, net
Other noncurrent assets
Total other assets
Total assets
Current Liabilities:
Current maturities of long-term debt and capital leases
Air traffic liability
Accounts payable
Accrued salaries and related benefits
Frequent flyer deferred revenue
Fuel card obligation
Other accrued liabilities
Total current liabilities
Noncurrent Liabilities:
Long-term debt and capital leases
Pension, postretirement and related benefits
Frequent flyer deferred revenue
Other noncurrent liabilities
Total noncurrent liabilities
Commitments and Contingencies
Stockholders’ Equity:
Common stock at $0.0001 par value; 1,500,000,000 shares authorized, 714,674,160 and
744,886,938 shares issued at December 31, 2017 and 2016, respectively
Additional paid-in capital
Retained earnings
Accumulated other comprehensive loss
Treasury stock, at cost, 7,476,181 and 14,149,229 shares at December 31, 2017 and 2016,
respectively
Total stockholders’ equity
Total liabilities and stockholders’ equity
Dec. 31, 2017
Dec. 31, 2016
Dec. 31, 2015
Dec. 31, 2014
$ 1,814
825
2,377
$ 2,762
487
2,064
$ 1,972
1,465
2,020
$ 2,088
1,217
2,297
916
413
519
372
119
379
318
925
534
318
1,499
7,844
1,247
7,451
1,987
796
9,056
1,078
701
9,158
26,563
24,375
23,039
21,929
9,794
4,847
9,794
4,844
9,794
4,861
9,794
4,603
935
3,309
18,885
53,292
3,064
1,733
19,435
51,261
4,956
1,428
21,039
53,134
7,595
926
22,918
54,005
2,242
4,888
3,674
3,022
1,822
1,067
1,858
18,573
1,131
4,626
2,572
2,924
1,648
431
1,907
15,239
1,563
4,503
2,743
3,195
2,581
1,635
1,306
17,526
1,184
4,296
2,622
2,266
2,772
1,580
2,127
16,847
6,592
9,810
2,296
2,111
20,809
6,201
13,378
2,278
1,878
23,735
6,766
13,855
2,246
1,891
24,758
8,477
15,138
2,602
2,128
$ 28,345
0
0
0
$0
12,053
12,294
12,936
12,981
9,636
(7,621)
(158)
7,903
(7,636)
(274)
5,562
(7,275)
(373)
3,456
(7,311)
(313)
13,910
$ 53,292
12,287
$ 51,261
10,850
$ 53,134
8,813
$ 54,005
Consolidated Statements of Operations – USD ($) $ in Millions
Passenger:
Mainline
Regional carriers
Total passenger revenue
Cargo
Other
Total operating revenue
Operating Expense:
Salaries and related costs
Aircraft fuel and related taxes
Regional carriers expense
Depreciation and amortization
Contracted services
Aircraft maintenance materials and outside repairs
Passenger commissions and other selling expenses
Landing fees and other rents
Passenger service
Profit sharing
Aircraft rent
Other
Total operating expense
Operating Income
Non-Operating Expense:
Interest expense, net
Miscellaneous, net
Total non-operating expense, net
Income Before Income Taxes
Income Tax Provision
Net Income
Basic Earnings Per Share (usd per share)
Diluted Earnings Per Share (usd per share)
Cash Dividends Declared Per Share (usd per share)
12 Months Ended
Dec. 31, 2017
$ 29,105
5,714
34,819
729
5,696
41,244
10,436
5,733
4,503
2,235
2,184
1,992
1,787
1,528
1,067
1,065
351
2,249
35,130
6,114
(396)
(17)
(413)
5,701
(2,124)
$ 3,577
$ 4.97
4.95
$ 1.02
12 Months Ended
Dec. 31, 2016
Dec. 31, 2015
Dec. 31, 2014
$ 28,105
5,672
33,777
668
5,194
39,639
$ 28,898
5,884
34,782
813
5,109
40,704
$ 28,688
6,266
34,954
934
4,474
40,362
10,034
5,133
4,311
1,902
1,991
1,823
1,710
1,490
907
1,115
285
1,986
32,687
6,952
8,776
6,544
4,241
1,835
1,848
1,848
1,672
1,493
872
1,490
250
2,033
32,902
7,802
8,120
11,668
5,237
1,771
1,749
1,828
1,700
1,442
810
1,085
233
2,513
38,156
2,206
(388)
72
(316)
6,636
(2,263)
$ 4,373
$ 5.82
5.79
$ 0.68
(481)
(164)
(645)
7,157
(2,631)
$ 4,526
$ 5.68
5.63
$ 0.45
(650)
(484)
(1,134)
1,072
(413)
$ 659
$ 0.79
0.78
$ 0.30
Consolidated Statements of Cash Flows – USD ($) $ in Millions
Cash Flows From Operating Activities:
Net income
Adjustments to reconcile net income to net cash provided by operating activities:
Depreciation and amortization
Hedge derivative contracts
Deferred income taxes
Pension, postretirement and postemployment payments greater than expense
Equity investment earnings
Changes in certain assets and liabilities:
Receivables
Fuel inventory
Hedge margin
Prepaid expenses and other current assets
Air traffic liability
Frequent flyer deferred revenue
Profit sharing
Accounts payable and accrued liabilities
Other, net
Net cash provided by operating activities
Property and equipment additions:
Flight equipment, including advance payments
Ground property and equipment, including technology
Purchase of equity investments
Purchase of short-term investments
Redemption of short-term investments
Other, net
Net cash used in investing activities
Cash Flows From Financing Activities:
Payments on long-term debt and capital lease obligations
Repurchase of common stock
Cash dividends
Fuel card obligation
Payments on hedge derivative contracts
Proceeds from hedge derivative contracts
Proceeds from long-term obligations
Other, net
Net cash used in financing activities
Net (Decrease) Increase in Cash and Cash Equivalents
Cash and cash equivalents at beginning of period
Cash and cash equivalents at end of period
Supplemental Disclosure of Cash Paid for Interest
Non-Cash Transactions:
Treasury stock contributed to our qualified defined benefit pension plans
Flight and ground equipment acquired under capital leases
12 Months Ended
Dec. 31, 2017
Dec. 31, 2016
Dec. 31, 2015
Dec. 31, 2014
$ 3,577
$ 4,373
$ 4,526
$ 659
2,235
(7)
2,071
(3,302)
(1)
1,902
(342)
2,223
(717)
(160)
1,835
(1,366)
2,581
(1,013)
(35)
(328)
(397)
(5)
(57)
262
192
(51)
992
(33)
5,148
(147)
(140)
81
(26)
123
45
(383)
285
88
7,205
(56)
155
806
(102)
207
(301)
734
(201)
157
7,927
1,771
2,186
414
(723)
758
268
106
(2,704)
(1,187)
(1,245)
(925)
584
111
(5,366)
(2,617)
(774)
0
(1,707)
2,686
257
(2,155)
(2,223)
(722)
(500)
(998)
739
(251)
(3,955)
(1,258)
(1,677)
(731)
636
(244)
20
2,454
70
(730)
(948)
2,762
1,814
390
(1,709)
(2,601)
(509)
211
(451)
291
450
58
(4,260)
790
1,972
2,762
385
(2,558)
(2,200)
(359)
(340)
(71)
429
1,038
(27)
(4,088)
(116)
2,088
1,972
452
(302)
62
172
(922)
58
174
(238)
264
(36)
276
4,947
(1,662)
(587)
0
(1,795)
1,533
0
0
48
(2,463)
(2,928)
(1,100)
(251)
(41)
0
0
1,020
60
(3,240)
350
261
350
86
0
111
(756)
2,844
Consolidated Statements of Stockholders’ Equity – USD ($) shares in Millions, $ in Millions
Beginning balance at Dec. 31, 2014
Beginning balance (shares) at Dec. 31, 2014
Consolidated Statements of Stockholders’ Equity (Deficit)
Net income
Dividends declared
Other comprehensive income (loss)
Shares of common stock issued and compensation expense associated with equity awards
(Treasury shares withheld for payment of taxes)
Shares of common stock issued and compensation expense associated with equity awards
(Treasury shares withheld for payment of taxes) (shares)
Stock options exercised
Stock options exercised (shares)
Stock purchased and retired
Stock purchased and retired (shares)
Ending balance at Dec. 31, 2015
Ending balance (shares) at Dec. 31, 2015
Consolidated Statements of Stockholders’ Equity (Deficit)
Net income
Change in accounting principle
Dividends declared
Other comprehensive income (loss)
Shares of common stock issued and compensation expense associated with equity awards
(Treasury shares withheld for payment of taxes)
Shares of common stock issued and compensation expense associated with equity awards
(Treasury shares withheld for payment of taxes) (shares)
Stock options exercised
Stock options exercised (shares)
Treasury stock, net, contributed to our qualified defined benefit pension plans
Treasury stock, net, contributed to our qualified defined benefit pension plans (shares)
Stock purchased and retired
Stock purchased and retired (shares)
Ending balance at Dec. 31, 2016
Ending balance (shares) at Dec. 31, 2016
Consolidated Statements of Stockholders’ Equity (Deficit)
Net income
Dividends declared
Other comprehensive income (loss)
Shares of common stock issued and compensation expense associated with equity awards
(Treasury shares withheld for payment of taxes)
Shares of common stock issued and compensation expense associated with equity awards
(Treasury shares withheld for payment of taxes) (shares)
Stock options exercised
Stock options exercised (shares)
Treasury stock, net, contributed to our qualified defined benefit pension plans
Treasury stock, net, contributed to our qualified defined benefit pension plans (shares)
Stock purchased and retired
Stock purchased and retired (shares)
Ending balance at Dec. 31, 2017
Ending balance (shares) at Dec. 31, 2017
ares in Millions, $ in Millions
Total
Common Stock
$ 8,813
[1]
$0
845
4,526
(359)
36
16
[1]
Additional Paid-In Capital
$ 13,621
76
1
18
18
2
(2,200)
10,850
[1]
(779)
(48)
$0
800
4,373
95
(509)
(361)
65
[1]
12,936
105
2
32
32
3
343
204
(2,601)
12,287
[1]
[1]
(983)
(60)
$0
745
3,577
(731)
15
68
12,294
107
1
28
28
2
343
188
(1,677)
$ 13,910
(564)
(33)
$0
715
$ 12,053
Retained Earnings
$ 2,816
Accumulated Other Comprehensive Loss
$ (7,311)
Treasury Stock
$ (313)
20
4,526
(359)
36
$ (60)
1
(1,421)
5,562
(7,275)
$ (373)
21
4,373
95
(509)
(361)
$ (40)
1
$ 139
(8)
(1,618)
7,903
(7,636)
$ (274)
14
3,577
(731)
15
$ (39)
1
$ 155
(8)
(1,113)
$ 9,636
$ (7,621)
$ (158)
7
CASE STUDY: DATA AND ACCOUNTING APPLICATIONS- Student Version
Case Study Overview
The airline industry is impacted by changes in its external environment from political, economic, social,
technological, environmental, and legal factors. These aren’t controlled directly by the companies in the
industry, but the companies nevertheless might be forced to alter their business models, pricing, revenue,
and cost structures, etc. in response to changing conditions. Knowledge of external environmental trends
can help predict opportunities and risks. Knowledge of ratio analysis can help assess what is working in a
company’s strategy to adapt to changing conditions.
In this case study, we will look at the financial statements of a couple of different major players in the
airline industry, as well as international routes companies are flying out of the U.S. The focus will be on
knowledge of Excel, practice with Excel functions, such as VLOOKUP and LOOKUP, practice creating
and using a pivot table, and visualization.
Case Study Resources
DATA OVERIEW
There are two data sources for this project that are included as resources:
o
o
Delta_Airlines_Financial_Statements.xlsx
Departures_fy_2015_and_2016_Student.xlsx
The financial statements for Delta Airlines were obtained from the U.S. Securities and Exchange
Commission through the Electronic Data Gathering, Analysis, and Retrieval system (EDGAR). Since 1934,
the SEC has required disclosure in forms and documents. In 1984, EDGAR began collecting electronic
documents to help investors get information that can be downloaded. Financial statements for publicly
traded companies are available through the site:
https://www.sec.gov/edgar/searchedgar/companysearch.html.
The Departures data set includes information provided by the U.S. Department of Transportation to the
public concerning international aviation: https://www.transportation.gov/policy/aviation-policy/usinternational-air-passenger-and-freight-statistics-report.
U.S. International Air Passenger and Freight data is confidential for a period of six months, after which it
can be released. The data provided includes nonstop commercial traffic traveling between U.S. airports
and international cities. (Caveat: Note that global air travel systems are comprised of complex, everchanging networks and alliances, and the majority of international passengers to and from the U.S. make
at least one connection before reaching their final destination—that information is not contained in the
data. U.S. carriers serve some international points only through an international connection; therefore, it
might look as if no U.S. carrier serves a certain international point, when in fact U.S. carrier traffic is first
flowing through a connecting city. Figures for the U.S. nonstop market share do not necessarily correlate
to the total service provided to that point by all U.S. carriers.)
Case Study Instructions
There are two parts to this case study:
•
•
Part One involves an analysis of the financial ratios of major airlines.
Part Two involves analyzing travel destinations by carrier, and looking at trends and factors that
could affect the air travel industry.
You will be using Microsoft Excel, including functions and pivot tables, to analyze the data. If you need a
refresher on Excel, review the resources provided in DA Lesson 3: Review Material of the Data Analytics
and Accounting module.
Part One: Ratio Analysis
Data Description
The Delta Air Lines Inc. data (Delta_Air_Lines_Financial_Statements.xls) has been provided to you, along
with the computations of the ratios (the formulas are contained in the Excel worksheet, Ratios.) The
workbook includes worksheets for Ratios, Consolidated Balance Sheet, and Statements of Operations,
Cash, and Stockholders’ Equity.
1.
2.
3.
4.
Review the ratios provided. (Note that there are two inventories to consider for the company: Fuel
and expendable parts. The ratios provided ONLY take fuel into account.) Identify each of the
following ratios that are provided in the spreadsheet for the year 2017, and provide an explanation
of its meaning, comparable to other years:
•
Current Ratio
•
Asset Turnover
•
Inventory Turnover
•
Debt to Asset Ratio
•
Interest Coverage
•
Profit Margin
•
Return on Assets
•
Return on Equity
Based on the ratios provided, identify 2-3 questions you might have about the company’s
operations that you would like more information about if you were an analyst, company manager,
or investor.
Go to: https://www.sec.gov/edgar/searchedgar/companysearch.html and search for Delta Air Lines
Inc. (DAL). Every company has a standard classification code for what industry they’re in, such
as The Standard Industrial Classification code (SIC) or North American Industry Classification
System (NAICS) code. Identify the SIC code for Delta Air Lines Inc. in the information shown on
the EDGAR site under the Delta Air Lines Inc. name. (Note: You can click on this code to find
other companies with the same code.)
The other major players in the U.S. airline industry include United Continental (UAL), American
Airlines (AAL), and Southwest Airlines (LUV). Choose one of these companies, and use EDGAR
(https://www.sec.gov/edgar/searchedgar/companysearch.html) to search for the annual reports for
this company from 2014-2017. (Note: In the Filing Type Box, enter “10-K” and press Enter.
Then, click on the Interactive Data button for one of the years. You can view the statements by
clicking on the left menu bar, or click View Excel Document to download the data. Do this for
each of the years.) Conduct the ratio analysis for the company you chose and compare the ratios
across years and to those of Delta Air Lines Inc. Identify any observations or questions you might
have.
5.
Create a visualization (graph, table, etc.) to compare the two companies on at least one factor (a
ratio, fuel costs, inventory levels, revenues, expenses, etc.) Your visualization should tell a clear
story about the comparative performance of the two companies. Format your visualization so it is
clearly readable and attractive using some of the techniques you learned in the module. Provide a
brief explanation of what the visualization is supposed to show.
Part Two: Environmental Analysis
The Board of Directors of a major airline is concerned about the possibility of an economic downturn
affecting demand for air travel. Consider that you are a manager and have been tasked to do an
environmental analysis for the industry and a destination analysis to determine which of the flight
destinations are most popular.
1.
Identify some of the questions you might have about the overall environmental industry trends
(political, economic, social, technological, environmental, and legal, or others). You might include
questions such as “What affects demand?” or “Who is likely to travel the most in the next five
years?” “How are fuel prices determined” or “How can fuel efficiency be improved?”, for
instance.
a. Identify at least one question for each of the environmental factors (political, economic,
social, technological, environmental, and legal).
b. For at least one of the questions, hypothesize the answers to it, and identify where you
might go to evaluate resources. For instance, if you asked a question regarding what
affects demand, you might hypothesize that personal income could be a factor, and you
might conclude that evaluating the overall U.S. economy or household income from data
on the Bureau of Economic Analysis site could be valuable information.
Identify at least one other question and at least two sources of possible information.
2.
Open the Excel workbook, Departures_fy_2015_and_2016_Student.xls, and familiarize yourself
with the fields in each of the worksheets.
Metadata (Data Dictionary):
For the tbl_Export_Departures worksheet:
Column Name
Year
Month
usg_apt_id
usg_apt
usg_wac
fg_apt_id
fg_apt
fg_wac
airlineid
carrier
carriergroup
type
Scheduled
Charter
Example Value
2015
11
10299
ANC
1
12277
ICN
778
19917
5Y
1
Departures
245
792
Description
Data Year
Data Month
US Gateway Airport ID – assigned by US DOT to identify an airport
US Gateway Airport Code – usually assigned by IATA; otherwise FAA-assigned code
US Gateway World Area Code – assigned by US DOT to represent a geographic territory
Foreign Gateway Airport ID – assigned by US DOT to identify an airport
Foreign Gateway Airport Code – usually assigned by IATA, otherwise FAA-assigned code
Foreign Gateway World Area Code – assigned by US DOT to represent a geographic territory
Airline ID – assigned by US DOT to identify an air carrier
IATA-assigned air carrier code; otherwise, FAA-assigned code
Carrier Group Code – 1 denotes US domestic air carriers, 0 denotes foreign air carriers
Defines the type of flight operated
Tons of freight carried by scheduled service operations
Tons of freight carried by charter operations
Total
3247
Total tons of freight carried by scheduled service and charter operations
For the Airport Codes worksheet:
Column Name
From tbl_Export_Departures
Example
Name
AirportCode
fg_apt
ICN
Foreign Gateway Airport
Agra, India
City and Country Name
Location
For the Air Carrier Codes worksheet:
Column Name
From tbl_Export_Departures
Example
Name
Code
carrier
DL
IATA-assigned air carrier code
Delta Air Lines, Inc.
Name of air carrier
Carrier Name
3.
4.
Open the Excel workbook, Webdeparturesfy2016and2016.xls, and familiarize yourself with the
fields in each of the worksheets.
To analyze the destinations by carrier, create a pivot table in a new worksheet. Review Pivot
Tables in Section 2 of the module if you need further instructions.
•
•
•
•
In Excel for Windows, click anywhere in the main data set (in the tbl_Export_Departures
worksheet) and click Insert, then Insert Pivot Table, and then click OK.
In Excel for Mac, click anywhere in the main data set, and click Data > Pivot Table.
Rename the new worksheet “Pivot Table 1”
Set the Pivot Table Builder up as follows:
You should be able to count the fg_apt field, which will tell you how many times each carrier
departed to the destination.
5.
Apply your knowledge of Pivot Tables to answer the following questions based on the data:
a. What was the top destination for all carriers from the U.S. in the Year 2016 and how
many total departures for that destination were there?
b. What was the top destination for all carriers from the U.S. in the Year 2016 and how
many total departures for that destination were there?
c. What was the top destination for ONLY Delta Air Lines, Inc., United Airlines, and
American Airlines from the U.S. in the Year 2016 and how many combined total
departures for that destination were there?
6. On the tbl_Export_Departures worksheet, use the VLOOKUP function to determine the city and
country for a given airport code. You will need to choose the array from the Airport Codes
worksheet. On the tbl_Export_Departures worksheet, you should be able to enter a code used in
the fg_apt column, such as AMS, and have it return the value; in this case, AMSTERDAM,
NETHERLANDS.
7. Create another pivot table around another facet of information that you find interesting and can
gather from the data.
8. Download the data for fuel prices from https://www.transtats.bts.gov/fuel.asp and calculate the
average fuel costs for all airlines for 2014-2017. How does Delta Airlines fuel costs over this time
period compare to the average? What has been their cost vs. revenue over the years?
9. Create a visualization (graph, table, etc.) to compare variables. Your visualization should tell a
clear story about the comparative performance of airline companies around a factor you find to be
important. Format your visualization so it is clearly readable and attractive using some of the
techniques you learned in the module.
10. Extra Challenge Activity: Create an array that links countries to different regions, such as the
following:
EGYPT
ALGERIA
PAKISTAN
IVORY COAST
ETHIOPIA
COTE D’IVOIRE
AFRICA
NIGERIA
GHANA
CHAD
MAURITANIA
MOROCCO
MADAGASCAR
DJIBOUTI
MAURITANIA
COMORO IS.
GABON
LIBYA
ALGERIA
MOZAMBIQUE
TANZANIA
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
BURKINA FASO
ANGOLA
ERITREA
ASCENSION ISLAND
KENYA
GABON
BOTSWANA
ZAMBIA
GUYANA
NIGERIA
SEYCHELLES
ZAIRE
MOZAMBIQUE
ZIMBABWE
CAMEROON
SOMALIA
GAMBIA
BURUNDI
MALI
MALAWI
GUINEA BISSAU
CONGO
RWANDA
SENEGAL
LIBERIA
MALAWI
SIERRA LEONE
INDONESIA
CHINA
SRI LANKA
INDIA
JAPAN
INDONESIA
KAZAKHSTAN
MYANMAR
LAOS
TURKMENISTAN
MONGOLIA
UZBEKISTAN
PHILIPPINES
THAILAND
NEPAL
MALAYSIA
VIETNAM
BANGLADESH
KOREA
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Africa
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
Asia
SOUTH KOREA
SRI LANKA
TAIWAN
HONG KONG
MALDIVES
NEW ZEALAND
AUSTRALIA
COCOS-KEELING
TASMANIA
JAMAICA
KITTS
BERMUDA
INDIES
BAHAMAS
LUCIA
ARUBA
BARBADOS
ANTILLES
CUBA
CAPE VERDE
WINDWARD IS
DOMINICAN
B.W.I.
EL SALVADOR
HONDURAS
PANAMA
COSTA RICA
BELIZE
NICARAGUA
HAITI
GUATEMALA
GERMANY
DENMARK
FINLAND
SCOTLAND
UK
U.K.
CANARY IS.
SWITZERLAND
TURKEY
ETHIOPIA
NORWAY
ICELAND
SWEDEN
FRANCE
Asia
Asia
Asia
Asia
Asia
Australia
Australia
Australia
Australia
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Caribbean
Central America
Central America
Central America
Central America
Central America
Central America
Central America
Central America
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
AZORES
SPAIN
GREENCE
ITALY
ANDORRA
NETHERLANDS
BELGIUM
ROMANIA
GUADELOUPE
BUCHAREST
SERBIA
CROATIA
PORTUGAL
CZECH REPUBLIC
HUNGARY
BOSNIA
BULGARIA
IRELAND
POLAND
MOLDOVA
GREENLAND
MONTENEGRO
MALTA
REYKJAVIK
LUXEMBOURG
YUGOSLAVIA
CANADA
EDMONTON
MEXICO
MX
CAN
SANTA CRUZ HUATULCO
KIRIBATI
NEW GUINEA
SAMOA
PACIFIC
SOLOMON ISLANDS
MARSHALL ISLAND
POLYNESIA
FIJI
MARQUESAS
VANUATU
POLYNESIA
RUSSIA
BRA.
Spain
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
Europe
North America
Canada
North America
Mexico
North America
North America
Pacific
Pacific
Pacific
Pacific
Pacific
Pacific
Pacific
Pacific
Pacific
Pacific
Pacific
Russia
South America
BRAZIL
VENEZUELA
SURINAM
COLOMBIA
ARGENTINA
PARAGUAY
PERU
CHILE
BOLIVIA
ECUADOR
ARG
URUGUAY
USA
EMIRATES
KUWAIT
QATAR
YEMEN
IRAN
JORDAN
Arabia
SYRIA
BAHRAIN
AZERBAIJAN
ISRAEL
IRAQ
OMAN
CYPRUS
South America
South America
South America
South America
South America
South America
South America
South America
South America
South America
South America
South America
USA
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Western Asia
Use a LOOKUP function, such as:
=LOOKUP(10^10,SEARCH($F$2:$F$170,B2),$G$2:$G$170)
Here, the yellow highlight is the first column (i.e., EGYPT) and the green highlight is the second
column (i.e., AFRICA). B2 represents the first cell to lookup and will be the first value in the
Airport Codes spreadsheet (i.e., AL ARISH, ARAB REP. OF EGYPT). Copy this formula in a
new column for every row in the spreadsheet to link the Airport Code and the Location to the
Region.
The challenge is to determine, using pivot tables, the top 4 regions for departures by Delta Air
Lines, Inc., American Airlines, and United Airlines, combined.
Purchase answer to see full
attachment
