Accounting case study
Case Study Overview
The airline industry is impacted by changes in its external environment from political, economic, social, technological, environmental, and legal factors. These arent 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 companys 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/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/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 destinationthat 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.
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 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. 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
2. Based on the ratios provided, identify 2-3 questions you might have about the companys operations that you would like more information about if you were an analyst, company manager, or investor.
3. 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.)
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/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 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 territory 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 territory airlineid 19917 Airline ID – assigned by US DOT to identify an air carrier carrier 5Y IATA-assigned air carrier code; otherwise, FAA-assigned code carriergroup 1 Carrier Group Code – 1 denotes US domestic air carriers, 0 denotes foreign air carriers type Departures Defines the type of flight operated Scheduled 245 Tons of freight carried by scheduled service operations Charter 792 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
Location Agra, India City and Country Name
For the Air Carrier Codes worksheet:
Column Name From tbl_Export_Departures Example Name
Code carrier DL IATA-assigned air carrier code
Carrier Name Delta Air Lines, Inc. Name of air carrier
3. Open the Excel workbook, Webdeparturesfy2016and2016.xls, and familiarize yourself with the fields in each of the worksheets.
4. 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 Africa ALGERIA Africa PAKISTAN Africa IVORY COAST Africa ETHIOPIA Africa COTE D’IVOIRE Africa AFRICA Africa NIGERIA Africa GHANA Africa CHAD Africa MAURITANIA Africa MOROCCO Africa MADAGASCAR Africa DJIBOUTI Africa MAURITANIA Africa COMORO IS. Africa GABON Africa LIBYA Africa ALGERIA Africa MOZAMBIQUE Africa TANZANIA Africa
BURKINA FASO Africa ANGOLA Africa ERITREA Africa ASCENSION ISLAND Africa KENYA Africa GABON Africa BOTSWANA Africa ZAMBIA Africa GUYANA Africa NIGERIA Africa SEYCHELLES Africa ZAIRE Africa MOZAMBIQUE Africa ZIMBABWE Africa CAMEROON Africa SOMALIA Africa GAMBIA Africa BURUNDI Africa MALI Africa MALAWI Africa GUINEA BISSAU Africa CONGO Africa RWANDA Africa SENEGAL Africa LIBERIA Africa MALAWI Africa SIERRA LEONE Africa INDONESIA Asia CHINA Asia SRI LANKA Asia INDIA Asia JAPAN Asia INDONESIA Asia KAZAKHSTAN Asia MYANMAR Asia LAOS Asia TURKMENISTAN Asia MONGOLIA Asia UZBEKISTAN Asia PHILIPPINES Asia THAILAND Asia NEPAL Asia MALAYSIA Asia VIETNAM Asia BANGLADESH Asia KOREA Asia
SOUTH KOREA Asia SRI LANKA Asia TAIWAN Asia HONG KONG Asia MALDIVES Asia NEW ZEALAND Australia AUSTRALIA Australia COCOS-KEELING Australia TASMANIA Australia JAMAICA Caribbean KITTS Caribbean BERMUDA Caribbean INDIES Caribbean BAHAMAS Caribbean LUCIA Caribbean ARUBA Caribbean BARBADOS Caribbean ANTILLES Caribbean CUBA Caribbean CAPE VERDE Caribbean WINDWARD IS Caribbean DOMINICAN Caribbean B.W.I. Caribbean EL SALVADOR Central America HONDURAS Central America PANAMA Central America COSTA RICA Central America BELIZE Central America NICARAGUA Central America HAITI Central America GUATEMALA Central America GERMANY Europe DENMARK Europe FINLAND Europe SCOTLAND Europe UK Europe U.K. Europe CANARY IS. Europe SWITZERLAND Europe TURKEY Europe ETHIOPIA Europe NORWAY Europe ICELAND Europe SWEDEN Europe FRANCE Europe
AZORES Spain SPAIN Europe GREENCE Europe ITALY Europe ANDORRA Europe NETHERLANDS Europe BELGIUM Europe ROMANIA Europe GUADELOUPE Europe BUCHAREST Europe SERBIA Europe CROATIA Europe PORTUGAL Europe CZECH REPUBLIC Europe HUNGARY Europe BOSNIA Europe BULGARIA Europe IRELAND Europe POLAND Europe MOLDOVA Europe GREENLAND Europe MONTENEGRO Europe MALTA Europe REYKJAVIK Europe LUXEMBOURG Europe YUGOSLAVIA Europe CANADA North America EDMONTON Canada MEXICO North America MX Mexico CAN North America SANTA CRUZ HUATULCO North America KIRIBATI Pacific NEW GUINEA Pacific SAMOA Pacific PACIFIC Pacific SOLOMON ISLANDS Pacific MARSHALL ISLAND Pacific POLYNESIA Pacific FIJI Pacific MARQUESAS Pacific VANUATU Pacific POLYNESIA Pacific RUSSIA Russia BRA. South America
BRAZIL South America VENEZUELA South America SURINAM South America COLOMBIA South America ARGENTINA South America PARAGUAY South America PERU South America CHILE South America BOLIVIA South America ECUADOR South America ARG South America URUGUAY South America USA USA EMIRATES Western Asia KUWAIT Western Asia QATAR Western Asia YEMEN Western Asia IRAN Western Asia JORDAN Western Asia Arabia Western Asia SYRIA Western Asia BAHRAIN Western Asia AZERBAIJAN Western Asia ISRAEL Western Asia IRAQ Western Asia OMAN Western Asia CYPRUS 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.
