Cancellation Risk Scorecard & Deposit Policy Simulator: score cancellation risk and test deposit terms

Full description

For revenue managers, reservations managers, front office managers and general managers who need a realistic net on-the-books and want to test deposit or cancellation terms before changing them. From your booking history the workbook measures the cancellation rate for every lead-time band, channel, rate type, length of stay, deposit status and party size, smooths small groups towards the overall rate and turns each into log-odds points (20 points double the odds). A booking's score is the sum of its six points. Score deciles calibrate score to probability with SLOPE and INTERCEPT, so overlapping factors are not counted twice. Each reservation on the books gets a probability at booking and a remaining probability from the historical cancellation-timing curve, which add up to expected cancellations, net on-the-books and wash factors for the next 60 nights. The policy simulator re-prices the history period under four sets of terms and compares retained revenue with conversion loss.

Save with a bundle: this workbook is also in the Hotel Revenue & Data Analytics Pro Bundle, 25 workbooks for $59 instead of $319. All 100 Pro workbooks are in the Pro Library for $119, and all 241 templates and workbooks are in the Ultimate Hospitality Library for $149.

What is inside

• Cancellation_Risk_Scorecard.xlsx: 11 tabs, 28,101 formulas, 5 native charts, 15 integrity checks, a Method tab with 18 calculation notes.
• History: 3,000 sample bookings (illustrative) with 1,000 spare rows; Future_OTB: 900 reservations on the books with 300 spare rows.
• Scorecard: 30 attributes across 6 factors with counts, raw and smoothed rates, log-odds and points, plus factor strength.
• Calibration: 10 score deciles, SLOPE, INTERCEPT and RSQ fit, capture of the riskiest 30%, lift and a 17-point cancellation timing curve.
• Daily_Forecast: 60 nights of rooms on the books, expected cancellations, net on-the-books, wash factor and occupancy, with 7/14/30/60-night totals.
• Policy_Simulator: 4 editable policies with coverage rule, penalty window, basis, collection, cancellation reduction and conversion loss.
• Dashboard: 7 tiles, a check tile, 2 charts and the 10 riskiest arrivals of the next 14 nights.
• Cancellation_Risk_Scorecard_Guide.docx and Cancellation_Risk_Scorecard_Guide.pdf: 8-page guide with the method, a worked example on the sample results, policy results, policy wording examples, limitations, a monthly checklist and a glossary.
• README.txt and LICENSE-and-Terms.txt.

How it works

1. Set rooms, the as-of date, risk cut-offs and the resale share on Settings.
2. Paste your booking history with outcomes and cancel dates. Points and the calibration update by themselves.
3. Paste the reservations on the books. Each gets a score, a probability at booking and a remaining probability.
4. Read expected cancellations, net on-the-books and wash factors on Daily_Forecast and the Dashboard.
5. Set four policies on Policy_Simulator and compare expected room revenue. Confirm the Checks tab shows ALL OK.

Details

• Formats: XLSX workbook, DOCX guide, PDF guide.
• Language: English.
• Software: Excel 2016 or later, or LibreOffice Calc. No macros, no add-ins, no array formulas.
• Up to 4,000 historical bookings and 1,200 reservations on the books.
• Delivery: instant download.

Good to know

• Sample data is illustrative and does not describe any hotel. Replace it with your own figures.
• Not legal or financial advice. Verify local consumer and payment rules before you change booking terms.
• Cancellation reduction and conversion loss are your estimates; measure them in a test before a full roll-out.
• Single-user license.

FAQ

How much history do I need?

At least 12 months and about 2,000 bookings, including cancelled ones with their cancel date. Small groups are pulled towards the overall rate so they do not swing the points.

Why is there a calibration step?

Factors overlap: advance-purchase rates often come with a paid deposit. Adding their points counts the same effect twice. The decile fit scales the score back to real cancellation rates, and a check keeps predicted cancellations within 10% of actual.

What is the remaining probability?

A booking that is still on the books a few days before arrival has already passed most of its risk. The timing curve from your history gives the share of risk still ahead, so the wash forecast does not overstate near-term cancellations.

Can I change the factors or bands?

Lead-time, stay and party-size bands and the channel, rate type and deposit lists are on Settings. Rename or re-cut them and every tab follows.

Does it tell me how many rooms to overbook?

It gives expected cancellations and rooms likely to wash per night. Overbooking also needs no-show rates and the cost of walking a guest, so treat it as a starting point.