Workforce Planning Problem - Modelling and Solution with ILP and Simulation

Aim

The assignment requires you to model and solve a workforce planning problem for the number of cashiers operating tills in a supermarket. You will use integer linear programming and simulation to model the problem and solve/analyse the model using Excel Solver and Simul8. You will need to submit your models as an .xls file and .S8 file along with a report that explains your models of the problem, presents and analyses your results and discusses recommendations. Details of how the report should be structured and how the assignment is assessed are given after the case study below

The tills at a small supermarket are expected to service up to 12 customers each per hour. We are only considering the period between 10am and 7pm 8pm as outside of these hours the number of customers is low and only one till will be open.

The table below indicates the average number of customers by time period

The supermarket employs a mix of full and part time cashiers to operate the tills. There must always be at least one full time member of staff working (they don’t count for this purpose while they are having a break).Part-time cashiers work 4 hours a day if they are requested to work at all, and can start anytime from 10 am to 4 pm. They earn £8 per hour. Full-time cashiers work 8 hours, so they can start between 10 am and noon, they earn £90 per day. They are entitled to one hour break in their 5th hour of work. You have been asked to design a roster to minimise the total workforcecost.

a. Formulate the problem as an integer linear programme.

b. Solve the model using Excel solver

c. Write a report of no more than 500 words and a maximum of 4 pages. The report should includethe ILP formulation with an explanation of each equation. It should include screen shots of your Excel model with and without equations visible. It should show your Answer and Sensitivity report and give a full description of the data in these reports along with a recommendation for the roster.

Having determined the optimal supermarket roster, you understand that the system is stochastic and you want to understand how the solution would work in practice. The arrival rate of customers is exponentially distributed (with the mean number of arrivals in each time period as given in the table above) and the service time is normally distributed with a mean of 5 minutes and a standard deviation of 2.6minutes.

d. Build a simulate model using the number of cashiers determined in part 1b.

e. Run the model taking into account appropriate experimental design to give you results that are sufficiently robust to draw conclusions.

f. Write a report of no more than 1500 words and a maximum of 8 pages. The report should give an explanation of your simulation model and a discussion of the experimental design. Also include any assumptions. It should include screen shots of your Simul8 model. Your report should addressthe following questions. Each question is independent and you should start with your originalsimulation model (if you modify your model to answer a question, do not keep the modifications for thenext question):

i. What sort of service are customers provided with if you implement the optimal solution to the ILP?

ii. Given the results of the simulation, would you consider any changes to the number of cashiers and what would they be? Explain your answer using evidence from the model.

iii. Consider the situation that customers will leave the shop if there are more than 6 customers waiting for service. Add this to your model and discuss theresults. Management want to minimise cost while retaining a good service level. How many cashiers are needed in each time slot so the probability of a customer leaving the queue approximately 1%? Howdid you determine this?

iv. What does this case study tell you about the usefulness of deterministicoptimisation methods (i.e. ILP) and simulation models?