Project. Workforce Planning

Hello, dear friend, you can consult us at any time if you have any questions, add WeChat: daixieit

Project. Workforce Planning

Mr. Good is a customer service department manager of an e-commerce company. To better serve its customers, Mr. Good needs to make sure a sufficient number of Employees/Tellers are on duty 24 hours a day to answer questions/complaints from its customers. The Table 1 below indicates the number of persons needed on duty in each 4-hour period of each day

Table 1

The department will hire two different types of Tellers - Full-time Tellers and Part-time Tellers.

v Full-time Tellers:

Ø There are three 8-hour shifts of Full-time tellers, with starting time at 9 am, 5 pm, or 1 am.

Ø Nightshift payments are given to tellers who work other than regular day shift (9:00am-5:00pm). The costs per day (8-hour shift) for different shifts are given in the following table 2.

Table 2

v Part-time Teller:

Ø There are six 4-hour shifts of Part-time tellers, with starting time at 1 am, 5 am, 9 am, 1 pm, 5 pm, or 9 pm.

Ø Similarly, the costs for Part-time Tellers who in different shifts are summarized below.

Table 3

❖ Besides, the labor law requires companies to limit the number of Part-time Employees to the number of Full-time Employees ratio. Assume that the total number of work hours of part-time Employees is no more than 40% of the total combined Employee work hours.

Your job is to help Mr. Good to find the best hiring plan (with the lowest total cost) for the number of Full-time and Part-time tellers in each 4-hour shift that meet teller requirements given in Table 1. Use the Linear Programming model learned in the course to help Mr. Good find the best decision. Then answer the following questions

a) If the daily pay for a Part-Time Teller cost for shift 5 am – 9 am is reduced by $2, will the optimal decision remain the same? What will be the optimal workforce-related costs now? Explain.

b) If the daily pay for a Part-Time teller cost for shift 5 am – 9 am is Increased to $80 per shift? Will the optimal decision remain the same? Explain.

c) If the number of Tellers needed during the period 9 am – 1 pm is increased by 10, How much you may save on workforce-related costs?  Will this change lead to a change in the optimal decision (hiring plan)?

d) If the number of tellers needed during the period 9 am – 1 pm is decreased to 35, what might happen to the optimal decision and optimal hiring costs?

Your job is to complete the following Parts

Part 1: LP formulation

a) Give a summary of the project
In your summary, describe in WORDS 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 a Linear Programming model
In your LP model formulation, please define and explain clearly each of the following,

o All decision variables -  each variable should be clearly defined

o The objective function -  an objective function is a linear function of decision variables.

o All constraints -  a constraint is a linear equality or inequality.

Note: LP model, except explanations, should be expressed mathematically, not qualitatively.

Part 2: Use Excel Solver to solve the LP model formulated. The Excel file should contain 3 sheets

Note: you need to complete Part 1 before you start Part 2, since the Excel solver setup is based on the LP formulation from Part 1.

a) Sheet 1 contains the Excel Solver setup and Solutions of the LP model.

o Note: The spreadsheet must contain the formula for the LP model formulated and the solver setup to solve the problem.

b) Sheet 2&3 contains the Answer and Sensitivity reports generated by the Solver Solution Outputs.

Note: Since we use the LP model to help us make decisions, the solver solution may turn out to be non-integer while the # of tellers should be integers in real applications. We will assume that a non-integer decision is acceptable. (Do not Force decision variables to integers in the Solver Parameter Box and Solver solutions. Otherwise, we will not be able to answer questions in Part 3).

Part 3: Use the information from the Excel Answer and Sensitivity reports from Part 2 to answer questions asked at the end of the project.

a) Summarize the Optimal Solutions/Decisions and the Optimal Objective Value/Cost.

b) Use information from the Sensitivity Report to answer the following questions

Note: The questions in Part 3 should be answered based on 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 give explanations on how you came up with your answer by referring to information obtained to the exact location in the Output Reports.