Bank of America is working to develop an efficient work schedule for full-time and part-time tellers at its Monroe branch. The schedule must provide for adequate customer service as well as union mandated breaks for tellers. On weekdays, the branch is open from 9:00 A.M. to 7:00 P.M. The number of tellers necessary to provide adequate service during each hour of operation is given in the table below.

 

 

Time # Tellers Time # Tellers

 

9:00 A.M. - 10:00 A.M. 6 2:00 P.M. - 3:00 P.M. 5

10:00 A.M. - 11:00 A.M. 4 3:00 P.M. - 4:00 P.M. 6

11:00 A.M. - Noon 5 4:00 P.M. - 5:00 PM 8

 

Noon - 1:00 P.M. 8 5:00 P.M. - 6:00 P.M. 10

1:00 P.M. - 2:00 P.M. 7 6:00 P.M. - 7:00 P.M. 6

 

 

Each full-time employee must start his or her shift on the hour; and work for four consecutive hours, take an hour off for lunch, and then work four more hours. Part-time employees must all work for four consecutive hours beginning on the hour. Also, there must be at least one full-time employees working during every period of the day. Considering salary and fringe benefits, full-time employees cost the bank $160 a day and part-time employees cost them $72 a day. Develop an employment schedule that will satisfy customer service and minimize Bank of America's weekday employee cost.

Solution.

Period Time  Teller full-time Teller parcial-time

1 9:00 a.m. - 10:00 a.m. 1 5

2 10:00 a.m. - 11:00 a.m. 1 0

3 11:00 a.m. - noon 0 6

4 Noon - 1:00 p.m. 0 0

5 1:00 p.m. - 2:00p.m. 0 0

6 2:00 p.m. - 3:00p.m. 0 3

7 3:00 p.m. - 4:00p.m. 0 5

 

At a minimum cost of $ 1688

 

see explanation

Step-by-step explanation

Variables:

xfi: Full-time tellers starting in period i (i = 1,2) 

xpj: Part-time tellers starting in period j (j = 1,2,3,4,5,6,7

 

Objective function: minimize employee costs.

min. z = 160xf1+160xf2 + 72xp1 + 72xp2 + 72xp3 + 72xp4 + 72xp5 + 72xp6 + 72xp7

 

 

Restrictions.

 

Minimum number of employees to provide good service.

Period 1 (9:00 a.m. - 10 a.m.)

xf1 + xp1> = 6

Period 2 (10:00 a.m. - 11 a.m.)

xf1 + xf2 + xp1 + xp2> = 4

Period 3 (11:00 a.m. - noon)

xf1 + xf2 + xp1 + xp2 + xp3> = 5

Period 4 (noon-1: 00 p.m.)

xf1 + xf2 + xp1 + xp2 + xp3 + xp4> = 7

Period 5 (1:00 p.m. - 2:00 p.m.)

xf2 + xp2 + xp3 + xp4 + xp5> = 5

Period 6 (2:00 p.m. - 3:00 p.m.)

xf1 + xp3 + xp4 + xp5 + xp6> = 6

Period 7 (3:00 p.m. - 4:00 p.m.)

xf1 + xf2 + xp4 + xp5 + xp6 + xp7> = 8

Period 8 (4:00 p.m. - 5:00 p.m.)

xf1 + xf2 + xp5 + xp6 + xp7> = 10

Period 9 (5:00 p.m. - 6:00 p.m.)

The minimum of full-time employees in each period is 1 xf1> = 1 xf2> = 1

No negativity.

xfi> = 0, i = 1,2

xpj, j=1,2,3,4,5,6,7

 

Model

min. z = 160xf1+160xf2 + 72xp1 + 72xp2 + 72xp3 + 72xp4 + 72xp5 + 72xp6 + 72xp7

s.t.

xf1 + xp1> = 6

xf1 + xf2 + xp1 + xp2> = 4

xf1 + xf2 + xp1 + xp2 + xp3> = 5

xf1 + xf2 + xp1 + xp2 + xp3 + xp4> = 7

xf2 + xp2 + xp3 + xp4 + xp5> = 5

xf1 + xp3 + xp4 + xp5 + xp6> = 6

xf1 + xf2 + xp4 + xp5 + xp6 + xp7> = 8

xf1 + xf2 + xp5 + xp6 + xp7> = 10

xf1> = 1 

xf2> = 1

xfi> = 0, i = 1,2

xpj, j=1,2,3,4,5,6,7

 To solve the problem in Excel, we first load the data as indicated in the figure: 

Solution.

Period Time  Teller full-time Teller parcial-time

1 9:00 a.m. - 10:00 a.m. 1 5

2 10:00 a.m. - 11:00 a.m. 1 0

3 11:00 a.m. - noon 0 6

4 Noon - 1:00 p.m. 0 0

5 1:00 p.m. - 2:00p.m. 0 0

6 2:00 p.m. - 3:00p.m. 0 3

7 3:00 p.m. - 4:00p.m. 0 5

At a minimum cost of $ 1688

Please see the attached file for the complete solution