The online travel market never stands still. As a leading provider of hotel acomodation
and travel worldwide, Expedia brands offer booking services through operation of more
than 85 sites, serving travellers in more than 60 countries around the world.
With thousands of partners acros the globe, Expedia and Hotels.com seek to turn web
traffic into bokings and happy customers. Analysing trafic and booking behaviour is the
key to excel in sucessfuly delivering world clas products to our partners to beter
satisfy customers.
The Challenge!
Following the market tendency of forming alliances betwen airlines, Norwegian Air Shutle (DY) and Finnair
Oyj (AY) two of the most important System User Airlines of Amadeus decide to merge into a new alliance
called Nordic Airlines (NA) as of 1
st
January 2017. The objectives are to find out synergies betwen both
airlines and to reduce distribution costs (in the future both airlines operate under a unique code NA)
One of the main benefits of being a System User in Amadeus is that Amadeus pays a distribution fe to
the airline for net bokings made from Airline’s own ofices - called ACTOs or Airline City Ticketing
Offices. The new alliance (NA) will stay as a System User in Amadeus.
In order to estimate the impact of this merger, you (the Analyst) are asked to provide the Air Manager
with a series of figures:
Additional information:
The distribution fes paid to the Airline for net bokings made from ACTOs are as follows:
Own Net bokings = €2.5
Net bokings on other airlines = €1.08
Amadeus charges a net boking fe of €3.70 for each net boking made on an Airline through the Amadeus
system. This fe is charged directly to the Airline on which the boking is done.
The Net Bokings made on DY and AY during 2016 were as follows:
Total Net Bokings made on Norwegian Air = 637,50
Total Net Bokings made on Finnair = 361,250
God luck!
The ACTO activity for Norwegian and Finnair for 2016 was as follows:
a) Net Bokings made from DY ACTOs
DY ACTO 2016 Own Net Bokings
Net Bokings made on
AY
Net Bokings made on
other airlines
Jan-March 41,580 13,500 2,760
April-June 39,138 11,700 4,620
July-Sept 47,850 10,380 4,752
Oct-Dec 38,148 7,620 3,120
b) Net Bookings made from AY ACTOs
AY ACTO 2016 Own Net Bokings
Net Bokings made on
DY
Net Bokings made on
other airlines
Jan-March 23,232 8,760 2,160
April-June 17,424 7,800 4,320
July-Sept 22,836 9,840 4,440
Oct-Dec 16,698 5,520 2,940
Coments:
1) A System User Airline uses the Amadeus system in its own ofices (ACTOs) to make reservations/net bokings. Bokings
made by ACTOs on the owner airline are caled ‘own bokings’, i.e. a boking made from a Norwegian Air ACTO on a Norwegian
Air flight is caled ‘own boking’.
2) This includes al net bokings made on this Airline such as own ACTO net bokings (Norwegian ACTOs), ACTO net bokings
of other System Users (other ACTOs) and Travel Agency net bookings.
Questions:
Please answer the following questions using Excel
Before Merger
A1) Total amount invoiced to DY and AY for Net bokings in 2016 (Gros Revenue for Amadeus)
A2) Total amount of Distribution fes paid to DY and AY respectively
A3) Net Revenue for 2016 (from an Amadeus perspective) from these two providers. Net Revenue’s
percentage of the Amadeus Gros Revenue.
A4) Share of ‘own bokings’ when compared to total bokings per provider (DY and AY)
A5) Show graphically the evolution of the total number of net bokings created per ACTO split acording
to the type (Own, on DY/AY, on other airlines) - optional.
After Merger
B1) Supose that DY and AY had merged already in January 2016. How does this afect the result for
question A1 and A2?
B2) Supose that DY and AY had merged already in January 2016. Evaluate the % uplift driven by NA
compared to DY+AY for both question A1 and A2? What are your conclusions?
Please se below Net Bokings made from NA ACTOs in 2017
NA ACTO 2017 Own Net Bokings
Net Bokings made on other
airlines
Jan-March 113,194 5,904
April-June 110,290 11,175
July-Sept 118,178 11,030
Oct-Dec* 88,382 7,272
*Estimate for Q4 2017
B3) Suppose that DY and AY had merged already in January 2016. Evaluate the YoY growth % for both Own Net
Bookings (NA), Net Bokings made on other airlines and Overal Net Bokings by comparing 2017 vs 2016 results.
B4) Suppose that DY and AY had merged already in January 2016. Please identify the quarter where we had the
largest YoY Growth for both Own Net Bokings and Net Bokings made on other airlines. What are their percentages?
B5) Suppose that DY and AY had merged already in January 2016. Show graphicaly the evolution of the total number
of net bookings made on other airlines in 2016 and 2017 - optional.
Forecast
We expect a 15% YoY growth for Nordic Airlines in terms of “Own Net Bokings” and “Net Bokings made on other
airlines” in Q1 and Q2 (2018) whereas the second half of the year wil likely se a reduced growth (40% of Q1 and
Q2).
Please note that Amadeus wil also charge a net boking fe of €4.0 rather than €3.70 as of 1
st
October 2018;
distribution fes wil not change.
C1) Evaluate the estimated volume of Own Net Bokings and Net Bokings made on other airlines for Nordic Airlines
in 2018 (please provide a view per quarter)
C2) Evaluate the estimated total amount invoiced to Nordic Airlines for Net bokings in 2018 (Gross Revenue for
Amadeus)
Submision Format – due 1 wek from the date received
1. Your findings should tell a story. In order to illustrate and bring your propositions
to life, please submit in a presentation format (such as Powerpoint), highlighting
the decisions you made and the relative motivations.
- When building your presentation, think about how you would present this to a
group of stakeholders and convince them to implement your recommendations
2. In a supplement document, please provide your Excel calculation/methodology,
rather than just your answer to the question.