Booking and Reservation Calendar

• Version
• 85'924 Downloads
• 5.1 Mb File Size
• January 3, 2022 Updated
• 103 Number of comments
• ★★★★★

  ★★★★★
  Rating

This booking and reservation spreadsheet comes with multiple templates. Take a look below to pick the best one for your needs.

You will need Microsoft Excel to edit all these templates. If you don’t have it yet, you can get it as part of "Office 365" from Microsoft's website here.

Template Contents

Hotel Reservations: this spreadsheet is great for hotel room occupancy management. You can use this if you manage a small hotel or rooms and you don’t want to use any complicated software to manage your reservation.

Monthly Booking Calendar: This reservation template is having month layout, like monthly calendar layout. So, you can check or manage your booking by viewing the availability month by month.

Daily and Hourly Reservation Calendars: These are somewhat general reservation calendars that do not require to tweak the formulas, and it comes with several options that allow easy customization. There is no guest management function in this template. If you need an all-in-one guest management software, this template won't work for you. Try an all-in-one guest management software like Trafft or Amelia instea. This one is aimed to hotel, apartment, car rental or other small businesses and it is made to manage up to 100 rooms or 100 cars. 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.

Restaurant Reservation: This reservation spreadsheet is based on hour reservation. The basic reservation scheme is the same, but I put tables number, smoking and non-smoking preferences, and table capacities as parameters in this spreadsheet. But, the formula will work only on the table number

Banquet Halls Reservation: If you have or manage a banquet hall and very often reserved for wedding ceremonies, school parties, press conferences or many other purposes, you might need this spreadsheet to help you manage your reservation. This is a single worksheet reservation where you can see your banquet hall availability time and your reservation information.

Room Booking Calendar: This room booking calendar is an excel spreadsheet that should help you managing your room reservation for one year period in calendar visualization. It is simple and you can use it across different Excel version and Operating System where you can manage it in any laptops without incompatibility issues.

Hotel Reservations

How to use this reservation template:

1. Go to availability worksheet and put your room type and number in the corresponding column. There are samples there that you can modify. You can continue the number by putting your room information and the reservation column will be revealed automatically.
2. Put your start date reference. This start date is used for the scroll bar as its scroll start date.
3. There are two dates next to the Hotel Availability Table title to help you monitor the reservation window.
4. All reservation information in that window will be differentiated by the color. The blue color means that the room is available on that date, red color means that the room is reserved, and yellow color means the room is double booked, so you have to adjust the reservation.
5. Once you see that the room is available on customer requested date, go to Room Reservation worksheet to fill your customer information.

Since this is a simple template, you can modify it to suit other type of reservations.

Monthly Booking Calendar

And I created this booking calendar for hotel booking purposes. If you already downloaded my previous hotel booking template, you will see the different. You can put all room information down along the column because the period layout is column based. But, this one has a calendar layout which mean you cannot put your room information down along the column. So, you have to put your room information in room booking worksheet.

In this worksheet, you can put type of the room and room detail. For example, you have 4 Standard Rooms, 3 Deluxe Rooms, 2 Superior Rooms and 1 Suite Room. Put that information in corresponding table. After that you can break down each room in the table below room type column.

You can see how the room displayed in Calendar worksheet. You can set your booking period by selecting the month and fill the year. And you can select whether you want to see room availability based on room type or room detail. If you select “room type”, you will see numbers inside the calendar that represent the availability of rooms with that type. If the cells turn red, it means the rooms are not available. If you select “room number”, the calendar will display the availability of that room where if the room is available it will show you number 1 (not blue color due to conditional formatting limitation). If the cell is red, it means the room is not available. If the cell is yellow, it means the room is double booking.

There is a scroll bar to scroll between rooms, since the maximum room availability that can be displayed is four. And the same with the previous hotel reservation template, once your customer agreed with the time, you can go back to “room booking” worksheet to fill your customer detail information.

Daily Reservation Calendar

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 :

> Setup
Here, you can set all of your spreadsheet parameters before you use it daily

> Booking
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

> Availability
This worksheet will summarize detail information on hotel rooms/cars availability within 1 year period.

> Summary
You can check records of your guests/customers information monthly.

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.

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 as for the daily reservation calendar above.

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.

Restaurant Reservation

This spreadsheet consists of two worksheets. To customize this spreadsheet, go into availability worksheet and put your tables information in table type, capacity, and table number columns. And you can custom the display of reservation time here, by change the time step value (in minutes). After you finished entering all information, you can go to restaurant reservation worksheet to start your reservation log. You can custom the column in this worksheet except the table number because this is the reference of the formula. Once you fill the reservation or you want to see the reservation information, go back to the availability worksheet and slide the date to the date of reservation.

You can change the scroll bar in the worksheet the new scroll bar if it can’t work with your Excel version.

Feel free to give me some feedback about real situation, since I don’t have a restaurant :-).

Banquet Halls Reservation

Here is how you can use this spreadsheet:

1. Define your time period for reservation. I limit to only have maximum 4 time periods. But, you even define one period, if you only have one time period.
2. Enter your reference start date information which will be used as reference for scroll bar starting date.
3. There are two cells next to banquet hall window period title to help you see your reservation window.
4. The availability will be based on the cell color. Blue color means that the period is available for reservation, red color means that the period is already taken, and yellow period means that the period is double booking where you have to change the booking time period.
5. Once you and your customer agreed on the date and period, you can fill your customer information in the customer reservation table below the availability table.
6. There are a color conditional formatting inside the customer reservation table, where the cells will turn into gray color if the reservation is out of date.

Room Booking Calendar

This template uses Excel where you can transfer, process and analyze inputted data easily using other Microsoft Office programs, like Word and PowerPoint. Or, you can create your own chart within or in another Excel program. It is equipped with formulas and functions that automate availability calculation, calendar date placement and booking calendar visualization in seconds. It erases your manual tasks which sometimes could confuse you. This spreadsheet should suite any room rental business, like hotel, apartment, villa, and many others who rent their rooms on daily bases.

Features

In this room booking calendar you will find useful features as summarized below.

1. Calendar style booking availability

You can quickly see dates with your room availability status in one full year with respective color markers.

> Availability color markers

You can set until 3 different remaining rooms as color markers in the Availability Calendar. As you can see in picture above, there are four different color markers that can be set based on availability numbers that you put in respective cells, where red color cannot be altered. It has zero value by default. You may set remaining colors where orange box must has less number than yellow box, and yellow box must have less number than light green box.

> Availability and rates quick info table

Table helper in Availability Calendar worksheet to see room availability and rates at respective dates. You can see this table on the right side of the picture above. You can set the start date and you can toggle between “Show Availability” and “Show Rates” to see detail information on respective week.

2. 30 room categories and 100 room numbers

You can define until up to 30 categories for all of your rooms and up to 100 room numbers.

3. One full year booking board

You can see map of your room availability in board alike visualization.

4. 30 different price groups

You can have until 30 price groups that you can set, select and distribute over 365 days.

5. Double booking marker

All remaining rooms at respective dates will be calculated automatically by created excel formulas. And any double bookings will be marked in Booking Board worksheet where you can spot it quickly to fix its respective double bookings.

6. Custom summary

You can see summary of all your guests based on their booking status

7. Booking dashboard

You can analyze your hotel occupancy ratio and generated revenue monthly in Chart visualization. You can analyze performance of your booking agents.

8. 6000 booking lines

You can book guests until 6000 entries. You can expand it to accommodate more guests if you need more lines.

All worksheets are unprotected to fully tweaking the formula and customizing its layout. You can put your company logo, name, adjust columns and rows, add more worksheets for your specific needs, or tweak formulas (you need to have excel knowledge to do this).

You can use this spreadsheet with Excel 2010 for Windows and above and Excel 2008 for Mac and above without having incompatibility issues.

The template is Multi User License (within one Company). Any employees are allowed to use this spreadsheet as long as they are working within one company.