Question

16-16 A hospital is planning an $8 million addition to its existing facility. The architect has been asked to con- sider the following design parameters:(1) There should be at least 10 and no more than 20 intensive care unit (ICU) rooms; (2) there should be at least 10 and no more than 20 cardiac care unit (CCU) rooms; (3) there should be no more than 50 double rooms; (4) there should be at least 35 single rooms; and (5) all patient rooms should fit inside the allotted 40,000-square-foot space (not including hallways). The following table summarizes the relevant room data: use per ther rew of a per fice our. per SINGLE DOUBLE ICU CCU Cost per room to build and furnish $45 $54$110 $104 ing and em-Minimum square (Sthousands) 300 360 320 340 feet required ith Profit per room per$21 month (Sthousands) $28 $48 $41 ew ion nce ing: How many rooms of each type should the architect include in the new hospital design? Then answer the following questions based on your Excel solution Optmal soution How many rooms of each type should the architect include in the new hospital design? If you are lucky enough not to have been to any hospital room, remember all four types of rooms are mutually exclusive, ie a single room is not an ICU room and so on Single Double □ Icu CCU Optimal Profit (000) Hospital saved's (thousands) by using this optimal solution Hospital used up□ sqft space out of total□avaible sq ft

Answer 1

A hospital is planning to extend its facilities by building more rooms with the given design parameters. Its is necessary to find the optimal number of each types of room.

First define the decision variables to solve the problem .

Let S be the decision variable for the number of Single rooms.

D be the decision variable for the number of double rooms.

I be the decision variable for the number of ICUs.

C be the decision variable for the number of CCUs.

The aim is to maximize the profit by the optimal number of different types of rooms to be built in the hospital, hence the objective function can be now defined as,

Maximize

  

Z = ($21,000) S + ($28,000) D + ($48,000) 1-($41,000) C

The constraints are

Constraint 1: The total space used for all the types of rooms should be used within 40,000 square -feet. The minimum square-feet required for each of the type is 300, 360 , 320 and 340 for single,double,ICU and CCu respectively. Hence the constraints is

300S +360D +3201 +340C 40,000

Constraint 2: The total cost of constructing all the types of rooms should be used within $8,000,000. The minimum cost required for each of the type is $45,000 ,$54,000 , $110,000 and $104,000 for single ,double,ICU and CCU respectively .Hence the constriants is

($45,000) S + ($54,000) D + ($ 1 1 0,000) 1-($ l 04.000) C $8,000,000

Constraint 3: The numbe rof each types of rooms are restricted to a certain minimum and maximum limit, which are mentioned as follows

S235 Ds 50 I 210 Is20 C2 10 С 20 S,D,I,C20

Hence IP formulated that maximizes the profit from the rooms is as shown below.

Maximize

Z = ($21,000) S + ($28,000) D + ($48,000) 1-($41,000) C

subject to the constraints

300S360D +3201+340C s 40,000 ($45,000) S + ($54,000) D + ($ 1 1 0,000) 1 + ($ 104,000) с S235 Ds 50 $8,000,000

12 10 I s 20 C210 Cs 20

S,D,I,C20

Now, start using excel.input the decision vairables  S,D,I,C values in EXcel worksheet as shown below. Calculate the total number of rooms using the SUM( ) function.

The screen shot is given below.

Single Double ICU CCU Total Decision VariablesS No of Rooms 1 4

Next,enter the details of minimum square feet required for constructing each of the different types of rooms , and also calculate the total square feet used on the number of rooms using the SUMPRODUCT( ) function as shown below: Also,specify the constraint value of total square feet that can be used in the cell I6.

The screen shot is given below.

SUMPRODUCT(C6:F6,C5:$F$5) G6 Single Double ICUCCUTotal Decision Variables No of Rooms Min Sq. Feet S 1 300 1 1 4. 300 360 20 40320 AC),KK

Next,enter the details of cost for constructing each of the different types of rooms, and also calculate the total cost based on the number of rooms, using the SUMPRODUCT( ) function as shown below: Also,specify the constraint value of total cost that can be use din the cell I7

The screen shot is given below.

SUMPRODUCT(C7:F7,SC$5:$F$5) G7 Single Double ICU Total Reuirement CCU Decision VariablesS No of Rooms Min Sq. Feet Cost per room $45,000 $54,000 $110,000 S104,000 $313,000 1 1 4. 1320 300 360 320 40,000 $8,000,000

Similarly,enter the details of profit that can be earned by constructing each of the different types of rooms, ans also calculate the total profit based on the number of rooms, using the SUMPRODUCT( ) function as shown below.

The screen shot is given below.

SUMPRODUCT(C8:F8,SC$5:$F$5) G8 Single DoubleICU CCU Total Reuirement Decision VariablesS No of Rooms Min Sq. Feet Cost per room$45,000 $54,000 $110,000 $104,000 $313,000 Profit per room$21,000 $28,000 $48,000 $41,000 $138,000 1 1 1320 300 360 320 40,000 $8,000,000

Finally,mention the details of minimum and maximum requirement constraints of the different types of room as mentioned in the problem.

The screen shot is given below

Total Single DoubleICU CCU Reuirement Decision VariablesS No of Rooms Min Sq. Feet Cost per room $45,000 $54,000 $110,000 $104,000 $313,000 Profit per room $21,000 $28,000 $48,000 $41,000 $138,000 4. 360 320 1320 40,000 $8,000,000 10 10 20 Min Requirement Max Requirement 35 50 20

All the inputs have been made hence click on the Solve icon. A dialog box will appear. In the Target cell box, input G8, Where the final result of total profit based on the number of rooms will appear. Also,select the MAx option box. In the text box"By changing cells",update the cell names where the actual numbers of rooms are to be updated.

The screen shot is given below.

Solver Parameters Set Target Cell: $G$8 Solve Equal To: MaxO Min yalue of 0 Close $C$5:$F$5 Guess Subject to the Constraints: Options Add Change Delete Reset Al Help

Now,define the constraints in the text field named"subject to the constraints". These constraints can be input by clicking on the add button, and then a dialog box will appear. Define the constraint which indicates that all the decision variables should be in integer value. Click add button

THe screen shot is given below

Add Constraint Cell Reference: Constraint: $C$5:$F$5 vinteger Cancel Add Help OK

Next,define the constraint that the total feet used should be within 40,000 square feet. Click Add button.

The screen shot is given below.

Add Constraint Cell Reference: Constraint: $G$6 Cancel Add Help OK

Next,define the constraint that the total cost that will be incurred for constructing the rooms should be used within $8,000,000. Click Add button.

The screen shot is given below.

Add Constraint Cell Reference: Constraint: $G$7 Cancel Add Help OK

Next,define the multiple constraints related to the minimum and maximum numbers of rooms required. Click Add button.

The screen shot is given below.

Change Constraint Cell Reference: Constraint: $C$5 Cancel Add Help OK

Change Constraint Cell Reference: Constraint: SD$5 Cancel Add Help OK

Add Constraint Cell Reference: Constraint: 10 $E$5:$F$5 Cancel Add Help OK

Click OK and then the solver dialog box will return. After clicking on Solve button, a dialog box will appear indicating that the solution has been found.

The screen shot is given below.

Solver Results Solver found a solution. All constraints and optimality conditions are satisfied. Reports Answer Sensitivity Limits O Keep Solver Solution Restore Original Values Cancel Help OK Save Scenario...

If the"Keep solver solution" option button is clicked,it is observed that the values of the number of rooms that are needed to be constructed wil be updated.

The screen shot is given below.

Single Double ICU CCU Total Reuirement Decision Variables S No of Rooms Min Sq.Feet Cost per room $45,000 $54,000 $110,000 $104,000 $7,976,000 Profit per room $21,000 $28,000 $48,000 $41,000 $3,690,000 120 39960 36 300 50 360 20 320 14 340 40,000 Min Requirement Max Requirement 35 10 20 10 50

Based on the solver results,the optimal solution is obtained. This it can be concluded that in the new hospital design,the rooms of each type that the architect should include are

Single rooms

36

double rooms

50

ICUs

20

CCUs

and the total profit that will be acquired will be

$3,690,000