Case Study Group Assignments with Excel QM Scheduling # 36 Done by: Group 3 The Question: Dr. Maureen Becker, the head administrator at Jefferson County Regional Hospital, must determine a schedule for nurses to make sure there are enough nurses on duty throughout the day. During the day, the demand for nurses varies. Maureen has broken the day into 12 two-hour periods. The slowest time of the day encompasses the three periods from 12:00 A.M. to 6:00 A.M., which, beginning at midnight, require a minimum of 30, 20, and 40 nurses, respectively. The demand for nurses steadily increases during the next four daytime periods. Beginning with the 6:00 A.M. – 8:00 A.M. period, a minimum of 50, 60, 80, and 80 nurses are required for these four periods, respectively. After 2:00 P.M. the demand for nurses decreases during the afternoon and evening hours. For the five two-hour periods beginning at 2:00 P.M. and ending at midnight, 70, 70, 60, 50, and 50 nurses are required, respectively. A nurse reports for duty at the beginning of one of the two-hour periods and works eight consecutive hours (which is required in the nurses' contract). Dr. Becker wants to determine a nursing schedule that will meet the hospital's minimum requirements throughout the day while using the minimum number of nurses. Problem Description The problem is summarized by a need from an administrator of a hospital to prepare a nursing schedule in order to make sure that there are enough nurses available during the day. Dr. Maureen Becker analysis showed that the day can be divided into a 12 (2) hours periods to determine the demand for nurses. The analysis showed the following findings:  From 12:00 A.M. to 6:00 A.M. required a minimum of 30, 20, and 40 nurses, respectively.  From 6:00 A.M. – 8:00 A.M. required a minimum of 50, 60, 80, and 80 nurses.  From 2:00 P.M. and ending at midnight required 70, 70, 60, 50, and 50 nurses. Taking into consideration that the each nurse work for an eight consecutive hours. Mathematical Solution In order to find the optimum solution to minimize the overspending on labor cost, Dr. Maureen Becker draw a linear solution by the following stpes: Define the decision variables by breaking the day time into 12 periods and set the minimum nurses required. X1 = numbers of nurses starting at Midnight and working 8 hours X2 = numbers of nurses starting at 2am and working 8 hours X3 = numbers of nurses starting at 4am and working 8 hours X4 = numbers of nurses starting at 6am and working 8 hours X5 = numbers of nurses starting at 8am and working 8 hours X6 = numbers of nurses starting at 10am and working 8 hours X7 = numbers of nurses starting at noon and working 8 hours X8 = numbers of nurses starting at 2pm and working 8 hours X9 = numbers of nurses starting at 4pm and working 8 hours X10 = numbers of nurses starting at 6pm and working 8 hours X11 = numbers of nurses starting at 8pm and working 8 hours X12 = numbers of nurses starting at 10pm and working 8 hours Set the Objective: Minimize Z = X1 + X2 + X3 + X4 + X5 + X6 + X7 + X8 + X9 + X10 + X11 + X12 Subject to the following Constraints: The slowest time of the day encompasses the three periods from 12:00 A.M. to 6:00 A.M., which beginning at midnight; require a minimum of 30, 20, and 40 nurses, respectively X1 + X10 + X11 + X12 ≥ 30 X1 + X2 + X11 + X12 ≥ 20 X1 + X2 + X3 + X12 ≥ 40 Beginning with 6:00 A.M.- 8:00 A.M. period, a minimum of 50. 60, 80, and 80 nurses are required for these four periods, respectively. X1 + X2 + X3 + X4 ≥ 50 X2 + X3 + X4 + X5 ≥ 60 X3 + X4 + X5 + X6 ≥ 80 X4 + X5 + X6 + X7 ≥ 80 For the five 2-hour periods beginning at 2:00 P.M. and ending midnight, 70, 70, 60, 50, and 50 nurses are required, respectively X5 + X6 + X7 + X8 ≥ 70 X6 + X7 + X8 + X9 ≥ 70 X7 + X8 + X9 + X10 ≥ 60 X8 + X9 + X10 + X11 ≥ 50 Subject to X1 X2 X3 X4 X5 X6 X7 X8 X9 X10 X11 X12 Sign min 1 1 1 1 1 1 1 1 1 1 1 1 1 1 0 0 0 0 0 0 0 0 0 0 0 > 30 2 1 1 0 0 0 0 0 0 0 0 0 0 > 20 3 1 1 1 0 0 0 0 0 0 0 0 0 > 40 4 1 1 1 1 0 0 0 0 0 0 0 0 > 50 5 0 1 1 1 1 0 0 0 0 0 0 0 > 60 6 0 0 1 1 1 1 0 0 0 0 0 0 > 80 7 0 0 0 1 1 1 1 0 0 0 0 0 > 80 8 0 0 0 0 1 1 1 1 0 0 0 0 > 70 9 0 0 0 0 0 1 1 1 1 0 0 0 > 70 10 0 0 0 0 0 0 1 1 1 1 0 0 > 60 11 0 0 0 0 0 0 0 1 1 1 1 0 > 50 12 0 0 0 0 0 0 0 0 1 1 1 1 > 50 Program screenshots: Getting started It's required to solve the mathematical equations by using either QM for windows 3. In this case study we used QM for windows 3 to solve and answer the question and it was easier because it had about 12 variables and 12 constraints, beside, it's much easier to be used than the Excel QM. By opening the QM for windows 3, we have required to input the module which is in this case will be Linear programming, as shown below: Then to proceed in the case study, a new file should be generated in the program to define the variable and constrains. Inputting data After generating a new file, the QM for windows 3 will ask us to input number of variables, constrains, maximizing or minimizing the profit. In this case study, the variables are 12 and constrains are 12, with a minimizing profit, as shown below: Then the program will generate a spread sheet where the constrains and the minimizing equation can be input as a matrices. Then input data for the program will be: Then we click solve to solve the mathematical equations. Linear Programming Results: The results that it's generated after running the program is shown below with description of variables, slack and optimize value(Z): As shown, the optimal Value (Z) =170$ Ranging: Discussion & Conclusion The minimum number of nurses required is 170 that will meet all requirements. Results showed that shifts started at 2 a.m, 2 p.m, 8 p.m and 10 p.m do not required to add additional nurses as the accumulated nurses from previous shifts satisfy the minimum requirements. In conclusion, The case study was a good example for simulating the objective and constrains equations, also it's got a lot of hard effort in inputting the data in the QM for windows 3, because of the lengthy constrains (12 constrains).