Solving Nonlinear
Optimization Problems
with Excel Solver
for Microsoft Excel 2000
Denise Sakai Troxell (2000)
A Nonlinear Optimization Problem
a simple Inventory Model*
A manufacturer would like to produce
98,000 units of a certain product in a year,
in lots of a fixed size.
The fixed setup cost per lot is $500 and
the production cost per unit is $5.
The average inventory during a year is
half of the lot size and the average annual
inventory carrying cost per unit is $0.50.
What is the fixed lot size that minimizes
the balance between production and
inventory carrying costs?
* from Mathematics with Applications in Management and Economics
by Gordon Prichett and John Saber, Richard D. Irwin, Inc., 7th ed., 1994.
Denise Sakai Troxell (2000)
Formulate the Problem
obtain the function to be optimized
The objective is to find the lot size x, where 0 x 98,000,
that minimizes the balance between production and
inventory carrying costs given by the function
numbe
r of
lots
fixed
cost per
lot
f(x) = 98,000 (500) + 98,000 (5) + x (0.50)
x
2
Fixed Cost
Denise Sakai Troxell (2000)
Formulate the Problem
obtain the function to be optimized
The objective is to find the lot size x, where 0 x 98,000,
that minimizes the balance between production and
inventory carrying costs given by the function
production
cost per
unit
f(x) = 98,000 (500) + 98,000 (5) + x (0.50)
x
2
Fixed Cost
Denise Sakai Troxell (2000)
Variable Cost
Formulate the Problem
obtain the function to be optimized
The objective is to find the lot size x, where 0 x 98,000,
that minimizes the balance between production and
inventory carrying costs given by the function
Production
Cost
f(x) = 98,000 (500) + 98,000 (5) + x (0.50)
x
2
Fixed Cost
Denise Sakai Troxell (2000)
Variable Cost
Formulate the Problem
obtain the function to be optimized
The objective is to find the lot size x, where 0 x 98,000,
that minimizes the balance between production and
inventory carrying costs given by the function
Production
Cost
Carrying
Cost
f(x) = 98,000 (500) + 98,000 (5) + x (0.50)
x
2
average number
of units in
inventory
Denise Sakai Troxell (2000)
average
carrying cost
per unit
Formulate the Problem
obtain the function to be optimized
The objective is to find the lot size x, where 0 x 98,000,
that minimizes the balance between production and
inventory carrying costs given by the function
Production
Cost
Carrying
Cost
f(x) = 98,000 (500) + 98,000 (5) + x (0.50)
x
2
NOTE: We assume that x can be noninteger
Denise Sakai Troxell (2000)
Preparing the Worksheet for Solver
start with a blank sheet
Denise Sakai Troxell (2000)
Preparing the Worksheet for Solver
enter labels
Enter labels in cells
A1:B1
Denise Sakai Troxell (2000)
Preparing the Worksheet for Solver
enter labels
NOTE: These labels are not
essential for the use of
Solver
Denise Sakai Troxell (2000)
Preparing the Worksheet for Solver
enter the formula of the function to be optimized
Variable
values in cell
A2
f(x)
Function
formula in cell
B2
NOTE: These cells will be
colored to indicate that
they are essential for
Solver
Remember
f(x) = 98,000 (500) + 98,000 (5) + x (0.50)
x
2
Denise Sakai Troxell (2000)
Preparing the Worksheet for Solver
enter the formula of the function to be optimized
Click on cell B2
Remember
f(x) = 98,000 (500) + 98,000 (5) + x (0.50)
x
2
Denise Sakai Troxell (2000)
Preparing the Worksheet for Solver
enter the formula of the function to be optimized
Type in the formula
=(98000/A2)*500+98000*5+(A2/2)*0.50
Remember
f(x) = 98,000 (500) + 98,000 (5) + x (0.50)
x
2
Denise Sakai Troxell (2000)
Preparing the Worksheet for Solver
enter the formula of the function to be optimized
Hit Enter
NOTE: The formula in cell B2
is not defined if cell A2
contains the value 0 or it is
blank.
Remember
f(x) = 98,000 (500) + 98,000 (5) + x (0.50)
x
2
Denise Sakai Troxell (2000)
Preparing the Worksheet for Solver
enter the formula of the function to be optimized
NOTE: Avoid the error message #DIV/0! in cell
B2 by typing in an initial value different from 0
in cell A2
Denise Sakai Troxell (2000)
Using Solver
invoke Solver
Click on Tools
Denise Sakai Troxell (2000)
Using Solver
invoke Solver
Click on Solver
Denise Sakai Troxell (2000)
Using Solver
invoke Solver
Denise Sakai Troxell (2000)
Using Solver
complete the Solver Parameters dialog box
Click on cell B2
NOTE: The cell displayed in the Set Target Cell: box must
contain the formula of the function being optimized
(minimize cell B2)
Denise Sakai Troxell (2000)
Using Solver
complete the Solver Parameters dialog box
Check the
Min: circle
NOTE: The cell displayed in the Set Target Cell: box must
contain the formula of the function being optimized
(minimize cell B2)
Denise Sakai Troxell (2000)
Using Solver
complete the Solver Parameters dialog box
Click on the By
Changing Cells:
box
Denise Sakai Troxell (2000)
Using Solver
complete the Solver Parameters dialog box
Click on cell A2
NOTE: The cell displayed in the By Changing Cells:
box must be the cell containing variable values
(cell A2)
Denise Sakai Troxell (2000)
Using Solver
complete the Solver Parameters dialog box
Click on Add
NOTE: The Subject to the Constraints: box must
contain the constraints on the variable values (x
98,000)
Denise Sakai Troxell (2000)
Using Solver
complete the Solver Parameters dialog box
Click on cell A2
Click on the Cell
Reference: box
NOTE: The Subject to the Constraints: box must
contain the constraints on the variable values (x
98,000)
Denise Sakai Troxell (2000)
Using Solver
complete the Solver Parameters dialog box
Make sure <= is
displayed
NOTE: The Subject to the Constraints: box must
contain the constraints on the variable values (x
98,000)
Denise Sakai Troxell (2000)
Using Solver
complete the Solver Parameters dialog box
Click on the
Constraint: box
and type in 98000
NOTE: The Subject to the Constraints: box must
contain the constraints on the variable values (x
98,000)
Denise Sakai Troxell (2000)
Using Solver
complete the Solver Parameters dialog box
Click on OK
NOTE: The Subject to the Constraints: box must
contain the constraints on the variable values (x
98,000)
Denise Sakai Troxell (2000)
Using Solver
set the Options
Click on Options
Denise Sakai Troxell (2000)
Using Solver
set the Options
Check the box
Assume NonNegative
NOTE: The formula (in the Target Cell B2) is
non-linear on the non-negative variable (x
in A2)
Denise Sakai Troxell (2000)
Using Solver
set the Options
Accept the
remaining default
options by clicking
on OK
NOTE: The formula (in the Target Cell B2) is
non-linear on the non-negative variable (x
in A2)
Denise Sakai Troxell (2000)
Using Solver
execute Solver
Click on Solve
Denise Sakai Troxell (2000)
Using Solver
read solution
A lot size of
14,000 units
minimizes the balance
between production
and inventory carrying
costs at $497,000.00.
NOTE: Solver uses a method
known as GENERALIZED REDUCED
GRADIENT
Denise Sakai Troxell (2000)
Using Solver
end execution
Click on OK
Denise Sakai Troxell (2000)
Using Solver
end execution
Denise Sakai Troxell (2000)
Final Comments
Solver might not find the solution
Try to enter different initial lot sizes (in cell A2), for
example, 1, 49000, or 98000 tand
execute Solver.
o
w
Ho dle
han
ms
e
l
b
pr o i th
w er?
lv
So
Click on the
button if you want
some tips now
Denise Sakai Troxell (2000)
