Use the Spreadsheet Part 1 in the attached "Group Evaluation and Sales Mix Proposal worksheet to determine the profitability of this potential group. The group would like to confirm 165 guest rooms arriving on Monday April 25 for 3 nights. The group will spend $10,000 per day on food and beverage. Their budget is $275 per night. The transient rate that would be displaced on those nights would be $350. Complete the data in the yellow highlighted cells on the Spreadsheet Part 1 tab. Based on the findings in the summary section at the bottom, answer the following questions in a 300-400-word report back to the director of sales: 1. Would this group be profitable for the hotel? What additional profit would the group bring to the bottom line? 2. What recommendations do you have for the director of sales to negotiate with the group to make the business more attractive to the hotel? Some factors to take into consideration: What will the group spend in addition to rooms? What is the profit on the additional amount spent by the group? (Food and beverage have lower profit margins than rooms.) What additional catering/banquet revenue could the director of sales propose to the group? Is the group commissionable? (Travel agent commissions are typically 10% of rooms revenue.) Part 2: Segmentation Review the mix of business for the previous year at a hotel using the Spreadsheet Part 2 tab in the attached "Group Evaluation and Sales Mix Proposal" worksheet. Use the following assumptions to propose the desired segmentation (including occupancy and rate) for the next year. The hotel's goal is to achieve 6% revenue growth. Occupancy will not exceed 80% due to seasonality constraints. The market will support a 5% transient rate increase with comparable volume. A reduction in contract rooms will translate into a 60% increase in occupancy for other segments (for example, if you reduce contract rooms by 1,000 rooms, 600 rooms will be shifted into other segments). AutoSave OFF A& HOS-466-T3-Group Evaluation and Sales Mix Proposal View Tell me Home Insert Draw Page Layout Formulas Data Review Share 0 Comments Calibrl (Body) v 11 A 9 Wrap Text General 2 Insert v ' . G X Delete v V Pastc BIU V av A Merge & Center Y Conditional Format Cell Formatting as Table Styles Sort Filter # Format Find & Select Analyze Data Sensitivity Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left oft? Yes No C14 B C D D E F G H I K I M N 0 P Q R S LI U V W X Y GROUP EVALUATION 400 Initech Hotel Capacity: Group Name Arrival: Day of Week Dace THU 7 8 4/20/22 SUN MON 4/17/22 4/18/22 150 325 TUE 4/19/22 325 WED 4/20/22 325 4/21/22 250 FRU 4/22/22 280 SAT 4/23/22 350 Trent Demand 50 75 75 75 50 50 50 Forecased Group Definite Total Demand 200 400 400 400 300 3.30 400 0 9 10 11 12 13 14 15 16 17 18 19 20 21 22 New Group Opprtunit, Room Nights! Total 200 100 400 400 300 330 400 0 0 0 0 0 0 0 o Estimate Roomnight Displacement 23 24 $ New Catering Hevenue ADR Summary: New Group: Rooms 0 Profit $0 $0 25 26 27 28 29 30 31 32 33 34 Profit 75% ' 25% 0 SU SO Displaced Transient Rooms Profit New Group & Westing Room Rental Total Group Profit $ 50 $0 30% 0 $0 35 36 37 38 39 40 SPREADSHEET PART 1 SPREADSHEET PART 2 + Select destination and press ENTER or choose Paste # 2 - + 100% AutoSave OFF A& HOS-466-T3-Group Evaluation and Sales Mix Proposal View Tell me Home Insert Draw Page Layout Formulas Data Review Share 0 Comments Calibrl (Body) v 11 A Wrap Text General 2 Insert v ' . G Y X Delete v WS Paste BIV Merge & Center v Y Conditional Format Cell Formatting as Table Styles Sort Filter # Format Find & Select Analyze Data Sensitivity Yes No Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? fx B1 B C. D E F G H I L M N P Q R S S . T LI V W SALES MIX PROPOSAL TY 5 6 Segment Transient Business Tranden Government OTA Advance Purchase AAA Group Contract TOTAL Occupancy Rcomnights Mix of Total Roamnights LY Che TY LY * Che 13000 -100.0% ADIV/0! 18% 12000 -100.0% ADIV/01 17% 7000 100.C ADIV/01 10% % 8000 -100.0% ADIV/0! 11% 5000 -100.0% ADIV/01 7% SODO 100.C ADIV/0! 744 14000 -100.0% ADIV/0! % 20 7000 -100.0% ADIV/01 10% 71000 200.0% 77.8% % Average Race TY LY % Che 280 -100.00 220 -100.0% 175 100.0% 210 -100.0N 181 -100.0% 175 100.0% 215 -100.0% 159 -100.0% ADIV/0! 213.0704 IV/0! | / TY so $0 $0 $0 $0 $0 $0 $0 $o Revente LY * Che $3,640,000.00 www $2,640,000.00 $1,225,000.00 $1,680,003.00 W $945.000 on $575,000.00 $3,010,003.00 W $1,113,000.00 $15,128,000.00 D 0.0% wailablerooms 91250 9 10 11 12 13 14 15 16 17 12 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 SPREADSHEET PART 1 SPREADSHEET PART 2 + Select destination and press ENTER or choose Paste # E2 - + 100% Use the Spreadsheet Part 1 in the attached "Group Evaluation and Sales Mix Proposal worksheet to determine the profitability of this potential group. The group would like to confirm 165 guest rooms arriving on Monday April 25 for 3 nights. The group will spend $10,000 per day on food and beverage. Their budget is $275 per night. The transient rate that would be displaced on those nights would be $350. Complete the data in the yellow highlighted cells on the Spreadsheet Part 1 tab. Based on the findings in the summary section at the bottom, answer the following questions in a 300-400-word report back to the director of sales: 1. Would this group be profitable for the hotel? What additional profit would the group bring to the bottom line? 2. What recommendations do you have for the director of sales to negotiate with the group to make the business more attractive to the hotel? Some factors to take into consideration: What will the group spend in addition to rooms? What is the profit on the additional amount spent by the group? (Food and beverage have lower profit margins than rooms.) What additional catering/banquet revenue could the director of sales propose to the group? Is the group commissionable? (Travel agent commissions are typically 10% of rooms revenue.) Part 2: Segmentation Review the mix of business for the previous year at a hotel using the Spreadsheet Part 2 tab in the attached "Group Evaluation and Sales Mix Proposal" worksheet. Use the following assumptions to propose the desired segmentation (including occupancy and rate) for the next year. The hotel's goal is to achieve 6% revenue growth. Occupancy will not exceed 80% due to seasonality constraints. The market will support a 5% transient rate increase with comparable volume. A reduction in contract rooms will translate into a 60% increase in occupancy for other segments (for example, if you reduce contract rooms by 1,000 rooms, 600 rooms will be shifted into other segments). AutoSave OFF A& HOS-466-T3-Group Evaluation and Sales Mix Proposal View Tell me Home Insert Draw Page Layout Formulas Data Review Share 0 Comments Calibrl (Body) v 11 A 9 Wrap Text General 2 Insert v ' . G X Delete v V Pastc BIU V av A Merge & Center Y Conditional Format Cell Formatting as Table Styles Sort Filter # Format Find & Select Analyze Data Sensitivity Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left oft? Yes No C14 B C D D E F G H I K I M N 0 P Q R S LI U V W X Y GROUP EVALUATION 400 Initech Hotel Capacity: Group Name Arrival: Day of Week Dace THU 7 8 4/20/22 SUN MON 4/17/22 4/18/22 150 325 TUE 4/19/22 325 WED 4/20/22 325 4/21/22 250 FRU 4/22/22 280 SAT 4/23/22 350 Trent Demand 50 75 75 75 50 50 50 Forecased Group Definite Total Demand 200 400 400 400 300 3.30 400 0 9 10 11 12 13 14 15 16 17 18 19 20 21 22 New Group Opprtunit, Room Nights! Total 200 100 400 400 300 330 400 0 0 0 0 0 0 0 o Estimate Roomnight Displacement 23 24 $ New Catering Hevenue ADR Summary: New Group: Rooms 0 Profit $0 $0 25 26 27 28 29 30 31 32 33 34 Profit 75% ' 25% 0 SU SO Displaced Transient Rooms Profit New Group & Westing Room Rental Total Group Profit $ 50 $0 30% 0 $0 35 36 37 38 39 40 SPREADSHEET PART 1 SPREADSHEET PART 2 + Select destination and press ENTER or choose Paste # 2 - + 100% AutoSave OFF A& HOS-466-T3-Group Evaluation and Sales Mix Proposal View Tell me Home Insert Draw Page Layout Formulas Data Review Share 0 Comments Calibrl (Body) v 11 A Wrap Text General 2 Insert v ' . G Y X Delete v WS Paste BIV Merge & Center v Y Conditional Format Cell Formatting as Table Styles Sort Filter # Format Find & Select Analyze Data Sensitivity Yes No Open recovered workbooks? Your recent changes were saved. Do you want to continue working where you left off? fx B1 B C. D E F G H I L M N P Q R S S . T LI V W SALES MIX PROPOSAL TY 5 6 Segment Transient Business Tranden Government OTA Advance Purchase AAA Group Contract TOTAL Occupancy Rcomnights Mix of Total Roamnights LY Che TY LY * Che 13000 -100.0% ADIV/0! 18% 12000 -100.0% ADIV/01 17% 7000 100.C ADIV/01 10% % 8000 -100.0% ADIV/0! 11% 5000 -100.0% ADIV/01 7% SODO 100.C ADIV/0! 744 14000 -100.0% ADIV/0! % 20 7000 -100.0% ADIV/01 10% 71000 200.0% 77.8% % Average Race TY LY % Che 280 -100.00 220 -100.0% 175 100.0% 210 -100.0N 181 -100.0% 175 100.0% 215 -100.0% 159 -100.0% ADIV/0! 213.0704 IV/0! | / TY so $0 $0 $0 $0 $0 $0 $0 $o Revente LY * Che $3,640,000.00 www $2,640,000.00 $1,225,000.00 $1,680,003.00 W $945.000 on $575,000.00 $3,010,003.00 W $1,113,000.00 $15,128,000.00 D 0.0% wailablerooms 91250 9 10 11 12 13 14 15 16 17 12 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 SPREADSHEET PART 1 SPREADSHEET PART 2 + Select destination and press ENTER or choose Paste # E2 - + 100%