Acme De Mexico’s Manager has requested assistance in developing a minimum cost daily assignment schedule for the customer service employees in their newly built store. Specifically, he wants to know the minimum total cost per day, which is the decision variable. He also wants to know the exact amount of part time and full time employees which will determine the total cost. The objective function is to minimize costs. Acme specified a minimum number of employees required for each shift, a maximum number of employees per shift, specific shifts for full time and part time workers, and a maximum percentage of 50% of the total hours for part time employees. These constraints were input into Microsoft solver, which determined $47,800 to be the minimum employee cost per day, employing 23 fulltime workers and 45 part time workers per day. Specific assumptions were made which will be discussed in detail, along with the impact of non-typical days. A sensitivity analysis will then be performed to determine how the percentage of part time employees’ constraint affects the total cost per day.
Now the Acme De Mexico has completed the building process, it is now time to properly staff the store. The store manager, Mr. Rodriguez, has requested a minimum cost daily assignment schedule for the customer service employees at the new store. In order to have Acme De Mexico become a profitable business, it must make the best use of its resources (Jacobs & Chase, 2013). In this case the resources are time, money, and employees. In order to provide Mr. Rodriguez with the information he requested, linear programming will be utilized. Linear programming is the “several related mathematical techniques used to allocate limited resources among competing demands in an optimal way” (Jacobs & Chase, 2013, appendix A).
In this case, we are given the following information. This report will provide an employee assignment schedule for a typical day, developed with a linear programming model (Attachment 1). This model and its cells will be referenced throughout the report. An explanation will be provided to explain the model to include the assumptions made. The report will also briefly touch on how non-typical days may affect the schedule. Employee Assignment Schedule
Acme De Mexico is open daily, from 7:00am to 11:00pm. Employee shifts are broken out over those 16 hours. For every hour of the day, a minimum amount of employees are required to be on the floor, which is depicted in the table below.
The minimum number of employees (limit) needed on the floor at a given hour is one of the constraints. This constraint is displayed in cells G22 though V22. Additionally, only 30 employees are allowed on the floor at any given time for safety reasons. This constraint can be seen in cells G26 though V26
This is also a constraint, or limit. See cells A5-21 through cells C5-21. Part time employees are paid $500 (Pesos) per day, and full time employees are paid $1100 per day. Another constraint is the hours worked by part time employees cannot exceed 50% of the total hours worked per day (total hours = part time+full time). This is displayed in cell F36. Excel solver was used to solve the decision variable (E33), which is set as the objective. Cells D5 though D21 are the number and type of employees per hour, and are variable.
The goal is to determine the minimum total cost per day. This is our decision variable, and is found in cell E33 of Attachment 1. The constraints mentioned above are input into solver. The first line shows the total number of part time employees must be less than or equal to 50% to the total labor hours each day. The second line ensures that the changing values are integers. We do not want half an employee to show up for his or her shift. The third line constraint ensures that the number of employees per shift does not exceed 30. Lastly, the fourth line constraint took into account the minimum employees per shift as specified by Acme.
Our objective function is to minimize Acme’s the total employee cost per day. The total employee cost per day was calculated by multiplying the number of fulltime workers per day (E8) by the salary per day (C31). This total is reflected in cell C33. The same was done for part time workers: (E21)*(D31)=(D33). These two numbers were then added together, (C33)+(D33)=(E33). Solver determined $47,800 (E33) to be the minimum employee cost per day, employing 23 fulltime workers and 45 part time workers per day.
According to Knode, a few key assumptions are made when using linear programming: “The assumption of a linear relationship (between the objectives, the constraints, etc.), the assumption of continuous relationships, and the assumption of non-negative relationships” (2011). Additionally, the assumption was made that the solution and variables would be integers, that is, not a fraction of an employee. It is also assumed that variables and solutions will be non-negative numbers. It can be assumed that there are enough employees to cover for employees who call in sick.
Non-typical days may affect the schedule. For example, employees may call in sick. Employees who are off may have to come in to cover these shifts, or employees may have to work overtime to cover for the sick employee. This could increase the daily cost if the overtime rate is more than the hourly rate. Overtime may also come into play during holidays or busy times of the year. Acme may decide to open earlier and/or stay open later during these times. Acme would need to hire more employees to cover the extra shifts, or employees would have to work overtime.
Sensitivity Analysis allows us to look at “variations in key aspects of the problem that could change the baseline answer” (Knode, 2011). One such key aspect is the constraint that hours worked by part time employees cannot exceed 50% of the total hours worked per day. The percentage of part time employees was varied to explore the possible outcomes. The results are displayed in the table below. It is interesting to note that with 0% part time employees, the total cost is the lowest.
Linear programming is a very useful tool which can help mangers solve many problems, including the problem of employee staffing. In the Acme De Mexico case, the decision variable was the minimum total cost per day for employee staffing. This also required determining the number of part time and full time employees per shift. Constraints were given and were input into solver, which resulted in a minimum daily cost of $47,800, with 23 full time employees and 45 part time employees.
Knode, C.S. (2011). Linear programming – Part 1 – Formulating the problem
. Retrieved from: http://vimeo.com/duffer44/linear-programming-part-1 Jacobs, F.R & Chase, R.B. (2013). Operations and supply management: The
core, 3e. Chapter 1 and Appendix A