Question

The director of the Computer Center needs to schedule the staffing of the center. It is open from 8am until midnight. The director has monitored the usage of the center at various times of the day and determined that the following number of computer consultants are required:

Time of Day	Minimum number of consultants required to be on duty
8am-noon	12
Noon-4pm	18
4pm-8pm	24
8pm-midnight	12

The two types of computer consultants can be hired: full-time and part-time.

• The full-time consultants work for 8 consecutive hours in any of the following shifts: morning (8am-4pm), afternoon (noon-8pm), and evening (4pm-midnight). Full-time consultants are paid $60 per hour.
• Part-time consultants can be hired to work any of the four shifts listed in the table. Part-time consultants are paid $45 per hour.

An additional requirement is that during every time period, there needs to be at least two full-time consultants on duty for every part-time consultant on duty. The director wants to determine the number of full-time and part-time staff for each period so as to minimize the cost.

(Hints: the ratio of FT:PT ≥ 2:1 à FTPT≥21 à FT ≥ 2PT)

1. Formulate the linear programming model for this problem.
2. Solve the model using Excel Solver.

Answer 1

Ans: Instead of FT we can use X and Pt we can use Y for solving the linear Programming Model and also the Excel Solver.

a) Formula for Linear Progrmming Model

X1 = No of the full-time consultants work for 8 consecutive hours in Morning shift (8am-4pm)

X2 = No of the full-time consultants work for 8 consecutive hours in Afternoon shift (noon-8pm)

X3 = No of the full-time consultants work for 8 consecutive hours in Evening Shift (4pm-midnight)

Y1 = No of Part-time consultants work in the Morning shifts (8am-12pm)

Y2 = No of Part-time consultants work in the Afternoon shifts (12pm-4pm)

Y3 = No of Part-time consultants work in the Evening shifts (4pm-8pm)

Y4 = No of Part-time consultants work in the Night shifts (8pm-12am)

Minimize = (Shift per hour) [(X1 + X2 + X3) + (Y1 + Y2 + Y3 +Y4)]

Subject to

X1 + Y1 >= 4

X1 + X2 + Y2 >= 8

X2 + X3 + Y3 >= 10

X3 + Y4 >= 6

X1 >= 2Y1

X2 + X3 >= 2Y2

X2 + X3 + Y3 >= 2Y3

X3 + Y4 >= 2Y4

X1 >= 0, X2 >= 0, X3 >= 0, Y1 >= 0, Y2 >= 0, Y3 >= 0, Y4 >= 0

b) Model using Excel Solver

Time	X1	X2	X3	Y1	Y2	Y3	Y4	Tactics(>=)	Minimum Required
8am-12pm	1	0	0	-1	0	0	0	4	4
	1	0	0	-2	0	0	0	4	0
12pm-4am	1	1	0	0	1	0	0	8	8
	1	1	0	0	-2	0	0	8	0
4pm-8pm	0	1	1	0	0	1	0	10	10
	0	1	1	0	0	-2	0	10	0
8pm-12am	0	0	1	0	0	0	1	6	6
	0	0	1	0	0	0	-2	6	0
Unit Cost Solution	14	14	14	12	12	12	12	$196.00
	4	4	6	0	0	0	0