Solve step by step w Excel please
Example B4* Scheduling Bank Tellers Co Mexico City Bank of Commerce and Industry is a busy bank that has requirements for between 10 and 18 tellers depending on the time of day Lunchtime, from noon to 2 P.M., is usually heaviest. The table below indicates the workers needed at various hours that the bank is open TIME NUMBER OF TELLERS TIME NUMBER OF TELLERS PERIOD I REQUIRED PERIOD REQUIRED 124: 9 A.M.-10 10 1 P.M.-218 A.M. P.M. 10 A.M.-11 1 2 P,M.-3 10 AM.-1112 2 P.M.-3 17 A.M. P.M. 14 3 P.M.-415 Noon P.M. Noon-1 16 4 P.M.-5 10 P.M. P.M. The bank now employs 12 full-time tellers, but many people are on its roster of available part-time employees. A part-time employee must put in exactly 4 hours per day but can start anytime between 9 A.M. and 1 P.M. Part-timers are a fairly inexpensive labor pool because no retirement or lunch benefits are provided them. Full-timers, on the other hand, work from 9 A.M. to 5 P.M. but are allowed 1 hour for lunch. (Half the full-timers eat at 1 1 A.M., the other half at noon.) Full- timers thus provide 35 hours per week of productive labor time. By corporate policy, the bank limits part-time hours to a maximum of 50% of the day's total requirement. Part-timers earn $6 per hour (or $24 per day) on average, whereas full-timers earn $75 per day in salary and benefits on average. APPROACH The bank would like to set a schedule, using LP, that would minimize its total manpower costs. It will release 1 or more of its full-time tellers if it is profitable to do so. ireless CO2R. We can let: F full-time tellers P part-timers starting at9 A.M. (leaving atl P.M.) P2 part-timers starting at 10 A.M. (leaving at 2 P.M.) Pg part-timers starting at l i .. (leaving at 3 P.M.) Ps part-timers starting at noon (leaving at 4 P.M.) Ps part-timers starting at 1 P.M. (leaving at 5 P.M.) 3484 SOLUTION Objective function: Minimize total daily manpower cost- $75FS24 (PP2 +Ps +P +Ps) irel Co Constraints: For each hour, the available labor-hours must be at least equal to the required labor-hours: F+P 210 (9A.M.to 10A.M.needs) z 12 (10AM. to 11 A.M.needs) 2 14 (I1 A.M.to noon needs) +P+P 215 (3 P.M. to 4 P.M. needs +P 10 (4 P.M. to 5 P.M. needs) Only 12 full-time tellers are available, so: F 12 Part-time worker-hours cannot exceed 50% of total hours required each day. which is the sum of the tellers needed each hour: 4 (P1 + P2 + Ps + P4 +Ps) S.50 (101214 +16+ 18 17+ 15 + 10) or: 4P1 +4P2 +4P3 +4Ps +4Ps S 0.50 (112) F, P1, P2, P3, P4, Ps 20 There are two alternative optimal schedules that Mexico City Bank can follow. The first is to employ only 10 full-time tellers (F 10) and to start 7 part-timers at 10 AM, (P2-7), 2 part-timers at 11 A.M. and noon (P3-2 and P., 2), and 3 part-timers at 1 P.M. (Ps 3). No part-timers would begin at 9 A.M. The second solution also employs 10 full-time tellers, but starts 6 part-timers at 9 A.M. (P, 6), 1 part-timer at 10 A.M. (P2 1), 2 part-timers at 11 A.M. and noon (P, 2 and P 2), and 3 part-timers at 1 P.M. (Ps 3). The cost of either of these two policies is $1,086 per day. INSIGHT It is not unusual for multiple optimal solutions to exist in large LP problems. In this case, it gives management the option of selecting, at the same cost, between schedules. To find an alternate optimal solution, you may have to enter the constraints in a different sequence LEARNING EXERCISE The bank decides to give part-time employees a raise to $7 per hour. Does the solution change? [Answer: Yes, costs $1,142, F= 10, P 6. RELATED PROBLEMS B.36
