The police chief of Thidwick City, home of Thidwick State University, is trying
ID: 3266332 • Letter: T
Question
The police chief of Thidwick City, home of Thidwick State University, is trying to develop a schedule for patrol officers for Thursday, Friday, Saturday, and Sunday of each week. Her full-time officers work 8-hour shifts and her part-time officers only 4-hour shifts. She divides each day into six four-hour time buckets and has developed forecasts for needed officers for each time bucket. This information is given in the table below. In Excel using the Solver function, formulate a math optimization model which will:
a) Minimize the total number of officers employed in the four-day window.
b) Make certain in each time bucket there are at least twice as many full-time officers on duty as part-time officers.
c) Make certain in each time bucket that part-time officers make up no less than 20% of patrolling officers.
Officers Needed in Each Time Bucket
Thursday
Friday
Saturday
Sunday
00:00 – 04:00
35
55
65
75
04:00 – 08:00
20
25
35
45
08:00 – 12:00
50
50
20
15
12:00 – 16:00
35
35
35
25
16:00 – 20:00
55
55
20
35
20:00 – 00:00
40
50
50
35
Note: Though there can be several ways of building the formulation, when your professor formulated the model for this problem it had 48 variables and 72 constraints.
Officers Needed in Each Time Bucket
Thursday
Friday
Saturday
Sunday
00:00 – 04:00
35
55
65
75
04:00 – 08:00
20
25
35
45
08:00 – 12:00
50
50
20
15
12:00 – 16:00
35
35
35
25
16:00 – 20:00
55
55
20
35
20:00 – 00:00
40
50
50
35
Explanation / Answer
A
B
C
D
E
F
G
H
1
No. of officers
Thursday
Friday
Saturday
Sunday
2
00:00 – 04:00
35
55
65
75
3
04:00 – 08:00
20
25
35
45
4
08:00 – 12:00
50
50
20
15
5
12:00 – 16:00
35
35
35
25
6
16:00 – 20:00
55
55
20
35
7
20:00 – 00:00
40
50
50
35
8
9
Assignment
Thursday
Friday
Saturday
Sunday
Officers Assigned
Total Supply
10
00:00 – 04:00
0
0
0
0
0
11
04:00 – 08:00
1
1
0
0
2
12
08:00 – 12:00
0
0
1
1
2
13
12:00 – 16:00
0
0
0
0
0
14
16:00 – 20:00
0
0
0
0
0
15
20:00 – 00:00
0
0
0
0
0
16
Number Assigned
1
1
1
1
4
4
17
Total no. of officers
18
Demand
1
1
1
1
1
80
We have assigned the following :
Range Name
Cells
No. of officers
B2:E7
Assignment
B10:E15
Officers Assigned
F10:F15
Total Supply
4
Number Assigned
B16:E16
Demand
B18:E18
Total no. of officers
H18
Our problem is to minimize the total no. of officers given the constraints: assignment will be binary, total officers assigned will be equal to 4 and number assigned will be equal to demand and we can colve this using SOLVER by Simplex LP method.
A
B
C
D
E
F
G
H
1
No. of officers
Thursday
Friday
Saturday
Sunday
2
00:00 – 04:00
35
55
65
75
3
04:00 – 08:00
20
25
35
45
4
08:00 – 12:00
50
50
20
15
5
12:00 – 16:00
35
35
35
25
6
16:00 – 20:00
55
55
20
35
7
20:00 – 00:00
40
50
50
35
8
9
Assignment
Thursday
Friday
Saturday
Sunday
Officers Assigned
Total Supply
10
00:00 – 04:00
0
0
0
0
0
11
04:00 – 08:00
1
1
0
0
2
12
08:00 – 12:00
0
0
1
1
2
13
12:00 – 16:00
0
0
0
0
0
14
16:00 – 20:00
0
0
0
0
0
15
20:00 – 00:00
0
0
0
0
0
16
Number Assigned
1
1
1
1
4
4
17
Total no. of officers
18
Demand
1
1
1
1
1
80
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.