Background: Linear Programming is used to inform business decisions. It involves constructing a Linear Programming model then solving the model to find the optimum choices for the decision variables. When making decisions informed by a LP model it is necessary to understand the assumptions in the model and how changes in those assumptions may affect the results of the model. I.e$.$ a knowledge of how sensitive the results are to the assumptions that have been made is critical when assessing how the results should inform the decision, including any contingencies that should be allowed for.

EXCEL provides a practical way of doing a sensitivity analysis as the Solver tool can generate a sensitivity refort. $($For a fuller description see Lesson $5\mathrm{.}4).$

About the lab: A hotel chain is planning a new resort hotel. There will be three types of room in the resort hotel, each with a different revenue per night when occupied, as shown in the table below.

$\backslash$table$[[$Room type,Revenue $($$$/$night$)],[$Standard$,175],[$Deluxe$,225],[$Deluxe Suite,$300]]$

The hotel chain wants to maximize the total revenue per night by choosing the appropriate mix of room types $($assuming $100\%$ occupancy$).$ The following constraints apply based on the Hotel Chain's experience with this type of resort:

The total number of rooms will be $300,$

The number of deluxe suites will not be more than a $\frac{1}{4}$ of the number of Deluxe and Standard rooms combined,

The number of Standard rooms will not be less than $40\%$ of the total number of rooms, and

The number of Deluxe rooms will not be more than $50\%$ of the number of Standard rooms.All of this information in the form of an LP model, has been placed in the template for this lab. For the purpose of setting up the worksheet the number of each type of room was set to $100.$ The purpose of the lab is to find the number of each room type in the optimal solution and the corresponding revenue, and to generate a Sensitivity Report and analyse the information in the report.

Directions

Review the lab template to make sure you understand how the situation described abovG is represented in the EXCEL version of the LP model.

Open Solver, complete the selections required in the upper part of the dialogue box, add all of the constraints, ensure any implicit constraints are dealt with, select the Simplex LP solution method, and click solve. $($It may take a few moments for Solver to find the solution$).$

Once Solver finds a solution it will display a new dialogue box. Click on the Sensitivity option in the reports box; then, accept Solver's solution.

Open the new worksheet created by Solver to see the Sensitivity report.

Using the Sensitivity Report answer the following three questions:

a$.$ If the hotel increases the revenue per night for a Standard room, what is the maximum increase before a new optimal solution must be calculated?

b$.$ If the hotel decreases the revenue per night for a Deluxe room, what is the minimum new revenue for the Deluxe room before a new optimal solution must be calculated?

c$.$ If the hotel increases the total number of rooms to $400,$ what will the change in the revenue per night be$?($Again assume $100\%$ occupancy$).$

Enter your answers to a$),$ b$)$ and c$)$ in Cells E$18,$ E$19$ and E$20$ respectively.