Please help me to solve the statistics problem below. Detailed step by step instructions are needed for doing this problem in Microsoft Excel using Binomial and Poisson Distributions as appropriate. Methods should be easy to follow and document.

Thanks

While vacationing in England, you decide to take an impromptu trip outside of London to see some of the country. You phone several hotels in the area but are told that all rooms are reserved. You and your friends decide to go anyway, vowing that if worse comes to worse you will all sleep in the car. Having driven the 100 miles from London, you stop at the first hotel you see, Ye Olde Ox and Bow, and are surprised to find that rooms are available. Having settled in, you go down to the Hotel Pub for a drink and strike up a conversation with the Hotel Manager. You inform him of your experience and he points out that it is not uncommon. The problem is that about 20% of the people with reservations either call back and cancel or just don't show up. He tells you that there is a great deal of uncertainty in how long guests will stay. From experience he has found that about half of the guests who stayed the night will stay for another night irrespective of their original plans. He therefore adds half the number of rooms rented to the number of vacant rooms to come up with his estimate of the number of rooms that will be available (if there are 92 rooms rented, one half is 46 to which he adds the 8 vacant rooms to come up with an estimated 54 vacant rooms the next night). He then will take a maximum of 54 reservations. Of course, many of the reservations become no-shows so that he usually has rooms available.

You point out that airlines regularly overbook flights since they have a similar "no-show" problem. He has never tried this approach and is worried since if a person with a reservation showed up, he would feel compelled to pay for a similar room at a competitor and provide the inconvenienced party with a free dinner and breakfast.

You wake up the next morning to a terribly rainy English day, which ruins all of your plans. Your friends decide to see the sites of the town, which is a Museum dedicated to Quilting. You beg off and find yourself with most of the morning and afternoon free. Your thoughts return to the previous evening's conversation and you think that you might be able to help the owner make a bit more profit by changing his reservation procedure.

Assignment:

By using simulation techniques compare the current reservation system with an open reservation system where anyone who calls for a reservation is given one. Specifically determine if the open reservation system is more or less profitable than the current system. Your simulation should be no smaller than one year in length.

Facts and Assumptions:

1) The hotel has 100 rentable rooms;

2) Assume that of the n rooms that are occupied on a given night, the number that will vacate the next day follows the Binomial Distribution with parameters n and p = ;

3) Assume that the number of new reservations follows the Poisson Distribution with mean equal to 60;

4) If m is the number of reservations made, assume that the number of "no-shows" will follow the Binomial Distribution with parameters m and p=.2;

5) The profit break even point is 50 rented rooms;

6) The net profit is $90 per room for each rented room over 50;

7) If a person with a reservation cannot be accommodated, the cost to put the person up at another hotel plus dinner and breakfast is $270.

8) In your simulation you may assume that holidays and weekend lodging and reservations are the same as on any other day.

Hints:

a) Assume that the hotel starts off with 92 rented rooms.