Question

Based on the number of students dine in Deer Park Tavern, the manager determines that following number of waiters and waitresses are needed for each day of a week:

Mon	Tue	Wed	Thur	Fri	Sat	Sun
5	7	9	8	10	9	5

The manager hires full time workers (waiters or waitresses) who normally work consecutively for 5 days followed by 2 day off. Additional part time workers can be hired who are required to work two days in a row. Part time workers are paid 25% more daily. Assuming that all workers are equally paid (you may assume $1/day for full time workers) within each category, respectively. In addition, to maintain the quality of service, part time workers on each day should be no more than 40% of full time workers.

Set up an Excel LP model to find a shift schedule for the manager to minimize the operating cost.

Answer 1

Decision variables:

Let Fi be the number of full-time employees in ith shift, where i = 1, 2, 3, 4, 5, 6, 7 for the shifts starting from Monday…to... Sunday respectively.

Let Pi be the number of part-time employees in ith shift, where i = 1, 2, 3, 4, 5, 6, 7 for the shifts starting from Monday…to... Sunday respectively.

The seven shifts will be required to schedule full time employees for 5 consecutive days and next 2 days off. The seven shifts will be required to schedule part-time employees for 2 consecutive days. Following the schedule for each shift where 1 represent working day and 0 represent off-day:

	Full-time employee shift	Part-time employee shift
Day	F1	F2	F3	F4	F5	F6	F7	P1	P2	P3	P4	P5	P6	P7
# of Employees -Monday	1	0	0	1	1	1	1	1	0	0	0	0	1	1
# of Employees -Tuesday	1	1	0	0	1	1	1	1	1	0	0	0	0	0
# of Employees -Wednesday	1	1	1	0	0	1	1	0	1	1	0	0	0	0
# of Employees -Thursday	1	1	1	1	0	0	1	0	0	1	1	0	0	0
# of Employees -Friday	1	1	1	1	1	0	0	0	0	0	1	1	0	0
# of Employees -Saturday	0	1	1	1	1	1	0	0	0	0	0	1	1	0
# of Employees -Sunday	0	0	1	1	1	1	1	0	0	0	0	0	1	1

The objective of scheduling is minimize the requirement of employees.

The cost of each full time employee is $1 per day and that of part-time employee is $1.25 per day

Objective Function:

Min Z = $1*∑Fi + $1.25∑Pi

Subject To:

Constraint:

Employees required per day constraint:

Day	Equation
Monday	F1 + 0F2 + 0F3 + F4 + F5 + F6 + F7 + P1 + 0P2 + 0P3 + 0P4 + 0P5 + 0P6 + P7 >= 5
Tuesday	F1 + F2 + 0F3 + 0F4 + F5 + F6 + F7 + P1 + P2 + 0P3 + 0P4 + 0P5 + 0P6 + 0P7 >= 7

And so on for rest of days

Part-time employees should be not more than 40% o full-time employees on each day

Day	Equation
Monday	P1 + 0P2 + 0P3 + 0P4 + 0P5 + 0P6 + P7 <= 0.4(F1 + 0F2 + 0F3 + F4 + F5 + F6 + F7) -0.4F1 + 0F2 + 0F3 -0.4*F4 -0.4*F5 -0.4*F6 -0.4*F7 + P1 + 0P2 + 0P3 + 0P4 + 0P5 + 0P6 + P7 <= 0
Tuesday	P1 + P2 + 0P3 + 0P4 + 0P5 + 0P6 + 0P7 <= 0.4(F1 + 1F2 + 0F3 + 0F4 + F5 + F6 + F7) -0.4F1 + -0.4F2 + 0F3 + 0F4 -0.4*F5 -0.4*F6 -0.4*F7 + P1 + P2 + 0P3 + 0P4 + 0P5 + 0P6 + 0P7 <= 0

And so on for the rest of days

Excel model is as follows:

	Full-time employee shift	Part-time employee shift
Day	F1	F2	F3	F4	F5	F6	F7	P1	P2	P3	P4	P5	P6	P7
# of employees	2	4	2	2	0	1	0	0	0	0	0	0	0	0

Total cost = $11