A popular resort hotel has 300 rooms and is usually fully booked. All reservations are completed online
Question:
A popular resort hotel has 300 rooms and is usually fully booked. All reservations are completed online and paid in full at the time of reservation. Even though the resort sells out every room, the past data shows that even though they paid in full not all the guests show up. For this reason the hotel is considering an overbooking strategy to further increase the profits. Initially the management is considering accepting 320 reservations per day. The hotel charges $299 per room per day at the time of reservation. The resort will incur a cost for any guest denied a room. This cost, which covers the added expenses of accommodation at a nearby hotel with a room upgrade as well as loss of goodwill, is estimated to be $750 per guest. The probability distribution for the number of rooms occupied ranges from 260 to 320 for any given day. The daily spending of the guests staying at the resort can be represented by a normal distribution with a mean of $100 and a standard deviation of $15 per occupied room. Also, operating expenses of the resort can be represented by a uniform distribution with values ranging from $80,000 to $100,000 per day.
Develop a spreadsheet simulation model (using 1,000 trials) for this overbooking strategy that calculates the daily profit and answer the following questions:
a. What is the expected average profit per day with the overbooking strategy?
b. What is the probability that the net profit with the overbooking strategy will be less than the net profit without overbooking strategy?
c. Create a frequency distribution (using the FREQUENCY() function) and a histogram (using a column chart) for the profit with overbooking strategy. For your calculations use the minimum and the maximum values to determine a reasonable range and use increments of $4,000.
d. The resort is also considering accepting 310 and 330 reservations per day as an alternative overbooking strategy. Compute and compare the average expected profit values for each strategy. What is your conclusion?
Business Analytics Methods Models and Decisions
ISBN: 978-0321997821
2nd edition
Authors: James R. Evans