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,...

Need 45 to 50 pages and 15 slides of powerpoint


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/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 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. 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 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/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: ColumnName ExampleValue Description Year 2015 DataYear Month 11 DataMonth usg_apt_id 10299 USGatewayAirportID-assignedbyUSDOTtoidentifyanairport usg_apt ANC USGatewayAirportCode-usuallyassignedbyIATA;otherwiseFAA-assignedcode usg_wac 1 USGatewayWorldAreaCode-assignedbyUSDOTtorepresentageographicterritory fg_apt_id 12277 ForeignGatewayAirportID-assignedbyUSDOTtoidentifyanairport fg_apt ICN ForeignGatewayAirportCode-usuallyassignedbyIATA,otherwiseFAA-assignedcode fg_wac 778 ForeignGatewayWorldAreaCode-assignedbyUSDOTtorepresentageographicterritory airlineid 19917 AirlineID-assignedbyUSDOTtoidentifyanaircarrier carrier 5Y IATA-assignedaircarriercode;otherwise,FAA-assignedcode carriergroup 1 CarrierGroupCode-1denotesUSdomesticaircarriers,0denotesforeignaircarriers type Departures Definesthetypeofflightoperated Scheduled 245 Tonsoffreightcarriedbyscheduledserviceoperations Charter 792 Tonsoffreightcarriedbycharteroperations Total 3247 Totaltonsoffreightcarriedbyscheduledserviceandcharteroperations 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-assignedaircarriercode 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
Dec 18, 2021
SOLUTION.PDF

Get Answer To This Question