Question

Linear Programming Formulation Project

IE-311 Operation Research I

Western Bank of Commerce and Industry (WBCI) is a busy bank that has requirements for between 10 and 18 tellers, depending on the time of the day. The afternoon time, 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	Number 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

The bank now employs 12 full-time tellers but also has several people available on its roster of 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 fairly inexpensive labor pool because no retirement or lunch benefits are provided for them. Full-timers, on the other hand, work from 9 A.M. to 5 P.M. but are allowed 1 hour for lunch (Half of the full-timers eat at 11 A.M., the other half at noon). Each full-timer thus provides 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 $8 per hour (or $32 per day) on average, and full-timers earn $100 per day in salary and benefits, on average. The bank would like to set a schedule that would minimize its total personnel costs. It is willing to release one or more of its full-time tellers if it is cost-effective to do so. Develop a linear programming problem model, define your decision variables; write the objective function and all the relevant constraints, which will determine how many employees (part-timers as well as fulltimers) need to start their work at the different starting time permitted in order to minimize total personnel costs. Use the Excel solver to solve the model. What is the optimal schedule and optimal costs?

Note: As and Industrial Engineer you are required to submit a four pages technical report (excluding cover page), which consists of the following:

1. The problem description

2. The mathematical formulation

3. The Excel Solver Linear Programming problem (Screen Shot!!)

4. The conclusion, where you present your system outcomes as well as your observations.

Answer 1

1) The problem is about determining the optimal number of manpower, full-timers and part-timers, to be scheduled, in order to satisfy the manpower requirement at a minimum cost.

2) Mathematical formulation is as follows:

F = Full-time tellers

P1 = Part-time tellers starting at 9 AM (leaving at 1 PM)

P2 = Part-time tellers starting at 10 AM (leaving at 2 PM)

P3 = Part-time tellers starting at 11 AM (leaving at 3 PM)

P4 = Part-time tellers starting at noon (leaving at 4 PM)

P5 = Part-time tellers starting at 1 PM (leaving at 5 PM)

Objective: Min 100*F+32*(P1+P2+P3+P4+P5)

s.t.

9AM - 10AM: F+P1 >= 10

10AM - 11AM: F+P1+P2 >= 12

11AM - 12PM: 0.5F+P1+P2+P3 >= 14

12AM - 1PM: 0.5F+P1+P2+P3+P4 >= 16

1PM - 2PM: F+P2+P3+P4+P5 >= 18

2PM - 3PM: F+P3+P4+P5 >= 17

3PM - 4PM: F+P4+P5 >= 15

4PM - 5PM: F+P5 >= 10

4*(P1+P2+P3+P4+P5) <= 0.5*(10+12+14+16+18+17+15+10) or P1+P2+P3+P4+P5 <= 14

F, P1, P2, P3, P4, P5 >= 0

3) Solution by Excel Solver is as follows

Formula: H2 =SUMPRODUCT(B2:G2,$B$13:$G$13) copy to H2:H11

4) Conclusion: Total Full timers = 10   (which means 2 full-timers released from the existing team of 12 )

Number of part-timers to be scheduled:

P1 = 6

P2 = 1

P3 = 2

P4 = 5

P5 = 0

Total daily cost = $ 1,448