Data Analytics and Accounting_Case Study_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 Delta_Airlines_Financial_Statements.xlsx
o 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/edga
searchedga
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/us-
international-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, ever-
changing 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. ca
iers serve some international points only through an international connection; therefore, it
might look as if no U.S. ca
ier serves a certain international point, when in fact U.S. ca
ier traffic is first
flowing through a connecting city. Figures for the U.S. nonstop market share do not necessarily co
elate
to the total service provided to that point by all U.S. ca
iers.)
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 ca
ier, and looking at trends and factors that
could affect the air travel industry.
CASE STUDY: DATA AND ACCOUNTING APPLICATIONS- Student Version
You will be using Microsoft Excel, including functions and pivot tables, to analyze the data. If you need a
efresher 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. 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:
• Cu
ent Ratio
• Asset Turnover
• Inventory Turnover
• Debt to Asset Ratio
• Interest Coverage
• Profit Margin
• Return on Assets
• Return on Equity
2. 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.
3. Go to: https:
www.sec.gov/edga
searchedga
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.)
4. 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/edga
searchedga
companysearch.html) to search for the annual reports for
this company from XXXXXXXXXXNote: 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
atio, 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
ief 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).
. 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 Example Value Description
Year 2015 Data Year
Month 11 Data Month
usg_apt_id 10299 US Gateway Airport ID - assigned by US DOT to identify an airport
usg_apt ANC US Gateway Airport Code - usually assigned by IATA; otherwise FAA-assigned code
usg_wac 1 US Gateway World Area Code - assigned by US DOT to represent a geographic te
itory
fg_apt_id 12277 Foreign Gateway Airport ID - assigned by US DOT to identify an airport
fg_apt ICN Foreign Gateway Airport Code - usually assigned by IATA, otherwise FAA-assigned code
fg_wac 778 Foreign Gateway World Area Code - assigned by US DOT to represent a geographic te
itory
airlineid 19917 Airline ID - assigned by US DOT to identify an air ca
ier
ca
ier 5Y IATA-assigned air ca
ier code; otherwise, FAA-assigned code
ca
iergroup 1 Ca
ier Group Code - 1 denotes US domestic air ca
iers, 0 denotes foreign air ca
iers
type Departures Defines the type of flight operated
Scheduled 245 Tons of freight ca
ied by scheduled service operations
Charter 792 Tons of freight ca
ied by charter operations
Total 3247 Total tons of freight ca
ied 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
Location Agra, India City and Country Name
For the Air Ca
ier Codes worksheet:
Column Name From tbl_Export_Departures Example Name
Code ca
ier DL IATA-assigned air ca
ier code
Ca
ier Name Delta Air Lines, Inc. Name of air ca
ier
3. Open the Excel workbook, Webdeparturesfy2016and2016.xls, and familiarize yourself with the
fields in each of the worksheets.
4. To analyze the destinations by ca
ier, 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 ca
ier
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 ca
iers from the U.S. in the Year 2016 and how
many total departures for that destination were there?
. What was the top destination for all ca
iers 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 a
ay 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