How do I set up Excel to solve the Chase Bank Case Study? I am really struggling with how to set it up to make Solver work.

Here is the case study.

Total Cost: total cost of $3253.34

Chase Manhattan Bank Case Study:

Review, analyze, and complete the Chase Manhattan Bank Case Study below.

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 in New York, 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 around 1 P.M. For efficient use of resources, the personnel available should, therefore, vary correspondingly. A variable capacity can be achieved effectively by employing

part-time personnel. Because part-timers are not entitled to all the fringe benefits, they are often more economical than full-time employees. Other considerations, however, may limit the extent to which part-time people can be hired in a given department. The problem is to find an optimum workforce schedule that would meet personnel requirements at any given time and also be economical. Some of the factors affecting personnel assignment are listed here:

1. By corporate policy, part-time personnel hours are limited to a maximum of 40% of the day's total requirement.
2. Full-time employees work for 8 hours (1 hour for lunch included) per day. Thus, a full-timer's productive time is 35 hours per week.
3. Part-timers work for at least 4 hours per day but less than 8 hours and are not allowed a lunch break.
4. Fifty percent of the full-timers go to lunch between 11 A.M. and noon, and the remaining 50% go between noon and 1 P.M.
5. The shift starts at 9 A.M. and ends at 7 P.M. (i.e., overtime is limited to 2 hours). Any work left over at 7 P.M. is considered holdover for the next day.
6. A full-time employee is not allowed to work more than 5 hours overtime per week. He or she is paid at the normal rate for overtime hoursnot at one-and-a-half times the normal rate applicable to hours in excess of 40 per week. Fringe benefits are not applied to overtime hours.

In addition, the following costs are pertinent:

1. The average cost per full-time personnel hour (fringe benefits included) is $10.11.

2. The average cost per overtime personnel hour for full-timers (straight rate excluding fringe benefits) is $8.08.

3. The average cost per part-time personnel hour is $7.82.

The personnel hours required, by hour of day, are given in the following Table.

TABLE: Workforce Requirements

TIME PERIOD REQUIRED

NUMBER OF PERSONNEL

9-10 A.M.

14

10-11

25

11-12

26

12-1 P.M.

38

1-2

55

2-3

60

3-4

51

4-5

29

5-6

14

6-7

9

The bank's goal is to achieve the minimum possible personnel cost subject to meeting or exceeding the hourly workforce requirements as well as the constraints on the workers listed earlier.

Discussion Questions:

1. What is the minimum-cost schedule for the bank?

2. What are the limitations of the model used to answer question 1?

3. Costs might be reduced by relaxing the constraint that no more than 40% of the day's requirement be met by part-timers. Would changing the 40% to a higher value significantly reduce costs?

Hints & Suggestions:

Constraints:

Note the first ten constraints will be based on the workforce requirements listed on the assignmentpage, 11th constraint to meet the 40% condition, 12th constraint number of overtime hours, constraints 13-21 based on conditions for part-time employees (for example,part-Time employees must work at least 4 hours so those who leave at 1pm cannot start after 9am, i.e. the number of those who leave at 1pm has to be smaller than the number of employees who start at 9am. and 22nd constraint for all employees leaving at some time.

Variables:

A: Let F denote the number of full-time employees. Some number, F1, of them will work 1 hour of overtime between 5 P.M. and 6 P.M. each day and some number, F2, of the full-time employees will work overtime between 6 P.M. and 7 P.M.

B: Pj: number of part-time employee who begin their work day at hour 9am for j=1, 10am for j=2, 11am for j=3, 12noon for j=4, 1pm for j=5, 2pm for j=6, and 3pm for j=7. None can start after 3pm because they have to work for at least 4 hours and the last shift is at 7pm.

C: Qj: number of part-time employee who leave their work day at the end of hour j, 1pm for j=4, 2pm for j=5, 3pm for j=6, 4pm for j=7, 5pm for j=8, and 6pm for j=9. Everyone leaves at 7pm.

There will be 3 variables for A, 7 variables for B and 6 for C for a total of 16 variables.

How to develop constraints?

Constraint 1: number of personnel required for 9-10am as shown in Table

Constraint 2: number of personnel required for 10-11am as shown in Table

Constraint 3: number of personnel required for 11-12am as shown in Table(Half of the full-time employees go to lunch)

Constraint 4:number of personnel required for 12-1pm as shown in Table(The other half of the full-time employees go to lunch.)

Constraint 5: number of personnel required for 1-2pm as shown in Table (Some part time workers start to leave so have to bededucted.)

Constraint 13: part-Time employees must work at least 4 hours so those who leave at 1pm cannot start after 9am, i.e. the number of those who leave at 1pm has to be smaller than the number of employees who start at 9am.

Constraint 14: part-Time employees must work at least 4 hours so those who leave at 2pm cannot start after 10am, i.e. the number of those who leave at 2pm has to be smaller than the number of employees who start before 10am.

Translated as

1) F + P1>= 14

2) F + P1 + P2 >= 25

3) 0.5F + P1 + P2 + P3>= 26

4) 0.5F + P1 + P2 + P3 + P4 >= 38

5) F + P1 + P2 + P3 + P4 + P5 - Q4 >= 55

13) Q4<= P1

14)Q5 <= P1 + P2 - Q4

Use a similar approach to create other constraints.

Software tool:

You can use QM for Windows or Microsoft Excel Solver for the assignment.