Sign in with Microsoft
Sign in or create an account.
Hello,
Select a different account.
You have multiple accounts
Choose the account you want to sign in with.

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. You can use Excel's Solver add-in to create a staffing schedule based on those requirements.

Schedule your workforce to meet labor demands (example)

The following example demonstrates how you can use Solver to calculate staffing requirements.

Contoso bank processes checks 7 days a week. The number of workers needed each day to process checks is shown in row 14 of the Excel worksheet shown below. For example, 13 workers are needed on Tuesday, 15 workers are needed on Wednesday, and so on. All bank employees work 5 consecutive days. What is the minimum number of employees the bank can have and still meet its labor requirements?

Data used in example

  1. Start by identifying the objective cell, changing cells, and constraints for your Solver model.

    Note: The objective cell is called the target cell in Excel 2007.

    • Objective cell – Minimize the total number of employees.

    • Changing cells – Number of employees who start work (the first of five consecutive days) each day of the week. Each changing cell must be a non-negative integer.

    • 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. (Number of employees working)>=(Needed employees)

  2. To set up the model, you need to track the number of employees working each day. Begin by entering trial values for the number of employees who start their five-day shift each day in the cell range A5:A11. For example, in A5, enter 1 to indicate that 1 employee begins work on Monday and works Monday through Friday. Enter each day’s required workers in the range C14:I14.

  3. To track the number of employees working each day, enter a 1 or a 0 in each cell in the range C5:I11. The value 1 in a cell indicates that the employees who started working on the day designated in the cell’s row are working on the day associated with the cell’s column. 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.

  4. To compute the number of employees working each day, copy the formula =SUMPRODUCT($A$5:$A$11,C5:C11) from C12 to D12:I12. For example, in cell C12, this formula evaluates to =A5+A8+A9+A10+A11, which equals (Number starting on Monday)+ (Number starting on Thursday)+(Number starting on Friday)+(Number starting on Saturday)+ (Number starting on Sunday). This total is the number of people working on Monday.

  5. After computing the total number of employees in cell A3 with the formula =SUM(A5:A11), you can enter your model in Solver as shown below.

    Solver Parameters dialog box

  6. In the objective cell (A3), you want to minimize the total number of employees. The constraint C12:I12>=C14:I14 ensures that the number of employees working each day is at least as large as the number needed for that day. The constraint A5:A11=integer ensures that the number of employees beginning work each day is an integer. To add this constraint,click Add in the Solver Parameters dialog box and enter the constraint in the Add Constraint dialog box (shown below).

    Change constraints dialog box

  7. 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.

  8. Click Solve. You'll see the optimal number of employees for each day.

    In this example, a total of 20 employees is needed. 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.

    Note that this model is linear because the objective cell is created by adding changing cells, and the constraint is created by comparing the result obtained by adding the product of each changing cell times a constant (either 1 or 0) to the required number of workers.

Top of Page

Need more help?

You can always ask an expert in the Excel Tech Community or get support in Communities.

See Also

Load the Solver Add-in in Excel

Get Microsoft schedule templates

Need more help?

Want more options?

Explore subscription benefits, browse training courses, learn how to secure your device, and more.

Communities help you ask and answer questions, give feedback, and hear from experts with rich knowledge.

Was this information helpful?

What affected your experience?
By pressing submit, your feedback will be used to improve Microsoft products and services. Your IT admin will be able to collect this data. Privacy Statement.

Thank you for your feedback!

×