Situation Description and Objective
This
exercise is based on Example 4.2 (Worker and Production Planning at SureStep)
in the textbook. Most companies need to determine a production schedule in
order to meet the demand of their customers. We will use Excel Solver to plan a production schedule that will minimize total
costs given certain constraints. During the next 4 months, SureStep Company
wants to meet on time the following demands for pairs of shoes:
Month 
Pairs
of shoes 
1 
3000 
2 
5000 
3 
2000 
4 
1000 
Note that
this is the certainty assumption that the company faces. Normally, we do not
know how much to produce in each month given the limited amount of resources
needed to produce the shoes. But, here, for the sake of simplicity, we set the
target to those 4 months values. The following are the input variables to begin
with:
Figure
1
The
solution requires that we keep track of each month’s beginning inventory,
ending inventory, production and all costs
The goal of this spreadsheet is to
determine the optimal production schedule and labor policy
We will
look at some of the variables that
will determine the final output.
We know
that the company has 100 workers at the beginning of month 1. If they decided
to hire 0 worker and fire 6, then the number of workers available after firing
and hiring = 94. The # of worker to hire and the # of
workers to fire at the beginning of each month are the decision variables. Each
worker has 160 hours per month. Hence, the “regular total hours available” = 94
x 160 = 15040 hours. This does not include the number of overtime labor hours
the company decides to use. Now, we have 96 workers available to produce shoes.
Each worker has overtime limit of 20 hours. The maximum overtime hours
available = 96 x 20 = 1920. Does it mean that the company will use the maximum
overtime hours available? The overtime labor hours
used is the decision variable and it varies from month to month.
Remember that the company wants to use the combination of variables that best
maximize the total profit. Let’s say, the company uses 0 overtime hours. Total
hours used for production = regular total hours available + overtime labor
hours used (15040 = 15040 + 0)
Next, we
determine the “production capacity” by dividing the “number of hours used for
production” by the “number of hours used to produce a pair of shoes.” For
example, if there is 15040 total hours for production and it takes 4 labor
hours to produce a pair of shoes, then the production capacity = 15040/4 = 3760
We know
that the production capacity in month 1 = 3760. Suppose the company decides to
produce 3760 shoes (The decision to produce x number of shoes is the decision
variable). Then “inventory after production” is simply the addition of
last month’s ending inventory (500) to this month’s # of shoes produced (3760).
The demand for the first month is 3000. Ending Inventory is calculated by
subtracting “demand” by “Inventory after production.” If the result is
negative, then it means the company fails to meet the desired demand. A
shortage cost = $15 per unit. If “demand” is less than “inventory after
production,” the company has some shoes left in the inventory that is not sold.
The holding cost = $3 per pair of shoes. This example shows us that the company
has 1260 ending inventory. The cost of holding excess inventory = 1260 x $3 =
$3780.
The
company wants to make sure that by the end of month 4, demand is met. This
means that Ending Inventory in month 4 cannot be negative (the company will not
incur shortage cost). If there’s excess inventory, the company will incur
holding cost. If the ending inventory = 0, the company does not incur any cost.
This is consistent with the idea of minimizing cost.
Figure
2
Click
here to get the spreadsheet
Variables & Attributes
Variables 
How Measured 
Related to 
# of
workers available after hiring and firing 
Integer 
# of
workers from previous month & hiring of new workers and firing of
existing workers 
Regulartime
hours available 
Hours 
# of
workers after hiring and firing & each worker’s hours per month before
receiving overtime 
Maximum
overtime labor hours available 
Hours 
# of
workers after hiring and firing & each worker’s 20 overtime hours per
month 
Total
hours for production 
Hours 
Regulartime
hours available & Overtime labor hours used 
Production
capacity 
Number
of pairs of shoes (cannot be fraction) 
Total
hours for production & # of labor hours required to produce a pair of
shoes 
Inventory
after production 
Number
of pairs of shoes (cannot be fraction) 
Prior
month’s ending inventory & # of shoes produced in the current month 
Ending
inventory 
Number
of pairs of shoes (cannot be fraction) 
Inventory
after production & # of units demanded 
Decision Variables 
How Measured 
Subject to constraints: 
# of
workers hired 
Integer 
Value
has to be integer (can not be fraction) 
# of
workers fired 
Integer 
Value
has to be integer (can not be fraction) 
Overtime
labor hours used 
Hours 
Maximum overtime labor hours available 
Shoes
produced 
Number
of pairs of shoes (cannot be fraction) 
Production
capacity 
Excess
shoes 
Number
of pairs of shoes (cannot be fraction) 

Shortage
shoes 
Number
of pairs of shoes (cannot be fraction) 

Mathematical Model Formulation
The objective of this linear model is to minimize total costs. Therefore, “total costs” has to
be set as a target cell. We choose
to minimize total costs by adjusting the number of workers hired (B19:E19), the
number of workers fired (B20:E20), the overtime labor hours used (B24:E24) and
the number of shoes to produce (B31:E31). These are the decision variables. The values in these changing cells can be
changed to optimize the objective.
Figure
3
Constraints must be set as follows:
The
number of workers hired & fired must be set as integer (cannot be fraction).
The inventory after production in month 4 must be bigger or equal to the number
of shoes demanded in that month. Overtime labor hours used in each month cannot
exceed the maximum overtime labor hours available. The number of shoes produced
in each month cannot exceed the full production capacity. Net excess minus
shortage equals Ending Inventory. If there’s excess of 10 pairs of shoes, then
there’s no shortage. If there’s excess of 10 pairs of shoes, then shortage =
10 pairs.
The values
in the changing cells cannot be negative.
Development of Spreadsheet Model
We have
already shown the steps to develop the spreadsheet model. The first step is to
enter the values given in the exercise in the input cells (shown in Figure 1). Next,
we input trial values in the changing cells. The formulas for other variables
must also be set. For example, Production capacity in month 1 (B33) = Total
hours for production (B28) x number of labor hours used to produce a pair of
shoes (B12).
Then we
invoke Excel Solver and set it up as shown in Figure 3 (subject to
Constraints). Since this model is a linear model, we go to Options menu and
check both “Assume Linear Model” and “Assume NonNegative.”
We’re now
ready to tell Solver to find the optimum solution. Clicking Solve button will
do all the jobs in no time.
The final
step is to run sensitivity analysis and see the impact of the increase/decrease
in input variable on the output.
Results
We found
that in order to minimize total costs, we will have to fire some workers in
month 13 and hire none in each of the 4 months. Overtime labor hours will not
be used. The company will have to use full production capacity. Finally, we see
that the company will have to incur holding cost in month 1 and shortage cost in
month 2 & 3. There is no shortage or holding cost in the last month because
the company has to meet the demand. The minimum total costs solver found is
$690,180. Let’s do whatif analysis. Is there any way to decrease the total
costs by changing any of the input variable? We will change the unit shortage
cost (increment of $5) to determine if there is a better alternative for
SureStep.
Sensitivity of
shortages in the first three months and total cost to unit shortage cost
Unit
shortage cost 
Shortage1 
Shortage2 
Shortage3 
TotalCost 

$B$40 
$C$40 
$D$40 
$F$53 
$0 
0 
2,280

1,640

$621,740

$5 
0 
2,220

1,580

$640,920

$10 
0 
2,220

1,580

$659,920

$15 
0 
2,220

1,580

$678,920

$20 
0 
20 
500 
$690,180

$25 
0 
20 
20 
$692,780

$30 
0 
0 
0 
$692,820

$35 
0 
0 
0 
$692,820

As we
see, when the unit shortage cost is below $20, SureStep is willing to incur
large shortages – at significantly lower total cost. However, shortages become
much less attractive when the unit shortage cost increases, and no shortages
are incurred at all when this unit cost is above $25.
DSS Guidelines & Limitation
This model proves to be simple but useful in assisting labor and production planning. It is essentially every manufacturing company’s problem. The model could be enhanced by including other variables which may prove to be relevant in addressing company’s issues. All in all, this spreadsheet model is a linear model that helps solve the optimal value of an output given certain constraints and formulas that follow it. We have proven that we can use Excel Solver to solve an interesting optimization problem such as this one. We know that the target cell in this model is a linear function of the changing cells. The lefthand and righthand sides of each constraint are linear functions of the changing cells. When we use the functions =IF, =ABS, =MAX, =MIN which depends on any of the models changing cells, then the model becomes nonlinear. This in turn become an optimization problem for which Excel Solver is ill suited to find optimal solutions. Fortunately, genetic algorithm technique, as discussed in the textbook, can be used as a superior tool to find the optimal solution. The problem becomes more challenging if there’s uncertainty factor in it, such as a demand which is normally distributed with certain monthly mean and standard deviation.