The workload in many areas of bank operations has the characteristics of a nonuniform distribution with respect to time of day. For example, at Chase Manhattan Bank, the number of domestic money transfer requests received from customers, if plotted against time of day, would appear to have the shape of an inverted-U curve with the peak reached around 1 P.M. For efficient use of the resources, the manpower available should, therefore, also vary correspondingly.
There are several ways to set up this problem. Here is one approach.
Note: I've used a 24 hour clock for the shift times.
The first part of the model describes the different shifts. (Note that these are not correct answers)
Given an eight hour shift for FT, there are three shifts available.
There are also two overtime slots, on each for the the first two shifts.
There are six PT shift start times. The case also includes a statement that they work at least four, but up to seven. The amount of people on an 'overtime' shift can only be equal to or less than the number of people on that shift.The constraints section will be used to help identify which FT and PT overtime hours should be used. All changeable solver cells are highlighted.
What about lunch? A separate line in the spreadsheet (not shown) calculates the number of available employees.
Below is a screenshot of the constraints. The x corresponds to the shift the constraint applies to (B is 9-10, C is 10-11, etc.)
The first constraint is the 40% max PT employees, followed by the hourly personnel requirements. The last series of <= constraints (rows 41 and down) represent the 'overtime' constraints.
So where does the 128.4 come from? If you look at the total HR requirement, by hour, you get a total of 321. 321 x .4 = 128.4
If you have followed this far, programming the costs for each shift, as well as the .5 FT break for lunch should be straightforward. My model had three lines for that. One for counting the total number of FT people on each shift (which is reduced by 50% for lunch), one line to total the FT pay, and another to calculate the PT employees. Combining those will help you get the info for formulas to satisfy the objective function and constraints.
Since some of the end of day employees are working overtime, and some are not, you'll need to account for this in your formulas.
You'll notice there are many variable cells, each separated by a comma. These are the shaded cells in the first screen shot.
These cells are also identified as integers, as we can't have .2 of an employee show up.
The bottom three rows represent the constraints column in the model.
Hope this helps.
