Question

Create an excel workbook for the following questions. Answer these questions under your Solver work for each respective problem.

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?

Answer 1

Decision variables

Let the decision variables are,

x1 = number of suits: smoking

x2 = number of suits: non-smoking

x3 = number of budget rooms: smoking

x4 = number of budget rooms: non-smoking

Objective function

The objective is to maximize the total profit,

Constrains

1. Total building cost

2. number of budget rooms be at least 1.5 times the number of suites

3.number of budget rooms be no more than 3 the number of suites

4. at least 80 suites

5. no more than 200 suites

6. smoking rooms should be less than 50% of the non-smoking room

7. at least 4 smoking rooms.

8. Non-negativity constraints

The LP is formulated as,

Subject to

Now, the LP is solved using the excel solver by following these steps,

Step 1: Write the decision variable with value zero. The screenshot is shown below

Step 2: Write the objective function equation while taking the decision variable value. The screenshot is shown below,

Step 3: Write the constraints equation while taking the decision variable value and write the right-side value of the constraint

The screenshot is shown below,

Step 4: DATA > Solver > OK. The screenshot is shown below,

Step 5:

Set Objective: Select objective value,

To: Select Max

Subject to the Constraints > Add > in Cell Reference select constraint value and in Constraint: select right-hand side value of constraint and select the >= inequality.

Tick Make Unconstrained Variables Non-Negative

Select a Solving Method: Simplex LP

then clicks Solve. The screenshot is shown below,

Step 6: Select Reports > Answer, Sensitivity then Ok

The result is obtained. The screenshots are shown below,

The Answer Report

The sensitivity report

1)

From the Answer Report:

Total revenue = $90440

x1 = 4

x2 = 196

x3 = 0

x4 = 374

2)

From the Sensitivity report

The shadow price for the Total building cost constraint = 0.01167

Allowable increase = 2712000

Since the increase of 1500000 is within the allowable increase we can use calculate the increase in revenue by multiplying the shadow price by the increased right-hand side value of constraint,

3)

From the Sensitivity report

The Allowable increase for the construction cost = 2712000

From the Sensitivity report

The optimal value for the budget non-smoking room = 0

Range = (Allowable decrease to Allowable Increase)

Range = 0 to 0

Hence no increase is allowed.