It’s that time of year. The updated Hotel Revenue Pickup Report is now available.
Nothing has changed with the Excel file, apart from updating the weekend highlights. It still includes:
- A Summary sheet – Monthly Rooms, OCC %, Revenue, ADR, Quarterly Revenues
- Monthly Pickup – to track monthly revenues for current and following year
- Pickup Sheet – the backend of the file, tracking the daily rooms and revenue for a given month
- Weekly Comparison – Weeks from January 1st to the end of the year, compiled automatically.
- Monthly Revenue Sheets – Cleaned up versions of the Pickup Sheet to show actual daily numbers with summary of pickups.
If you are unfamiliar with the Pickup Report I have developed, I would suggest taking a look at the Hotel Revenue Pickup Report page.
Advanced Pickup Report
The Advanced 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 2015 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 time-frame though and enter the data a week in advance or other 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.
The blue cells at the bottom are going to be a total for the day, and days prior, so you can see the progression easily of rooms and revenue growing (hopefully!)
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 2015 sheet.
MONTH SHEETS looks like the DATA 2015 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. There 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.