Mexico City Bank of Commerce and Industry is a busy bank. Due to different customers needs, it needs to assign a different number of tellers at the counter at different times of each day. The table below indicates the number of bank Employees/tellers needed at different hours of a day (from 9 am. to 5 pm) at one of its branches.
The branch will hire two different types of Employees/tellers: Full-time Teller and Part-time Tellers. The following is asummary of the info.
d Nadie 23 Han LA D Project. Bank Workforce Planning Mexico City Bank of Commerce and Industry is a busy bank. Due to different customers' needs, it needs to assign a different number of tellers at the counter at different times of each day. The table below indicates the number of bank Employees/tellers needed at different hours of a day (from 9am to 5pm) at one of its branches. The branch will hire two different types of Employees/tellers: Full-time Teller and Part-time Tellers. The following is a summary of the info. Employees * Full-time Employees: The bank currently has employed 18 Full time tellers/employees. Full-time tellers that work from 9 a.m. to 5p.m. Each full-timer will be paid $85 per day on average. . The 18 employees can't be layoff. However, the manager may use only part of the 18 Full- Time Employees in this branch and relocate the rest to another branch with a relocation-related cost $10 per- employee per-day. That is, the employees to be relocated to another department will cost this department $10, instead of $85, a day. Full-timers will have 1-hour lunchtime. To better use the workforce, the manager will divide the Full-timers (working in this branch) lunchtime into 2 groups (not necessarily equal size). One group will have lunchtime between 11-noon and the Rest will have lunchtime between noon-1pm. * 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 be hired for one of the 4- hour shifts which start any hour between 9 a.m. and 1 p.m. Each part-timer will be paid $40 per 4-hour shift on average. There is no lunchtime for part-timers. Besides, 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 be used in the branch, the number of part-time employees to be hired in Each 4-hour shift so that the total work- force related costs, that include Full timer cost, Part time cost, and relocation cost are minimized. Use the Linear Programming model learned in the course to help the manager find the best decision. Your job is to complete the 3 tasks listed in the Project Tasks listed in the previous page. The detailed requirements of what we need to do Task 1: LP formulation a) Give a summary of the project Problem i) In your summary, describe in WORDS on what Decision(s) you need to make, and criterion on which your decision is based, and how Linear Programming models can be used to help you make your decision. b) Formulate the project problem into an LP model i) In your Toledo Linear Programming formulation, please define and give explanation clearly on each of the followings, All decision variables. The objective function. All constraints needed, include the non-negativity constraints. Task 2: Use Excel solver to solver the LP model formulated 1) Sheet 1 contains the Excel Solver setup and Solutions of the LP model. Note: The spreadsheet must contain the formula created to solve the problem. 2) Sheet 2&3 contain the Answer and Sensitivity reports generated by the Solver Solution Outputs. Task 3: Use the information from the Excel Answer and Sensitivity reports from Task 2 to answer questions asked at the end of the project. a) Summarize the Optimal Solutions Decisions and the Optimal Objective Value/Cost. Note: Since we use the LP model to help us make decisions, solver solution may turn out to be non- integer while the # of employees should be integers in real applications. We will assume that non-integer optimal solution decision is acceptable. (Do not generate Task 3: Use the information from the Excel Answer and Sensitivity reports from Task 2 to answer questions asked at the end of the project a) Summarize the Optimal Solutions/Decisions and the Optimal Objective Value/Cost. Note: Since we use the LP model to help us make decisions, solver solution may turn out to be non- integer while the # of employees should be integers in real applications. We will assume that non-integer optimal solution/decision is acceptable. (Do not Force decision variables to be integers in the LP formulation and solver parameter bor. Otherwise, we will not able to answer questions in Task 3). b) Use information from the Sensitivity Report to answer the following questions Note: The questions in the should be answered by using the information from the Answer Report and Sensitivity Report ONLY. It is NOT acceptable 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 up with your answer by referring to the exact location in the Output Reports. Questions a) If the pay for a Full-time teller is Reduced by S4 a day, will the optimal decision remain the same? What will be the optimal workforce-related costs now? Explain. b) If the pay for a Full-time teller is increased by $5 a day? Will the optimal decision remain the same? Explain. c) If the required number of tellers in the 10:00-11:00am period is increased to 20, 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? d) If the required number of employees in the 11:00-12:00 am period is Reduced by 8, what may happen to the optimal decision and optimal hiring costs? d Nadie 23 Han LA D Project. Bank Workforce Planning Mexico City Bank of Commerce and Industry is a busy bank. Due to different customers' needs, it needs to assign a different number of tellers at the counter at different times of each day. The table below indicates the number of bank Employees/tellers needed at different hours of a day (from 9am to 5pm) at one of its branches. The branch will hire two different types of Employees/tellers: Full-time Teller and Part-time Tellers. The following is a summary of the info. Employees * Full-time Employees: The bank currently has employed 18 Full time tellers/employees. Full-time tellers that work from 9 a.m. to 5p.m. Each full-timer will be paid $85 per day on average. . The 18 employees can't be layoff. However, the manager may use only part of the 18 Full- Time Employees in this branch and relocate the rest to another branch with a relocation-related cost $10 per- employee per-day. That is, the employees to be relocated to another department will cost this department $10, instead of $85, a day. Full-timers will have 1-hour lunchtime. To better use the workforce, the manager will divide the Full-timers (working in this branch) lunchtime into 2 groups (not necessarily equal size). One group will have lunchtime between 11-noon and the Rest will have lunchtime between noon-1pm. * 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 be hired for one of the 4- hour shifts which start any hour between 9 a.m. and 1 p.m. Each part-timer will be paid $40 per 4-hour shift on average. There is no lunchtime for part-timers. Besides, 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 be used in the branch, the number of part-time employees to be hired in Each 4-hour shift so that the total work- force related costs, that include Full timer cost, Part time cost, and relocation cost are minimized. Use the Linear Programming model learned in the course to help the manager find the best decision. Your job is to complete the 3 tasks listed in the Project Tasks listed in the previous page. The detailed requirements of what we need to do Task 1: LP formulation a) Give a summary of the project Problem i) In your summary, describe in WORDS on what Decision(s) you need to make, and criterion on which your decision is based, and how Linear Programming models can be used to help you make your decision. b) Formulate the project problem into an LP model i) In your Toledo Linear Programming formulation, please define and give explanation clearly on each of the followings, All decision variables. The objective function. All constraints needed, include the non-negativity constraints. Task 2: Use Excel solver to solver the LP model formulated 1) Sheet 1 contains the Excel Solver setup and Solutions of the LP model. Note: The spreadsheet must contain the formula created to solve the problem. 2) Sheet 2&3 contain the Answer and Sensitivity reports generated by the Solver Solution Outputs. Task 3: Use the information from the Excel Answer and Sensitivity reports from Task 2 to answer questions asked at the end of the project. a) Summarize the Optimal Solutions Decisions and the Optimal Objective Value/Cost. Note: Since we use the LP model to help us make decisions, solver solution may turn out to be non- integer while the # of employees should be integers in real applications. We will assume that non-integer optimal solution decision is acceptable. (Do not generate Task 3: Use the information from the Excel Answer and Sensitivity reports from Task 2 to answer questions asked at the end of the project a) Summarize the Optimal Solutions/Decisions and the Optimal Objective Value/Cost. Note: Since we use the LP model to help us make decisions, solver solution may turn out to be non- integer while the # of employees should be integers in real applications. We will assume that non-integer optimal solution/decision is acceptable. (Do not Force decision variables to be integers in the LP formulation and solver parameter bor. Otherwise, we will not able to answer questions in Task 3). b) Use information from the Sensitivity Report to answer the following questions Note: The questions in the should be answered by using the information from the Answer Report and Sensitivity Report ONLY. It is NOT acceptable 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 up with your answer by referring to the exact location in the Output Reports. Questions a) If the pay for a Full-time teller is Reduced by S4 a day, will the optimal decision remain the same? What will be the optimal workforce-related costs now? Explain. b) If the pay for a Full-time teller is increased by $5 a day? Will the optimal decision remain the same? Explain. c) If the required number of tellers in the 10:00-11:00am period is increased to 20, 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? d) If the required number of employees in the 11:00-12:00 am period is Reduced by 8, what may happen to the optimal decision and optimal hiring costs
