Use Excel's Solver tool to schedule your employees

by Mary Ann Richardson | May 22, 2007 5:45:00 PM

Tags: Microsoft Excel, Microsoft Office, Use Excel, Mary Ann Richardson, tool, Solver, Click OK, Microsoft Office Suite Excel Tips Newsletter

Takeaway: Solve a problem with Excel's Solver. This tool can help you help you optimize your employees by determining the right number of techs you need at any particular time.

You don't need to guess when it comes to determining how many employees you will need to cover a shift -- Excel's Solver can help. Excel's Solver can help. With the Solver what-if analysis tool, you can find an optimal result for a formula in one cell (called the target cell) by having Solver adjust the values in other cells (called the adjustable cells) according to your specifications and constraints.

For example, Solver can help you create a schedule for your IT staff who are working the weekend shift. Factors to consider include: an employee will work Friday and Saturday or Saturday and Sunday but not all three days; also, you want to keep costs down, but you need to schedule at least 25 employees on Friday, 35 on Saturday, and 12 on Sunday.

If you do not have Solver on your Excel 2003/2002 Data menu, you will need to install it first. Follow these steps:

1. Go to Tools | Add-ins.
2. Click the Solver Add-in check box.
3. Click OK and then click Yes in the Install message box.

After it is installed, Solver will display on the Tools menu.

To continue with scheduling, follow these steps:

1. Open a blank worksheet.
2. In C2, enter Total Weekend Employees.
3. In C3, enter =SUM(C5:C6).
4. In C4, enter Number Starting.
5. In D4, enter Day Employee Starts.
6. In E4, enter Friday.
7. In F4, enter Saturday.
8. In G4, enter Sunday.
9. In D5, enter Friday.
10. In E5, enter 1.
11. In F5, enter 1.
12. In G5, enter 0.
13. In D6, enter Saturday.
14. In E6, enter 0.
15. In F6, enter 1.
16. In G6, enter 1.
17. In D8, enter Number Working.
18. In E8, enter the formula =SUMPRODUCT($C$5:$C$6,E5:E6).
19. Copy the formula in E8 to F8 and G8.
20. In D9, enter >=.
21. In D10, enter Number Needed.
22. In E10, enter 25.
23. In F10, enter 35.
24. In G10, enter 12.
25. Go to Tools | Solver.
26. Enter $C$3 in the Set Target Cell text box.
27. Click the Min button.
28. Enter $C5:$C6 in the By Changing Cells text box.
29. Click the Add button.
30. Enter $C5:$C6 in the Cell Reference box.
31. Click the drop-down arrow, select int, and then click Add.
32. Enter $E$8:$G$8 in the Cell Reference box.
33. Click the drop-down arrow and select >=.
34. Enter $E$10:$G$10 in the Constraint box and then click Add.
35. Click Cancel, and then click the Options button.
36. Click the Assume Linear Model and the Assume Non-Negative check boxes and then click OK.
37. Click Solve.

Solver results show that, in order to cover all shifts over the weekend, you will need 25 people to start on Friday and 12 to start on Saturday, with a minimum of 37 employees for the entire weekend.

To see an example of the worksheet, click here.

Miss a tip?

Check out the Microsoft Excel archive, and catch up on our most recent Excel tips.

Help users increase productivity by automatically signing up for TechRepublic's free Microsoft Office Suite newsletter, featuring Word, Excel, and Access tips, delivered each Wednesday.

Print/View all Posts Comments on this article

vallinathvalleenath@...  | 05/23/07

A slight correction?apaludet@...  | 05/24/07

do not forget to thinkgerard.de.graan@...  | 05/25/07

RE: Use Excel's Solver tool to schedule your employeesAvner_Uzan@...  | 08/22/07

RE: Use Excel's Solver tool to schedule your employeeslouly_gheith@...  | 03/02/08