PLEASE SHOW ANSWERS/CHARTS & HOW YOU GOT THEM USING EXCEL/EXCEL FUNCTIONS

Scheduling Bank Tellers 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 following table indicates the workers needed at various hours that the bank is open. TIME NUMBER OF TELLERS REQUIRED TIME PERIOD NUMBER OF TELLERS REQUIRED PERIOD 9 A.M.-10 10 1 P.M.-2 P.M. 18 A.M. 10 A.M.-11 12 2 P.M.-3 P.M. 17 A.M. 11 A.M. 14 3 P.M.-4 P.M. 15 Noon Noon-1 P.M. 16 4 P.M.-5 P.M. 10 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 11 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. We can let: = F P P2 full-time tellers part-timers starting at 9 A.M. (leaving at 1P.M.) part-timers starting at 10 A.M. (leaving at 2P.M.) = = P3 PA P3 part-timers starting at 11 A.M. (leaving at 3P.M.) part-timers starting at noon (leaving at 4P.M.) part-timers starting at 1P.M. (leaving at 5P.M.) SOLUTION Objective function: Minimize total daily manpower cost = $75F + $24(P+ + P2 + P3 + P4 + Ps) Constraints: For each hour, the available labor-hours must be at least equal to the required labor-hours: F + P F + P + P2 1 F + P + P + P3 > 10 (9 A.M. to 10 A.M. needs) > 12 (10 A.M. to 11 A.M. needs) > 14 (11 A.M. to noon needs) F + P + P + P3 + P4 > 16 (noon to 1 P.M. needs) F + P2 + P + P + P > 18 (1 P.M. to 2 P.M. needs) F + P3 + P + P > 17 (2 P.M. to 3 P.M. needs) F + P4 + P > 15 (3 P.M. to 4 P.M. needs) F + Ps > 10 (4 P.M. to 5 P.M. needs) Only 12 full-time tellers are available, so: F RELATED PROBLEMS B.360