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:
Rooms Forecasting Matrix | |||
| Transient Rooms | Group Rooms | Total Rooms |
Confirmed transient reservations |
| ||
Forecasted “pickup” reservations | |||
Total transient rooms sold forecast | |||
Definite group rooms | |||
Tentative group rooms | |||
Total group rooms sold forecast | |||
TOTAL ROOMS SOLD FORECAST |
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.
Room Revenue Forecasting Twosomes | |
Forecasting Segment | Forecasting Twosome |
Market Segment | Transient rooms and Group rooms |
Day of Week | Weekdays Monday–Thursday Weekends Friday–Sunday |
Reservation Status | Confirmed reservations made Reservations to be made (pickup) |
Group Status | Definite, groups with a signed contact Tentative, groups still to be booked |
Room Revenue Calculations | Rooms sold by market segment Times average room rate per segment |
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.
ROOMS SOLD FORECASTING WORKSHEET ________ WEEK ______ PERIOD | |||||||||
MARKET SEGMENT | Offset Friday/ | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Total |
Transient ROOMS |
| ||||||||
Reservations Booked | |||||||||
Reservation Pickup | |||||||||
Total Transient Reservations | |||||||||
GROUP ROOMS | |||||||||
Definite Groups | |||||||||
Tentative /Prospective Groups | |||||||||
Total Group Reservations |
| ||||||||
TOTAL ROOM RESERVATIONS | |||||||||
OCCUPANCY PERCENTAGE | |||||||||
Arrivals/Check-ins | |||||||||
Departure/Check-outs |
ROOMS REVENUE FORECASTING WORKSHEET ________ WEEK ______ PERIOD | |||||||||
MARKET SEGMENT | Friday/ | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Total |
Transient REVENUE |
| ||||||||
Rooms Sold | |||||||||
Average Rate | |||||||||
Total Transient Revenue | |||||||||
GROUP REVENUE | |||||||||
Rooms Sold | |||||||||
Average rate | |||||||||
Total Group Revenue | |||||||||
TOTAL REVENUE | |||||||||
Total Rooms Sold | |||||||||
Occupancy Percentage | |||||||||
Average Rate | |||||||||
Total Room Revenue |
ROOMS SOLD FORECASTING WORKSHEET-600ROOMS ____1____ WEEK ___1___ PERIOD | ||||||||||
MARKET SEGMENT | Offset Friday/ | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Total | |
Transient ROOMS |
| |||||||||
Reservations Booked |
| 150 | 120 | 300 | 400 | 400 | 350 | 180 | 1,900 | |
Reservation Pickup |
| 10 | 10 | 30 | 50 | 40 | 40 | 20 | 200 | |
Total Transient Reservations |
| 160 | 130 | 330 | 450 | 440 | 390 | 200 | 2,100 | |
GROUP ROOMS |
| |||||||||
Group #1 |
| 30 | 20 | 20 | 10 |
|
|
| 80 | |
Group #2 |
|
|
| 70 | 80 | 80 | 80 | 10 | 320 | |
Group #3 |
|
|
|
| 40 | 40 | 20 | 20 | 120 | |
Group #4 |
|
|
|
| 20 | 20 | 20 | 20 | 80 | |
Total Group Reservations |
| 30 | 20 | 90 | 150 | 140 | 120 | 50 | 600 | |
TOTAL ROOM RESERVATIONS | 300/ | 190 | 150 | 420 | 600 | 580 | 510 | 250 | 2,700 | |
OCCUPANCY PERCENTAGE |
| 31.7% | 25.0% | 70.0% | 100% | 96.7% | 85.0% | 41.7% | 64.3% | |
ROOMS REVENUE FORECASTING WORKSHEET ____1___ WEEK ___1___ PERIOD | ||||||||||
MARKET SEGMENT | Friday/ | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Total | |
Transient REVENUE |
| |||||||||
Rooms Sold |
| 160 | 130 | 330 | 450 | 440 | 390 | 200 | 2,100 | |
Average Rate |
| $ 110 | $ 110 | $ 130 | $ 145 | $ 145 | $ 140 | $ 110 | $ 133.55 | |
Total Transient Revenue |
| $17,600 | $14,300 | $42,900 | $65,250 | $63,800 | $54,600 | $22,000 | $280,450 | |
GROUP REVENUE |
| |||||||||
Rooms Sold |
| 30 | 20 | 90 | 150 | 140 | 120 | 50 | 600 | |
Average rate |
| $ 100 | $100 | $ 120 | $ 125 | $ 125 | $ 125 | $ 100 | $ 120.08 | |
Total Group Revenue |
| $ 3,000 | $ 2,000 | $10,800 | $18,750 | $17,500 | $15,000 | $ 5,000 | $ 72,050 | |
TOTAL REVENUE |
| |||||||||
Total Rooms Sold |
| 190 | 150 | 420 | 600 | 580 | 510 | 250 | 2,700 | |
Occupancy Percentage |
| 31.7% | 25.0% | 70.0% | 100% | 96.7% | 85.0% | 41.7% | 64.3% | |
Average Rate |
| $108.42 | $108.67 | $127.86 | $140.00 | $140.17 | $136.47 | $108.00 | $ 130.56 | |
Total Room Revenue |
| $20,600 | $16,300 | $53,700 | $84,000 | $81,300 | $69,600 | $27,000 | $352,500 | |
25 Points Week 3 of Period 1
Another Busy Week
This problem set is worth 25 points and can be done individually or in a group. Follow the same steps as in forecasting the second week. Following are the changes to be used in forecasting the third week:
1. Begin with the first week rooms sold forecast. All changes will be made to those numbers.
2. Increase the third weeks rooms sold forecast as follows:
A. Increase weekday transient room reservations 30 per day.
B. Increase weekday transient pickup reservations 15 per day.
C. Increase weekend transient room reservations 20 per day.
D. Increase weekend transient pickup reservations 10 per day.
3. Third week group room blocks are as follows:
Sat
Sun
Mon
Tue
Wed
Thurs
Fri
Tot
Definite group
#1
20
40
40
40
140
#2
30
30
20
80
Tentative group
#1
40
40
40
20
140
#2
70
70
40
180
4. Complete the total rooms sold forecasting w0rksheet.
5. Enter the Week 3 rooms sold from the rooms sold worksheet onto the Week 3 room revenue forecasting worksheet.
6. Use U1e same daily ave1age rates as Week 1 for both the transient and group rooms sold.
7. Calculate the total room revenue by day for Week 3 and complete the Week 3 room revenue forecasting worksheet.
The 25 points will be graded on the daily and weekly totals from the room revenue forecasting worksheet. Turn both forecasting worksheets in for credit.
ROOMS SOLD FORECASTING WORKSHEET 25-POINT PROBLEM SET ____3____ WEEK ___1___ PERIOD | |||||||||
MARKET SEGMENT | Offset Friday/ | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Total |
Transient ROOMS |
| ||||||||
Reservations Booked | |||||||||
Reservation Pickup | |||||||||
Total Transient Reservations | |||||||||
GROUP ROOMS | |||||||||
Definite Group #1 | |||||||||
Definite Group #2 | |||||||||
Tentative Group #3 | |||||||||
Tentative Group #4 | |||||||||
Total Group Reservations | |||||||||
TOTAL ROOM RESERVATIONS | |||||||||
OCCUPANCY PERCENTAGE | |||||||||
Check-Ins |
| ||||||||
Check-Outs |
|
ROOMS SOLD FORECASTING WORKSHEET 25-POINT PROBLEM SET ____3____ WEEK ___1___ PERIOD | |||||||||
MARKET SEGMENT | Friday/ | Saturday | Sunday | Monday | Tuesday | Wednesday | Thursday | Friday | Total |
Transient REVENUE |
| ||||||||
Rooms Sold | |||||||||
Average Rate | |||||||||
Total Transient Revenue | |||||||||
GROUP REVENUE | |||||||||
Rooms Sold | |||||||||
Average rate | |||||||||
Total Group Revenue | |||||||||
TOTAL REVENUE | |||||||||
Total Rooms Sold | |||||||||
Occupancy Percentage | |||||||||
Average Rate | |||||||||
Total Room Revenue |
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
We need at least 10 more requests to produce the solution.
0 / 10 have requested this problem solution
The more requests, the faster the answer.