Analyze hotel occupancy, ADR, and RevPAR without mixing room nights
Measure occupancy, ADR, and RevPAR from one stay-date inventory grain, with cancellations and unavailable rooms treated explicitly. Start with the what one row represents and definitions, calculate only from eligible records, and check the result against the source before using it.
From the field · Hotel revenue manager
Measure occupancy, ADR, and RevPAR from one stay-date inventory grain, with cancellations and unavailable rooms treated explicitly.
Booking exports are reservation-grain while operations and revenue decisions are stay-date-grain. Multi-night stays, room moves, complimentary rooms, closures, taxes, and cancellations distort simple reservation counts.
Columns used in the example
property_id · stay_date · room_id · room_type · available_flag · occupied_flag · room_revenue · channel · rate_plan · booking_status
Follow it with your own file
- Expand reservations to one room-night per stay date and join approved available inventory.
- Separate sold, complimentary, out-of-order, cancelled, and no-show room nights.
- Calculate occupancy, ADR, and RevPAR by date, room type, and channel with compatible denominators.
- Compare pace and mix while retaining holidays, events, closures, and promotion context.
Check: Occupied room nights cannot exceed approved available room nights, and room revenue used in ADR must reconcile to the revenue ledger definition.
What needs resolving first
Booking exports are reservation-grain while operations and revenue decisions are stay-date-grain. Multi-night stays, room moves, complimentary rooms, closures, taxes, and cancellations distort simple reservation counts.
What changes the answer
- Measure occupancy, ADR, and RevPAR from one stay-date inventory grain, with cancellations and unavailable rooms treated explicitly.
- Occupied room nights cannot exceed approved available room nights, and room revenue used in ADR must reconcile to the revenue ledger definition.
- Out-of-order rooms may be excluded from available inventory or retained for capacity reporting; use the hotel's approved metric definition consistently.
Frame the decision before calculating
Measure occupancy, ADR, and RevPAR from one stay-date inventory grain, with cancellations and unavailable rooms treated explicitly.
The exception that can reverse the conclusion
Out-of-order rooms may be excluded from available inventory or retained for capacity reporting; use the hotel's approved metric definition consistently.
Build the result step by step
- Define the decision: Expand reservations to one room-night per stay date and join approved available inventory.
- Prepare the evidence: Separate sold, complimentary, out-of-order, cancelled, and no-show room nights.
- Calculate the result: Calculate occupancy, ADR, and RevPAR by date, room type, and channel with compatible denominators.
- Review the exceptions: Compare pace and mix while retaining holidays, events, closures, and promotion context.
Choose the depth of analysis the decision deserves
| Approach | What it answers | What remains at risk |
|---|---|---|
| Quick spreadsheet total | A narrow descriptive question | Definitions, duplicate rows, timing, and exceptions may be hidden |
| Reviewed analysis | The stated decision with reconciled evidence | Human judgment is still required for policy and action |
| Ongoing monitored workflow | The same decision across new periods | unexpected column changes and changed business rules still need review |
Warning signs in the file
- The source files do not share one documented what one row represents or cutoff.
- The decision owner cannot trace the headline result to eligible source records.
- Out-of-order rooms may be excluded from available inventory or retained for capacity reporting; use the hotel's approved metric definition consistently.
Doing these steps in Prepyr
Prepyr accepts CSV files only. Export the worksheet or table you need as a UTF-8 CSV first; workbook formulas, formatting, macros, charts, and other sheets will not be included. Describe the result you need: Analyze hotel occupancy ADR and RevPAR from booking data. Check the columns selected, preview any proposed changes, and compare the output with the source before applying or exporting it.
Prepyr steps used here: Statistical Profiling · Interactive Charts · Insight Graph · Scheduled Delivery
Make the output easy to check
- what one row represents and eligible population documented
- Dates, units, currencies, and identifiers validated
- Occupied room nights cannot exceed approved available room nights, and room revenue used in ADR must reconcile to the revenue ledger definition.
- Exceptions retained for human review
- Published result links back to its evidence
A decision-ready result for “Analyze hotel occupancy ADR and RevPAR from booking data,” with its assumptions, exceptions, and source totals still visible.
Questions readers usually ask next
Can this hotel revenue manager analysis be done in Excel?
Yes for a small, stable file. Keep the source unchanged, document formulas and exclusions, and check outputs against the source.
What should not be automated?
Do not automate the final policy or business judgment. Out-of-order rooms may be excluded from available inventory or retained for capacity reporting; use the hotel's approved metric definition consistently.