Nested protection levels for hotel rates, in one Excel function

One of the business hotels I asset manage had an ad hoc rate structure. Levels set by feel, closed by instinct.

When I asked how protection levels were being calculated, the honest answer was that they were not. Nobody in the room had heard the term.

A training gap, not a failing. So — for GMs and owners, branded or independent — the concept, the rationale, the spreadsheet.

WHAT IT IS

Rank your rates, BAR 1 to BAR 4. Protect rooms for the higher rates cumulatively, never in sealed buckets.

A room protected from BAR 4 stays fully sellable at BAR 3, BAR 2 and BAR 1. Nothing is stranded. BAR 1 never closes — if every room sold at the top rate, magnificent day. BAR 2 is your historical ADR. That is the rate you defend.

WHY IT MATTERS

Discrete allocations produced the insidious spectacle of a closed discount tier beside rooms that went out empty, with no way to release them upward. Spoilage on one side, displacement on the other.

Nesting extirpates that. Every room flows to the highest willing payer.

THE METHOD — EMSR-b, one Excel function

y(j) = NORM.INV( 1 − r(j+1)/R(j), M(j), S(j) )

y is the protection level — rooms held back from every rate below. y(j) protects BAR 1 through j, cumulatively. r(j+1) is the next rate down. R(j) is the weighted average rate above. M(j), S(j): aggregated demand mean and std dev.

Booking limit for BAR (j+1) = capacity minus y(j).

BUILDING IT

Capacity in C1. Columns C to F for BAR 1 to 4. Rows 3, 4, 5: rate, demand mean, std dev — type those in. The rest is formula, written once in C, dragged right.

Row 6 =SUM($C4:C4)
Row 7 =SQRT(SUMSQ($C5:C5))
Row 8 =SUMPRODUCT($C3:C3,$C4:C4)/C6
Row 9 =D3/C8
Row 10 =NORM.INV(1-C9,C6,C7)
Row 11 in C: =MEDIAN(0,ROUND(C10,0),$C$1)
in D: =MEDIAN(C11,ROUND(D10,0),$C$1) — drag to F
Row 12 in C: =$C$1 in D: =$C$1-C11 — drag to F

The locked $C against an unlocked second reference makes it cumulative — drag it and the range stretches. That one trick does all the nesting. MEDIAN clamps protection between the level above it and capacity.

LIVE EXAMPLE — 200 keys

100 Deluxe, 70 Premier, 30 Suites.
Deluxe ladder 24,000 / 20,000 / 16,500 / 13,500. Premier +6,000. Suite +18,000.
Deluxe demand 20 / 35 / 40 / 45, sigma 9 / 14 / 16 / 18.

y(1) = NORM.INV(0.167, 20, 9.0) = 11 rooms held for BAR 1
y(2) = NORM.INV(0.231, 55, 16.6) = 43 held for BAR 1+2
y(3) = NORM.INV(0.303, 95, 23.1) = 83 held for BAR 1+2+3

Deluxe limits 100 / 89 / 57 / 17
Premier 70 / 63 / 42 / 15
Suites 30 / 25 / 18 / 10
House open 200 / 177 / 117 / 42

Thirty suites cannot ride the same curve as a hundred deluxe rooms. Rate-driven logic, applied to each type's own scarcity.

TWO CAUTIONS

Sigma moves the answer more than the mean. A lazy standard deviation hands you a confident, wrong number.

Audit your leakage first. LRA contracts, upgrades and loyalty redemptions move rooms across the ladder, outside the arithmetic.

Rate decisions stop being opinions when one Protects value, not rooms.