6 years ago, I created several reservation calendar templates for hotel, cars, ballroom business where the excel templates are not protected where you can modify it to suit your own business. There were some requests arrived to my inbox asking about customization where at that time I didn’t have time to fulfill it. Just one month ago I tried to summarize all of those requests and started to create a more general reservation calendar where people don’t have to tweak the formulas, and put several options where people can customize it easily. Perhaps, my new reservation calendar will only fulfill 80% of those requests, because I didn’t create any features beyond reservation functions. There is no guest management function when they stay in the hotel. If you need an all-in-one guest management software, this one shouldn’t be your option. This one is aimed to hotel, aparment, car rental or other small businesses with 100 rooms or 100 cars who need to manage their guest/customer reservation with Microsoft Excel. But, you can use all the recorded booking information to create your own report either for your own needs or to be represented to your hotel owner.
Microsoft always does development on Microsoft Excel which generates incompatibility among different Excel version, especially if the spreadsheets used VBA/macro codes. At the beginning, I assumed it is more safe if I used built-in Form and Active-X Control functions since it is available as part of Excel built-in functions. But, I found some of those built-in codes have problems in Microsoft Excel 2013. So, I decided not to use those built in functions again and replace it with a more safer dropdown list (for options) and there will be no scrolling button. It will make the spreadsheet looks like a big table, but it will be safer for you to move between different Excel version as well as different Operating System.
I separated this reservation calendars into 2 spreadsheets. Daily Reservation Calendars template, which is more suitable for hotel, apartment, villa, and Hourly Reservation Calendar template, which is more suitable for Car, Party equipments, Music Studio rental businesses. I have to split it to reduce processing time when you are working with them. Compared with previous free reservation calendars, these one will reduce your booking management time. There are 4 big parts in this spreadsheet. Those are :
Here, you can set all of your spreadsheet parameters before you use it daily
This would be your daily worksheet. You should type all of your guests/customers information here, and there is an availability panel where you can see the availability of your rooms/cars at respective dates quickly
This worksheet will summarize detail information on hotel rooms/cars availability within 1 year period.
You can check records of your guests/customers information monthly.
Daily Reservation Calendar
Below are steps on how to use this calendar :
1. Go to Setup worksheet
2. Type reservation calendar start period (the spreadsheet will show calendar for 12-month period only, start from the month you put on setup worksheet).
3. Type total number of your hotel room
4. Go to “edit room information”, it will move to “Room Information” worksheet automatically
5. Type your room
6. Type number of rooms for each room types as well as its prices for low, normal and peak season dates. You can type their description and description column.
7. Select room type and type related room numbers on the second table. You can type additional information for each room in Notes column.
8. Back to Setup worksheet
9. Type Status name for your reservation status (maximum 10 status). Put “X” at UCode column for Cancellation status, to release room reservation when any guests are cancelling their reservation.
10. Define a number of available room color marker which will be shown on reservation calendars when the conditions are met. Only yellow and light green color are available to be customized with your number where yellow color should have less number than light green color. Red and green color are already set with fully booked and available conditions, where available conditions is define as number above light green number.
11. Type Peak Season period (maximum 5 range period). It will differentiate font color on reservation calendar (dark orange) and also will show Peak Season price on Calendar helper in Booking Guest Information worksheet
12. Type Low Season period (maximum 5 range period). It will differentiate font color on reservation calendar (dark red) and also will show Low Season price on Calendar helper in Booking Guest Information worksheet
13. Type Holiday information in Holidays table (maximum 50 holiday dates). Cells with holiday dates will be bordered with Pink color.
14. Go to Booking Guest Information worksheet. This is a place to manage your reservation daily. You can check and put any guest information quickly. There is a helper table on top of this worksheet which should help you getting useful information before typing guest booking information. It will show you room availability as well as price on 20 days period.
15. Check Room Availability and Price. Type the dates where guest plans to book at Check Availability cell (M17). It will show the the availability on its date and the next 19 days on the table
16. Go to Show cell (F17) and select “Room Availability” to show number of available rooms. Select “Room Price” to show its price.
17. When guests are confirming their bookings, you can go to guests booking table below. Row 1 to 20 are freezed. You can scroll guest table information without top information being scrolled.
18. Input your guest checkin date at Checkin date column, and type their length of stay at Checkout length column. Checkout date will show automatically. If your guest know Checkout dates only, use the Check Availability cells to calculate their length of stay period. Type checkin date at cell M17 (the same cell as starting availability period), and checkout date at cell O17. You will get the length of stay at cell N17 (cell with blue color). Copy/type this number to your guest booking table checkout length.
19. Input your guest name at family name column
20. Input your room number. To see which specific rooms are available, go to Availability – Room Number worksheet
21. Select month where your guest plan to stay and expand the rows by clicking the (+) sign at the left bar of the worksheet.
22. Back to Booking Guest Information worksheet and type the room number. If there is a double booking, or you accidentally type incorrect room number, you will see dates cell on Room Availability table table turn into dark orange which means there is a double booking on the same room on those dates.
23. Type Booking Status dan Dates in respective columns
24. Type Cancelled in Booking Status column if any guests cancel their reservation to release the room
25. Type other information in other columns. You can modify add, delete or modify any columns starting from column R to the right to fit your own needed information.
You can download lite version below.
Daily Reservation Calendar Lite V2.43 (575.0 KiB, 13,866 hits)
Hourly Reservation Calendar
Main differences between this calendar and the daily one, you should tipe time interval in setup worksheet to set your monitoring time within one day. There will be 2000 time intervals. If you set 24 hours, it will cover 2000 days, while if you set 1 hour, it will cover 83 days. Also, those 2000 time interval is arranged to the right in availability worksheet. As informed above, it is arranged in purpose to minimize any incompatibility issues, if it is using a scroll bar, which should arise if you used this spreadsheet in other computers.
In this spreadsheet, you will find a recurring customer worksheet which will ease you on managing customers who book your cars/equipment frequently on weekly/monthly bases. This feature should be needed if you are renting a sport courts, or applying membership scheme to your customers. Steps on how to use it are the same with daily reservation calendar steps. There is a how to use worksheet inside them that you can read also.
Hourly Reservation Calendar Lite V3.03 (782.6 KiB, 4,150 hits)
If you are satisfied with lite version of these spreadsheets and want to expand it to handle until 12000 guests/customers, you can purchase the Pro version below. There are fully unprotected also (not just shown worksheets), so you can modify it to meet your own business needs. You can purchase them as separate spreadsheets or as a single package. Price reduction applied when you purchase them as a single package.
HOURLY Reservation Calendar Pro – USD 19
DAILY Reservation Calendar Pro – USD 19
HOURLY and DAILY Reservation Calendar Pro – USD 33