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.
Unlike the advanced pickup report, the 90 Day Pickup Report is one spreadsheet only.
In the file, there are various sheets:
| SUMMARY | – basic summary of rooms, revenue, ADR, RevPAR by month for current year and previous years |
| MONTHLY PICKUP | – the sheet to track monthly progressions through the year and a year in advance (optional) |
| MONTH SHEETS | – a sheet per month to track data through the year |
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.
MONTHLY DATA SHEETS will be the work horse 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 timeframe though and enter the data a week in advance or other date.
The other columns are for the daily data (rooms sold per day, revenue on the books per day) of reservations on the books. The fields in light purple 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 walk-ins that are staying for multiple days. This report is designed to be filled out 90 days at a time. The first day is going to cover January 1st to March 31st. The second day, January 2nd to April 1st, and so on. By the end of January, you will be filling out to April 30th.
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?”
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. Dark purple cells are the actual sales totals and is pulled from the DATA 2023 sheet.
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.