Hotel Bookings … Cost per Room … Total £££ per Month … All in One!

NOTE: I updated this page on 12th May 2016. You can now download the file under discussion on this page.

I was staying at a small hotel cum guest house a couple of years ago and since I was there for an extended period, I got to know the manager quite well. One day she came to me and said she’d heard I might know something about Excel! I said, yes, why? She had a couple of things that were niggling her, one of which was the control sheet for the rooms, all 30 of them.

I accepted the mission and what she gave me was a simple spreadsheet matrix with the room numbers across the top and the days of the month down the side. So far so good. She wanted, she said, to make her life simpler as all she seemed to be able to do was to make it complicated because she didn’t know Excel so well. Here is what I did for her: microscope time!! You can download the file from the link at the end of the article.

Here are the key aspects of what I did:

• all cells are colour coded according to the guests (see the next point, however): they had specific guest groups so everyone working for ABC had their cells shaded, say, blue, anyone working for XYZ had their cells shaded pink … I used conditional formatting for this aspect of my work
• individual guests’ cells were not colour coded
• the room rate was entered at the top of each column, in row 2 and that was used to find the total revenue per room in row 35. I did not build in the possibility of there being multiple room rates during a month because I was told that it never changed during the month.
• row 34 contained a count of the number of days in the month that a room had been occupied by means of an IF statement
• finally, in the extreme bottom right corner of the worksheet there are summary cells: total nights occupancy for all rooms and total revenue for all rooms for the month
• I did not provide any calculations for daily and cumulative occupancy … just the monthly final

Of course, it’s simple when you know how!

• The table you can see covers the range A1:AE35
• The range A2:A35 contains the dates
• The range B1:AC1 contains the room numbers
• The range B2:AC2 contains the room rate as a number
• The range B34:AC34 contains the formula to count total days’ occupancy per month per room. For example, in B34 =IF(COUNTA(B3:B33)=0,””,COUNTA(B3:B33))
• The range B35:AC35 contains the formula to find the total revenue for the month for a particular room. For example, in cell B35 =IFERROR(B34*B2,””)

No one says this is the best hotel control sheet there ever was: you might ask how it links with the central booking system … it doesn’t because there wasn’t one. 30 rooms, one manager who managed all operational aspects of the hotel including the centralised booking system. There were many repeat clients each with several guests so the problem with this sheet was really a problem of total occupancy and monthly revenues and not bookings.

Download the file here: dinara_hotel

Duncan Williamson