need solution in excel QM.

Chase Manhattan Bank 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 PM. For efficient use of resources, the personnel available should, therefore, vary comespondingly. Figure 8.2 shows a typical workload curve and corresponding personel requirements at different hours of the day. A variable capacity can be achieved effectively by employing part-time personel. Because part-timers are not entitled to all the fringe benefits, they are often more economical than fulltime 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 II A.M. and noon, and the remaining 50% go between noon and I RM. 5. The shift starts at 9 A.M. and ends at 7 P.M. (i.e., overtime is limited to 2 botrs). Any work left over at 7PM. 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 hours-not at one-and-a-half times the normal nate 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 hotir for full-timers (straight rate excluding fringe benefits) is $8,08. 3. The average cost per part-time personel hour is $7.82. The personel hours required, by hour of day, are given in Table 8.9. The bank's goal is to achieve the minimum possible personnel cost subject to mecting 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 parttimers. Would changing the 40% to a higher value significantly reduce costs? Seurce: Adapted from Shyam L. Moondra. "An L. P. Model for Work Force Schedaling for Banks," Joumal of Rank Besrach (Winter 1976): 299-301. TABLE 8.9 Workforce Requirements FIGURE 8.2