In order to reduce the number of vacant rooms the hotel overbooks three rooms, i.e. accepts three more reservations than the number of rooms available. The hotel’s policy is to send any guests who miss out on a room to a competing hotel down the street at Heartbreak’s expense of $125 for each such guest. If the number of no-shows is more than three the hotel has vacant rooms resulting in an opportunity cost of $50 per room.
(a) Using Excel set up a model to simulate 1 month (30 days) of operation to calculate the hotel’s monthly cost due to overbooking and opportunity loss.
You can use this template to guide you:
You need to complete the Cu.ulative probabilities.
All data are shown in rows 3:9 except for cell J9 which will contain a formula.
There should be no numbers in your model which should consist of all formulas from row 13 onwards except for column A.
Column A shows the day number (1 to 30).
In column B random numbers are generated.
In column C there is a LOOKUP function to simulate the number of no-shows to be entered in column C.
In column D compute the number of short rooms (unavailable for guests) by comparing the number of no-shows with the number of rooms decided to be overbooked: e.g. =Max($J$4-C13,0). If the number of rooms overbooked exceeds the number of no-shows there is a shortage of available rooms, else if no-shows exceed rooms overbooked there is no shortage, but possibly vacancies (the formula would be negative but by placing a maximum of zero in the formula it comes out zero (no shortage)).
The short cost in column E is found by multiplying the cell J6 by D13 etc.
In column F (vacant rooms) the formula is the reverse of the one in column D: = max(C13-$J$4,0).
The cost of vacant rooms in column G is the product of the cost in J7 and the number of vacant rooms.
Total cost in column H sums col E and col G.
Copy formulas down to day 30, sum the total costs in col H and divide by the 30 to put the result in I9.
“Looking for a Similar Assignment? Get Expert Help at an Amazing Discount!”
What Students Are Saying About Us
.......... Customer ID: 12*** | Rating: ⭐⭐⭐⭐⭐"Honestly, I was afraid to send my paper to you, but splendidwritings.com proved they are a trustworthy service. My essay was done in less than a day, and I received a brilliant piece. I didn’t even believe it was my essay at first 🙂 Great job, thank you!"
.......... Customer ID: 14***| Rating: ⭐⭐⭐⭐⭐
"The company has some nice prices and good content. I ordered a term paper here and got a very good one. I'll keep ordering from this website."
