Ravindra BagaleCourses & study guides मराठी Track your progress

27. 20 Industry Projects

27.15 Hospitality: Occupancy, ADR and RevPAR

By Last updated:

Example first. Amir manages revenue for six fictional hotels and wants the three standard hotel KPIs for last month.

Hospitality project — in 30 seconds

What: A Hospitality tracker: one Table tblHotels, the calculated column Occupancy, KPI cells, a PivotTable by City, a chart and a one-page dashboard.

Why: This is the shape of a real Hospitality report: Occupancy, ADR (average daily rate), RevPAR are the numbers a manager reads first.

Tiny example: From 6 rows: Occupancy = 69.0%, ADR (average daily rate) = ₹3,344.84, RevPAR = ₹2,309.33.

First look at the data (before → after):

Hospitality project: before and after tables, fictional Blinkit Maharashtra data

Expected result — Calculated column

Property Occupancy
H-Pune-1 77.0%
H-Pune-2 65.8%
H-Nashik-1 70.0%
H-Kolhapur-1 65.0%
H-Nagpur-1 70.0%
H-Solapur-1 57.5%

Expected result — KPI cells (expected results)

KPI Formula Result
Occupancy =SUM(tblHotels[RoomsSold])/SUM(tblHotels[RoomsAvailable]) 69.0%
ADR (average daily rate) =SUM(tblHotels[Revenue])/SUM(tblHotels[RoomsSold]) ₹3,344.84
RevPAR =SUM(tblHotels[Revenue])/SUM(tblHotels[RoomsAvailable]) ₹2,309.33
Before table and formula as text

Before — Table tblHotels (fictional sample rows)

Property City RoomsAvailable RoomsSold Revenue
H-Pune-1 Pune 3,000 2,310 97,02,000
H-Pune-2 Pune 2,400 1,580 55,30,000
H-Nashik-1 Nashik 1,800 1,260 36,54,000
H-Kolhapur-1 Kolhapur 1,500 975 25,35,000
H-Nagpur-1 Nagpur 2,100 1,470 47,04,000
H-Solapur-1 Solapur 1,200 690 15,87,000

Formula / click path

Occupancy: =[@RoomsSold]/[@RoomsAvailable]
Occupancy: =SUM(tblHotels[RoomsSold])/SUM(tblHotels[RoomsAvailable])
ADR (average daily rate): =SUM(tblHotels[Revenue])/SUM(tblHotels[RoomsSold])
RevPAR: =SUM(tblHotels[Revenue])/SUM(tblHotels[RoomsAvailable])

Do it step by step:

  1. Type the data and press Ctrl+T; name the Table tblHotels in Table Design › Properties › Table Name.
  2. Add the column Occupancy with =[@RoomsSold]/[@RoomsAvailable]; the Table fills it down.
  3. On a KPI sheet type =SUM(tblHotels[RoomsSold])/SUM(tblHotels[RoomsAvailable]) and the other KPI formulas from the table below.
  4. Click Insert › PivotTable › From Table/Range, put City in Rows and Revenue in Values (Sum).
  5. Click PivotTable Analyze › PivotChart › Clustered Column, then PivotTable Analyze › Insert Slicer › Property.
  6. Move the KPI cells, the chart and the slicer onto a sheet named Dashboard.

The brief. Build a monthly hotel sheet (room-nights): occupancy per property, overall occupancy, ADR (room revenue ÷ rooms sold), RevPAR (room revenue ÷ rooms available), revenue by city, a chart and a Property slicer.

Calculated column formulas:

Column Formula
Occupancy =[@RoomsSold]/[@RoomsAvailable]

KPI cells (check your numbers):

KPI Formula Result
Occupancy =SUM(tblHotels[RoomsSold])/SUM(tblHotels[RoomsAvailable]) 69.0%
ADR (average daily rate) =SUM(tblHotels[Revenue])/SUM(tblHotels[RoomsSold]) ₹3,344.84
RevPAR =SUM(tblHotels[Revenue])/SUM(tblHotels[RoomsAvailable]) ₹2,309.33

PivotTable (expected):

City Sum of Revenue
Kolhapur ₹25,35,000
Nagpur ₹47,04,000
Nashik ₹36,54,000
Pune ₹1,52,32,000
Solapur ₹15,87,000
Grand Total ₹2,77,12,000

Chart and dashboard (one under the other):

  1. Click inside the PivotTable, then PivotTable Analyze › PivotChart › Clustered Column › OK.
  2. Add a chart title and data labels with Chart Design › Add Chart Element.
  3. Click PivotTable Analyze › Insert Slicer, tick Property and click OK.
  4. Insert a new sheet named Dashboard. In it, link a card cell to each KPI (for example =KPI!B2) and format it large and bold.
  5. Cut and paste the chart and slicer onto the Dashboard sheet.
  6. On the View tab, untick Gridlines in the Show group. Select the dashboard area and click Page Layout › Print Area › Set Print Area.

Template and guide: download the practice template Hospitality_Template.xlsx and read the step-by-step template guide. The template's sample rows may differ from the rows above; the method is the same.

Related ready-made dashboard: the Hospitality dashboard guide and its workbook Hotels_Dashboard.xlsx show a finished version of this kind of report.

Practice task (try it before you open the answer):

  1. Which city has the highest sum of Revenue?
  2. Filter the Table on City = Pune (Data › Filter): what is Occupancy for Pune alone?
  3. Add the Clustered Column PivotChart and the Property slicer to your Dashboard sheet.
Answer

Pune (₹1,52,32,000). For Pune only (2 rows): Occupancy = 72.0%. The chart shows 5 bars, one per city.

Ravindra Bagale's Tip

Keep the calculated column Occupancy inside the Table. New rows then get the formula automatically, and the PivotTable only needs Refresh.

Loading practice tasks...

  1. Total days admitted (Easy): Add up Days admitted in D2:D11.
  2. Average bill amount (Easy): Average of E2:E11, rounded to 2 decimals.
  3. Rows in Cardiology (Easy): How many rows have Department "Cardiology"?
  4. Largest days admitted (Easy): The largest value in D2:D11.
  5. Days admitted for Cardiology (Medium): Total of D2:D11 where B is "Cardiology".
  6. Cardiology in Pune (Medium): Total of D2:D11 where B is "Cardiology" and C is "Pune".
  7. Best in Cardiology (Medium): Largest E2:E11 value where B is "Cardiology".
  8. Value of row 2 (Medium): D2 times E2.
  9. Look up Sneha Patil (Medium): The Days admitted for "Sneha Patil" (use a lookup on column A).
  10. Total value (Hard): Sum of D times E for all rows.
  11. Share of Cardiology (Hard): Share of D2:D11 that is "Cardiology", rounded to 3 decimals.
  12. Rank of row 2 (Hard): Rank of D2 among D2:D11 (largest = 1).
  13. Above average? (Hard): "Above" if E2 is above the average of E2:E11, else "Below".

See all practice tasks in the Task Hub →