Calculating The Cost Of Overbooking Airline Tickets Using Analytics
This article was originally posted on Linkedin here prior to the pandemic Using Python to calculate the cost of overbooking an airline ticket.
The code can be found in GitHub here.
I have been curious to use data analytics in aviation operations. I found that one of the models that airlines use is the binomial function to determine what is the optimum revenue the airline, after calculating the probability of the number of passengers that will show up when overbooking a specific route. The probability is modeled with binomial function assuming that each show or no show sample is independent of each other. This probability is affected by many possible factors like delayed flights, traffic jams, canceling a flight due to personal reasons, etc.
This article was inspired by Cory Simon ’s article on “ By How Many Flights an Airline Should Overbook”. In his analysis, he uses a normal approximation of a binomial distribution. He allocates fixed parameters such as a number of seats per aircraft, the probability of a passenger and expands to a range of additional tickets beyond capacity sold the price per ticket and the price of the voucher. The voucher in his example is defined as the cost the airline incurs to pay passengers for an overbooked ticket.
I decided to expand a little more and create a function that could allow passing the value of the ticket, the voucher cost, the number of seats, the probability rate of a passenger showing up and the number of additional seats. I used Python’s numpy cumulative distributive function to calculate the probability of passengers to show to a flight beyond capacity (Total Seats Available in the aircraft + total additional overbooking tickets). The function also calculates the max number of possible seats available that could be overbooked. This is defined by p * x=Total Seats Available. If the probability is equal to 1 then all seats will be taken. By solving x, then x=Total Seats Available/p _will give the maximum seats available for that probability _p. A good explanation of the calculation of the probability of overbooking can also be found here.
Let’s assume the case of Embraer 175 flying Miami to Cleveland with 128 seats in the main cabin.
For this calculation, I am focused on the number of additional tickets beyond capacity. The following example tests a ticket with a value of $512.00 from Miami to Cleveland in American Airlines, assuming a probability rate of passengers to show at this time on this date at 90%. The maximum number of additional seats the airline could sell is equal to_ Max_Seats = 128 /.9 = 142.22 - 128 = 14.22 _rounded down will give 14 additional seats on 128 seat aircraft, with a probability of 90 % of the passengers showing up and a voucher cost for overbooking of $200.00.
The airline by selling additional tickets increases its revenue, but the net profit decreases if they end up paying for too many vouchers. The voucher cost could include the cost of re-scheduling passengers. In this example, it is omitted. I am trying to find the optimum amount of additional tickets the airline could sell.
The result of this graph shows the maximum profit per additional ticket at a fixed voucher cost, with the maximum amount of overbooking tickets (14). The maximum total profit can be reached at $66,995.94 overbooking 6 tickets of the 14.
The following table test different vouchers values based on the suggested overbooking tickets found on the previous table.
The table shows that the ideal Voucher_Cost should be less than $200.00, overbooking 6 seats. If the voucher cost offered is higher than $600.00 the airline should not overbook any additional tickets.
This analysis is just the first to understand a model, in this case, the binomial function, when applying analytics in aviation operations. It is important to highlight that there are 4 moving variables: Ticket sale price, the voucher cost, probability of passengers to show at a specific time and date, and the number of seats per aircraft.