Every hotel with a no-show rate above zero is either leaving rooms empty or learning to overbook. The math is one division: how many reservations do you need to accept so that, after the no-shows cancel themselves out, you still fill every room? This recipe turns your historical no-show percentage into a specific number of reservations to sell, and a cushion — the extra bookings above room count that the no-show rate buys you.
120 rooms with an 8% no-show rate needs 131 reservations accepted — an 11-room cushion.
The example
Three properties with different no-show histories. The higher the no-show rate, the bigger the cushion needed to stay full.
| A | B | C | D | E | |
|---|---|---|---|---|---|
| 1 | Property | Rooms | No-show % | Sell to | Cushion |
| 2 | Main house | 120 | 8.0% | 131 | 11 |
| 3 | Annex | 80 | 5.0% | 85 | 5 |
| 4 | Extended-stay wing | 200 | 12.0% | 228 | 28 |
The formula
One division scaled up for the no-shows, then rounded up because you cannot sell a fraction of a reservation:
How it works
The trick is inverting the no-show rate into a multiplier:
1-C2is the share of accepted reservations that actually show up. An 8% no-show rate means 92% show.B2/(1-C2)asks: if only 92% of what I sell shows up, how much do I need to sell to net 120 arrivals? Dividing by 0.92 is the same as multiplying by roughly 1.087.ROUNDUP(...,0)rounds up to a whole reservation, because rounding down risks an empty room and rounding to nearest can still leave you one short half the time.- Subtract the room count (
D2-B2) to show the cushion by itself — the number that matters when a manager asks 'how many extra can we take?'
Feed this from a trailing 90-day no-show rate, not a single bad weekend, or the cushion will overreact to noise.
Try it: interactive demo
Enter the room count and the no-show rate.
Variations
Cap the cushion so a bad night stays rare
Add a MIN so the cushion never exceeds a policy ceiling, protecting you from a walked guest on the rare night everyone shows up.
Blend a weekday and weekend no-show rate
Weekend leisure bookings and weekday corporate bookings rarely no-show at the same rate. Look up the right rate by day type before applying the formula.
Pitfalls & errors
Walking a guest (turning away someone with a confirmed reservation) costs far more in refunds, comped rooms and reviews than an empty room does. Round the cushion down from what the formula suggests until you trust your no-show data.
A no-show rate measured over one holiday weekend is not a no-show rate — use at least 60–90 days of history so one anomalous night does not set your overbooking policy.
Track walks (times you overbooked and ran out of rooms) alongside empty-room nights. If walks start appearing, the cushion is too aggressive; if empty rooms persist, it is too conservative.
Practice workbook
Frequently asked questions
Is overbooking legal?
What no-show rate should I start with if I have no history?
Stop fighting formulas. Learn them in a day.
This recipe is one of hundreds of real-world formulas we teach. Our Excel Formulas & Functions class covers lookups, logic, text, and dynamic arrays hands-on — live in Dallas–Fort Worth, Houston, Austin, Oklahoma City, Denver, or online.
See the Formulas & Functions Class