INTRODUCTION

Redox Airline is a small startup airline, operating out of the Chicago, Illinois area in the United States. The airline's management team has a strong airline operations background but doesn't have financial or data analytics experience.

I was shocked to find out that until recently, the airline could not pull specific aircraft details, such as route and seating capacity, from a single report but needed to open two or more files and compare the information.

TASK

The CEO has requested me to analyze the data and present key insights to drive the business growth and position in the industry.

DATASET

The dataset is a 2-year data (2014 - 2015) and contains four tables;

• Flight
• Route
• Aircraft
• Airport

FLIGHT TABLE

The flight table contains the following columns; Flight ID, Date, Route ID, Departure delay, Aircraft ID, Scheduled departure, Average Ticket price, Total fare, Flight month and Flight year.

ROUTE TABLE

This table contains the following columns; Route ID, Departure Airport, Distance and List price.

AIRCRAFT TABLE

The aircraft table contains the following columns; Aircraft ID, Aircraft type, Seat capacity and Fuel cost per seat mile in cents.

AIRPORT TABLE

This table contains the following columns; Airport Id, Airport name and Gates number.

DATA MANIPULATION, ANALYSIS AND VISUALIZATION

CASE SCENARIOS

CASE 1

• How many flights use the A319 aircraft?

The Flight table doesn't contain the Aircraft type column, but the Flight and Aircraft tables have a column in common, which is the AIrcraft ID. I used the XLOOKUP function to give the corresponding value of the Aircraft type from the Aircraft table. This allowed me to have the Aircraft type and the Flight ID in one column.

• What is the most common type of aircraft across all flights?

The above analysis has been able to answer this question.

• What is the maximum average ticket price for flights on the A320 aircraft?

CASE 2

Show the fuel cost per mile in dollars

Fuel cost per mile (Cents) = Fuel cost per seat mile x Seat capacity

I converted cents to dollars by dividing the figures by 100.

• Show the total revenue per flight, assuming a 10% tax on all fares.

10% Tax on Fares = Total Fare x 0.1

Total Fare with Tax = 10% tax on fares + Total fare

CASE 3

• Where are we losing the most money?

CASE 4

• Where are we the most profitable?

CASE 5

• How many flights are flown from O'Hare (ORD) to Los Angeles International Airport (LAX)?

I used Case 1 to answer this question using Route ID as a slicer.

CASE 6

• Which route accounts for the lowest percentage of total revenue?
• 

CASE 7

• What was the most popular month to fly to Fort Lauderdale (FLL)?

I created a monthly trend of flights, then a Route ID slicer will be used to visualise the number of flights per month such as the Fort Lauderdale.

CASE 8

• Create a visual representation to show the number of flights per month on the ORD - PHL route.

INSIGHTS AND RECOMMENDATIONS

• Aircraft type B737 generated the highest number of flights and revenue, 4,459 and $235,636,935 respectively with its highest number of flights (403) in December and the lowest (351) in February from 2014 - 2015.
• Aircraft type A320 has the least performance of all three aircraft, with 1,298 flights and a $65,628,691 revenue from 2014 - 2015. The growth and strategy department should look into this aircraft, this could result from poor customer experience and service, inadequate staffing, flight delays, fuel consumption, operational issues etc.
• In 2015, the airline generated 4,822 flights and $244,870,661. There is a growth increase of 8 flights and $6,808,774 between 2014 and 2015. This signifies growth, although not a wide growth margin but there is room for improvement.
• Route ORD - LAX, from Chicago O'Hare International to Los Angeles International generated the most revenue (9.6%, $46,489,870) of the 24 routes. Its total number of flights is 699 flights and the $263,270 average ticket price.
• Route ORD - SLC, from Chicago O'Hare International to Salt Lake City International generated the least revenue (0.4%, $1,968,984) of the 24 routes. Its total number of flights is 36 and the $11,136 average ticket price. This route's performance is significantly poor, are customers aware of this route? is the flight constantly delayed? are passengers satisfied with the service provided? what is the ratio of passengers to staff via this route? Questions like these will help to boost this route's performance.

Interact with my dashboard here