Revenue Forecasting Problem SetsThis section involves the revenue forecasting process for...

Revenue Forecasting Problem Sets

This section involves the revenue forecasting process for the Rooms, Room Service, and Restaurant Departments. The process will be to first present the forecast for the first week to explain and demonstrate how a weekly forecast is prepared and how it is used. Information will then be given to prepare the second week forecast that will include changes from the first week that increase or decrease the volume and revenue. Students will prepare the second week forecast for practice. It will be discussed in class and the answers are provided in the book. Students can do this work individually or in groups.

The third week forecast will include changes from the first week forecast either increasing or decreasing rooms sold compared to the first week forecast. Students will prepare the third week forecast as a problem set and turn it in. It will be graded and is worth 25 points. Students can do the third week forecast individually or as a group. The fourth week forecast will be an in-class 25-point quiz where students are expected to prepare the fourth week forecast by themselves.

This process will be followed for room revenue forecasts, restaurant and room se1vice revenue forecasts, and front desk and housekeeping wages forecasts.

Room Revenue Forecasts

Developing the Rooms Department revenue forecast involves two steps. The first is to forecast rooms sold and the second is to forecast room revenue. The rooms sold forecast involves several variables as demonstrated in the following matrix:

The important elements of forecasting room revenues can be combined into pairs of information. Each one of these or “twosomes” is important in preparing room revenue forecasts. Let’s review them again.

The forecast for each of these segments is prepared in different ways. The transient room reservations confirmed or guaranteed are generated from the Revenue Management or demand tracking system of the hotel for each day. The Hotel Director of Revenue Management or Hotel Reservations Manager then projects or forecasts the number of additional reservations expected to be made between the current date and the day of arrival, including same day walk-ins. These pickup reservations are added to the confirmed reservations to forecast the total rooms sold for the day.

The group rooms forecast is generated by the group booking report that provides the number of definite group rooms booked per day according to the group room blocks. The Director of Sales and Marketing will then determine the amount of tentative or prospective group rooms that are in negotiations having a high probability of becoming definites. Tentative group rooms that are expected to become definites are then added to existing definites to get the forecasted group rooms sold for the week.

When the total rooms sold forecast for the week is completed, average room rates are projected for each segment for each day. Total room revenues are then calculated by multiplying rooms sold times the average room rate for each market segment and then adding them together to get the total rooms revenue forecast for each day for the next week. The weekly forecast will include rooms sold, occupancy percent, average room rate, and total room revenue for each day of the week and for the total week. The hotel selling strategy team then reviews, approves, and distributes the weekly revenue forecast.

The steps to prepare a room revenue forecast are as follows:

1. Collect confirmed transient reservations and definite group reservations for each day of the week (DOA) from the Revenue Management report and group rooms report.

2. Project the number of expected additional transient reservations pickup and tentative group reservations for each day.

3. Determine the daily average room rate for transient and group rooms sold.

4. Calculate the daily room revenue for transient and group rooms by multiplying the daily number of rooms sold times the daily average room rate to get the daily room revenue.

5. Add the total daily rooms sold for each day of the week to get the total rooms sold for the week.

6. Add the total daily revenue for each day of the week to get the total room revenue for the week.

7. Calculate the average weekly room rate by dividing total room revenue for the week by total rooms sold for the week.

8. Calculate the daily and weekly occupancy percent by dividing daily rooms sold by total hotel rooms per day and per week.

9. Double check the weekly amounts by adding the daily rooms sold and room revenues across for the seven d11ys and comparing it by adding the transient and group market segment down to get the same total rooms sold for the week and the same total room revenue for the week.

Apply this process to the weekly practice exercises to calculate the total weekly room revenues. Following are the worksheets and weekly forecasts to use in preparing weekly room revenue forecasts. Week 1 will be provided as an example and explain how the weekly room revenue forecast was prepared. Week 2 will have changes in rooms sold compared to the first week and be a practice week with the answers provided in the book. Students should use Week 2 to learn the forecasting process and be prepared to do the Week 3 forecast. Week 3 will again have changes from Week 1 forecast and be a 25-point practice exercise that will require students to calculate and prepare the 3rd week room revenue forecast that will be turned in and graded. Week 4 will also have changes from Week 1 in rooms sold for transient and group and will be a 25-point in-class quiz.

Week 2 of Period 1

A Busier Week

For the second week, the hotel is forecasting a busier week and more rooms sold than in the first week’s forecast. To prepare the second week rooms sold forecast, use the following steps:

1. Begin with the first weeks rooms sold forecast. All changes will be made to the transient rooms sold numbers from the first week.

2. Increase the second weeks rooms sold forecast as follows: Weekdays are Monday through Thursday, weekends are Friday through Sunday:

A. Increase transient weekday and weekend rooms reservations by 15 per day.

B. Increase the transient reservations pickup per day by 10 on weekdays and 5 on weekends.

C. Total the transient rooms sold by day and add up the seven days to get the total rooms sold for the week. Check this weekly total rooms sold number by adding down the weekly reservations made plus weekly reservations picked up.

D. Total the group rooms sold by day for all of the group room blocks to get the daily group rooms forecast.

E. Add up the daily group room totals for the seven days to get the weekly group room totals.

F. Add the daily transient and group rooms sold for each day to get total the daily rooms sold forecast.

G. Add the seven days of total rooms sold to get the total weekly rooms sold. Check that by adding total transient rooms sold for the week to total group rooms sold for the week.

3. Calculate the daily and weekly occupancy percent by dividing total rooms sold by total hotel rooms, 600 for each day and 4.200 for the week.

4. Disregard the arrivals and departure lines. They will be used to forecast and schedule front office wages.

5. Use the following 2nd Week Forecast Form for your calculations:

Check your forecast with the answers on the 2nd week forecast that follows the blank forecast page. Be sure and prepare your forecast first before checking it to the correct forecast.

To forecast the total mom revenue for the second week, use the same daily room rates for the transient and group as in the first week and make the calculations on the rooms revenue forecasting worksheet. Take the dally rooms sold from the rooms sold forecasting worksheet and enter them on the room revenue forecasting worksheet. You will find this worksheet after the rooms sold forecasting worksheet. Remember that you will have to calculate new daily average room rates on the bottom of the worksheet and new weekly average room rates on the right side of the worksheets for each week forecasted.

The final part of the room revenue forecasting section is to prepare the fourth week forecast. This will be given as a 25-point quiz and students are expected to do their own work on this quiz.

Restaurant and Room Service Forecasts

This section will involve the revenue forecasting process for the Restaurant and Room Service Departments. The rooms sold forecast for the hotel is used by all the departments in the hotel to forecast because it is the best indicator of business activity in the hotel. We will use the same rooms sold forecasts for Period 1 that was prepared in the previous section to prepare the restaurant and room service revenue forecasts. Many of the steps in forecasting food and beverage revenues are the same as those used in forecasting room revenues.

We will follow the same format as that used in the room revenue forecasts by starting with a discussim1 of how the first week room service and restaurant forecast was prepared and then prepare and discuss the second week forcc;1st. The third week forecast again is a 25-point practice exercise. The fourth week forecast will be a 25-poinl in-class quiz.

Note how similar the restaurant and room service forecasting process is to the rooms forecasting process. This includes the formal of the forecasting worksheets. The forecasting process will include forecasting customers and average checks to calculate restaurant and room service revenues. The weekly forecast will be prepared day by day and added up for the total weekly customers, average check, and revenues. The breakfast, lunch, and dinner meal periods of a restaurant will replace the transient and group market segments used in the room forecast.

Refer to the following restaurant and room service forecasting worksheets as we goover the steps to prepare each of these forecasts. We will start with the room service forecast and then move on to the restaurant forecast

Room Service Customer Count Forecast

1. Enter the transient, group, and total rooms sold by day for the week from the appropriate weekly rooms sold forecast that were used in the previous rooms forecasting section.

2. Convert the rooms sold by day to hotel guest count by day by using the hotel’s historical average of number of guest per occupied room. In our forecasting examples, we will use a historical average of 1.2 guests per room.

3. Identify the percentage capture rates calculated from the room service history for each meal period. A capture rate is the historical percentage of hotel guests that use room service for each meal period. In our forecasting examples, we will use guest capture rates of 25% of hotel guest counts for room service breakfast, 8% for room service lunch, and 15% for mom service dinner. This step converts hotel guests into meal period customers.

4. Calculate room service customers by day by meal period by multiplying the meal period capture rate by the daily guest counts.

5. Add up the seven days of room service daily customer counts to get the weekly total customer counts. Check by adding across the seven daily totals for the week and comparing them by adding down the three meal period totals fm the week. They should be the same.

6. Transfer room service customer counts for each meal period and day and the total to the room service revenue worksheet.

Restaurant Customer Count Forecast

1. Enter the historical average daily customer count for each day for each meal period on the restaurant forecasting worksheet. Total the week down and across.

2. Based on the forecasted hotel house counts and on the outside activities, enter the appropriate house count adjustment that will reflect the changes from the historical daily customer counts in expected business levels for the week for the restaurant.

3. Total the new forecasted daily customer counts for each day by adding or subtracting the house count adjustment number to the historical daily customer count. The adjustment can be zero if there is no need to adjust the historical average. This gives you total customers for the day. Add the seven days across to get the weekly totals and check by adding the three meal period totals down.

4. Transfer restaurant customer cow1ts to the restaurant revenue worksheet.

Room Service and Restaurant Revenue Forecasts

1. Double check the customer counts for each outlet by comparing the customer counts calculated on the customer count forecasting worksheet to the customer counts forecasts entered on the revenue forecasting worksheets. They should be the same.

2. Enter daily average checks by meal period for each day. These are obtained from historical daily average checks for each meal period in the restaurant and in room service.

3. Calculate the meal period revenue for each day and each meal period by multiplying meal period customer cow1ts by meal period average checks.

4. Add up the revenues for the seven days to get the total revenue for each meal period for the week. Check by adding down the total weekly revenues for the three meal periods. The total weekly revenues should be the same.

5. Calculate the weekly average check for each meal period and the total week by dividing total weekly sales by total weekly customer counts.

6. Check your weekly totals by adding the seven days of daily customer counts and revenues across and comparing that by adding down the three weekly meal period totals for customers and revenues. They should be the same.

7. You will have one restaurant and room service customer count forecast worksheet and two separate revenue forecasting worksheets—one for room service revenues and the other for restaurant revenues