Revenue Management Essentials: Hotel Pickup Report [Free Download]

There are two versions of the hotel pickup report available. Keep reading to learn more about what a pickup report can do for you, or download now:

90 Day Pickup Report

Download Now

When I first started down the path of revenue management when I worked as the Revenue Manager for two hotels, it quickly became obvious to me that I was going to have to learn and master Excel. The reason for this is because revenue managers have to keep track of a lot of data and be able to analyze it quickly and effectively. Having to run the same reports constantly grows tiresome quickly, so you start to throw quick spreadsheets together to keep track of the data you want in a way you can read. These spreadsheets that get thrown together can track all sorts of data depending on the day and the line of work you are in, but one of the most essential reports that a revenue manager will have to build is a pickup report and/or a revenue tracking sheet. Anyone using a decent PMS knows that you can easily get daily revenue reports or monthly reports that break down the numbers daily, so why build one in Excel?

Working with Data

The problem with most reports that you receive from a PMS is that you can’t do anything with the numbers. You can’t figure out quickly a lot of things, like how well Mondays do in comparison to Sunday nights without picking up a calculator and plugging away at it. Building a revenue tracking sheet allows you to easily develop the formulas you need to give you answers to the questions being asked by yourself, members of your team, or the ownership group. Developing a hotel pickup report allows you to prepare answers to other questions more firmly without a lot of guess work, such as:

• How much revenue has the hotel picked up for the summer months over the past few months?
• How much revenue is on the books for March, and how does that compare to a year ago at this time?
• How much revenue did we pickup within the past month or past week?

Pickup reports can be as simple or as complicated as you see fit. At the Xotels website, Patrick Landman suggests having a pickup report that can track how well the various segments (rate tiers, for example) are performing. A more complicated pickup report could be useful for a busy resort, but I tend to keep things simple for my own tastes.

Developing a Pickup Report

A pickup report is extremely useful in determining rate strategies and can help calm the fears of the owner who sees $0.00 on the books for a month down the road. With a properly filled out pickup report, you can tell when it’s appropriate to raise the rates slightly (i.e. during the month of April when people book the most rooms for June) or whether you need to target a certain month for some marketing campaigns because booking activity is relatively soft. I have been on the search for a decent pickup report that wasn’t too complicated to work with from Day 1, and still have yet to find something worthwhile to use. I have been slowly developing my own over the course of the year and thought I would share the bare-bones version of it to see if anyone is interested in using it.

Getting the Pickup Report

The old one is being made available for people to download and modify freely. The main difference between the older one and the 2014 version is the weeks being highlighted. The weeks (Sunday to Saturday) will not align from previous years to the current year. If that isn’t important to you, then you can still use the spreadsheet without issue. For the price of the 2014 version, you will get full support if you have any problems, as well as, the 2015 version when it is completed. The Day-to-Day version is a bit more advanced. You can read more below and it will be getting updated for 2014 as soon as possible. Here is what it consists of:

• 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.

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.

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 2018 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.

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!)

New for 2018’s version of the spreadsheet, the white cell below the blue cell for Total Rooms is an estimated revenue forecast for the month. It calculates how many rooms are remaining to sell, multiplies it by last year’s occupancy percentage and the month’s ADR to date, then adds on the current revenues on the books. It’s not exact, but will give you a better idea of what will happen based on current and past trends. 

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 2016 sheet.

MONTH SHEETS looks like the DATA 2016 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.

Download Now