Example 4.

Postal Employee Scheduling

 Background Information
• A post office requires different numbers of full-time
employees on different days of the week.
• The number of full-time employees required each
day is given in this table.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Background Information --
continued
• Union rules state that each full-time employee
must work 5 consecutive days and then receive 2
days off.
– For example, an employee who works Monday to Friday
must be off Saturday and Sunday.
• The post office wants to meet its daily
requirements using only full-time employees.
<정수로 제한
• Its objective is to minimize the number of full-time
employees that must be hired.
Albright/Winston Management Science Modeling South-Western/Cengage Learning
Thomson/South-Western 2007 ©©
 Solution
• To model the Post Office problem with a
spreadsheet, we must keep track of the following:
– Number of employees starting work on each day of the
week
– Number of employees working each day
– Total number of employees
• It is important to keep track of the number of
employees starting work each day, because this is
the only way to incorporate the fact that workers
work 5 consecutive days.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Worker Scheduling.xlsx
• This file shows the spreadsheet model for this
problem.
• The spreadsheet figure on the next slide shows
the optimal solution.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
Albright/Winston Management Science Modeling South-Western/Cengage Learning
Thomson/South-Western 2007 ©©
 Developing the Model
• To form this spreadsheet, proceed as follows.
1. Inputs and range names. Enter the number of employees
needed on each day of the week in the shaded range, and create
the range names shown.
2. Employees beginning each day. Enter any trial values for the
number of employees beginning work on each day of the week in
the Employees_starting range.
3. Employees on hand each day. The important key to this solution
is to realize that the numbers in the Employees_starting range do
not represent the number of workers who will show up each day.
As an example, the number who start on Monday work Monday
through Friday. Therefore, enter the formula =$B$4 in cell B14 and
copy it across to cell F14. Proceed similarly for rows 15-20, being
careful to take “wrap arounds” into account.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Developing the Model --
continued
After completing these rows calculate the total number
who show up each day by entering the formula
=SUM(B14:B20) in cell B23 and copying across to cell
H23.
4. Total employees. Calculate the total number of
employees in cell B28 with the formula
=SUM(Employees_starting)
• At this point, you might want to try rearranging
the numbers in the Starting range to see if you
can “guess” an optimal solution. It’s not that easy.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Developing the Model --
continued
• Using the Solver – Now invoke the Solver and
complete the main dialog box as shown. Also,
check Assume Linear Model and Assume Non-
Negativity options in the Options dialog box

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Developing the Model --
continued
• This optimal solution requires the number of
employees starting work on some days to be a
fraction.
• Because part-time employees are not allowed,
this solution is unrealistic.
• We will now show how to solve the post office
model when the number of employees beginning
work each day must be an integer.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Using Solver with Integer
Constraints
• In the Solver dialog box, add the constraint as
shown below.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Using Solver with Integer
Constraints -- continued
• As we see, the post office needs to hire 23 full-
time employees. This solution reveals an aspect
of some modeling problems.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Using Solver with Integer
Constraints -- continued
• One other comment about integer constraints concerns
Solver’s Tolerance setting.
• As Solver searches for the best integer solution, it is often
able to find “good” solutions fairly quickly, but it often has
to spend a lot of time finding slightly better solutions.
• A nonzero tolerance setting allows it to quit early. The
default tolerance setting if 0.05. This means that if Solver
finds a feasible solution that is guaranteed to have an
objective value no more than 5% from the optimal value, it
will quit and report this “good” solution.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Sensitivity Analysis
• The most obvious type of sensitivity analysis
involves examining how the work schedule and
the total number of employees change as the
number of employees required each day changes.
• Suppose the number of employees needed each
day of the week increases by 2, 4, 6. How does
this change the total number of employees
needed?
• We can answer this by using the SolverTable add-
in, but we first have to alter the model slightly as
shown on the next slide.
Albright/Winston Management Science Modeling South-Western/Cengage Learning
Thomson/South-Western 2007 ©©
Albright/Winston Management Science Modeling South-Western/Cengage Learning
Thomson/South-Western 2007 ©©
 Sensitivity Analysis --
continued
• The problem is that we want to increase each of
the daily minimal required values by the same
amount. Therefore, we move the original
requirements up to row 12, enter a trial value for
the extra number required per day in cell K12, and
enter the formula =B12+$K$12 in cell B27, which
is then copied across to cell H27.
• Now we can use the one-way SolverTable option,
using cell K12 as the single input, letting it vary
from 0 to 6 in increments of 2, and specifying the
Total_employees cell as the single output cell.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©
 Sensitivity Analysis --
continued
• The results appear in rows 34-37 of the optimal
solution.
• When the requirement increases by 2 each day,
only 2 extra employees are necessary. However,
when the requirement increases by 4 each day,
more than 4 extra employees are necessary. The
same is true when the requirement increases by 6
each day.

Albright/Winston Management Science Modeling South-Western/Cengage Learning

Thomson/South-Western 2007 ©©