You have just been hired as an analyst to assist the manager of Heartbreak Hotel Your first assignment is to examine and report on the reservations policy in the hotel.

Heartbreak Hotel routinely experiences no-shows (people who make reservations for a room and don't show up) during the peak season when the hotel is always full. No-shows follow the distribution shown in the attached Excel proforma in cells A3:A9 and C3:C9.

In order to reduce the number of vacant rooms the hotel overbooks three rooms, to. 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 Cumulative probabilities.

All data are shown in rows 39 except for cell J9 which 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 19.

(b) Now print 2 copies of your model showing row and column numbers Copy 1 should show the output, and copy 2 should show the formulas.

(c) Now lest to find the number of rooms that Heartbreak Hots should overbook each day. Test for 0, 1, 2, 3, 4, 5 checking the total average daily cost each time Al you should have to do's change cell J4 and observe the change in average daily cost and tabulate them somewhere in your model State the number of overbookings which gives minimum average daily cost over the 30 days You need to take care of getting the same figure for each level of overbooking by either tabulating the results each time manually, or using an IF statement.

(d) You present your findings to the hotel manager with your recommendation as to how many rooms should be overbooked each day. The report must be dated, addressed to the Manager and signed off by You (Word limit: No more than 150 words)

B D E F G H 1 2 S 6 7 8 9 10 11 12 13 14 HEART BREAK HOTEL Prob 0.10 0.15 0.30 0.15 0.20 0.10 Day 1 2 Cum prob RN no-shows 0.10491 0.45405 No-shows 0 1 2 3 4 S No shows Short Short Vacant Vacant rooms cost rooms cost Total cost Rooms overbooked Cost per room short Cost per room vacant Average daily cost 3 $125 $50