Question

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?

Answer 1

Let no. of smoke suites be Xs, non-smoking suites be Xn, Smoking budget rooms be Ys, Non-smoking budget rooms be Yn

Cost for smoking suite = 190 + 20 = $210

Cost for smoking budget room = 140 + 20 = $160

Total Revenue = 210*Xs + 190*Xn + 160*Ys + 140*Yn

We have to maximize this revenue

Hence, we get the objective function as:

Maximize Total Revenue R = 210*Xs + 190*Xn + 160*Ys + 140*Yn

Subject to Constraints

18000*Xs + 15000*Xn + 15000*Ys + 12000*Yn <= 7,500,000........Constraint for Total Budget for construction

(Ys + Yn) >= 1.5*(Xs + Xn)...............................................................Constraint for minimum no. of budget rooms

(Ys + Yn) <= 3*(Xs + Xn)..................................................................Constraint for maximum no. of budget rooms

(Xs + Xn) >= 80................................................................................Constraint for minimum no. of suites

(Xs + Xn) <= 200..............................................................................Constraint for maximum no. of suites

Ys <= 50%*Yn..................................................................................Constraint for no. of smoking budget rooms

Xs <= 50%*Xn..................................................................................Constraint for no. of smoking suites

Ys >= 4............................................................................................Constraint for minimum no. of smoking budget rooms

Xs >= 4............................................................................................Constraint for minimum no. of smoking suites

Xs, Xn, Ys, Yn >= 0..........................................................................Non-negativity constraint

We solve above LPP in Excel using excel solver as shown below:

The above solution in the form of formulas along with Excel Solver extract is shown below for better understanding and reference:

As shown above,

No. of smoking suites = 4 nos.

No. of non-smoking suites = 196 nos.

No. of smoking budget rooms = 4

No. of non-smoking budget tooms = 369

We generate a sensitivity report as shown below:

The constraint for the budget is in Cell B11. Allowable increase against cell B11 in the above sensitivity report = 2,724,000. Hence, for an increase of each $ up to $2,724,000, the objective function will increase by shadow price of $0.01167

Hence, if we get $1,500,000 additional, the revenue will increase by 1,500,000 * 0.01167 = $17,500. As stated above, this procedure can be used between range 7,500,000 + 2,724,000 = $10,224,000 and 7,500,000 - 876000 = $6,624,000

C. The optimum value for budget non-smoking rooms is given in Cell B5. Here the allowable increase = allowable decrease = 12, hence, the range will be 369 + 12 = 381 and 369 - 12 = 357 rooms

_______________________________________________________________________________________

In case of any doubt, please ask through the comment section before Upvote/downvote.