Scheduling Bank Tellers
Salt Lake City Bank of Commerce 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 Period # of Tellers Required
9 A.M.–10 A.M. 10 10 A.M.–11 A.M. 12 11 A.M.–Noon 14 Noon–1 P.M. 16
1 p.m.–2 p.m. 18 2 p.m.–3 p.m. 17 3 p.m.–4 p.m. 15 4 p.m.–5 p.m. 10
In order to save money on payroll, the bank employs only part-time tellers. A part-time employee must put in exactly 4 hours per day but can be assigned to start their shift anytime between 9 A.M. and 1 P.M. Part-timers are a fairly inexpensive labor pool because no benefits are provided them.
Part-timers earn $8 per hour (or $32 per day).
Determine the optimal schedule of employees to minimize overall workforce expenses.
Type the LINEAR PROGRAMMING formulation and solve using Excel Solver. Submit your Excel file through Canvas.
a) How many employees work each shift – (eg. How many tellers begin work at 9AM? At 10AM? Etc.)
b) What is the total number of workers employed each day and what is their total cost?
c) Are there any time periods during the day when there are more tellers scheduled than are required? If so, when and how many?