Western Bank of Commerce and Industry (WBCI) is a busy bank that has requirements for between 4 and

Western Bank of Commerce and Industry (WBCI) is a busy bank that has requirements for between 4 and 9 tellers, depending on the time of the day. On a typical business day, the number of tells required at a local bank varies depending on the time of the day as follows. The demands are low in the morning, but peak from noon to 2 P.M.

TIME PERIOD	TELLERS REQUIRED
9 A.M.–10 A.M.	4
10 A.M.–11 A.M.	5
11 A.M.–Noon	7
Noon–1 P.M.	8
1 P.M.–2 P.M.	9
2 P.M.–3 P.M.	7
3 P.M.–4 P.M.	6
4 P.M.–5 P.M.	7

The bank currently has 5 full-time tellers, but use some part-time employees on a regular basis. A part-time teller works on a 4 hour shift per day but can start anytime between 9 A.M. and 1 P.M. Full time tellers work from 9 A.M. to 5 P.M. but are allowed 1 hour for lunch. (About half [2 or 3] the full-timers eat at 11 A.M., the other half [2 or 3] at noon.) Part-timers earn $15 per hour (or $60 per (four hour) day) on average, whereas full-timers earn $200 per (eight hour) day in salary and benefits on average.
The bank would like to set a schedule that would minimize its total manpower costs.

1. Formulate the problem as a linear program in algebraic form.
2. Formulate the problem as a linear program on a spreadsheet. (Always color the cells properly and use range names where appropriate.)
3. Solve the problem using the Solver. In this case, the bank does not plan to lay off any teller. In the optimal solution, how many part-time tellers (each working four hours) are required? What is the total cost of hiring part-timers? What is the total idle time of full time tellers? Show your computations.
4. The bank is reviewing its cost structure of the full-time and part-time tellers. They are thinking about using more part-time employees in lieu of full-timers. But, they would like to keep the full time hours at a minimum of 50% of the daily total requirement. It means the number of full-time tellers may be reduced. With these new conditions, reformulate the problem on a spreadsheet to minimize the total cost. Note that you must change the range names. Otherwise, they would refer to the original spreadsheet.
5. Solve the problem in (d) using the Solver. In the optimal solution, how many part- time tellers (each working four hours) are required? What is the total cost of hiring part-timers? What is the total cost of hiring full-timers? How many full- timers will be let go? Show your computations.