Project. Bank Workforce Planning . Mexico City Bank of Commerce and Industry is a busy bank that needs a different number of tellers at different times of working due to a different customers' needs. The table below indicates the number of bank Employees/tellers needed at different hours of a day at one of its branches. The branch will hire two different type of Employees/tellers: Full-time Teller and Part-time Tellers. The followings are summary of the info. Employees # of 4 Full-time Employees: Full-time tellers that work from 9 a.m. to 5 Time Period Tellers p.m. Needed Each full-timer will be paid $85 per day on average. 9 a.m. -10a.m. 10 The bank currently has employed 16. The manager may use 10 am-11 a.m. 14 only part of the 16 Full-Time Employees in this department. The unused Full-Time Employees will be relocated to another 11 a.m. -Noon 18 branch with a relocation related cost $10 per-employee per-day. 16 That is, the relocated employees will cost the branch $10 a day Noon-1 p.m. instead of $85 a day. 1 p.m.-2 p.m. 20 Full-timers will have 1-hour lunchtime. To better use the 16 workforce, the manager will divide the Full-timers' lunchtime 2 p.m. -3 p.m. into 2 groups. Half of them will have lunch between 11-noon 3 p.m.4 p.1 14 and another half will have lunch time between noon-1 pm 4 p.m.-5 p.m. 12 of Part-time Employee: The bank will hire Part-time Employees, many are available, to cover part of the tellers needed. Each part-time employee will work for one of the 4- hour shifts with starting time between 9 a.m. and 1 p.m. Each part-timer will be paid $40 per 4-hour shift ($10 per hour) on average. There is no lunchtime for part-timers. In addition, the bank limits part-time hours to a maximum of 40% of the day's total labor hours. The bank manager needs to make decisions on the number of full-timers to use in the branch, the number of part-time employees to hire in each 4-hour shift so that the total work-force related costs are minimized. You can a Linear Programming learnt to help the manager to make the best decision. More specifically, we need to complete the followings Part 1. Formulate the problem into a Linear Programming model. Part 2. Solve the Linear Programming using Excel Solver. Part 3. Summarize your optimal decision on i) the number of Full timers to use; ii) the number of part-time employees of different 4-hour shifts to be hired, and iii) the total workforce-related costs each day. a. If the pay for a Full-time employee is Reduced by $10, will the optimal decision remain the same? What will be the optimal workforce-related costs now? Explain. Will the optimal total costs change if the daily Full-time employee pay is Reduced by $20 a day? Explain. b) If the required number of employees in 10:00-11:00am is Increased to 17, How much more the bank needs to spend on workforce-related costs? Will this increase in the required number of employees leads to a change in the optimal decision? c) If the number of employees needed in period 11:00-12:00am is reduced by 2, what will be the optimal total costs? Will this change lead to a change in the optimal decision on Full-time and Part-time employees? How about if the required number of employees in period 11:00-12:00am is increased by 2, what may happen to the optimal decision and optimal hiring costs? Note: Since we use the Linear Programming model for the workforce scheduling decision, the optimal solution (decision) from the solver outputs may not be Integers (they should be integers in real applications). We Page 2 assume that we will accept Non-integer. That is, we will not require decision variables to be integers in our LP formulation and solver setup. Otherwise, we will not able to answer questions in Part 3. Then, use the Sensitivity Report from the solver outputs to answer the following questions. (Note Again: the questions should be answered ONLY use the information from the Answer Report and Sensitivity Report. It is Not allowed to answer the questions by Re-solving the LP models with updated parameters again and again. It is required that you explain how do you come with your answer by referring to the exact location in the Reports. Project. Bank Workforce Planning . Mexico City Bank of Commerce and Industry is a busy bank that needs a different number of tellers at different times of working due to a different customers' needs. The table below indicates the number of bank Employees/tellers needed at different hours of a day at one of its branches. The branch will hire two different type of Employees/tellers: Full-time Teller and Part-time Tellers. The followings are summary of the info. Employees # of 4 Full-time Employees: Full-time tellers that work from 9 a.m. to 5 Time Period Tellers p.m. Needed Each full-timer will be paid $85 per day on average. 9 a.m. -10a.m. 10 The bank currently has employed 16. The manager may use 10 am-11 a.m. 14 only part of the 16 Full-Time Employees in this department. The unused Full-Time Employees will be relocated to another 11 a.m. -Noon 18 branch with a relocation related cost $10 per-employee per-day. 16 That is, the relocated employees will cost the branch $10 a day Noon-1 p.m. instead of $85 a day. 1 p.m.-2 p.m. 20 Full-timers will have 1-hour lunchtime. To better use the 16 workforce, the manager will divide the Full-timers' lunchtime 2 p.m. -3 p.m. into 2 groups. Half of them will have lunch between 11-noon 3 p.m.4 p.1 14 and another half will have lunch time between noon-1 pm 4 p.m.-5 p.m. 12 of Part-time Employee: The bank will hire Part-time Employees, many are available, to cover part of the tellers needed. Each part-time employee will work for one of the 4- hour shifts with starting time between 9 a.m. and 1 p.m. Each part-timer will be paid $40 per 4-hour shift ($10 per hour) on average. There is no lunchtime for part-timers. In addition, the bank limits part-time hours to a maximum of 40% of the day's total labor hours. The bank manager needs to make decisions on the number of full-timers to use in the branch, the number of part-time employees to hire in each 4-hour shift so that the total work-force related costs are minimized. You can a Linear Programming learnt to help the manager to make the best decision. More specifically, we need to complete the followings Part 1. Formulate the problem into a Linear Programming model. Part 2. Solve the Linear Programming using Excel Solver. Part 3. Summarize your optimal decision on i) the number of Full timers to use; ii) the number of part-time employees of different 4-hour shifts to be hired, and iii) the total workforce-related costs each day. a. If the pay for a Full-time employee is Reduced by $10, will the optimal decision remain the same? What will be the optimal workforce-related costs now? Explain. Will the optimal total costs change if the daily Full-time employee pay is Reduced by $20 a day? Explain. b) If the required number of employees in 10:00-11:00am is Increased to 17, How much more the bank needs to spend on workforce-related costs? Will this increase in the required number of employees leads to a change in the optimal decision? c) If the number of employees needed in period 11:00-12:00am is reduced by 2, what will be the optimal total costs? Will this change lead to a change in the optimal decision on Full-time and Part-time employees? How about if the required number of employees in period 11:00-12:00am is increased by 2, what may happen to the optimal decision and optimal hiring costs? Note: Since we use the Linear Programming model for the workforce scheduling decision, the optimal solution (decision) from the solver outputs may not be Integers (they should be integers in real applications). We Page 2 assume that we will accept Non-integer. That is, we will not require decision variables to be integers in our LP formulation and solver setup. Otherwise, we will not able to answer questions in Part 3. Then, use the Sensitivity Report from the solver outputs to answer the following questions. (Note Again: the questions should be answered ONLY use the information from the Answer Report and Sensitivity Report. It is Not allowed to answer the questions by Re-solving the LP models with updated parameters again and again. It is required that you explain how do you come with your answer by referring to the exact location in the Reports