Hotel forecasting: occupancy, room rates and revenue

Hotel forecasting estimates room revenue from expected room nights sold and the rate earned on those nights. Start with available inventory, forecast occupancy and average daily rate, then compare the saved forecast with actual stay-date results. The useful output explains the difference in dollars and shows which operating assumption changed.

Start with the room-night equation

Available room nights = Sellable rooms × Nights in the period

Forecast room revenue = Available room nights × Forecast occupancy × Forecast average daily rate

Occupancy is rooms sold divided by available room nights. Average daily rate, or ADR, is room revenue divided by room nights sold; revenue per available room, or RevPAR, divides room revenue by available room nights. These are the definitions used in Hilton's annual report.

Keep the forecast limited to room revenue first. Food, parking and other revenue need their own drivers. A hotel's financial ratios help interpret the result, but the forecast review must first establish what sold and at what rate.

Prepare the exports on one basis

Take daily room inventory and room-revenue results from the property management export. Keep property, stay date, demand segment, rooms available, paid room nights sold and room revenue. Bring a separate forecast with the same keys, a version date and the occupancy and rate assumptions.

Use the stay date for the revenue comparison. A reservation made this month for a later stay belongs in the later stay's demand forecast. Keep reservation creation dates only for explaining booking pace. For the commercial review of that pace, see pace per property.

Document how the property treats unavailable rooms, complimentary stays, taxes, refunds and package allocations. The forecast and actual must follow the same policy. Also keep the original budget separate from the latest forecast: one records the approved plan, while the other reflects updated expectations.

Build a forecast from occupancy and rate

A fictional US hotel has 80 sellable rooms throughout a 30-night planning period. For a compact example, management groups 22 nights as weekdays and eight as weekends. The production workbook would retain individual dates and relevant segments.

Put these forecast columns in A:H, with the two day types on rows 2 and 3. Percentage inputs are stored as percentages, and revenue is in USD.

In Microsoft Excel, enter these formulas in D2, F2 and H2 respectively, then fill down:

=B2*C2
=D2*E2
=F2*G2

Calculate the total occupancy and ADR from their denominators:

=SUM(F2:F3)/SUM(D2:D3)
=SUM(H2:H3)/SUM(F2:F3)

The forecast is $303,200, with occupancy of 74.0% and ADR of $170.72. ADR and RevPAR are displayed to two decimals, occupancy to one decimal; calculations retain full precision. Never average the two ADR inputs, because weekday and weekend room-night volumes differ.

Compare actuals and prove the revenue bridge

Add actual sold nights in J, actual ADR in K and actual revenue in L. The hotel sells the following room nights on the same available inventory:

Calculate actual revenue and the two effects by entering these formulas in L2, M2 and N2 respectively:

=J2*K2
=(J2-F2)*G2
=J2*(K2-G2)

This bridge values extra room nights at forecast ADR, then values the rate difference on actual room nights. That ordering includes the interaction once. The identity is $303,200 + $10,800 - $3,600 = $310,400, so the entire $7,200 favorable revenue variance is explained.

A second revenue check is:

=SUMPRODUCT(J2:J3,K2:K3)-SUM(L2:L3)

It must return zero. Microsoft's SUMPRODUCT documentation confirms that the function multiplies corresponding ranges and sums their products. Covirage is not affiliated with Microsoft or Hilton.

Actual occupancy is 1,840 / 2,400 = 76.7%, while actual ADR is $168.70. Room revenue improved despite lower ADR. Forecast RevPAR was $126.33 and actual RevPAR was $129.33. Check the $310,400 against the room-revenue ledger and the 1,840 sold nights against the operating report before interpreting either figure.

Turn the difference into a decision

Weekday volume exceeded forecast, but lower rates absorbed much of the benefit. Ask whether the difference came from the mix of corporate, group and transient guests, a discount assumption or a rate change. The arithmetic locates the question; it does not prove its cause. A fuller revenue driver analysis should preserve the same reconciliation as it adds detail.

Where it goes wrong

• Changing inventory silently. Room closures alter the occupancy denominator. Explain inventory changes separately before comparing occupancy.
• Mixing booking and stay dates. Deposits and reservations do not represent room revenue earned on the booking date.
• Averaging rates across properties. Divide total room revenue by total sold nights instead.
• Overwriting forecasts. A refreshed forecast cannot fairly measure the accuracy of an earlier version.

Review your hotel forecast in Covirage

Bring a sample of your own hotel forecast, daily room results and inventory export. Covirage maps the columns, and its tools compute and check occupancy, rates, revenue and the forecast comparison; the external AI model explains the result with every figure traced to its rows. Create a free Covirage account in about a minute, then set up your business and bring your file.

See Covirage for hospitality teams for the industry context.