MBAA607- Dr. Leon
Project 1: Chase Manhattan Bank The workload in many areas of bank operations has the characteristic of being unevenly distributed throughout the day. For example, at Chase Manhattan Bank in New York, the number of domestic money transfer requests received from customers, if plotted against the time of day, would appear according to the workload described in Figure 1 below. For efficient use of resources, the personnel available should therefore vary correspondingly.
corresponding personnel requirements at different hours of the day.
A variable capacity can be achieved effectively by employing part-time personnel in conjunction with full-time personnel. Because part-timers are not entitled to all the fringe benefits, they are often more economical than full-time employees. Other considerations such as consistency and quality control 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 will meet personnel requirements at any given time and also be economical.
Some of the factors affecting personnel assignment for the Chase Manhattan Bank that you are modeling are as follows:
Corporate policy mandates that part-time personnel hours are limited to a maximum of 40% of the day’s total requirements.
Full-time employees start at 9:00 A.M. and work for 8 hours with one hour for lunch per day. Thus a full-timer’s productive time is 35 hours per week. They are not paid during their lunch break.
Part-timers work for at least 4 hours per day but no more than 6 hours and are not allowed a lunch break. A part-timer may start his/her shift anytime between 9:00 A.M. and 3:00 P.M.
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.
The bank operates from 9 A.M. to 7 P.M. Any work left over at 7 P.M. is considered holdover for the next day.
A full-time employee is allowed to work up to 2 hours of overtime per day, but the total overtime hours worked may not exceed more than 5 hours per week per employee. Overtime is worked between the hours of 5:00 P.M. and 7:00 P.M. The employee is paid at the normal rate of $10.85 for overtime hours (not at time and a half). Fringe benefits are not applied to overtime hours. When the fringe benefits are applied to the employee’s 35 hour work week, the average cost per full-time personnel hour is $12.40.
Part-time personnel are paid $8.80 per hour.
The personnel hours required, by hour of the day, are given in Table 1 below.
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.
Create a spreadsheet model that can be used to test any possible daily workforce schedule that the bank may wish to consider. Refer to the labor scheduling model presented in class for template layout ideas. Interpret the 5 hour weekly overtime limit per full-time employee as follows: the total number of overtime hours utilized per day should not exceed the number of full-time employees working. In this situation, each full-timer will not work more than one hour of overtime on average per day.
Optimize your spreadsheet model by filling in the solver dialog box with the appropriate set of constraints, changing cells and target cell.
In class, turn in a one page hardcopy executive summary along with as many table/graph attachments as you like that describes:
The recommended daily personnel schedule for Chase Manhattan Bank along with the projected costs using the guidelines and information described earlier.
A managerial analysis of the qualitative benefits and drawbacks of the schedule you are recommending. This should include any concerns that you might have about the schedule you identified due to limitations of the model you developed as well as the nature of the solution identified.
Your analysis of two suggestions that a manager at the bank recently proposed:
The corporate policy should be modified to allow part-time personnel hours to be limited to a maximum of 50% of the day’s total requirement. The manager feels that this will save the bank money and not hurt the quality of service provided. You should modify your model to see if you agree with his cost assessment.
Allow some of the full-timers to start their 8 hour shifts at 10:00 A.M. and 11:00 A.M. Overtime is utilized after a full-timer finishes working a 8 hour shift (including lunch). Lunches would now be taken after a full-timer works 3 hours of their shift. Thus all employees that start at 9:00 A.M. would now take their lunch break at noon, whereas employees that start at 10:00 A.M. would take their lunch break at 1:00 P.M. The manager feels that this new flexibility would allow the bank to save money and have the part-timers more evenly distributed throughout the day. Modify your model to see if you think this option has any potential. Consider this suggestion independently of the first suggestion.
In the same file, save your original model on one worksheet, the model that examines the PartTime suggestion in a second worksheet and the model that examines alternate Full Time shifts in a third worksheet. Submit your optimized models in MyLMUConnect’s Assignment section for project 1.
MODEL GRADE BREAKDOWN:
Part-Timer Modification (part C.i.)
Full-Timer Modification (part C.ii)
EXECUTIVE SUMMARY GRADESHEET:
Clear recommendation of daily personnel schedule and costs
Please note that your executive summary should not make any direct reference to your spreadsheet model. You should interpret the results and what you learned without referring to cells, formulas and constraints. Assume that the bank manager will not see your spreadsheet model!