A small hotel has 50 rooms that rent for $105 per night and cost $45 to...

A small hotel has 50 rooms that rent for $105 per night and cost $45 to clean and prepare each night they are used. All rentals are by reservation and there is a 10% chance that an individual reservation will not show up. If a customer arrives at the hotel with a reservation and no room is available due to overbooking, the hotel will refund the cost of the room and pay $150 to put the customer up at another hotel. The hotel’s current policy is to stop accepting reservations reaches the overbooking limit of 56 reservations.

a. Develop a simulation model to evaluate the hotel’s total profit and number of customers turned away when it receives the maximum number of reservations under the current policy.

b. Describe your estimate of the distribution of total profit?

c. What is your estimate of the mean total profit?

d. What is your estimate of the mean number of customers turned away?

Please Solve in Excel

Solutions

Expert Solution

Simulation model is following:

EXCEL FORMULA:

Note that rows from 16 to 60 of Excel are hidden to fit on screen

After setting up the simulation model, on the Excel toolbar ribbon , go to Add-in data tab,

Click on MCSim (MCSim is an Add-in for Monte Carlo Simulation, if it is not installed, then it needs to be installed)

Enter Simulation parameters as shown below:

Click on Proceed

------------------------------------------------------------------

Estimate of distribution of total profit :

Average	3396.075
SD	373.8752
Max	3990.000
Min	2100.000

------------------------------------------------------------------

Estimate of mean total profit = 3396.075

------------------------------------------------------------------

Estimate of mean number of customers turned away = 1.049

keosha answered 1 year ago

Next > < Previous

Related Solutions

A hotel in Saint Augustine books an average of 50 rooms per night. There is a...

A hotel in Saint Augustine books an average of 50 rooms per night. There is a standard deviation of 10 rooms per night. What is the probability that... a) more than 50 rooms are booked on a given night? b) between 50 and 60 rooms are booked on a given night? c) 60 rooms or less are booked on a given night? d) between 30 and 70 rooms are booked on a given night? e) less than 40...

The Harriet Hotel in downtown Boston has 100 rooms that rent for $150 per night. It costs the hotel $30 per room in variable costs

In Crystal BallThe Harriet Hotel in downtown Boston has 100 rooms that rent for $150 per night. It costs the hotel $30 per room in variable costs (cleaning, bathroom items, etc.) each night a room is occupied. For each reservation accepted, there is a 5% chance that the guest will not arrive. If the hotel overbooks, it costs $200 to compensate guests whose reservations cannot be honored.How many reservations should the hotel accept if it wants to maximize the average...

A hotel has 100 rooms. On any given night, it takes up to 105 reservations, because...

A hotel has 100 rooms. On any given night, it takes up to 105 reservations, because of the possibility of no-shows. Past records indicate that the number of daily reservations is uniformly distributed over the range 96-105. That is, each integer number in this range has a probability of 10%, of showing up. The no-shows are represented by the distribution in the table below. Number of No-Shows Probability 0 15% 1 20% 2 35% 3 15% 4 15% Based on...

The city of Seattle sells an average of 30,000 hotel rooms per night. Currently, those rooms...

The city of Seattle sells an average of 30,000 hotel rooms per night. Currently, those rooms have an average tax of $90. The city proposed an additional tax of $10 per room to help finance a stadium. Economists believe that without the tax, the city would average an extra 4000 rooms per night due to the new stadium. But with the tax, the number of rooms sold would fall to 27,000 even with the stadium. Based on these numbers, would...

The Tired Traveler Motel rents rooms for $50 per night. The variable cost per room rental...

The Tired Traveler Motel rents rooms for $50 per night. The variable cost per room rental is $10. The company incurs $300,000 of fixed costs per year. The company expects to rent 10,000 rooms in the coming year. a. Based on these data, prepare a static budget for the coming year. b. Holding all other factors constant, how would net income differ from the static budget if sales price was 10 percent lower than expected? c. Holding all other factors...

The Hotel El Politécnico has # 500 rooms. They usually have a cost per room of...

The Hotel El Politécnico has # 500 rooms. They usually have a cost per room of $ 70.00, plus a fixed cost of $ 5,000.00 per day. Each room is rented for $ 175.00 per day during the summer season. Answer the following questions using the above data: a) If the Hotel operates at 70% capacity for one day, what is the net profit (gain / loss)? b) What is the equilibrium point (B. E. P.)? in units, if it...

Glitz hotel, a 500-room hotel, rents hotel rooms for $120/night. At the current sales level of...

Glitz hotel, a 500-room hotel, rents hotel rooms for $120/night. At the current sales level of 400 rooms per night, Glitz currently makes a profit of $30/room: Per room Sales $120 Costs 90 Profit 30 It costs Glitz $90/night to maintain a room ($60 in depreciation, $25 in variable maintenance and $5 in fixed booking costs). Glitz has been approached by Conventions2000.com to purchase a block of 300 rooms for $80/room per night. If this special...

Sector hotel, a 500-room hotel, rents hotel rooms for $120/night. At the current sales level of...

Sector hotel, a 500-room hotel, rents hotel rooms for $120/night. At the current sales level of 400 rooms per night, Glitz currently makes a profit of $30/room: Per room Sales $120 Costs 90 Profit 30 It costs Sector $90/night to maintain a room ($60 in depreciation, $25 in variable maintenance and $5 in fixed booking costs). Sector has been approached by Conventions2000.com to purchase a block of 300 rooms for $80/room per night. If this special...

The Dow Jones Travel Index reported what business travelers pay for hotel rooms per night in...

The Dow Jones Travel Index reported what business travelers pay for hotel rooms per night in major U.S. cities. The average hotel room rates for 20 cities are recorded. 163 177 166 126 123 120 144 173 160 192 125 167 245 146 139 134 167 162 145 207 (e) Which measure of the spread (interquartile range & standard deviation) is better to use here? Briefly justify your answer with a graph generated with StatGraphics. [3] (f) Determine if there...

A 600-room hotel can rent every one of its rooms at $90 per room. For each...

A 600-room hotel can rent every one of its rooms at $90 per room. For each $1 increase in rent, 3 fewer rooms are rented. Each rented room costs the hotel $10 to service per day. How much should the hotel charge for each room to maximize its daily profit? What is the maximum daily profit?

Subjects