Financial Statement of Delta Airline

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 and 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:

1. Delta_Airlines_Financial_Statements.xlsx 2. Departures_fy_2015_and_2016_Student.xlsx

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:

1. Part One involves an analysis of the financial ratios of Delta Airlines over several years.

2. Part Two involves analyzing travel destinations by carrier, and looking at trends and factors that could affect the air travel industry.

3.

CASE STUDY: DATA AND ACCOUNTING APPLICATIONS

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.

(Note that there are two inventories to consider for the company: Fuel and expendable parts. The ratios provided ONLY take fuel into account.)

1- 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 company’s operations that you would like more information about if you were an analyst, company manager, or investor

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.

2. Open the Excel workbook, Departures_fy_2015_and_2016_Student.xls, and familiarize yourself with the fields in each of the worksheets

a. Apply your knowledge of Pivot Tables to answer the following questions based on the data: (See last page for instructions for the Pivot Table)

1- 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?

2- 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?

3- 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?