Taj Hotels Dashboard in Excel: Occupancy, ADR, RevPAR, F&B and Guest KPIs

By Ravindra Bagale · Last updated: 21 October 2026

Download the template: taj-dashboard-template.xlsx (Excel workbook, 6 sheets, example data).

1. Why this dashboard, what it shows, how to start

Why

A luxury hotel sells rooms, food and spa. Occupancy, ADR and RevPAR tell the manager if rooms earn enough. Looking at 1,700 rows one by one is slow. A dashboard turns them into a few clear numbers.

What

The dashboard answers questions like these:

• Bookings: All bookings made.
• Occupancy: Room nights sold ÷ available room nights (rooms: Pune 36, Solapur 14, Nashik 22, Sambhaji Nagar 20, Kolhapur 20, Nagpur 28 = 140; × 181 days, or 30 for one month).
• RevPAR: Revenue Per Available Room: room revenue ÷ available room nights (= ADR × occupancy).
• Avg length of stay: Average nights per completed booking.

How

1. Download taj-dashboard-template.xlsx.
2. Open it in Excel. Click Enable Editing if Excel asks.
3. Stay on the first sheet, Dashboard.
4. Read the 15 cards from left to right, top to bottom.
5. Then change one drop-down in row 6 and watch the numbers move.

2. What is inside the workbook

Why

Each sheet has one job. When you know the job, you know where to look and what not to touch.

What

The Data sheet has these columns: BookingID, Month, City, Hotel, RoomType, Channel, GuestType, LeadDays, Nights, Rooms, RoomNights, ADR, RoomRevenue, FnBRevenue, SpaRevenue, Cancelled, Repeat, Loyalty, Rating and Inc.

How

1. Click each sheet tab at the bottom once.
2. On Data, press Ctrl+Down to see the last row.
3. On Rules & Instructions, read section 3 (data rules) before you type anything.
4. On Formulas used, find any formula you want to learn.

3. The 15 KPIs: what each one means

Why

KPI means Key Performance Indicator: one number that tells you if the business is doing well. A KPI is useful only if everyone reads it the same way.

What

Example values are for all rows (every filter set to All). In the formulas, Data[Column] means that whole column on the Data sheet; in the workbook it is written as a range like Data!$B$2:$B$1701.

How

1. Read the small grey line under each card: it says what the card counts.
2. A green card meets its target rule. A red card needs attention.
3. Click a card and look at the formula bar to see the real formula.
4. Change a filter, for example City = Pune, and see which cards turn red or green.

4. The drop-down filters

Why

A total for the whole business hides problems. Filters let you look at one City, one Guest type or one Month at a time.

What

Row 6 has three yellow cells with drop-down lists: City, Guest type and Month. Each list starts with All. The Data sheet has one extra column, Inc (include). Its formula gives 1 when the row matches all three filters, otherwise 0:

=IF(AND(OR(Dashboard!$C$6="All",C2=Dashboard!$C$6),OR(Dashboard!$F$6="All",G2=Dashboard!$F$6),OR(Dashboard!$I$6="All",B2=Dashboard!$I$6)),1,0)

Every KPI and chart uses Data[Inc],1 as its first condition, so only matching rows are counted.

How

1. Click the yellow cell next to City.
2. Click the small arrow and pick a value.
3. Look at the line 'Rows in view' to see how many rows match.
4. Pick All to remove that filter.
5. Set all three back to All before you save.

5. The charts and the Top 5 table

Why

Numbers tell you how much. Charts show you where and when: which month dropped, which city leads.

What

• Revenue mix by month
• Bookings by channel
• Occupancy by month
• ADR by room type
• Revenue by guest type
• Room revenue by hotel, occupancy line
• Table: Hotels ranked by room revenue (uses INDEX, MATCH and LARGE)

Each chart reads a small table on the Calc sheet. Those tables also use the Inc column, so the charts follow the filters.

How

1. Point at a bar or line to see its exact value.
2. To see where a chart's numbers come from, right-click it and choose Select Data.
3. Open the Calc sheet and find the table with the same title.
4. Do not type in Calc; it is all formulas.

6. How to add new data

Why

A dashboard is only useful if you keep feeding it. Adding rows the wrong way breaks totals without any error.

What

How

1. Go to the first empty row under the Data table.
2. Type or paste the new rows. Keep the same spelling as the lists (for example Pune).
3. Check that Inc filled down by itself (it shows 1 or 0).
4. The formulas read rows 2 to 1701. If your data goes past row 1701, use Find & Replace on the Dashboard and Calc sheets to change 1701 to your new last row.
5. Press F9 if numbers do not update, then set the filters to All and compare totals.

7. Formulas used

This is the same list as the 'Formulas used' sheet. A formula marked 'copied down' is the same in the rows below it.

8. Common mistakes

9. FAQ

How do I calculate Occupancy in Excel?

Room nights sold ÷ available room nights (rooms: Pune 36, Solapur 14, Nashik 22, Sambhaji Nagar 20, Kolhapur 20, Nagpur 28 = 140; × 181 days, or 30 for one month). The workbook uses =IFERROR((SUMIFS(Data[RoomNights],Data[Inc],1))/((IF(Dashboard!$C$6="All",140,INDEX({36,14,22,20,20,28},MATCH(Dashboard!$C$6,{"Pune","Solapur","Nashik","Sambhaji Nagar","Kolhapur","Nagpur"},0)))*IF(Dashboard!$I$6="All",181,30))),0) in Dashboard!H9.

How do I calculate ADR in Excel?

Average Daily Rate: room revenue ÷ room nights sold. Formula: =IFERROR((SUMIFS(Data[RoomRevenue],Data[Inc],1))/(SUMIFS(Data[RoomNights],Data[Inc],1)),0).

How do the drop-down filters change every KPI?

Each Data row has an Inc formula. It gives 1 when the row matches all three drop-downs (City, Guest type, Month), or the drop-down says All. Every KPI counts only rows where Inc = 1.

Is this real Taj Hotels (IHCL) data?

No. All 1,700 rows are made up for practice. The colours are only inspired by the brand. It is not an official Taj Hotels (IHCL) report.

Which Excel version do I need?

Excel 2010 or newer works, because the file uses COUNTIFS, SUMIFS, AVERAGEIFS, IFERROR, INDEX and MATCH. We checked it in LibreOffice Calc with zero errors.

10. Practice tasks

1. Set City = Pune. Write down the new value of Occupancy.
2. Which month is highest in the chart 'Revenue mix by month'?
3. Add one new row to the Data sheet and check that Bookings changes.
4. Change one target rule in Conditional Formatting (Home › Conditional Formatting › Manage Rules) and watch the card colour.

Related: all Excel dashboards · Excel dashboards chapter · SUMIFS, COUNTIFS and AVERAGEIFS · KPI cards in Excel