Projects / How many rooms to hold back for late guests who pay more
How many rooms to hold back for late guests who pay more
Course project, Michigan Ross MBA. Monte Carlo simulation of guest arrivals, in Excel and Python. Not client or employer work.
| Situation | A lodge has 10 cabins and sells them at $100 a night. About 100 guests ask for a cabin for each night. |
|---|---|
| Complication | Most guests are leisure travelers who book early. A few business guests come late and will pay about three times more, but by then the cabins are sold. |
| Question | How many cabins must the lodge hold back for late guests, and at what rate? |
| Answer | Hold 4 of 10 cabins for a full rate of $260. Revenue per night rises 39% against one low rate for all cabins. |
Revenue comes from two rates; the cabins held back and the full rate are the two levers
- Revenue per night$1,388 in the answer
- Cabins sold at the low rate
- Cabins open at $100The lever: 10 less the cabins held back
How many cabins to hold back?
4. Revenue is highest with 4, and within 1% with 5.
- Leisure guestsFixed: 95% of guests, who pay $100 on average
- Cabins sold at the full rate
- Full rateThe lever
Which rate earns the most?
$260. Above $300, few business guests will pay.
- Business guestsFixed: 5% of guests, who pay $300 on average
- Held cabins that stay emptyThe limit: the cost of the wait
How many stay empty?
1.0 of 10. On an average night, at the best policy.
Lever what the decision changes Limit what the answer must respect Fixed outside the decision
Best policy
Hold 4, at $260
the last 4 of 10 cabins sell only at the full rate
Revenue per night
$1,388
+39% against one rate of $100 for all cabins ($1,000)
Cabins that stay empty
1.0 of 10
on an average night: the cost of the wait for guests who pay more
Revenue is highest when 4 cabins are held for a full rate near $260
| Held | $160 | $180 | $200 | $220 | $240 | $260 | $280 | $300 | $320 | $340 | $360 |
|---|---|---|---|---|---|---|---|---|---|---|---|
| 0 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 | 1,000 |
| 1 | 1,059 | 1,077 | 1,097 | 1,116 | 1,134 | 1,151 | 1,162 | 1,162 | 1,138 | 1,083 | 1,011 |
| 2 | 1,112 | 1,145 | 1,181 | 1,218 | 1,250 | 1,274 | 1,280 | 1,252 | 1,170 | 1,048 | 932 |
| 3 | 1,154 | 1,193 | 1,244 | 1,293 | 1,333 | 1,355 | 1,340 | 1,268 | 1,130 | 969 | 838 |
| 4 | 1,177 | 1,213 | 1,275 | 1,333 | 1,376 | 1,388 | 1,345 | 1,232 | 1,056 | 878 | 741 |
| 5 | 1,178 | 1,206 | 1,274 | 1,337 | 1,380 | 1,379 | 1,309 | 1,166 | 971 | 784 | 645 |
| 6 | 1,156 | 1,173 | 1,245 | 1,311 | 1,351 | 1,337 | 1,248 | 1,088 | 883 | 691 | 549 |
| 7 | 1,114 | 1,121 | 1,195 | 1,263 | 1,300 | 1,277 | 1,176 | 1,005 | 793 | 597 | 452 |
| 8 | 1,057 | 1,055 | 1,132 | 1,201 | 1,235 | 1,206 | 1,098 | 920 | 704 | 503 | 355 |
| 9 | 988 | 980 | 1,058 | 1,128 | 1,162 | 1,130 | 1,017 | 834 | 613 | 410 | 258 |
| 10 | 913 | 902 | 981 | 1,052 | 1,086 | 1,052 | 935 | 749 | 524 | 316 | 161 |
Each night 100 guests ask for a cabin, one at a time. 95% are leisure guests who pay $100 on average (standard deviation $25). 5% are business guests who pay $300 on average (standard deviation $45). A guest books when the rate is at or below what the guest will pay.
The model
Decision
How many of the 10 cabins to hold back, and the full rate for those cabins.
Objective
The highest average revenue per night.
Method
Monte Carlo simulation of the guests of one night, for each pair of protection level and full rate.
- A policy of 3 cabins at $220 earns $1,294. The best policy earns 7% more.
- The top of the table is flat: 4 or 5 cabins at $240 or $260 are all within 1% of the best policy. An exact value is not necessary.
- A full rate above $300 loses money quickly, because few business guests will pay it and the held cabins stay empty.
- The result depends on the 5% share of business guests. The lodge must measure that share before it changes the policy.