Manaz Afzal
Hospitality · Revenue management

Cancellations forecast night by night, and a resale level the revenue manager can approve

A dashboard for two hotels that lose a third of their bookings to cancellation. Built and tested on 119,390 real bookings.

37%of 119,390 bookings cancelled
0.87AUC, against 0.71 for the rule of thumb
±5.9rooms average miss per night; flat rate ±9.2
421guests walked at the best level; flat rate 1,068

Executive summary

Cancellations forecast night by night within 5.9 rooms, against 9.2 for a flat rate, and a resale level that earned €1.52M with 421 walked guests against €1.21M and 1,068.

0.87AUC on 2017 bookings, against 0.71 for the channel and deposit rule
±5.9rooms average miss per night; flat rate ±9.2
421guests walked at the best resale level; best flat level 1,068

Situation

Two Portuguese hotels lose about a third of bookings to cancellation, most through online travel agents. Overbooking is set by hand from one historical rate.

Approach

Each booking scored for cancellation risk, trained before 2017 and tested on 40,619 arrivals in 2017; scores summed into rooms freed per night and a resale level tested on past seasons.

Result

The forecast misses by 5.9 rooms a night against 9.2. The suggested level earns more and walks fewer guests than any flat level. Late, firm-looking cancellations remain the main miss.

The business problem

A hotel revenue manager decides each night how far to overbook and what rate to hold for the rooms that will come back. Cancellations differ by segment: group blocks, online travel agency bookings on free cancellation and direct bookings behave very differently. Revenue systems such as IDeaS and Duetto forecast cancellations by segment for this reason, and properties that act on those forecasts resell rooms that would otherwise go empty.

I measured how well a booking-level cancellation score, built from what the hotel knows at booking, sets an overbooking level night by night against a flat rate, and how many guests each plan would have walked.

Team
One revenue manager, front-office manager, reservations staff
Scale
About 150 to 250 bookings a day across channels
Systems
Opera, Mews or Cloudbeds PMS, a channel manager, IDeaS, Duetto or a spreadsheet forecast
Constraint
Overbooking and walk policy set by management; guest data under the Privacy Act or GDPR

What was built

A nightly job that reads new and changed bookings from the property-management system, scores each for cancellation risk, sums the scores into expected cancellations per night, and suggests a resale level that maximised net revenue on past seasons. The revenue manager sees the high-risk list, the forecast and the suggested level, and decides. Deposit requests or reminder emails can then go to high-risk bookings only.

PMS exportNew and changedbookings, nightlyScoreCancellation riskper bookingForecastRooms freed pernight, next 60 daysResale levelSuggested overbookingfor approvalActionsDeposit requests andreminders to high risk
Nightly workflow. The revenue manager approves the level; nothing changes in the channel manager without that.

Results

025507507-0207-1708-0108-1608-31Flat rateActualForecast
Last 61 nights of the test year for the city hotel; the flat rate applies one historical cancellation rate to each night’s bookings. Across 486 hotel-nights the model's average miss was 5.9 rooms against 9.2 for a flat rate. Data: Antonio, Almeida and Nunes (2019), Hotel booking demand, Data in Brief
Forecast of rooms freed per nightAverage miss (rooms)
Model (sum of booking scores)5.9 (95% CI 5.4–6.3)
Rule of thumb by channel6.0
Flat historical rate9.2
€0.0M€0.5M€1.0M€1.5M0.1×0.4×0.7×1.0×1.3×With the modelFlat rateBest with model: 1.05×€1.52M, 421 walkedResale level (times expected cancellations)
Selling 1.05 times the expected cancellations each night earned €1.52M with 421 walked guests; the best flat level earned €1.21M with 1,068 walked. A walked guest is costed at twice the nightly rate.

Who cancels

Online TA56,408 bookings37%Offline TA/TO24,181 bookings34%Groups19,790 bookings61%Direct12,582 bookings15%Corporate5,282 bookings19%Complementary728 bookings12%Non-refundable deposit14,586 bookings99%All bookings 37%
Group bookings cancel at 61% and non-refundable deposits at 99%; the latter reflects agents booking speculative blocks, not guests who paid.
0%20%40%60%80%0–78–3031–6061–9091–180181–270271–365365+City hotelResort hotelLead time, days
Risk climbs with lead time in both hotels; same-week bookings rarely cancel.

Ranking quality

Score tenth 10100%Score tenth 973%Score tenth 862%Score tenth 752%Score tenth 638%Score tenth 529%Score tenth 419%Score tenth 310%Score tenth 24%Score tenth 11%Base rate
The riskiest tenth of 2017 bookings cancelled at almost 100%, the safest at 1%.
MethodAUC (95% CI)PR-AUCBrier
Rule: segment × deposit history0.707 (0.702–0.712)0.6260.187
Logistic regression0.871 (0.868–0.875)0.8250.146
Gradient boosting0.887 (0.884–0.891)0.8440.142

Where it misses: 6,942 cancellations scored low. Their median lead time was 74 days against 151 for those caught, and 61% had made a special request, which usually signals a firm booking.

Data and method

Source
Antonio, Almeida and Nunes (2019), Hotel booking demand datasets, Data in Brief 22
Size
119,390 bookings, a city and a resort hotel in Portugal, arrivals 2015-07-01 to 2017-08-31
Split
Train on 78,589 arrivals before 2017; test on 40,619 arriving in 2017 (15,734 cancelled)
Audit
31,994 exact duplicates, 180 zero-guest bookings, 1,959 zero-rate stays; reported and handled

The rule of thumb scores bookings by channel and the guest's deposit history. Logistic regression was chosen over gradient boosting on validation. The reservation-status field gives the answer away and was removed; with other booking-time leaks AUC would read 0.895, and with a random split 0.952. The reported figures use neither.

Fairness

Country of origin lifts AUC from 0.852 to 0.887. Domestic (Portuguese) bookings cancel at 57% against 17% to 35% for other markets. A hotel may prefer to drop country and accept the lower ranking quality rather than treat guests differently by nationality; deposit rules based on nationality would also raise discrimination concerns.

Assumptions and limitations

What a hotel would do next

Technical depth

The nightly forecast is the sum of booking probabilities for each arrival night, so it updates as bookings change. Resale levels are tested by replaying the test year night by night. Calibration matters here because the probabilities are summed; logistic regression is better calibrated than the boosted model on this data.

Analysis code (Python)

Financial impact

About €310,000 more room revenue with 647 fewer walked guests

Measured on the test year for the city hotel: the resale level from the booking scores against the best flat overbooking level.

InputValueSource
Net revenue at the score-based level€1.52MMeasured test period
Net revenue at the best flat level€1.21MMeasured
Walked guests: score / flat421 / 1,068Measured
Cost of a walked guesttwice the nightly rate relocation and goodwill

Calculation

  1. Revenue difference€310,000
  2. Walked guests avoided647
  3. Result€310,000 and 647 fewer walks

Gain over a flat overbooking level€310,000

The walk cost is assumed; each hotel sets its own and the level is re-tested on its own seasons.

Implementation and rollout

Six weeks from PMS export to a nightly resale level the revenue manager approves

  1. Weeks 1 to 2

    Connect

    Export two years of bookings and cancellations from Cloudbeds, Mews or Opera.

    Revenue manager, Manaz

  2. Week 3

    Backtest

    Refit, check per-night forecast and resale level on the last season.

    Manaz

  3. Weeks 4 to 6

    Run alongside

    Nightly dashboard beside the current spreadsheet; the manager compares and decides.

    Revenue manager

  4. Then

    Act on risk

    Deposit requests or reminders to high-risk bookings only, if the forecast holds.

    Front office, Manaz

Tools

Cloudbeds, Mews, Opera or RMS exports, Channel manager (read-only), Python logistic regression, scored nightly, Looker Studio or Power BI dashboard

Team

  • Revenue manager approves the resale level every night
  • Front-office manager owns walk policy and guest contact
  • Manaz pipeline, model, dashboard and monthly review

The timeline is a plan for a typical engagement; it depends on data access and how many systems are involved.

Risks and mitigations

What could go wrong in production, and the control for each

RiskMitigation
Walked guests on a night the forecast missesLevel capped by management; manager approval required each night.
Non-refundable blocks from agents distort the scoreAgent blocks flagged separately and reviewed by the manager.
Fairness: scores leaning on guest countryCountry left out of pricing actions; checked in the fairness review.
Season shift after trainingMonthly refit and per-night error tracked on the dashboard.

Key takeaways

Key takeaways

  • Lead time, deposit type and channel carry most of the risk.
  • The reservation-status field gives the answer away and was removed.
  • Logistic regression was chosen over gradient boosting on validation, for a small loss and clearer reasons.
  • Missed cancellations are late, firm-looking bookings; no booking-level model will see those.