One employee starts on Monday, three start on Tuesday, four start on Thursday, one starts on Friday, two start on Saturday, and nine start on Sunday. In this example, a total of 20 employees is needed. You'll see the optimal number of employees for each day. You can also selected the options Assume Linear Model and Assume Non-Negative for the changing cells by clicking Options in the Solver Parameters dialog box and then selecting the check boxes in the Solver Options dialog box. To add this constraint,click Add in the Solver Parameters dialog box and enter the constraint in the Add Constraint dialog box shown below. In the objective cell A3you want to minimize the total number of employees. This total is the number of people working on Monday. To track the number of employees working each day, enter a 1 or a 0 in each cell in the range C5:I For example, the 1 in cell G5 indicates that employees who started working on Monday are working on Friday the 0 in cell H5 indicates that the employees who started working on Monday are not working on Saturday. Begin by entering trial values for the number of employees who start their five-day shift each day in the cell range A5:A For example, in A5, enter 1 to indicate that 1 employee begins work on Monday and works Monday through Friday. To set up the model, you need to track the number of employees working each day. Constraints - For each day of the week, the number of employees who are working must be greater than or equal to the number of employees required. Each changing cell must be a non-negative integer. Changing cells - Number of employees who start work the first of five consecutive days each day of the week. Objective cell - Minimize the total number of employees. Start by identifying the objective cell, changing cells, and constraints for your Solver model. What is the minimum number of employees the bank can have and still meet its labor requirements? All bank employees work 5 consecutive days. For example, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday, and so on.

The number of workers needed each day to process checks is shown in row 14 of the Excel worksheet shown below.

The following example demonstrates how you can use Solver to calculate staffing requirements.Ĭontoso bank processes checks 7 days a week. You can use Excel's Solver add-in to create a staffing schedule based on those requirements. Many businesses such as banks, restaurants, and postal service companies know what their labor requirements will be on different days of the week, and need a method to efficiently schedule their workforce. Keep in touch and stay productive with Teams and Officeeven when you're working remotely.

Machine shop scheduling excel