Question

Problem 2
The Walk-In Clinic at the Broken Leg Creek Regional Health Care Center in southern Alabama requires different numbers of nurses on different days of the week. The number of full-time nurses required each day is given in the table below.
Sun 18
Mon 25
Tue 12
Wed 13
Thu 20
Fri 28
Sat 32
Clinic rules require that full-time nurses work three consecutive days, are on call for one day, and then receive three days off. The clinic wants to meet its daily requirements, while minimizing the number of nurses that must be on staff.
a. (20 points) Assume that the schedule must be made without planning for any nurse to help meet the daily requirement for their on call day. Use Excel Solver to formulate and solve the clinic's problem. Obtain an Answer Report and a Sensitivity Report. How many full-time nurses does the Walk-In Clinic need? How will they be scheduled?
b. (10 points) Suppose the Broken Leg Creek Regional Health Care Center is experiencing a budget crunch and needs to use the nurses' on call day to temporarily staff the Walk-In Clinic. Use Excel Solver to formulate and solve the clinic's problem. Obtain an Answer Report. How many full-time nurses does the clinic need in this case? How will they be scheduled?
c. (10 points) Suppose the Walk-In Clinic has 56 nurses and is not allowed to hire or fire any employees, and cannot temporarily schedule nurses on their on call days. Use Excel Solver to determine a schedule that maximizes the number of weekend days off received by the nurses. Obtain an Answer Report. How will the Broken Leg Creek Regional Health Care Center schedule its 56 Walk-In Clinic nurses?

Answer 1

1. When there are no on call nurses scheduled.

There are 7 types of nurse(based on their shift start day).

Here the variables are how many nurses are assigned to each shift.

Let x1 = number of nurses who start on monday, Let x2 = number of nurses who start on tuesday and so on.

No. of nurses required: 53

How they are scheduled:

type of nurse	number of nurse
starts on monday	8
starts on tuesday	4
starts on wednesday	0
starts on friday	9
starts on saturday	19
starts on sunday	0
starts on monday	13

The excel solution, answer report and se for the problem is shown below:

Minimize Total nurses 53 X4 No. of eac 19 13 work week type 0 0 1 12 13 1 1 32

ANSWER REPORT

A B 7 Solution Time: 0.016 Seconds. 8 Iterations: 9 Subproblems: 0 9 Solver Options 10 Max Time Unlimited, Iterations Unlimited, Precision 0.000001 11 Max subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative 12 13 14 Objective Cell (Min) 15 16 $V$13 Total nurses Minimize 17 18 19 Variable Cells 20 21 $T$18 No. of each work week based full time worker x1 22 ŞUŞ$18 No. of each work week based full time worker x2 23 $VŞ$18 No. of each work week based full time worker x3 24 $W$18 No. of each work week based full time worker x4 25 $X$18 No. of each work week based full time worker x5 26 $Y$18 No. of each work week based full time worker x6 27 $Z$18 No. of each work week based full time worker x7 28 Cell Name Original Value Final Value 53 Cell Name Original Value Final Value Integer 8 Contin 4 Contin 0 Contin 9 Contin 19 Contin 0 Contin 13 Contin 0 30 Constraints 31 32 SAB$21 M 33 ŞAB$22 T 34 ŞAB$23 W 35 ŞAB$24 T 36 ŞAB$25 F 37 ŞAB$26 S 38 ŞAB$27 S 39 Cell Name Cell Value Formula Status Slack 21 SAB$21>-SAD$21 Not Bindin 25 $AB$22-$AD$22 Bindin 12 SAB$23>-SAD$23 Binding 13 SAB$24>-SAD$24 Binding 28 $AB$25>-SAD$25 Not Bindin 28 $AB$26>-$AD$26 Bindin 32 SAB$27>-SAD$27 Binding 0 0 0 0 0 42 43

SENSITIVITY REPORT

2.) When nurses are scheduled on call duty:

Number of nurses scheduled: 39

They are scheduled as give in below table:

type of nurse	number of nurse
starts on monday	0
starts on tuesday	6
starts on wednesday	1
starts on friday	13
starts on saturday	0
starts on sunday	14
starts on monday	5

The excel Solution and Answer report appears as below:

Minimize Total nurses 39 X4 X6 X7 No. of eac 6 13 14 work week type 0 19 25 12 1 32

ANSWER REPORT

Vicrosoft Excel 14.0 Answer Report Worksheet: [1.xlsx]Sheet3 eport Created: 2/27/2018 6:58:07 PM esul: Solver found a solution. All Constraints and optimality conditions are satisfied olver Engine Engine: Simplex LFP Solution Time: 0.015 Seconds Iterations: 9 Subproblems : 0 olver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 Max subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative Objective Cell (Min) Cell $V$13 Name Final Value Original Value 0 Total nurses Minimize 39 ariable Cells Cell T$18 No. of each work week based full time worker x1 SU$18 No. of each work week based full time worker x2 $V$18 No. of each work week based full time worker x3 $W$18 No. of each work week based full time worker x4 $X$18 No. of each work week based full time worker x5 SY$18 No. of each work week based full time worker x6 $Z$18 No. of each work week based full time worker x7 Name Final Value Original Value 0 0 0 0 0 Integer 0 Contin 6 Contin 1 Contirn 13 Contin 0 Contin 14 Contin 5 Contin 0 onstraints Cell ŞAB$21 M ŞAB$22 T ŞAB$23 VW ŞAB$24 T ŞAB$25 F ŞAB$26 S SABS27 S Name Cell Value Formula Status Slack 19 ŞAB$21>-$AD$21 Not Bindin 25 $AB$22-SAD$22 Bindin 12 $AB$23> SAD$23 Binding 20 $AB$24>-SAD$24 Not Bindin 20 SAB$25>-SAD$25 Binding 28 SAB$26>=$AD$26 Bindin 32 SAB$27-SAD$27 Binding 0 0 0

3.) When there are 56 nurses to be scheduled and weekend day off is to be maximized:

The following is the optimal schedule:

type of nurse	number of nurse
starts on monday	11
starts on tuesday	0
starts on wednesday	3
starts on friday	10
starts on saturday	18
starts on sunday	0
starts on monday	14

The Excel solution and Answer report is attached below

Minimize Total nurses56 X3 X4 x6 18 No. of each type of nurse x7 10 14 work week type 25 25 13 31 28 32 number of weekend off for each nurse type 2 2 total weekend off 52 Maximize

1 Microsoft Excel 14.0 Answer Report 2 Worksheet: [1.xlsx]Sheet3 3 Report Created: 2/27/2018 7:15:34 PM 4 Result: Solver found a solution. All Constraints and optimality conditions are satisfied 5 Solver Engine 6 Engine: Simplex LP 7 Solution Time: 0 Seconds 8 Iterations: 9 Subproblems: 9 Solver Options 10 Max Time Unlimited, Iterations Unlimited, Precision 0.000001 11 Max subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative 12 13 14 Objective Cell (Max) 15 16 $G$58 total weekend off x3 0 Cell Name Original Value Final Value 52 18 19 Variable Cells Original Value 0 Cell Name Final Value Integer 21 ŞEŞ43 No. of each work week based full time worker x1 22 ŞF$43 No. of each work week based full time worker x2 23 ŞG$43 No. of each work week based full time worker x3 24 ŞHS43 No. of each work week based full time worker x4 25 ŞIS43 No. of each work week based full time worker x5 26 ŞJŞ43 No. of each work week based full time worker x6 27 ŞK$43 No. of each work week based full time worker x7 28 29 30 Constraints 31 32 L$46 M Friday 33 ŞL$47 T Frida 34 ŞL$48 W Frida 35 ŞL$49 T Frida 36 ŞL$50 F Frida 37 $し$51 S Frida 38 ŞL$52 S Frida 39 N$43 No. of each work week based full time worker Sunday 11 Contin 0 Contin 3 Contin 10 Contin 18 Contin 0 Contin 14 Contin 0 0 Cell Name Cell Value Formula Status Slack 25$L$46>-$M$46 Not Bindin 25 ŞLS47-ŞAD$22 Bindin 14 $L$48>=$AD$23 Not Bindin 13 ŞL$49>-SAD$24 Bindin 31$L$50>=$AD$25 Not Binding 28 ŞL$51>-ŞAD$26 Bindin 32 SL$52> SAD$27 Binding 56 SN$A3<-SG$37 Binding 0 11 0 0