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

22. KPI in All Industries

22.16 Hospitality

By Last updated:

Hotel interviews ask occupancy, ADR (average daily rate), and RevPAR (revenue per available room). Restaurant covers can be busy while rooms are empty. These lodges are fictional names for class, not a hotel chain's filing.

KPI What the number means Who uses it Action it drives
Occupancy Room-nights sold divided by room-nights available. Front office manager Low occupancy is a rate or a channel problem. Covers do not fill rooms.
ADR Room revenue divided by room-nights sold. Revenue manager A falling ADR with full rooms may be discounting. Say so.
RevPAR Room revenue divided by room-nights available. Occupancy times ADR. General manager This is the one number that stops a discount war.

Fictional sample rows. One month. Available room-nights are rooms × nights, already multiplied. Type these rows into a new sheet from cell A1, press Ctrl + T, and name the table tblRooms (Table Design › Table Name).

Property City Available Sold Room revenue Covers
Shivaji Nagar lodge Pune 600 420 ₹8,40,000 900
Kothrud lodge Pune 300 150 ₹4,50,000 800
Nashik Road lodge Nashik 300 240 ₹3,60,000 400
Gangapur lodge Nashik 200 160 ₹2,40,000 350
Kolhapur lodge Kolhapur 400 200 ₹5,00,000 700
Nagpur lodge Nagpur 500 250 ₹3,75,000 650

Excel formulas for the whole file (type them in empty cells below or beside the table):

KPI Excel formula Result Number format
Occupancy =SUM(tblRooms[Sold])/SUM(tblRooms[Available]) 61.7% 0.0%
ADR =SUM(tblRooms[Room revenue])/SUM(tblRooms[Sold]) ₹1,947 ₹#,##0
RevPAR =SUM(tblRooms[Room revenue])/SUM(tblRooms[Available]) ₹1,202 ₹#,##0

Calculated columns (one result per row). Click the first empty column of the table, type the header, then the formula. The Table fills every row by itself.

New column Formula in the first row
Occupancy =[@Sold]/[@Available]
ADR =[@[Room revenue]]/[@Sold]
RevPAR =[@[Room revenue]]/[@Available]

Expected result per row:

Property Occupancy ADR RevPAR
Shivaji Nagar lodge 70.0% ₹2,000 ₹1,400
Kothrud lodge 50.0% ₹3,000 ₹1,500
Nashik Road lodge 80.0% ₹1,500 ₹1,200
Gangapur lodge 80.0% ₹1,500 ₹1,200
Kolhapur lodge 50.0% ₹2,500 ₹1,250
Nagpur lodge 50.0% ₹1,500 ₹750

One city with SUMIFS. Occupancy for Pune only:

=SUMIFS(tblRooms[Sold],tblRooms[City],"Pune")/SUMIFS(tblRooms[Available],tblRooms[City],"Pune")

Result for Pune: 63.3%. Change "Pune" to a cell reference (for example $K$1) and add a drop-down, and the same formula answers for any city.

Good KPI Vanity metric
From the same rows Kothrud occupancy 50.0% and RevPAR ₹1,500 Kothrud 800 covers
Why Half the rooms were empty. The rate on the rooms that sold is high, so the building still made RevPAR. The restaurant looks like the busiest property in Pune. It does not pay the empty rooms.

What the manager does. The Pune manager does not add a dinner promotion at Kothrud. The empty 150 room-nights are the job (a lower midweek rate, or a stop on a discount that is not in this table). The Nashik manager is fuller (80%) at a lower ADR (₹1,500). The test in the practice file is a higher weekend rate, not more covers. Shivaji Nagar is the Pune pattern to keep: 420 ÷ 600 = 70.0% occupancy and ADR 8,40,000 ÷ 420 = ₹2,000.

Practice workbook and guide: Hotels dashboard workbook (Excel) · How to build the Hotels dashboard in Excel. The workbook data is also fictional.

Practice workbook and guide: Restaurants dashboard workbook (Excel) · How to build the Restaurants dashboard in Excel. The workbook data is also fictional.

Ravindra Bagale's Tip

ADR divides by rooms sold; RevPAR divides by rooms available. Put both on the sheet. Empty rooms show up only in RevPAR.