Blog · Board and management reporting · Hospitality
Build a hotel dashboard from room inventory and stay exports. Follow the spreadsheet formulas for occupancy, average daily rate, room revenue, and segment mix, then reconcile the property totals.
A hotel dashboard compares occupancy, average daily rate, and room revenue by property and segment for the same stay period. Start with completed stays and a separate room inventory export, calculate the ratios from their underlying totals, and prove that each property reconciles before using the group result.
For a monthly operating review, use stay dates rather than reservation creation dates. Keep future bookings in a separate view. The hotel group sales KPI guide covers pace and account production; the dashboard here answers how the properties actually operated during a closed month.
The illustrative US operator Cedar Lake Hotels has two properties. The following September 2026 export has already been grouped into property and segment totals. In a spreadsheet sheet named Stays, use columns A through E in this order, with headers in row 1.
| Property | Stay month | Segment | Sold room nights | Net room revenue (USD) |
|---|---|---|---|---|
| Cedar | September 2026 | Corporate | 1,200 | 180,000 |
| Cedar | September 2026 | Leisure | 900 | 162,000 |
| Lake | September 2026 | Corporate | 600 | 84,000 |
| Lake | September 2026 | Leisure | 840 | 142,800 |
Room revenue here excludes lodging taxes, food, and meeting revenue. Credits relating to these stays have already reduced room revenue. Retain reservation and adjustment identifiers in the detailed export so every grouped amount can be traced back.
Keep inventory separately; repeating a property's capacity on each segment row would multiply the occupancy denominator.
| Property | Rooms in service | Days | Available room nights |
|---|---|---|---|
| Cedar | 100 | 30 | 3,000 |
| Lake | 80 | 30 | 2,400 |
| Total | 180 | 30 | 5,400 |
This example has no room closures. Available room nights equal rooms in service multiplied by days: 100 × 30 = 3,000 for Cedar and 80 × 30 = 2,400 for Lake. Where daily capacity changes, sum the daily inventory records instead.
Create a summary sheet with the columns below. Enter the property names in column A and the checked available room nights in B. Use the following formulas for Cedar in row 2 and fill them down for Lake in row 3.
C2 = SUMIFS(Stays!$D$2:$D$5,Stays!$A$2:$A$5,$A2)
D2 = SUMIFS(Stays!$E$2:$E$5,Stays!$A$2:$A$5,$A2)
E2 = IF(B2=0,"",C2/B2)
F2 = IF(C2=0,"",D2/C2)
G2 = IF(B2=0,"",D2/B2)
The labels before each equals sign identify the destination cell; enter the formula beginning with =. Microsoft documents SUMIFS as summing values that meet multiple criteria. These formulas assume the source contains only the stated month; filter a larger export to that month before using these ranges.
Occupancy = Sold room nights / Available room nights
Average daily rate (ADR) = Net room revenue / Sold room nights
Revenue per available room (RevPAR) = Net room revenue / Available room nights
| A: Property | B: Available nights | C: Sold nights | D: Room revenue (USD) | E: Occupancy | F: ADR (USD) | G: RevPAR (USD) |
|---|---|---|---|---|---|---|
| Cedar | 3,000 | 2,100 | 342,000 | 70.00% | 162.86 | 114.00 |
| Lake | 2,400 | 1,440 | 226,800 | 60.00% | 157.50 | 94.50 |
| Group | 5,400 | 3,540 | 568,800 | 65.56% | 160.68 | 105.33 |
Sum B, C, and D into row 4. Calculate E4 as =C4/B4, F4 as =D4/C4, and G4 as =D4/B4. Percentages and rates are displayed to two decimal places; retain unrounded values in calculations.
Covirage is not affiliated with Microsoft.
Add a segment table below the property view, rather than another headline score without supporting rows. The group has corporate revenue of 180,000 + 84,000 = $264,000 and leisure revenue of 162,000 + 142,800 = $304,800.
| Segment | Sold room nights | Room revenue (USD) | Revenue mix |
|---|---|---|---|
| Corporate | 1,800 | 264,000 | 46.41% |
| Leisure | 1,740 | 304,800 | 53.59% |
| Total | 3,540 | 568,800 | 100.00% |
For a segment name in J2, calculate its room revenue with:
=SUMIFS(Stays!$E$2:$E$5,Stays!$C$2:$C$5,J2)
Divide that result by the group's room revenue for revenue mix. At property level, add the property criterion and divide by that property's revenue. Keep room-night mix and revenue mix separately labeled: higher-rate business can contribute a larger share of revenue than of stays. The existing segment mix guide develops the commercial account interpretation.
The segment room nights must sum to 3,540, matching the property total. Room revenue must sum to $568,800 and match the closed room-revenue ledger after documented timing adjustments. Inventory of 5,400 nights less 3,540 sold leaves 1,860 unsold nights under this example's definitions.
The group ADR also has an independent weighted check:
=SUMPRODUCT(C2:C3,F2:F3)/SUM(C2:C3)
Microsoft's SUMPRODUCT documentation describes multiplying corresponding values and summing the products. Using unrounded property rates, this returns $160.68, matching $568,800 divided by 3,540 sold nights. Averaging the property ADRs directly gives $160.18, which weights the smaller Lake property too heavily.
One further identity must hold before rounding: occupancy × ADR = RevPAR. For the group, (3540/5400) × (568800/3540) = 568800/5400, or $105.33.
Cedar fills a larger share of its inventory and earns more per available room than Lake. That supports a review of Lake's segment production and available inventory, rather than an immediate rate cut. Rate changes need demand evidence and the costs of attracting and serving additional stays.
To isolate rate sensitivity, change only Cedar leisure ADR from $180 to $190 while keeping its 900 leisure room nights and every other input fixed. Leisure revenue becomes 900 × 190 = $171,000; Cedar revenue becomes $351,000, its ADR $167.14, and its RevPAR $117.00. Occupancy stays at 70.00%. This is a scenario, not evidence that raising the rate would leave demand unchanged.
Have the revenue manager explain rate and segment changes, the property manager explain inventory exceptions, and finance own the ledger tie. For another presentation approach, see the property operating dashboard layout.
Upload a sample of your own stay and room inventory exports; Covirage maps the columns and its tools compute and check the property and segment figures. The external AI model explains the results with every figure traced to its rows. Create a free Covirage account and bring a sample of your own export.
See Covirage for hospitality teams for the industry context.
Use a stay export with property, stay date, market segment, sold room nights, and net room revenue. Add a separate room inventory file for available room nights. Booking date and stay date answer different questions, so label the period basis before comparing properties.
State the inventory policy used by your company and apply it consistently. Keep physical room inventory and excluded room nights visible so reviewers can reconstruct available inventory. A property should never improve its reported occupancy through an unexplained change to the denominator.
Divide total room revenue by total sold room nights. An arithmetic average of property rates gives each hotel equal influence regardless of volume. Preserve the underlying room nights and revenue so the group rate can be checked against its component properties.
Occupancy measures room use, not profit. A fuller property may have lower rates, higher channel commissions, or higher servicing costs. Review occupancy beside rate and room revenue, then use a separate cost view before deciding whether additional business improves the property's financial result.