The Hotel Luxuria is a fictitious boutique hotel with ten rooms. The owner wanted to start analysing the bookings a bit more so he asked the Manager to “put something together”!
The first thing is to understand the potential of the hotel – what the revenue would look like at full occupancy – the manager copied the current room rates into an Excel Worksheet
He knew that this data would be a lot more manageable if it were an Excel Table. It is easy to convert the range of cells to a table as well as getting them nicely formatted at the same time by selecting the relevant cells and then choosing the Excel menu item “Format as table”, which is in the Home menu ribbon. The drop-down presents a lot of table styles to suit every taste.
Next thing is to sum all the room rates to show the revenue per night if the hotel were full. With any cell in the table selected, on the “Design” menu ribbon, from “Table Style Options”, select “Total Row”.
Excel adds the total row and the desired calculation. However, as can be seen from the drop down now available in the table, this not limited to straightforward totals – many functions are available. They can even be mixed and matched along the total row.
So far so good. Now the manager wants to start looking at actual bookings. He created a new table with all the bookings for the week, along with the discount given on the full room rate. One row per night per room and no entry at all for nights where the room wasn’t used at all.
Notice how each of the tables was intuitively given meaningful header labels as we went along. It is also a good idea to give meaningful names to entire tables as well. This will make the entire solution much more user-friendly later on. You need the Name Manager for this; it’s an option in the “Defined Names” drop-down in the Formulas menu ribbon.
In the Name Manager, edit each of the tables in turn and give them names, like “Room_Rates” and “Occupancy_Rates”.
Now to start analysing the occupancy rates. Back in the Room Rates table let’s add a new column to show how many nights each room was used for during the week for which we have data. Right-click any cell in the last column and select the menu options Insert | Table Column to the Right. Give the new column a meaningful name – like “Occupancy”.
Now we are going to add a formula to show, for each room, how many nights (out of seven) the room was used. In any cell in the Occupancy column you can enter the formula, which Excel will then apply to the entire column in the table body. The formula needed in this case is
=COUNTIF(Occupancy_Rates[Room Number],[@[Room Number]])
This formula is probably a familiar one – COUNTIF – but the rest of the formula may look a little unfamiliar at first, but not too difficult to comprehend after a few moments. This readability is thanks to the meaningful table names and column header names. I will go into all the detail around these kinds of formulas in a future article. Select the sum function in the drop down in the Total Row for the Occupancy column as well to show the total room-nights occupancy.
With the techniques already covered, it wouldn’t be a huge leap to start summing the revenue received and cross-referencing that against expected incomes, taking into account discounts given and so on. Obviously, there are many extras that the manager will be adding to this data model – Room Service, Restaurant and Bar bills, Gym and Health facilities, Excursions etc.