Slove question with excel and show excel formulas! The answer I had seem wrong and incomplete. Could another expert answer it please.

Case Study schedule that would meet personnel requirements at any given time and also be economical. Chase Manhattan Bank The workload in many areas of bank operations has the char- acteristics of a non-uniform 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. Figure 7.1 shows a typical workload curve and corresponding personnel requirements at different hours of the day. A variable capacity can be achieved effectively by employ- ing 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 Workload 9-10 AM 4 5 P.M. Hour Personnel Hours Required FIGURE 7.1 Figure for Case Study: Chase Manhattan Bank Some of the factors affecting personnel assignment are CASE STUDY 209 TABLE 7.17 Data for Chase Manhattan Bank TIME PERIOD HOURS REQUIRED 14 25 26 38 listed here: a. Full-time employees work for 8 hours per day, with a 1 hour break for lunch included. b. 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. c. Part-timers work for at least 4 continuous hours but no more than 7 continuous hours per day and are not allowed a lunch break. d. By corporate policy, part-time personnel hours are limited to a maximum of 40% of the day's total requirement. e. 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 con- sidered holdover for the next day. f. A full-time employee is not allowed to work more than 1 hour of overtime per day. He or she is paid at the normal rate even for overtime hours--not at one and one-half times the normal rate typically applicable to overtime hours. Fringe benefits are not applied to overtime hours. In addition, the following costs are pertinent: a. The average normal rate per full-time personnel hour is $24.08. b. The fringe benefit rate per full-time personnel hour is charged at 25% the normal rate. 9-10 AM 10-11 11-12 12-1 PM 1-2 2-3 3-4 4-5 5-6 6-7 55 60 51 29 14 9 personnel cost subject to meeting or exceeding the hourly work- force 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 19 Source: Based on Shyam L Moondra. "An L P Model for Work Force Scheduling for Banks," Journal of Bank Research (Winter 1976), 299-301 c. The average rate per part-time personnel hour is $17.82. The personnel hours required, by hour of day, are given in Table 7.17. The bank's goal is to achieve the minimum possible Internet Case Studies See the Companion Website for this textbook, at www.pearsonhigherod.com/balakrishnan, for additional case studies. Answer 1. Total minimum cost is $3253.34. Minimum cost schedule is given below. FULL TIME PERSONNEL OTI OT2 P1 P2 P3 P4 P5 P6 30 9:00-10:00 30 10:00-11:00 15 11 11:00-12:00 15 1114 12:00-13:00 30 1114 13:00-14:00 30 14:00-15:00 1114 30 15:00-16:00 30 16:00-17:00 17:00-18:00 18:00-19:00 2.Limitations of the model used to answer question no. 1 are - Part-timers must be less than 40% of the total workforce. Full-timers are allowed to work 8 hours including 1 hour lunch time Part-timers work at least 4 hours and maximum 7 hours without any lunch break. Fifty percent full timers go to lunch in the periods of 11-12 AM and 12-1 PM Every full-timers may have 2 hours over-time p