Simple forecast methods — last year + pickup
In lesson 19 we built the general frame of forecasting — what it is, who it’s for, what types it has. Now let’s build one concretely. In this lesson we walk a classic, Excel-level forecast method step by step, for Hotel Peaqplus City’s November 25 (Saturday).
The goal is to put a mature, context-sensitive daily forecast in your hands — not just in theory, but walked through on numbers. This is also the foundation for understanding lesson 38 (Smart Forecast — hybrid forecasting): automation only makes sense once you understand the manual version.
The model — a three-layer Excel forecast
The classic daily forecast is built from 3 layers:
- Layer 1 (base): last year’s final, projected onto the arrival day. “Last year November 25 Saturday closed at 88%.”
- Layer 2 (speed correction): extrapolating the current pace curve to check-in. “We’re at 62% 7 days out now — the final can be projected from last year’s pace curve.”
- Layer 3 (manual corrections): the deviations known for that specific day. “This year there’s no conference like last year’s. −3 pp correction.”
The three layers combine, weighted, into a concrete forecast number. Let’s walk through it.
Layer 1: Last year’s final, projected onto the arrival day
The first step: what’s the last-year base? In lesson 18 (Same Point) we saw that last year’s calendar date isn’t necessarily the right comparable. Here we use the days-of-week aligned version.
Hotel Peaqplus City’s November 25 (Saturday) figures last year:
| Metric | Last year, November 25 (Saturday) |
|---|---|
| Occupancy (final) | 88% |
| Room-nights sold | 70 |
| Room revenue | EUR 9,240 |
| Average rate (ADR) | EUR 132 |
| RevPAR | EUR 116 |
So the “last-year base” is 88% occupancy, EUR 132 ADR. But we can’t take it over directly — several corrections are needed.
Correction 1.1: A 2- or 3-year average, where possible
A single year is noise-sensitive — a one-off event, unexpected weather, or a COVID effect can distort it. A more mature forecast rests on a 2-3 year average.
Hotel Peaqplus City’s November 25 (Saturday) figures for the last 3 years:
| Year | Occupancy | ADR | RevPAR |
|---|---|---|---|
| 3 years ago | 82% | EUR 118 | EUR 97 |
| 2 years ago | 85% | EUR 125 | EUR 106 |
| Last year | 88% | EUR 132 | EUR 116 |
| 3-year average | 85% | EUR 125 | EUR 106 |
The 3-year average is 85%, EUR 125, EUR 106 RevPAR — a touch more conservative than last year’s number.
Correction 1.2: Calendar shift
In lesson 18 we saw the calendar-shift trap. Black Friday (the last Friday of November) falls on a different date every year, so our late-November Saturday lands sometimes the weekend after Black Friday, sometimes in Black Friday week itself — and that’s a different demand pattern.
Using the days-of-week aligned logic from lesson 18, we take the comparable Saturday from each year:
| Year | The comparable Saturday’s position | Occupancy |
|---|---|---|
| 3 years ago | In Black Friday week (different pattern) | 82% |
| 2 years ago | The Saturday after Black Friday | 85% |
| Last year | The Saturday after Black Friday | 88% |
| This year (target) | The Saturday after Black Friday | — |
This year’s November 25 is the Saturday after Black Friday — which matches the position of last year and 2 years ago, but differs from 3 years ago: then the late-November Saturday fell in Black Friday week itself. For a city hotel that’s typically weaker (guests stay home to shop) — hence the 82%, the lowest of the three. Since it’s not comparable to this year’s “Saturday after Black Friday,” we drop the 3-years-ago value and lean on the two genuinely comparable years:
Layer 1 base (2-year comparable average): (88 + 85) / 2 = 86.5% occupancy, (132 + 125) / 2 = EUR 128.5 ADR.
Layer 2: Pickup-trend extrapolation
Layer 1 gives only the “last-year base” — it doesn’t account for the current pace situation. That’s where Layer 2 comes in: we project occupancy from the pace curve to check-in. Two classic methods:
Method A: Proportional pace extrapolation
We look at how far ahead we stand versus the 2-year average at the same-point, and project that proportionally onto the final.
| Metric | This year (7 days out) | 2-year average (7 days out) | Difference |
|---|---|---|---|
| OTB occupancy | 62% | 58% | +4 pp (+6.9%) |
The extrapolation: 86.5% (Layer 1 base) × 1.069 (pace lead) = 92.5%.
This is a linear extrapolation — it assumes the pace lead stays proportional to check-in. That isn’t always true, but as a first approximation it’s fine.
Method B: Incremental pickup projection
The finer method: instead of extrapolating proportionally, we estimate the remaining pickup.
- 2-year average pickup between day 7 and day 0: 58% → 86.5% = +28.5 pp pickup.
- This year’s pace lead: +4 pp.
- Expected pickup over the next 7 days: 28.5 + 4 (lead carried forward) = +32.5 pp.
Expected final: 62% + 32.5 pp = 94.5%.
The two methods give slightly different values (92.5% vs. 94.5%). A more mature forecast takes the (weighted) average of the two.
Layer 1+2 combined: ~93% occupancy.
The ADR Layer 2 correction
The same methods apply to ADR. Suppose the transient leisure direct segment comes in more strongly in this year’s pace — that brings a higher ADR. The Layer 1 base ADR is EUR 128.5. Based on the current segment mix, transient leisure direct stands at 20% versus 15% (the 2-year average). The larger share of the higher-ADR segment yields +EUR 3 of ADR:
Layer 1+2 ADR forecast: EUR 131.5.
Layer 3: Manual corrections
The two previous layers are statistical — they extrapolate from past data. Layer 3 is context-sensitive: it corrects the forecast off concrete knowledge for that specific day.
A few classic Layer 3 corrections:
Correction 3.1: Event difference
This year there’s no international conference like last year’s, which added +8 pp pickup to the Tuesday-Thursday days. November 25 Saturday is not directly affected, but the Friday will be quieter, and less spillover demand carries over to the Saturday. Correction: −1 pp occupancy, −EUR 2 ADR (the conference guest’s higher spend is missing).
Correction 3.2: A new event
This year there is a major arena concert on this Saturday — the same mechanic as the concert weekend we discovered in lesson 12. That means +5 pp pickup for the Saturday night. Correction: +5 pp occupancy, +EUR 15 ADR (we can sell to the concert guests at a higher rate).
Correction 3.3: A group contract
The sales team just signed an 8-room MICE group contract for November 23-26. That booking has already shown up in the current OTB, so it’s already in the Layer 2 pace numbers. Correction: 0 — don’t add it again (or we’d double-count).
Correction 3.4: The weather forecast
Strong cold is expected next weekend (−5 °C). For a city-centre 4-star that slightly dampens the city-break leisure demand. Correction: −1 pp occupancy.
The Layer 3 summary
| Correction | Occupancy | ADR |
|---|---|---|
| 3.1 Conference absence (vs. last year) | −1 pp | −EUR 2 |
| 3.2 Arena concert | +5 pp | +EUR 15 |
| 3.3 New MICE group contract | 0 pp (already in) | EUR 0 (already in) |
| 3.4 Weather (cold) | −1 pp | EUR 0 |
| Layer 3 net correction | +3 pp | +EUR 13 |
The final forecast
The 3 layers combined:
| Layer | Occupancy | ADR | RevPAR |
|---|---|---|---|
| Layer 1 (2-year average, calendar-adjusted) | 86.5% | EUR 128.5 | EUR 111 |
| Layer 2 (pace extrapolation) | 93% | EUR 131.5 | EUR 122 |
| Layer 3 (manual corrections) | +3 pp | +EUR 13 | — |
| Final forecast | 96% | EUR 144.5 | EUR 139 |
This is the manual forecast: 96% occupancy, EUR 144.5 ADR, EUR 139 RevPAR.
As we saw in lesson 19, a mature forecast gives a confidence range:
- Occupancy: 96% ± 4 pp (92-100%)
- ADR: EUR 144.5 ± 6 (EUR 138-151)
- RevPAR: EUR 139 ± 12 (EUR 127-151)
The range is the measure of uncertainty — the arena concert’s impact isn’t fully known, the weather forecast’s accuracy is limited, and the manual corrections are estimates.
The method’s limits
The method above is an introduction, not a mature forecast. A few limits:
Limit 1: Linear extrapolation
Layer 2 assumes the pace lead is proportional. In reality the pickup curve is not linear — on an event-peak day the last 3 days’ pickup changes more dramatically. In lesson 37 (Booking curve) we cover finer models.
Limit 2: A static segment mix
The model worked at the whole-hotel level. A mature forecast builds at the segment level — every segment has its own pace curve and its own ADR pattern.
Limit 3: Time-intensive
A single Saturday night takes 20-30 minutes of manual work. The full 30-day forecast is a 15-20-hour monthly task. That doesn’t scale for an RM managing 5-10 hotels.
Limit 4: Reactive, not predictive
The model extrapolates from the past. A sudden demand change (an unexpected event, a pandemic wave, an economic shock) it doesn’t predict — it only adjusts afterward.
These limits are solved by lesson 38 (Smart Forecast) and lesson 55 (Smart Forecast Enhanced) with AI-based, multi-layer forecast models.
The Peaqplus Forecast module as automation
The Peaqplus Forecast module runs this 3-layer model automatically. A few concrete automation points:
- Layer 1 (last-year base) — the module aligns the comparable-date automatically (days-of-week alignment, calendar shift).
- Layer 2 (pace extrapolation) — the module computes and extrapolates the pace lead daily.
- Layer 3 (context) — the module takes the known events from the event calendar automatically.
- 3-year average, 2-year weighting — the module works with automatic corrections (filtering out COVID years, too-old data).
- Confidence range — every forecast number is also available as a range.
A mature RM doesn’t forecast manually. Peaqplus runs the 3-layer model for every day, and the RM reviews and adjusts the context-based decisions (e.g. an arena concert the system doesn’t yet know about).
But the ability to forecast manually is invaluable — a mature RM knows what the system is doing and when to question the automated forecast. That’s the main theme of lesson 38.
Key takeaways
- The classic 3-layer forecast: Layer 1 (last-year base, comparable-date aligned), Layer 2 (pace extrapolation), Layer 3 (manual corrections).
- The 2-3 year average is more robust than a single year, and filtering out the calendar shift is critical.
- Pace extrapolation has two methods: proportional vs. incremental pickup projection. A mature forecast takes the average of the two.
- Layer 3 is the context-sensitive corrections layer — events, time-shift, group contracts, weather. Judgement-based, the part that needs the RM’s expertise.
- The method is time-intensive and linear — a mature RMS (Peaqplus Forecast) automates it. But understanding the manual model is needed to audit the system.
Click an answer — you see immediately whether it is right.
Answer all of them and the lesson counts as complete — and toward your progress.
A hotel's December 31 (New Year's Eve) forecast: a 2-year average of 96% occupancy, EUR 280 ADR. This year, 30 days out, you stand at 78%; the 2-year average stood at 70% 30 days out. This year there's no MLOS in place; last year there was. What 3-layer (Layer 1 / 2 / 3) forecast analysis would you do, and what would you watch for in Layer 3? And: your manual forecast says November 25 closes at 96%, but the Peaqplus Smart Forecast estimates 88% — which do you trust, and what questions do you ask to find the source of the 8 pp gap?
- The 3-layer manual forecast is one of the foundations of classic revenue management training — almost every RM course starts here. Automated RMS tools implement the same logic, just faster and at the segment level.
The Revenue Management Handbook, Vol. II — the 17 lessons condensed, plus a pickup tracker spreadsheet, a self-audit and a variance mini-sheet. Delivered by email.