Read Case Study "Displacement at the Hotel Fernando". Fill in the missing data in the attached excel spreadsheet (highlighted in yellow). Then in the blue comment box indicate if they should or should not take the group and why.
Grading
30 points for entering correct data in the yellow highlighted fields
30 points for answering correctly on whether they should or should not accept the group
40 points for explaination of decision
Case Study: Displacement at the Hotel Fernando The 450-room Hotel Fernando is a four-star full-service hotel in San Petresco. It is highly regarded among both locals and tourists. Its service standards and amenities complement its exquisite rooms. In addition, the hotel boasts an impressive amount of meeting and conference function space to cater to events and groups. Due to a steady increase in group bookings, management is proposing to implement new revenue management tactics to expedite its process of deciding which groups to accept. Certain techniques, such as displacement analysis, would prove very helpful, especially during the main season when the hotel operates at high-occupancy levels. By using a displacement analysis spreadsheet, management would be able to more effectively select the booking opportunities with the highest net revenue potential. The hotel forecasts its occupancy levels for the upcoming Easter weekend, April 14–16, as follows: Apr 14Apr 15Apr 16 405420355 The average daily transient rate for those days has been posted at $175 per room. The variable cost for cleaning any occupied room, regardless of whether it has been assigned to a transient or group guest, averages $20. A franchise fee of 8 percent, which is composed of 4 percent for a marketing fees assessment and another 4 percent for royalty fees, must be allocated to the room cost; the fees are calculated based on the room rate. The following table explains these transient room costs: Total Transient Room Cost:$34.00 Variable Cost:$20.00 Franchise Fee: 8% (ADR: $175)$14.00 After all room costs have been established, the net transient room revenue may be calculated by subtracting the total room costs from the room rate. Therefore, in this scenario, the net room revenue (also called contribution margin) is $175 - $34, or $141. Hotels make a significant portion of their net revenue from rooms, but, if they have the facilities to support other revenue opportunities, such as food and beverage facilities and meeting and banquet halls, total revenue will increase. Based on historical data, the Hotel Fernando’s capture rate of transient guests purchasing a meal in its food and beverage facilities is as follows: MealAverage CheckFood Cost %Food CostNet RevenueTransient Capture % Breakfast$12.0030%$3.60$8.4070% Lunch$25.0035%$8.75$16.2525% Dinner$45.0032%$14.40$30.6040% The information in the preceding chart is based on the Hotel Fernando’s purchaseratio history. Since the hotel’s capture rate is higher during breakfast, it receives more business from transient guests during that time period. It is important to point out that the average check at breakfast is $12, which is lower than the average check at either lunch or dinner. The breakfast food cost percentage is the lowest of the three meals, but breakfast also brings in the lowest net revenue per meal. Dinner, on the other hand, has a slightly higher food cost percentage, but this meal period generates a substantially higher net revenue value due to the significantly higher average check. The Hotel Fernando’s capture rate of transient guests purchasing a beverage in its food and beverage facilities is as follows: Average Beverage Price$5.00 Average Beverage Cost20% Direct Variable Beverage Cost$1.00 Transient Capture Rate50% The 50 percent capture rate means that, on average, half of the transient guests staying in the hotel will purchase one beverage a day. The average beverage price is $5 and the direct variable cost associated with that one beverage is $1; therefore, the hotel would make $4 net revenue from each beverage sold. Furthermore, if 85 transient guests staying in the hotel have a capture rate of 50 percent, approximately 43 people (calculation rounded up) would purchase drinks. Consequently, the hotel would generate $170 net revenue from selling beverages to these transient guests. The Group Request. One of the most prestigious community groups in the state is the Rotary Club, whose members are known as Rotarians. The Rotary Club meets every month in one of the Hotel Fernando’s conference halls. Since the hotel has positioned itself as a leader in conference facilities, the Rotary Club has decided to host its annual Easter event at the Hotel Fernando. Rotarians from around the state will stay in the hotel to attend the conference that is being held on-site over the Easter weekend. The event coordinator for the Rotary Club requested a three-night stay, arrival on April 14, staying the night on Good Friday, and departure on April 17, which is Easter Monday. The coordinator also inquired about receiving special group room rates of $70, breakfast coupons for $10, lunch for $22, and dinner for $41. The group also wishes to rent a conference room and audiovisual equipment for two days. Normally, this situation would not pose a problem, but since the hotel has forecasted high-occupancy levels during that weekend, it would probably have to displace a number of its transient guests. Therefore, management has proposed that a displacement analysis spreadsheet be created so management can fully understand whether it is advantageous to accept the group booking. Occupancies. The occupancy forecasts for the transient guests and for the groupblock guests are as follows: Apr 14Apr 15Apr 16 Forecasted Occupancy405420355 Group Rooms Request758590 Other Revenue. The Hotel Fernando rents conference rooms for $250 per day and audiovisual equipment for $80 per day. Costs to the hotel for these items are $60 per day for conference room setup and $30 per day for the audiovisual equipment Assignment Perform a displacement analysis for the Hotel Fernando. Include a brief discussion explaining how the different revenue management tactics are applied in your scenario. If you do so by creating a displacement analysis spreadsheet, the spreadsheet should consist of a workbook with four worksheets: Net room revenue differential calculation Net food and beverage revenue differential calculation Other net revenue calculation Summary, where all the subtotals are linked and tallied up for decision-making Case Study: Displacement at the Hotel Fernando The 450 - room Hotel Fernando is a four - star full - service hotel in San Petresco. It is highly regarded among both locals and tourists. Its service standards and amenities complement its exquisite rooms. In addition, the hotel boasts an impressive amount of m eeting and conference function space to cater to events and groups. Due to a steady increase in group bookings, management is proposing to implement new revenue management tactics to expedite its process of deciding which groups to accept. Certain techniqu es, such as displacement analysis, would prove very helpful, especially during the main season when the hotel operates at high - occupancy levels. By using a displacement analysis spreadsheet, management would be able to more effectively select the booking o pportunities with the highest net revenue potential. The hotel forecasts its occupancy levels for the upcoming Easter weekend, April 14 – 16, as follows: Apr 14 Apr 15 Apr 16 405 420 355 The average daily transient rate for those days has been posted at $1 75 per room. The variable cost for cleaning any occupied room, regardless of whether it has been assigned to a transient or group guest, averages $20. A franchise fee of 8 percent, which is composed of 4 percent for a marketing fees assessment and another 4 percent for royalty fees, must be allocated to the room cost; the fees are calculated based on the room rate. The following table explains these transient room costs: Total Transient Room Cost: $34.00 Variable Cost: $20.00 Franchise Fee: 8% (ADR: $175) $14.00 After all room costs have been established, the net transient room revenue may be calculated by subtracting the total room costs from the room rate. Therefore, in this scenario, the net room revenue (also called contribution margin) is $175 - $34, o r $141. Hotels make a significant portion of their net revenue from rooms, but, if they have the facilities to support other revenue opportunities, such as food and beverage facilities and meeting and banquet halls, total revenue will increase. Based on h istorical data, the Hotel Fernando’s capture rate of transient guests purchasing a meal in its food and beverage facilities is as follows: Meal Average Check Food Cost % Food Cost Net Revenue Transient Capture % Breakfast $12.00 30% $3.60 $8.40 70% Case Study: Displacement at the Hotel Fernando The 450-room Hotel Fernando is a four-star full-service hotel in San Petresco. It is highly regarded among both locals and tourists. Its service standards and amenities complement its exquisite rooms. In addition, the hotel boasts an impressive amount of meeting and conference function space to cater to events and groups. Due to a steady increase in group bookings, management is proposing to implement new revenue management tactics to expedite its process of deciding which groups to accept. Certain techniques, such as displacement analysis, would prove very helpful, especially during the main season when the hotel operates at high-occupancy levels. By using a displacement analysis spreadsheet, management would be able to more effectively select the booking opportunities with the highest net revenue potential. The hotel forecasts its occupancy levels for the upcoming Easter weekend, April 14–16, as follows: Apr 14 Apr 15 Apr 16 405 420 355 The average daily transient rate for those days has been posted at $175 per room. The variable cost for cleaning any occupied room, regardless of whether it has been assigned to a transient or group guest, averages $20. A franchise fee of 8 percent, which is composed of 4 percent for a marketing fees assessment and another 4 percent for royalty fees, must be allocated to the room cost; the fees are calculated based on the room rate. The following table explains these transient room costs: Total Transient Room Cost: $34.00 Variable Cost: $20.00 Franchise Fee: 8% (ADR: $175) $14.00 After all room costs have been established, the net transient room revenue may be calculated by subtracting the total room costs from the room rate. Therefore, in this scenario, the net room revenue (also called contribution margin) is $175 - $34, or $141. Hotels make a significant portion of their net revenue from rooms, but, if they have the facilities to support other revenue opportunities, such as food and beverage facilities and meeting and banquet halls, total revenue will increase. Based on historical data, the Hotel Fernando’s capture rate of transient guests purchasing a meal in its food and beverage facilities is as follows: Meal Average Check Food Cost % Food Cost Net