Quantitative Methods in BUSN

Solve this problem using Excel Solver

1. Devos Inc. is building a hotel. It will have 4 kinds of rooms: suites where customers can smoke, suites that are non-smoking, budget rooms where the customers can smoke, and budget rooms that are non-smoking. When we build the hotel, we need to plan for how many rooms of each type we should have. The following are requirements for the hotel:

1. We want to figure out how many rooms of each type to build based on maximizing revenue if we fill up the hotel. We expect to charge $190 for a suite that is non-smoking and $140 for a budget room that is non-smoking. Smoking room customers for both suites and budget rooms will have to pay an additional $20 per night.
2. We can spend up to $7,500,000 on construction of our hotel. The cost to build a non-smoking budget room is $12,000. The cost to build a non-smoking suite is $15,000. It is $3,000 additional for a smoking room of either type for smoke detectors and sprinklers.
3. We require that the number of budget rooms be at least 1.5 times the number of suites, but no more than 3 the number of suites.
4. There needs to be at least 80 suites, but no more than 200.
5. Industry trends recommend that smoking rooms should be less than 50% of the non-smoking room and in addition, we require our builder gives us at least 4 smoking rooms.

Answer the following using your Solver answers:

1. How many of each room type should be built, and what would the revenue be for a night when our hotel was fully booked?
2. Without re-running Solver, what happens to our revenue if we get an additional $1,500,000 for building? Explain in words how you got this answer without re-running solver. Over what amount of construction costs can you use this procedure?
3. Over what range of room price can our budget non-smoking rooms vary over for us to get the same answer for the quantity of each type of room?