OĞUZ EROLAI·ADS·APPS

Hotel Occupancy Rate: Excel Calculations and a Shared Web Occupancy Page

6 min read

Many hotels still track occupancy in an Excel or Google Sheets month grid. Rooms run down the rows, days across the columns, and someone colours in the nights when a booking arrives. For a boutique hotel, I turned that grid into a web page that feeds itself from the booking system every day. It is one of the pieces of software I build for hotels. The screen below is that page drawn with sample data.

Visual Room Calendar screen: rows for all rooms, suite, residence and villa groups, room types and individual rooms; columns for the days of October and November. Top rows show daily occupancy as a percentage, room rows are coloured by the channel each booking came from.
Visual Room Calendar. Room names, channels and bookings are invented for this illustration; I do not share guest or revenue data from the real panel. Click the image for full size.

Calculating occupancy in Excel

Occupancy is sold room nights divided by available room nights. First document how out-of-service rooms, complimentary stays, staff use and owner use are treated. An internal report and an industry benchmark must use comparable definitions.

Fictional example: 20 rooms over 30 days provide 600 room nights. In an internal report that excludes two rooms each unavailable for five nights, capacity is 590. Selling 413 room nights produces 413 / 590, or 70% occupancy. This is an illustration, not a client result.

With rooms in rows and days in columns, use values rather than colour alone. Let O mean occupied and X mean out of service, with conditional formatting adding colour. In a sample range, use =COUNTIF(B2:AE21,"O") and =COUNTIF(B2:AE21,"X"). Divide occupied nights by available capacity, and expose a zero-capacity error instead of inventing a rate.

ADR and RevPAR: ADR divides room revenue by sold room nights; RevPAR divides the same revenue by available room nights. Keep taxes and package components consistent with the agreed accounting definition. Higher occupancy alone does not prove higher revenue.

Excel can perform these calculations. Re-entering booking-system data daily, different staff using different copies and unclear freshness are separate workflow problems.

Where does the occupancy spreadsheet break down?

The month grid is a good idea: at a glance it shows which room is taken on which night. The problem is the work that keeps it filled:

  • Someone types in every booking. A cancellation, a date change or a room move can easily miss the sheet.
  • Two versions of the truth appear. At this hotel, the old sheet, set side by side with the booking system, showed one month’s revenue about 45% higher than it really was. Once two sources drift apart, nobody knows which one is right.
  • Every month is a separate tab. Comparing with the same period last year means opening another file.
  • The channel is invisible. A cell is filled, but whether the booking came from Booking.com, the phone or a tour operator is not in that sheet.

What does the web page do differently?

  • Nobody enters data. The page reads the records coming from the hotel’s booking system every day. Cancellations and room moves show up the next day. Several years of history sit in the same place.
  • Four levels, from room to hotel. The whole hotel sits at the top, room groups (suites, residences, villas) below it, room types below those, and individual rooms at the bottom. The upper rows show daily occupancy as a percentage or as the number of rooms taken.
  • Colour tells you the booking channel. A single room is either taken or empty on a given night, so the colour in a room row shows the channel the booking came from rather than occupancy. Above the table, each channel’s colour is listed with the number of nights it brought in during that period.
  • Hovering over a cell opens the details. Guest name, channel, length of stay, check-in and check-out dates, nightly rate.
  • Three months at once. The arrows move the window one month at a time. Each month heading shows that month’s occupancy rate.
  • The same screen works for rates. In the “nightly rate” view, cells show the average nightly rate. Euro, lira, dollar or pound can be selected; conversion uses that night’s exchange rate, not today’s.
  • A fair comparison with last year. With comparison switched on, the screen answers the question what did we have on the books on this date last year. Days that are ahead turn green, days that are behind turn red. What matters is where you stand today, not how last season finally ended.
  • Links can be shared. The view you are looking at is written into the address, so you can send a link that says “look at this screen”.

How is it set up for your hotel?

The starting point is the spreadsheet you already use. Its row layout, its colours and who looks at what already describe the need. Next, I look at how your booking system’s data can be accessed. That differs from system to system, so I do not quote a time frame or scope before checking it. The first version is only the occupancy grid. The rate view and the comparison with last year come once the grid has proved useful.

This page does not replace your booking system; it reads its data and writes nothing back to it.

Frequently Asked Questions

Do we have to give up our booking system?

No. The occupancy page reads and displays the records in your booking system. You keep taking bookings, managing channels and entering rates in the system you already use.

Is the data in the screenshot real?

No. The design is identical to the screen in the panel I built for a hotel. Room names, channels and bookings are invented for this illustration. Guest names and revenue figures from the real panel are not shared.

Do we have to drop our spreadsheet completely?

You do not have to. If you want to keep receiving the data as a table, an export can be added. But if the same information keeps being typed into both the sheet and the system, the problem of two different figures stays too.

Who can see the occupancy page?

Only team members who sign in. The page is not public; which user sees which screen is decided during setup.

Let’s look at your hotel’s spreadsheet together

Tell me about the occupancy sheet and the booking system you use via WhatsApp on +90 549 654 64 89 or the contact page, and I’ll tell you how the setup would go and how long it takes. For other examples, see custom web apps for your team.

Back to top ↑
WHAT YOU CAN ASK FOR

How do I work?
Work-hour packages

Tell me about your business and what you need on WhatsApp or by phone; we agree on a work block of somewhere between 5 and 40 hours and I start doing what is needed. What you get is not a calendar window — it is net working hours spent directly on your business. I can use 40 hours working three days straight, or deliberately spread it out and finish it over a year — whatever the work calls for. I do not spend my time estimating how long something will take, I spend it doing the work; every hour I spend leaves a lasting result in your business. We keep going for as long as you find it useful.

WORK BLOCKS

Why not monthly?
Does a 40-hour block mean one week?

On a fixed monthly fee both sides are watching the wrong thing: you watch a fixed cost, the other side watches how many more contracts like this they can line up. Working by the hour puts us both in front of the same thing — the real value of the work that got done. I know not having an upfront answer to "how much will this cost in total?" is the natural cost of this model. But instead of paying a fixed fee for months to a setup that is not producing results, you only pay for the qualified time spent on your work — and you see exactly where that time went, start to finish. That is why my clients keep coming back.

5hours300€ + VAT60€ per hourChoose this block
10hours500€ + VAT50€ per hourChoose this block
20hours800€ + VAT40€ per hourChoose this block
40hours1,400€ + VAT35€ per hourChoose this block