90.2K

Verified Solution

Link Copied!

See attachment for an Excel Spreadsheet to accommodate your forecasting assignment. Please follow the instructions within the spreadsheet. . Fill in all cells in orange. I have completed some cells for you to get you started on both worksheet tabs.

Rooms Sold Forecasting Worksheet MARKET SEGMENT SATURDAY SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY Total 170 TRANSIENT ROOMS Reservations Booked Reservations Pickup Total Transient Reservations 150 25 20 330 45 375 190 175 40 40 25 0 0 0 125 20 20 0 30 30 0 0 0 GROUP ROOMS Definate Group #1 Definate Group #2 Definate Group #3 Definate Group #4 Tenative Group #1 Total Group Reservations 0 0 0 40 40 40 20 30 0 0 0 501 30 70 0 0 0 0 50 0 55 40 70 TOTAL ROOM RESERVATIONS 230 Rooms Sold Forecasting Worksheet-600 Rooms 1 Week Offset Friday MARKET SEGMENT Saturday Sunday Monday Tuesday Wednesday Thursday Friday Total 150 300 400 400 350 180 TRANSIENT ROOMS Reservations Bocked Reservations Pickup Total Transient Reservations 10 160 120 10 130 30 330 50 450 40 440 40 390 20 200 1,900 200 2,100 Week 3 of January 2018 Another Busy Week for Flagstaff Hotel This can be done individually or in a group. For the third week, the hotel is forecasting a busier week and more rooms sold (than week 1). To prepare the third week rooms sold forecast, have the first week's rooms sold forecast when making changes to week 3 numbers: 1. Increase the third week's 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. 2. Insert the following week 3 group room blocks into the spreadsheet: 40 40 25 30 30 GROUP ROOMS Definate Group #1 Definate Group #2 Definate Group #3 Definate Group #4 Tenative Group #1 20 20 40 40 50 40 30 70 20 30 50 3. Enter the Week 3 rooms sold from the rooms sold worksheet onto the Week 3 room revenue forecasting worksheet. 4. Use the same average daily rates (ADRS) as Week 1 for both the transient and group rooms sold. (Hint: Do NOT use the same ADRs for your TOTALS column) 5. Calculate the total room revenue for Week 3, and complete the Week 3 room revenue forecasting worksheet. Rooms Sold Forecasting Worksheet MARKET SEGMENT SATURDAY SUNDAY MONDAY TUESDAY WEDNESDAY THURSDAY FRIDAY Total 170 TRANSIENT ROOMS Reservations Booked Reservations Pickup Total Transient Reservations 150 25 20 330 45 375 190 175 40 40 25 0 0 0 125 20 20 0 30 30 0 0 0 GROUP ROOMS Definate Group #1 Definate Group #2 Definate Group #3 Definate Group #4 Tenative Group #1 Total Group Reservations 0 0 0 40 40 40 20 30 0 0 0 501 30 70 0 0 0 0 50 0 55 40 70 TOTAL ROOM RESERVATIONS 230 Rooms Sold Forecasting Worksheet-600 Rooms 1 Week Offset Friday MARKET SEGMENT Saturday Sunday Monday Tuesday Wednesday Thursday Friday Total 150 300 400 400 350 180 TRANSIENT ROOMS Reservations Bocked Reservations Pickup Total Transient Reservations 10 160 120 10 130 30 330 50 450 40 440 40 390 20 200 1,900 200 2,100 Week 3 of January 2018 Another Busy Week for Flagstaff Hotel This can be done individually or in a group. For the third week, the hotel is forecasting a busier week and more rooms sold (than week 1). To prepare the third week rooms sold forecast, have the first week's rooms sold forecast when making changes to week 3 numbers: 1. Increase the third week's 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. 2. Insert the following week 3 group room blocks into the spreadsheet: 40 40 25 30 30 GROUP ROOMS Definate Group #1 Definate Group #2 Definate Group #3 Definate Group #4 Tenative Group #1 20 20 40 40 50 40 30 70 20 30 50 3. Enter the Week 3 rooms sold from the rooms sold worksheet onto the Week 3 room revenue forecasting worksheet. 4. Use the same average daily rates (ADRS) as Week 1 for both the transient and group rooms sold. (Hint: Do NOT use the same ADRs for your TOTALS column) 5. Calculate the total room revenue for Week 3, and complete the Week 3 room revenue forecasting worksheet

Answer & Explanation Solved by verified expert

See Answer