Hotel Overbooking & No-Show Calculator: Overbooking Level Spreadsheet for Hotels

Full description

For revenue managers, front office managers and general managers who want to overbook with numbers instead of habit. You log arrivals due, no-shows and late cancellations by night and segment, and enter what an empty room and a walked guest really cost you. The workbook works out the average wash and its spread for each weekday, recommends an overbooking level for each of the next 28 nights at the risk you choose and at the cost balance point, and tells you how many rooms you can still sell. A results log then shows walks, empty rooms and the net gain from overbooking.

Save with a bundle: this template is also in the Hotel Sales, Marketing & Revenue Bundle, 14 templates for $39 instead of $100. All 141 templates are in the All-Access Hospitality Template Library for $79.

What is inside

• Hotel_Overbooking_No_Show_Calculator.xlsx: 9 tabs, 17,174 formulas, 3 native charts, 7 drop-down or number rules. No macros.
• Settings: hotel rooms, acceptable chance of walking a guest, method (lower of the two, chosen risk or cost balance), policy cap as % of rooms, late cancel cut-off, history window, first decision night and 5 segments.
• History_Log: room for 2,500 night-by-segment rows (811 sample rows covering 182 nights). Wash and wash % calculate.
• Nightly_History: adds the log up for up to 371 nights in the history window.
• Wash_Analysis: no-show %, late cancel %, wash % and spread for each of the 7 weekdays, a weekday-by-segment wash grid and a summary for each segment.
• Cost_Model: cost of an empty room (3 inputs) and of walking a guest (6 inputs), the cost balance ratio, and expected walks, empty rooms and cost for overbooking levels 0 to 15 with a chart.
• Nightly_Decision: 28 upcoming nights with expected wash, spread, level at your risk, level at the cost balance, recommended overbooking, sell-up-to limit and an action.
• Results_Log: room for 400 nights (56 sample nights) with overbooked rooms, wash, guests walked, empty rooms, costs, net gain and outcome.
• Dashboard: 8 KPI tiles, an outcome count, wash % by weekday and the recommended level for the next 28 nights.
• README.txt and LICENSE-and-Terms.txt.

How it works

1. Enter rooms, the walk risk you accept, the method and your policy cap on Settings.
2. Log at least 8 weeks of arrivals due, no-shows and late cancellations by segment.
3. Enter your own costs for an empty room and a walked guest on Cost_Model.
4. Each day, update rooms available, stayovers and arrivals on Nightly_Decision and follow the Action column.
5. Record the outcome on Results_Log the next morning and review the Dashboard each month.

Details

• Format: Excel workbook (.xlsx). No macros.
• Language: English. Currency is a label you choose.
• Software: Excel 2016 or later, or a spreadsheet program that opens .xlsx files, such as Google Sheets.
• Delivery: instant digital download. Nothing is shipped.

Good to know

• All sample history, forecasts, costs and results are illustrative. Replace them with your own data.
• The levels assume the night-to-night wash follows a normal curve. Use judgement on event nights, big groups and bad weather.
• Overbooking and walk rules differ by location. Check your policy against local consumer rules. This is a decision aid, not legal advice.
• Single-user license. Do not share or resell the files.

FAQ

What is the wash?

No-shows plus late cancellations, meaning reservations cancelled after your cut-off time. These are the rooms that overbooking can refill.

How much history do I need?

At least 8 nights for each weekday. Wash_Analysis marks a weekday red when it has fewer.

Why are there two overbooking levels?

One keeps the chance of walking a guest at or below the risk you choose. The other balances the cost of a walk against the cost of an empty room. By default the workbook uses the lower of the two, and never more than your policy cap.

Does it work in Google Sheets?

It opens in Google Sheets and the formulas calculate. Check the drop-downs after import.

Is this a physical product?

No. It is a digital download. Refund terms follow the store policy at the time of purchase.