I have the layout to this problem but i need help on what to input into excel and solver

Scheduling for Citibank Citibank is more than 200 years old. It was chartered by New York State on June 16, 1812, two days before the outbreak of the war between the United States and Great Britain. Its first president was Colonel Samuel Osgood, who had fought beside George Washington in the Revolution. In 1865, it became The National City Bank of New York, and by 1893, it was first among New York City banks in terms of deposits ($34 million) and assets ($39 million). In December 1921, National City was the first major U.S. bank to offer compound interest on savings accounts, and in 1936, it was the first New York City bank to offer checking accounts without a minimum balance requirement. The name changed to The First National City Bank of New York in 1955; it was shortened to First National City Bank in 1962, and finally in 1978 it became Citibank, N.A. By 1992, Citibank had become the largest bank in the United States. Its parent company, Citicorp, had a global reach with branches and offices in 92 countries. Suppose that you are the Manager of Operations Analysis and Control for the bank. Recently you had lunch with Dr. Megan Osgood, a great-great granddaughter of Samuel Osgood and vice president of Human Resources, who complained about the difficulties she has in hiring employees for one particular back-office operation of the bank because of its variable workload requirements. In this department the workload is low at the beginning of the day. As the day goes on it picks up rapidly, peaking during midday, and then decreasing until closing time. This peaking of the workload around the middle of the day contrasts with resources available to do the work, which normally are relatively constant during the day. Seeing this as a perfect situation for using a linear model, you appeared politely interested and asked Dr. Osgood for more details. It turns out that Dr. Osgood is currently using part-time employees to smooth out the workload. This option, she said, is particularly attractive because they do not need any special training to perform the required work. In addition, they are not entitled to benefits, which saves the bank a little money. However, she indicated that the bank and the agency where she obtains the part-time employees have so many complicated policies to follow that she is being driven crazy trying to figure out what to do. For example, she is constrained by a bank policy that part-time workers not be used for more than 45% of the total available hours worked by all employees during a day. In no case can there be more than 40 part-time employees working during any day. The agency requires that part-time workers be guaranteed at least 4 hours of work a day. Furthermore, the bank also dictates that part-time workers cannot work more than 7 hours per day. Full-time employees work 8 hours, 1 hour of which is a lunch break; so their productive time is only 7 hours. Also, full-time employees must work for at least two hours before taking their lunch break. One complicating factor here is that the bank's policy requires that one-half of the full-time workers take a lunch break from 11:00 a.m. to noon and the others between noon and 1:00 p.m., so that 50% of the full-time employees are on duty at all times during the middle of the day. Another especially troublesome problem for Dr. Osgood is that the department in question works until 7:00 p.m. This means that some full-time employees may be required to work overtime. However, company policy dictates that such employees are not allowed to work more than 5 hours of overtime per week. The ostensible reason for this is that full-time employees work 35 hours a week (not counting the lunch hours, for which they are paid) so that 5 hours of overtime gives a total of 40 hours per week. Thus, the pay for up to 5 hours of overtime is at the normal rate and not at time and a half (which would be applicable to hours in excess of 40 hours per week). WORKFORCE REQUIREMENTS NUMBER OF PERSONNEL TIME PERIOD REQUIRED 9.10a.m. 10-11 a.m. 11 a.m. -12 p.m. 12.1 p.m. 1-2 p.m. 2-3p.m. 3.4 p.m. 4-5 p.m. 5-6 p.m. 6-7 pm SS8 Through further discussions with Dr. Osgood, you find out that her goal is to meet the workload requirements at a minimum possible personnel cost, subject to all the bank's and the agency's policies and requirements. From the bank's accounting department, you learn that the average cost per full-time employee is $20 per hour including fringe benefits and $15 per hour excluding fringe benefits. The latter figure, you are informed, is applicable to the first 5 hours of overtime for any employee. The bank pays $14 per hour to the agency for the part-time employees. OBJECTIVE: MINIMIZE COST After you have solved the part (1) model, explore the following three parts. 1. If the union demands that all overtime be compensated at time and a half, how would this change your recommendations? 2. Suppose that the maximum number of part-time employees who can work on any one day, 40, is eliminated. How would this affect the original solution? 3. What would the effect be of changing the bank's policy so that exactly 25% of the full-time employees are required to have lunch at 11 a.m.? For parts 2-4, modify the original (part 1) model. On the spreadsheets created after the original (part 1) model, fill in with yellow any cells that your group (NOT SOLVER) changed or added to your original (part 1) model. C G H I J K L M N B 1 Citibank Scheduling D E F Objective: Minimize Costs Full Time - Part Time Up to 35 hours $14 $20 Full Time - More than 35 hours $15 5 Salary per hour 8 Part Time Full Time 8 Req. hours per day ours per day 4 9 Max, hours per day 7 10 9-10 am 10 - 11 am 12 Required 12 12 11 am - 12 pm 30 12-1 pm 56 1-2 pm 66 2-3 pm 52 3-4 pm 40 4-5 pm 26 5-6 pm 20 6-7 pm 1 0 10 - 11 am 11 am - 12 pm 12-1 pm 1-2 pm 2-3 pm 3-4 pm 4-5 pm 5-6 pm 6-7 pm Total 0 0 0 o 9-10 am 9am-5pm with 16 llam-12pm break 9am - 5pm with 17 12-1pm break 10am-6pm with 18 12.1pm break 10am-7pm 12- 19 1pm break 20 Sam -1pm 21 10am-2pm 22 11am-3pm 23 12.4pm 24 1 - 5pm 25 2-6pm 26 3 - 7pm 27 9am-2pm 28 10am-3pm 29 11am-4pm 30 12- 5pm 31 1-6pm 32 2-7pm 33 9am - 3pm 34 10am-4pm 35 11am - 5pm 36 12-6pm Sheet1 0 0 0 0 0 0 0 0 0 OOO OOOO 0 0 0 0 0 000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 19 1pm break 20 9am -1pm 21 10am-2pm 22 11am - 3pm 23 12 - 4pm 24 1 - 5pm 25 2 - 6pm 26 3 - 7pm 27 9am-2pm 28 10am - 3pm 29 11am-4pm 30 12- Spm 31 1-6pm 32 2 - 7pm 33 9am - 3pm 34 10am-4pm 35 11am - 5pm 36 12-6pm 37 1 - 7pm 38 9am-4pm 39 10am - 5pm 40 11am -6pm 41 12-7pm 42 Total 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Yo SIYO YO 44 Required > 40 12 3056 52 26 20 1 0 Full time employee cost Lunch break contraint 50% fulltime break 11am-12pm Mandator 0 O Actual 48 Full time Employees 9am - 5pm with 49 llam-12pm break 9am - 5pm with 50 12- 1pm break 10am-6pm with 51 12 - 1pm break Over-time Full 52 time employees 10am-7pm with 53 12-1 pm break 50% full time break 12-1pm Mandator 0 = O Actual 54 Total Sheet1 Part time employee cost 57 Part time Employees 58 9am -1pm 59 10am-2pm 60 11am - 3pm 61 12 - 4pm 62 1 - 5pm 63 2 - 6pm 64 3 - 7pm 65 9am-2pm 66 10am - 3pm 67 11am - 4pm 68 12- 5pm 69 1 - 6pm 70 2 - 7pm 71 9am - 3pm 72 10am-4pm 73 11am - 5pm 74 12 - 6pm 75 1 - 7pm 76 9am - 4pm 77 10am - 5pm 78 11am - 6pm 79 12 - 7pm 80 81 Total Max 82 83 .45 Constraint Total available hours Actual hours worked by part time employees Maximu m hours allowed to work by part time 86 87 Total Part time cost 88 Total Full time cost 89 Total cost $0 $0 SO 01 Scheduling for Citibank Citibank is more than 200 years old. It was chartered by New York State on June 16, 1812, two days before the outbreak of the war between the United States and Great Britain. Its first president was Colonel Samuel Osgood, who had fought beside George Washington in the Revolution. In 1865, it became The National City Bank of New York, and by 1893, it was first among New York City banks in terms of deposits ($34 million) and assets ($39 million). In December 1921, National City was the first major U.S. bank to offer compound interest on savings accounts, and in 1936, it was the first New York City bank to offer checking accounts without a minimum balance requirement. The name changed to The First National City Bank of New York in 1955; it was shortened to First National City Bank in 1962, and finally in 1978 it became Citibank, N.A. By 1992, Citibank had become the largest bank in the United States. Its parent company, Citicorp, had a global reach with branches and offices in 92 countries. Suppose that you are the Manager of Operations Analysis and Control for the bank. Recently you had lunch with Dr. Megan Osgood, a great-great granddaughter of Samuel Osgood and vice president of Human Resources, who complained about the difficulties she has in hiring employees for one particular back-office operation of the bank because of its variable workload requirements. In this department the workload is low at the beginning of the day. As the day goes on it picks up rapidly, peaking during midday, and then decreasing until closing time. This peaking of the workload around the middle of the day contrasts with resources available to do the work, which normally are relatively constant during the day. Seeing this as a perfect situation for using a linear model, you appeared politely interested and asked Dr. Osgood for more details. It turns out that Dr. Osgood is currently using part-time employees to smooth out the workload. This option, she said, is particularly attractive because they do not need any special training to perform the required work. In addition, they are not entitled to benefits, which saves the bank a little money. However, she indicated that the bank and the agency where she obtains the part-time employees have so many complicated policies to follow that she is being driven crazy trying to figure out what to do. For example, she is constrained by a bank policy that part-time workers not be used for more than 45% of the total available hours worked by all employees during a day. In no case can there be more than 40 part-time employees working during any day. The agency requires that part-time workers be guaranteed at least 4 hours of work a day. Furthermore, the bank also dictates that part-time workers cannot work more than 7 hours per day. Full-time employees work 8 hours, 1 hour of which is a lunch break; so their productive time is only 7 hours. Also, full-time employees must work for at least two hours before taking their lunch break. One complicating factor here is that the bank's policy requires that one-half of the full-time workers take a lunch break from 11:00 a.m. to noon and the others between noon and 1:00 p.m., so that 50% of the full-time employees are on duty at all times during the middle of the day. Another especially troublesome problem for Dr. Osgood is that the department in question works until 7:00 p.m. This means that some full-time employees may be required to work overtime. However, company policy dictates that such employees are not allowed to work more than 5 hours of overtime per week. The ostensible reason for this is that full-time employees work 35 hours a week (not counting the lunch hours, for which they are paid) so that 5 hours of overtime gives a total of 40 hours per week. Thus, the pay for up to 5 hours of overtime is at the normal rate and not at time and a half (which would be applicable to hours in excess of 40 hours per week). WORKFORCE REQUIREMENTS NUMBER OF PERSONNEL TIME PERIOD REQUIRED 9.10a.m. 10-11 a.m. 11 a.m. -12 p.m. 12.1 p.m. 1-2 p.m. 2-3p.m. 3.4 p.m. 4-5 p.m. 5-6 p.m. 6-7 pm SS8 Through further discussions with Dr. Osgood, you find out that her goal is to meet the workload requirements at a minimum possible personnel cost, subject to all the bank's and the agency's policies and requirements. From the bank's accounting department, you learn that the average cost per full-time employee is $20 per hour including fringe benefits and $15 per hour excluding fringe benefits. The latter figure, you are informed, is applicable to the first 5 hours of overtime for any employee. The bank pays $14 per hour to the agency for the part-time employees. OBJECTIVE: MINIMIZE COST After you have solved the part (1) model, explore the following three parts. 1. If the union demands that all overtime be compensated at time and a half, how would this change your recommendations? 2. Suppose that the maximum number of part-time employees who can work on any one day, 40, is eliminated. How would this affect the original solution? 3. What would the effect be of changing the bank's policy so that exactly 25% of the full-time employees are required to have lunch at 11 a.m.? For parts 2-4, modify the original (part 1) model. On the spreadsheets created after the original (part 1) model, fill in with yellow any cells that your group (NOT SOLVER) changed or added to your original (part 1) model. C G H I J K L M N B 1 Citibank Scheduling D E F Objective: Minimize Costs Full Time - Part Time Up to 35 hours $14 $20 Full Time - More than 35 hours $15 5 Salary per hour 8 Part Time Full Time 8 Req. hours per day ours per day 4 9 Max, hours per day 7 10 9-10 am 10 - 11 am 12 Required 12 12 11 am - 12 pm 30 12-1 pm 56 1-2 pm 66 2-3 pm 52 3-4 pm 40 4-5 pm 26 5-6 pm 20 6-7 pm 1 0 10 - 11 am 11 am - 12 pm 12-1 pm 1-2 pm 2-3 pm 3-4 pm 4-5 pm 5-6 pm 6-7 pm Total 0 0 0 o 9-10 am 9am-5pm with 16 llam-12pm break 9am - 5pm with 17 12-1pm break 10am-6pm with 18 12.1pm break 10am-7pm 12- 19 1pm break 20 Sam -1pm 21 10am-2pm 22 11am-3pm 23 12.4pm 24 1 - 5pm 25 2-6pm 26 3 - 7pm 27 9am-2pm 28 10am-3pm 29 11am-4pm 30 12- 5pm 31 1-6pm 32 2-7pm 33 9am - 3pm 34 10am-4pm 35 11am - 5pm 36 12-6pm Sheet1 0 0 0 0 0 0 0 0 0 OOO OOOO 0 0 0 0 0 000 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 19 1pm break 20 9am -1pm 21 10am-2pm 22 11am - 3pm 23 12 - 4pm 24 1 - 5pm 25 2 - 6pm 26 3 - 7pm 27 9am-2pm 28 10am - 3pm 29 11am-4pm 30 12- Spm 31 1-6pm 32 2 - 7pm 33 9am - 3pm 34 10am-4pm 35 11am - 5pm 36 12-6pm 37 1 - 7pm 38 9am-4pm 39 10am - 5pm 40 11am -6pm 41 12-7pm 42 Total 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 0 Yo SIYO YO 44 Required > 40 12 3056 52 26 20 1 0 Full time employee cost Lunch break contraint 50% fulltime break 11am-12pm Mandator 0 O Actual 48 Full time Employees 9am - 5pm with 49 llam-12pm break 9am - 5pm with 50 12- 1pm break 10am-6pm with 51 12 - 1pm break Over-time Full 52 time employees 10am-7pm with 53 12-1 pm break 50% full time break 12-1pm Mandator 0 = O Actual 54 Total Sheet1 Part time employee cost 57 Part time Employees 58 9am -1pm 59 10am-2pm 60 11am - 3pm 61 12 - 4pm 62 1 - 5pm 63 2 - 6pm 64 3 - 7pm 65 9am-2pm 66 10am - 3pm 67 11am - 4pm 68 12- 5pm 69 1 - 6pm 70 2 - 7pm 71 9am - 3pm 72 10am-4pm 73 11am - 5pm 74 12 - 6pm 75 1 - 7pm 76 9am - 4pm 77 10am - 5pm 78 11am - 6pm 79 12 - 7pm 80 81 Total Max 82 83 .45 Constraint Total available hours Actual hours worked by part time employees Maximu m hours allowed to work by part time 86 87 Total Part time cost 88 Total Full time cost 89 Total cost $0 $0 SO 01