Question

Project. Workforce Planning

Mexico City Bank of Commerce and Industry is a busy bank that needs different number of tellers depending on the time of day due to different numbers of customers needed service. The table below indicates the number of workers eeded at various hours that the bank is open.

Time Period	# of Tellers Needed	Time Period	# of Tellers Needed
9 a.m.–10 a.m.	10	1 p.m.–2 p.m.	18
10 a.m.–11 a.m.	12	2 p.m.–3 p.m.	16
11 a.m.–Noon	14	3 p.m.–4 p.m.	14
Noon–1 p.m.	16	4 p.m.–5 p.m.	10

Full-time Employees:
The bank currently has employs 14 full-time tellers right now that work from 9 a.m. to 5 p.m.

Part-time Employee:
The bank plans to hire Part-time Employees, many are available, to cover the additional tellers needed. A part-time employee will work exactly 4 hours per day, but can be scheduled to start at any time between 9am and 1pm

Hourly pay

full-timers will be paid $120 per day($15 per hour) in salary and benefits on average.

Part-timers will be paid $40 per 4-hour shift (10 per hour) on average.

Lunch time:
Full-timers are divided into 2 groups for 1 lunch time. (The 1^st group Full-timers eat between 11-noon., the rest (2^nd group) of the Full-timers eat between noon-1pm.) no lunch for part timers

Corporate policy:
The bank limits part-time hours to a maximum of 40% of the day’s total requirement.

It may release one or more of its full-time tellers to the other department if it is profitable to do so. However, each full time teller reallocation will cost the Bank $10 a day during the planning periods.

The bank would like to set a labor hiring schedule (full time and part time) that would minimize its total manpower costs. Linear Programming can be used to help the bank make the decision.

Tasks:

Part I. You need to do the followings

i)Formulate the problem into a Linear Programming model.

ii)Solve the Linear Programming using Excel solver.

a. summarize the optimal decision on the number of full time employees to use, the number of part time employees of different 4 hour ranges to hire, and the number of full timers' lunch schedule that minimize the total cost.

Part II. Use the Answer Report and Sensitivity Report of the solver outputs to answer the following questions.
(Note: It is required that we support our answer by referring your answer to the exact place in the Answer Report and Sensitivity Reports. It is not allowed to find the answers to questions by solving the LP problems by changing the parameters again and again).

i)If the per pay for the Full-time employee is Reduced by $10, will the optimal decision remain same? What will be the corresponding optimal total costs now? explain

ii)Will the optimal total costs change if the pay for the Full-time employee is Reduced by $75 a day? Explain your answer?

iii)If the required number of employees in period 10:00-11:00am is Increased to 14, what will be the optimal total costs? Do you think the optimal decision on Full-time and Part-time employees will change?

iv)If the required number of employees in period 10:00-11:00am is increased by 2, what can you say about the optimal total costs, and optimal decision on Full and Part time employees?

v) if the required number of employees in period 11:00-12:00am is changed to 11, what can you say about the optimal total cost, and optimal decision on Full and Part time employees?

*Show solution formulas as well

Please show solution using excel

Answer 1

Let,

F = number of full time (FT) employees required

Pi = number of part time (PT) employees required at the start of ith shift

i = 1, 2, 3, 4, and 5 for the shift of PT starting at 9:00 am, 10:00 am, 11:00 am, noon, and 1:00 pm.

The FT employees working for 8 hours are paid $80 per day and PT employees working for four hours is paid $25 per day. Then the objective function will be to minimize the total scheduling cost as follows:

Min Z = $80F + $25(P1 + P2 + P3 + P4 + P5)

Subject To:

Min. # of employees required between 09.00 AM - 10.00 AM	F + P1>= 8
Min. # of employees required between 10.00 AM - 11.00 AM	F + P1+ P2 >= 12
Min. # of employees required between 11.00 AM - Noon	0.5F + P1+ P2 + P3 >= 14
Min. # of employees required between Noon - 1.00 PM	0.5F + P1+ P2 + P3 + P4 >= 16
Min. # of employees required between 01.00 PM - 02.00 PM	F + P2 + P3 + P4 + P5 >= 18
Min. # of employees required between 02.00 PM - 03.00 PM	F + P3 + P4 + P5 >= 17
Min. # of employees required between 03.00 PM - 04.00 PM	F + P4 + P5 >= 15
Min. # of employees required between 04.00 PM - 05.00 PM	F + P5 >= 10
Max. # of FT available	F <= 12
Max. PT hours should be less than 40% of total day required men t	4(P1 + P2 + P3 + P4 + P5) <= 0.4(8+12+14+16+18+17+15+10) 4P1 + 4P2 + 4P3 + 4P4 + 4P5) <= 44
Non-negative constraint	All F, Pi >= 0

Excel Sheet:

Copy-paste following table in excel:

	FT Shift	PT Shifts
Shifts starting on	9:00 A.M	9:00 A.M	10:00 A.M.	11:00 A.M.	Noon	1:00 P.M.
DV	F	P1	P2	P3	P4	P5
Cost per Shift	80	25	25	25	25	25	Total Cost
Optimal number of employees	11.9999999999994	0	0	7.99999999999853	3.00000000000118	0	=SUMPRODUCT(B5:G5,B4:G4)
Subject To:							LHS		RHS
09.00 AM - 10.00 AM	1	1	0	0	0	0	=SUMPRODUCT($B$5:$G$5,B8:G8)	>=	8
10.00 AM - 11.00 AM	1	1	1	0	0	0	=SUMPRODUCT($B$5:$G$5,B9:G9)	>=	12
11.00 AM - Noon	0.5	1	1	1	0	0	=SUMPRODUCT($B$5:$G$5,B10:G10)	>=	14
Noon - 1.00 PM	0.5	1	1	1	1	0	=SUMPRODUCT($B$5:$G$5,B11:G11)	>=	16
01.00 PM - 02.00 PM	1	0	1	1	1	1	=SUMPRODUCT($B$5:$G$5,B12:G12)	>=	18
02.00 PM - 03.00 PM	1	0	0	1	1	1	=SUMPRODUCT($B$5:$G$5,B13:G13)	>=	17
03.00 PM - 04.00 PM	1	0	0	0	1	1	=SUMPRODUCT($B$5:$G$5,B14:G14)	>=	15
04.00 PM - 05.00 PM	1	0	0	0	0	1	=SUMPRODUCT($B$5:$G$5,B15:G15)	>=	10
Max. # of FT available	1	0	0	0	0	0	=SUMPRODUCT($B$5:$G$5,B16:G16)	<=	12
% of PT hours constraint	0	4	4	4	4	4	=SUMPRODUCT($B$5:$G$5,B17:G17)	<=	=SUM(J8:J15)*0.4

Excel Sheet and Solver Options:

FT Shift PT Shifts 1:00 9:00 A.MA.M A.M. A.M. Noon P.M. 9:00 10:00 11:00 Solver Parameters 2 Shifts starting on 3 DV 4 Cost per Shift Set Target Cell: P1 P2 80 25 12 P3 P4 P5 Equal To: Max Min Value of: By Changing Cells: 25 25 25 Total Cost Optimal number of 5 employees 1235 $B$5:SG$5 Subject to the Constraints: RHS 7 Subject To: 8 09.00 AM - 10.00 AM 9 10.00 AM - 11.00 AM 10 11.00 AM - Noon 11 Noon - 1.00 PM 12 01.00 PM - 02.00 PM 13 02.00 PM 03.00 PM 14 03.00 PM 04.00 PM 1504.00 PM 05.00 PM 16 Max, # of FT available 17 constraint LHS SH$8:$H$15SJ$8:$J$15 12| >= 12| >= 141 >= 17| >= 12 0.5 16 18 17 15 10 12 0.5 12| >= 121仁 % of PT hours 4 4 4 4 4

Optimal Solution:

Number of Full time employees = 12

Number of PT employees in shift starting on 11:00 am = 8

Number of PT employees in shift starting in Noon = 3

Total Number of PT required = 8 + 3 = 11

Total cost scheduling= $1,235

Sensitivity Report:

Al B 6 Adjustable Cells Fina Reduced Objective Allowable Allowable Coefficient Increase Decrease 42.5 Cell Name Value Cost 9 ŞB$5 Optimal number of employees F 10 SC$5 Optimal number of employees P1 11 $D$5 Optimal number of employees P2 12 ŞE$5 Optimal number of employees P3 13 $F$5 Optimal number of employees P4 14 $G$5 Optimal number of employees P5 15 16 Constraints 17 149.5 1E+30 1E+30 12 160 1E+30 Final Shadow Constraint Allowable Allowable R.H. Side Increase Decrease 1E+30 1E+30 Cell Name Value Price 19 $H$8 09.00 AM - 10.00 AM LHS 20 $H$9 10.00 AM - 11.00 AM LHS 21 $H$10 11.00 AM - Noon LHS 22 $H$11 Noon 1.00 PM LHS 23 $H$12 01.00 PM-02.00 PM LHS 24 $H$13 02.00 PM - 03.00 PM LHS 25 $H$14 03.00 PM - 04.00 PM LHS 26 $H$15 04.00 PM - 05.00 PM LHS 27 SH$16 Max. # of FT available LHS 28 SH$17 % of PT hours constraint LHS 12 12 14 53.33333333 17 23 23 15 53.33333333 12 12 44 -7.083333333 12 14 16 18 1E+30 1E+30 1E+30 15 10 12 1E+30 1E+30