Updated for 2023!
The Pickup Report is designed to be easy to use and provide the results you are after. Using this Excel sheet, you will be able to track the progress of rooms and revenue for each month on a daily basis, as long as the entry of the data is done in a consistent basis. The data won’t be reliable or useful if you fill it out daily for one week, and then skip a few days the next. Try to stick to a daily routine of entering the numbers in first thing in the morning or make it the last thing you do before leaving the office.
The Pickup Report is broken up into two files (JAN-JUN, JUL-DEC) to help keep the size and usability of the file to a better scale. Having all twelve months in one file will lead to slower load times towards the end of the year once all the fields have been filled in and calculated.
In each file, there are various sheets:
SUMMARY SHEET will be self-explanatory. You enter in the data manually for previous years, if wanted, and the data for the current year will be calculated automatically. Data for the six months not included in the file will have to be manually entered, unfortunately.
DATA 2023 will be the workhorse for the file. You begin by entering data for Day 0 (far left set of columns in blue). In most cases, this will be the last day prior to the start of the month (i.e. December 31st for the January Pickup Sheet.) You could use a different time-frame though and enter the data a week in advance or another date.
The other columns are for the daily data. The fields in green at the top of each day are for your actual sales, so enter in the data for those fields after the night audit has finished for that night. The other daily data will have to be entered in at a somewhat consistent basis to make it useful. I suggest entering it at the same time as you do the actual data so it accounts for any walkins that are staying for multiple days.
At the bottom of the column for the day, there are four extra cells.
Top Left: Total rooms on the books for the month
Top Right: Total revenue on the books for the month
Bottom Right: ADR for the reservations on the books
Bottom Left: A rough estimate of projected total revenue.
The revenue estimate is calculated with a simple formula: (Total remaining rooms x Last Year’s Occupancy %) x ADR + Total Revenue on the Books currently. It will be more accurate as the month progresses and the hotel fills up. A simple tool that you can use when asked, “What’s the projected revenue for the month?”
Most PMS systems should be able to give you a forecast report for a date range, and modern ones should allow you to export the data as an Excel or CSV file. From there, it’s just a matter of copying and pasting the data.
Important Note: Be sure to use Excel’s Match destination formatting option for pasting the data. Otherwise, you will end up with the numbers pasted in a different font and size than what the rest of the Excel sheet is.
MONTHLY PICKUP is a way for you to track rooms and revenues for months in advance. This is incredibly useful in tracking any peak months well in advance. The shaded columns will calculate the difference between the current and prior month’s data. Green data is the actual sales totals, and is pulled from the DATA 2023 sheet.
MONTH SHEETS looks like the DATA 2023 sheet, but you don’t do any data entry here. All the cells are calculating the difference between the current day and the day prior. The green cells are going to be the difference between the day and the Day 0 data you started with. The blue cells at the bottom are going to be a total pickup number for that day’s reservations (excluding the green cells).
There aren’t any charts or graphs in these Excel sheets. Their purpose was to help people collect the data that they need to make smart revenue management decisions. If you notice problems with the Excel Sheets, or have suggestions for future versions, please let me know. The tool is to help revenue managers around the world do better at their jobs.
Hope you find it incredibly useful and look forward to talking to some of you in the future.