Hotel Management System in Excel: Room Bookings, Daily Occupancy, ADR, RevPAR, GST Room Bill and Dashboard

By Ravindra Bagale · Last updated: 17 October 2026

← All Excel dashboards

Download the template: hotel-management-system-template.xlsx: a 7-sheet system with example data and real formulas (Read me, Setup, Bookings, Occupancy, Room bill, Report, Dashboard). Yellow cells are inputs you can type over; green cells hold formulas; blue rows are headers and totals. Replace the example data with your own and every report and the dashboard recalculate.

Made-up data: every name, amount, business and date on this page is made up for practice. Cities are used only to make the examples feel real.

1. The business story

Raja Residency, 1 to 17 October 2026 (made-up rooms and guests; GST rule checked 17 October 2026).

GST on hotel rooms: 5% without input tax credit when the value of supply per night is up to ₹7,500, and 18% above (rates since 22 September 2025). The Suite at ₹8,500 is billed at 18%. The industry view of these KPIs is in 27.15 hospitality: occupancy, ADR and RevPAR; the hotel restaurant can run on the restaurant system.

2. Build it sheet by sheet

Step 1. Setup: rooms and the GST rule

1. On Setup, type the as-of date in B3, the hotel in B4, the GST threshold ₹7,500 in B5, 5% in B6, 18% in B7 and the report start date in B8.
2. From row 11 list Room, Type, Rate per night: 101-104 Standard ₹2,800, 201-203 Deluxe ₹4,500, 301 Suite ₹8,500. Keep room types and booking sources as lists in E and F.

Step 2. Bookings: nights, GST and balance

1. On Bookings, type booking, guest, source, room, check-in, check-out, extras and advance. Type E5 and rate F5 come from the room with XLOOKUP (XLOOKUP).
2. Nights I5: =H5-G5 (check-out is the morning the guest leaves). Room amount J5: =I5*F5.
3. GST rate K5: =IF(F5<=Setup!$B$5,Setup!$B$6,Setup!$B$7); GST L5 =ROUND(J5*K5,2); total N5 =J5+L5+M5; balance P5 =N5-O5.
4. Status Q5: =IF(H5<=Setup!$B$3,"Checked out",IF(G5<=Setup!$B$3,"In house","Upcoming")) (nested IF). Colour In house green and Upcoming amber.

Step 3. Occupancy: rooms sold each night

1. On Occupancy, rooms available B3: =COUNTA(Setup!A11:A18) = 8. Dates from A6 =Setup!B8, then =A6+1 down to 17 October.
2. Rooms sold B6: =COUNTIFS(Bookings!$G$5:$G$200,"<="&A6,Bookings!$H$5:$H$200,">"&A6): a room is sold on a night when check-in ≤ that date < check-out (COUNTIFS).
3. Occupancy C6 =B6/$B$3; room revenue D6 with the same conditions in SUMIFS over the rate; ADR E6 =IFERROR(D6/B6,0); RevPAR F6 =D6/$B$3.
4. Period row 23: occupancy =B23/($B$3*COUNT(A6:A22)), ADR =D23/B23, RevPAR =D23/($B$3*COUNT(A6:A22)). Add a 3-colour scale on C6:C22 (colour scales).

Step 4. Room bill

1. On Room bill, pick the booking in B4. Each line is =XLOOKUP($B$4,Bookings!$A$5:$A$200,Bookings!$B$5:$B$200) with the right column.
2. CGST B16 is half the GST rounded; SGST B17 the rest. Total B19 =B13+B16+B17+B18, balance B21 =B19-B20.
3. Set the print area to A1:D22 (print area). H-327: 2 Suite nights ₹17,000 + GST ₹3,060 + extras ₹450 = ₹20,510; advance ₹5,000; balance ₹15,510.

Step 5. Report and Dashboard

1. On Report, per room type: bookings =COUNTIF(Bookings!$E$5:$E$200,A5), nights and room revenue with SUMIFS, average rate =IFERROR(D5/C5,0); per source: bookings, revenue and share.
2. On Dashboard, KPI cards for occupancy, ADR, RevPAR and room revenue from Occupancy row 23, guests in house =COUNTIF(Bookings!$Q$5:$Q$200,"In house"), upcoming arrivals and balance due from in-house guests =SUMIFS(Bookings!$P$5:$P$200,Bookings!$Q$5:$Q$200,"In house") (KPI cards).

3. Formulas used

Every formula above is written in the template, so you can click a cell and see it in the formula bar. All values on this page were recalculated in LibreOffice with zero errors.

4. Common mistakes

1. Counting the check-out day as a night. A guest checking in on the 15th and out on the 17th stays 2 nights. Nights = check-out − check-in, and rooms sold uses check-out greater than the date.
2. One GST rate for every room. The rate follows the value per night: 5% up to ₹7,500, 18% above. The Suite needs 18%.
3. Dividing revenue by all rooms and calling it ADR. ADR divides by rooms sold; dividing by rooms available is RevPAR. Track both.
4. Not checking a room before booking it. If a night shows more rooms sold than available, or a room twice, it is a double booking. Look at Occupancy before saving a booking.
5. Leaving advance in the extras column. Advance reduces the balance, extras increase the total. Keep them in separate columns.

5. More real examples

Example 1: Homestay with 3 rooms. Same sheets, rooms 1 to 3; occupancy and RevPAR are still the right questions.

Example 2: Hostel beds. Treat each bed as a room in Setup; occupancy becomes bed occupancy.

Example 3: Resort with meal plans. Add a Plan column (room only, breakfast, all meals) with a per-night plan charge looked up from Setup and added to the room amount.

Quick revision cards

Englishमराठीहिंदी

Quick revision

Each card shows formulas from this page, each with an example and its answer from the template. Scan the QR code on a card to open this lesson. All names and numbers are made up for practice.

More cards: Ravi Sir's Cards.

6. Frequently asked questions

How do I calculate hotel occupancy in Excel?

Count rooms sold each night with COUNTIFS(check-in ≤ date, check-out > date), divide by rooms available. For a period, divide total rooms sold by rooms × nights.

What is the difference between ADR and RevPAR?

ADR = room revenue ÷ rooms sold (average price of a sold room). RevPAR = room revenue ÷ rooms available (it falls when rooms stay empty).

What GST applies to hotel rooms?

From 22 September 2025: 5% without input tax credit when the value per night is up to ₹7,500, 18% with credit above. Check with your accountant.

How do I find guests in house today?

Status = In house when check-in ≤ today and check-out > today. Set Setup B3 to =TODAY() and count with COUNTIF.

Can I avoid double bookings?

Check the Occupancy sheet before saving: rooms sold cannot exceed rooms available. For a per-room check, add a COUNTIFS on room number with the same date conditions.

7. Quick quiz

Ten questions on this page. Sign in to save your score.