Projects / How many rooms a hotel can overbook, given the cancellation risk of each booking

How many rooms a hotel can overbook, given the cancellation risk of each booking

Course project, Michigan Ross MBA. Cancellation models in R on 119,390 public hotel bookings, tested on a later year. Not client or employer work.

R scriptModels and the business result

Situation	Two hotels took about 119,000 bookings in two years. 37% of the bookings were cancelled.
Complication	A cancelled booking leaves an empty room. The hotels can overbook, but with one average cancellation rate they turn away guests on the days when few bookings cancel.
Question	How many bookings can a hotel accept above capacity on each day?
Answer	A model of cancellation risk recovers 12,093 cancelled bookings in the test period and turns away 95 guests. A flat-rate rule turns away 868.

Overbooking trades empty rooms against guests with no room; a better forecast improves the two at the same time

Cost of cancellations
- Rooms left empty
  - Bookings accepted above capacityThe lever: expected cancellations less a safety margin
    How many cancellations does overbooking recover?
    77%. 12,093 of 15,745 in the test period.
  - CancellationsFixed: 37% of bookings
- Guests with no room
  - Forecast of cancellations for one dayThe lever: the risk of each booking
    Is a model better than one flat rate?
    Yes. Error of 5.2 cancellations a day against 9.2.
  - Guests turned awayThe limit
    How many guests have no room?
    95. On 23 of 486 hotel-days; 868 with the flat rate.

Lever what the decision changes Limit what the answer must respect Fixed outside the decision

Bookings recovered by overbooking

12,093

77% of the 15,745 cancellations in the test period

Guests with no room

868 with one flat cancellation rate for each hotel

Error in cancellations for one day

5.2

9.2 with the flat rate; about 84 bookings arrive each day

With the risk of each booking, the hotel overbooks about as many rooms and turns away 9 times fewer guests

Logistic regressionoff by 5.2 cancellations a day on average95guests with no room, on 23 of 486 days
12,093 bookings recovered

Decision treeoff by 6.0 cancellations a day on average107guests with no room, on 30 of 486 days
11,665 bookings recovered

Random forestoff by 4.8 cancellations a day on average208guests with no room, on 49 of 486 days
12,657 bookings recovered

Flat rate by hoteloff by 9.2 cancellations a day on average868guests with no room, on 142 of 486 days
11,695 bookings recovered

For each hotel and arrival day, the hotel overbooks by the expected number of cancellations less one standard deviation. Models were fitted on arrivals in 2015 and 2016 and tested on 40,687 bookings that arrive in 2017.

The time between booking and arrival is the clearest sign of a cancellation

0 to 7 days10%19,746 bookings

8 to 30 days28%18,960 bookings

31 to 90 days38%29,553 bookings

91 to 180 days45%26,439 bookings

More than 180 days57%24,692 bookings

Share of bookings that are cancelled, by days between booking and arrival. All 119,390 bookings; 37% are cancelled in total.

The model

Decision

How many bookings to accept above capacity, for each hotel and arrival day.

Objective

Fill the rooms that cancellations leave empty, with few guests turned away.

Method

Logistic regression, a decision tree and a random forest in R, with inputs that the hotel knows when the booking is made.

The three models rank bookings about equally well (AUC 0.84 to 0.85). The logistic regression turns away the fewest guests, so the simple model is the one to use.
Bookings with no special request are cancelled 48% of the time, against 22% with one request. A request is a sign that the guest plans to come.
Almost all bookings with a non-refundable deposit are recorded as cancelled (99%). This is a known feature of this public data set and must be checked before the model is used in a hotel.
The reservation status, the assigned room and later booking changes are not model inputs: the hotel does not know them when the booking is made.