You set a budget once a year. You update a forecast every week, because it's your best current view of how the month will end. A good hotel forecast template in Excel makes that view fast to update and easy to explain. It shows what's on the books, what you expect to pick up, and how that compares with budget. This guide lays out a template for a single property and walks through a fictional month.
What the template needs
- Inputs: rooms in inventory, days in the period, out-of-order rooms, and the rooms on the books (OTB) and ADR on the books for each segment.
- Pickup: the rooms you expect to add between today and the stay date, by segment. Base it on pace history, such as same time last year or the last few comparable weeks. Don't base it on feel.
- Group block and wash: contracted blocks, rooms picked up so far, and the rooms you expect to release unsold.
- Outputs: forecast rooms sold, occupancy, ADR, RevPAR and rooms revenue, next to budget and prior year.
- A log: last week's forecast beside this week's, so you can see and explain every change.
Keep inputs on one sheet and calculate everything else from them. Never type a number over a formula. If you need an override, give it its own input cell and a note.
The core formulas
- Forecast rooms = OTB + expected pickup, by segment.
- Forecast revenue = Σ (forecast rooms × segment ADR), in Excel =SUMPRODUCT(rooms,adr).
- Occupancy = forecast rooms ÷ rooms available; ADR = revenue ÷ rooms; RevPAR = revenue ÷ rooms available.
Fictional example: Sample Inn, November
Fictional example. Sample Inn has 40 rooms and a 30-day month, so 1,200 rooms available. Two weeks out, the forecast by segment looks like this:
| Segment (fictional) | OTB rooms | Expected pickup | Forecast rooms | ADR | Forecast revenue |
|---|---|---|---|---|---|
| Transient | 420 | 180 | 600 | $139.50 | $83,700 |
| Group | 170 | 10 | 180 | $130.00 | $23,400 |
| Contract | 90 | 0 | 90 | $115.00 | $10,350 |
| Total | 870 | $135.00 | $117,450 |
Forecast occupancy is 870 ÷ 1,200 = 72.5%. ADR is $135.00 and RevPAR is $97.88. The month's budget was 900 rooms at $132.00, or $118,800, so the forecast is $1,350 under budget. Your template should show that gap and split it into rooms and rate. The hotel budget variance analysis guide covers that split step by step.
Handle group wash on purpose
Sample Inn's group segment has a block of 200 rooms. Forecasting the full block would overstate the month. This group's history says it'll use about 180 rooms, so the forecast assumes a wash of 20. Keep the block, pickup to date and expected final count in separate columns. When the cut-off date passes, you'll see whether the assumption held.
The month-end forecast
Once the month starts, switch to actual-to-date plus a forecast for the remaining days. Fictional example: by day 18, Sample Inn has sold 520 rooms for $69,850. You forecast the remaining 12 days at 350 rooms and $136.00 ADR. That gives a month-end forecast of 870 rooms and $117,450 of rooms revenue. Your daily flash report feeds the actual-to-date line, and the forecast covers the rest.
Take it past rooms revenue
Owners and asset managers want profit, not rooms revenue alone. Add a simple departmental bridge. For each department, forecast revenue, then split expenses into fixed and variable (cost per occupied room or a percentage of revenue). That gives you a forecast gross operating profit to compare with budget. Expect costs to move less than revenue. The hotel flow-through calculation guide shows how to judge that.
Checks to build in
- Forecast rooms never exceed rooms available, net of out-of-order rooms.
- RevPAR equals occupancy × ADR.
- Segment totals add to the hotel total.
- The forecast date and the OTB extract date are on the sheet.
Review forecast accuracy
After each month closes, put your forecasts from 30, 14 and 7 days out next to the actual. You're not after a perfect number. You want a forecast whose misses you understand, so the next one is better.