InventoryAllocation

Calculating Overbooking Buffers from Historical No-Show Rates

A room reserved for tonight is not the same as a room occupied tonight, and the gap between the two — guests who cancel late or simply never arrive — is money a property leaves on the table unless it deliberately oversells to cover it. This page derives a defensible overbooking buffer per room type and date from historical no-show and cancellation behaviour, as the statistical companion to the overbooking prevention and inventory buffers guide that consumes the buffer table this job produces. The wrong buffer costs real money in both directions: too small and you sell out with empty rooms at check-out; too large and you walk a guest and eat the compensation.

The method is expected-value sizing. For a given room type and date, you know roughly what fraction of committed reservations will not materialize. Overbook by fewer rooms than that expected no-show count and you are being conservative; overbook by more and you take on walk risk. The right buffer is the point where the marginal expected revenue of filling one more no-show seat equals the marginal expected cost of walking a guest — and that point depends on this room type’s own history, not a portfolio-wide average.

Prerequisites and environment

This job runs as a scheduled batch over historical reservations and writes a buffer table keyed by (room_type_code, stay_date) that the availability recalculation reads. Pin these so the arithmetic is reproducible across runs:

The economic inputs — average recoverable revenue per room and the all-in walk cost — should come from finance, not be guessed. Walk cost is not just the competitor’s room rate; it includes transport, a goodwill gesture, and the lifetime-value hit of a soured guest, which is why it dwarfs the nightly rate for premium room types.

Expected value curve balancing empty-room cost against walk cost to pick a buffer A horizontal axis represents the overbooking buffer size, from zero rooms on the left to aggressive on the right. Two cost curves cross. The expected empty-room cost falls as the buffer grows, shown descending from upper left. The expected walk cost rises as the buffer grows, shown ascending to upper right. Their sum, total expected cost, is a U-shaped curve with a minimum at the crossover point, marked as the optimal buffer. Turquoise marks the empty-room cost, pink marks the walk cost, and violet marks the optimal buffer at the minimum of the total curve. Sizing the buffer at the minimum of total expected cost Empty-room cost falls as you overbook more; walk cost rises; the sum has one minimum. buffer size (rooms overbooked) → expected cost → empty-room cost walk cost total expected cost optimal buffer
Figure 1: The buffer is sized at the minimum of total expected cost, where the falling empty-room cost and the rising walk cost balance.

Step-by-step implementation

The pipeline is four passes: measure the historical no-show rate per segment, translate it into an expected no-show count for a date’s commitments, convert that into a buffer via the expected-value trade-off, and attach a confidence band so the buffer shrinks when the sample is thin.

Step 1 — Measure the no-show rate per room type and segment

Aggregate terminal reservation outcomes into a no-show probability, segmented by room_type_code and market_segment because a non-refundable corporate booking behaves nothing like a flexible leisure one. Count late cancellations that free the room too late to resell as no-shows for buffer purposes.

python
import polars as pl

def no_show_rates(history: pl.DataFrame) -> pl.DataFrame:
    # history: room_type_code, market_segment, status in {arrived, no_show, cancelled_late}
    return (
        history
        .with_columns(
            # a late cancel is functionally a no-show: the room could not be resold
            (pl.col("status").is_in(["no_show", "cancelled_late"]))
            .alias("is_noshow")
        )
        .group_by(["room_type_code", "market_segment"])
        .agg(
            pl.len().alias("n"),
            pl.col("is_noshow").mean().alias("noshow_rate"),
        )
    )

Grouping by market_segment as well as room_type_code is the choice that makes the buffer honest: a flat property-wide rate over-buffers non-refundable inventory (which rarely no-shows) and under-buffers flexible OTA inventory (which frequently does), producing walks on exactly the dates you most wanted to protect.

Step 2 — Project the expected no-show count for a specific date

For a date’s actual commitment mix, the expected number of rooms that will free up is the sum over each segment of its committed rooms times its no-show rate. This turns a probability into a room count you can act on.

python
def expected_noshows(commitments: pl.DataFrame, rates: pl.DataFrame) -> pl.DataFrame:
    # commitments: room_type_code, market_segment, stay_date, committed
    return (
        commitments
        .join(rates, on=["room_type_code", "market_segment"], how="left")
        .with_columns(pl.col("noshow_rate").fill_null(0.0))  # unseen segment → assume shows
        .with_columns(
            (pl.col("committed") * pl.col("noshow_rate")).alias("expected_noshow")
        )
        .group_by(["room_type_code", "stay_date"])
        .agg(pl.col("expected_noshow").sum().alias("expected_noshow"))
    )

Filling an unseen segment’s rate with 0.0 rather than the global average is the safe default: a segment you have never observed no-showing is assumed to arrive, so a novel booking mix errs toward under-overbooking (empty rooms) rather than over-overbooking (a walk).

Step 3 — Convert expected no-shows into a buffer via the cost trade-off

The buffer is not simply the expected no-show count — it is scaled by the ratio of the revenue you recover per filled room to the walk cost you risk. When walk cost dwarfs recoverable revenue (suites), the buffer shrinks below the expected no-show count; when they are comparable (commodity rooms), it approaches it.

python
from dataclasses import dataclass

@dataclass(frozen=True)
class RoomEconomics:
    recover_value: float   # expected revenue from filling one no-show seat
    walk_cost: float       # all-in cost of walking one guest (rate + transport + goodwill)

def buffer_from_expected(expected: pl.DataFrame,
                         econ: dict[str, RoomEconomics]) -> pl.DataFrame:
    # critical ratio: how much of the expected no-show gap is worth capturing
    def ratio(rtc: str) -> float:
        e = econ[rtc]
        return e.recover_value / (e.recover_value + e.walk_cost)
    return expected.with_columns(
        pl.struct(["room_type_code", "expected_noshow"]).map_elements(
            lambda r: r["expected_noshow"] * ratio(r["room_type_code"]),
            return_dtype=pl.Float64,
        ).alias("raw_buffer")
    )

The critical ratio recover_value / (recover_value + walk_cost) is the newsvendor service level applied to overbooking: it caps the buffer at a fraction of the expected no-shows so a room type with a punishing walk cost never overbooks all the way to its statistical no-show count.

Step 4 — Apply a confidence band and round down safely

A no-show rate measured over fifty reservations is far less trustworthy than one over five thousand. Shrink the buffer toward zero when the sample is small using the lower bound of a normal-approximation interval, then round down so uncertainty always costs you an empty room rather than a walk.

python
import math

def apply_confidence(df: pl.DataFrame, rates: pl.DataFrame,
                    z: float = 1.28) -> pl.DataFrame:  # ~80% one-sided
    sample = rates.group_by("room_type_code").agg(pl.col("n").sum().alias("n"))
    return (
        df.join(sample, on="room_type_code", how="left")
        .with_columns(
            # widen the discount as n shrinks: buffer * (1 - z/sqrt(n))
            (pl.col("raw_buffer") *
             (1 - z / pl.col("n").cast(pl.Float64).sqrt()).clip(0.0, 1.0))
            .alias("buffer_lb")
        )
        .with_columns(
            pl.col("buffer_lb").floor().cast(pl.Int64).clip(0, None).alias("buffer_reserve")
        )
        .select(["room_type_code", "stay_date", "buffer_reserve"])
    )

Rounding with floor() rather than round() is the asymmetry the whole job hinges on: the cost of an empty room is bounded and recoverable, while the cost of a walk is large and reputational, so every rounding decision should resolve in favour of selling one fewer room.

Gotchas and production notes

Verification snippet

Confirm the two behaviours that keep the buffer safe: that a punishing walk cost shrinks the buffer below the raw expected no-show count, and that a thin sample shrinks it further toward zero.

python
import polars as pl

def test_high_walk_cost_shrinks_buffer():
    expected = pl.DataFrame({"room_type_code": ["SUITE_EXEC"],
                             "stay_date": ["2026-08-14"], "expected_noshow": [4.0]})
    econ = {"SUITE_EXEC": RoomEconomics(recover_value=300.0, walk_cost=1500.0)}
    out = buffer_from_expected(expected, econ)
    # ratio = 300 / 1800 ≈ 0.167 → raw_buffer well below 4 expected no-shows
    assert out["raw_buffer"][0] < 1.0

def test_thin_sample_floors_to_conservative_buffer():
    df = pl.DataFrame({"room_type_code": ["DLX_KING"],
                       "stay_date": ["2026-08-14"], "raw_buffer": [3.0]})
    rates = pl.DataFrame({"room_type_code": ["DLX_KING"], "n": [16]})  # tiny sample
    out = apply_confidence(df, rates)
    # 1 - 1.28/sqrt(16) = 1 - 0.32 = 0.68 → 3.0 * 0.68 = 2.04 → floor → 2
    assert out["buffer_reserve"][0] == 2

test_high_walk_cost_shrinks_buffer()
test_thin_sample_floors_to_conservative_buffer()

The first test proves the economics actually bind — a suite with a 1,500-unit walk cost must not overbook to its full statistical no-show count — and the second proves the confidence band does its job, pulling a buffer derived from only sixteen observations down to a level you can defend. In production, also assert that every emitted buffer_reserve is a non-negative integer no larger than the room type’s physical count before it reaches the availability worker.

← Back to Overbooking Prevention & Inventory Buffers