Revenue Management Essentials: Hotel Pickup Report [Free Download]

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.

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.

Also, weekends are highlighted in both the Pickup Sheet and the Monthly Sheets (except for January).

Using the Report

I have tried to make this as easy as possible to limit the amount of time you have to spend entering in information so you can get the most out of using the report.

To set it up properly, you need to enter in a few pieces of information on the Summary sheet:

• Hotel Name
• Year
• Hotel Rooms (used in calculating OCC%, RevPAR)
• 2011 Totals
• Budget (Revenue only, in 2012 section)

After that, you’ll do most of the work in the Pickup Sheet and the Monthly Pickup Sheet.

In the Monthly Pickup Sheet, at the beginning of each month, go through the revenues for the year and enter in the amounts going down. The 2013 grid is there if you would like to track revenues beyond the current year.

With the Pickup Sheet, it is the same process. On the last day of the month, pull up the daily revenue report for the following month and enter in the Rooms and Revenue in the far left columns. The ADR will adjust automatically.

Then at least once a week (or it becomes too cumbersome to do properly) enter in the Actual Rooms and Actual Revenue. When you enter in those numbers, you should see the numbers start to shift in other parts of the spreadsheet. The pickup totals will be calculated, and the numbers will be transposed to the corresponding month sheet and to the Weekly Comparison sheet.

At the end of the month, you will be able to tell with a quick glance which weeks performed better than others, which days did, and how much revenue you picked up within the month.

Please note that the 2013 file will import the data from the 2012 file, as long as the files remain in the same folder and the file names go unchanged. It will pull in the data for rooms and revenue for the month, and compile the numbers for the weekly comparison.

Getting the Pickup Report

The file is a small Excel file, which is completely blank – meaning you won’t have to go through and clear out any old data.

Price? Free

There is the option to specify a price. Any amount will be greatly appreciated as it will go towards my time to develop future versions, and more advanced versions. If you have ideas for what you would like to see, please let me know.

Hotel Pickup Report 2012 + 2013

Note: I do not provide support for people who download the file for free, nor will updates appear on the website.

Here are some screen shots of the file: