# Using Solver for Non-Linear Programming

Document Sample

```					Using Solver for Non-
Linear Programming
(NLP)
NLP with Solver

 Requires Microsoft Excel
 Requires Premium Solver, which is
 Requires a spreadsheet model that
needs to be optimized.
 We’ll use our EOQ model as an example.
The Model
D Q
MIN: DC  S  Ci
Q   2
Subject to: Q  1
(Note the nonlinear objective!)
D = Annual demand
C = Box purchase costs
S = Order costs
I = Inventory carrying costs
Q = Quantity ordered
The Model
Make sure cell formulas are correct.

D Q
MIN: DC  S  Ci
Q   2

D = Annual demand
C = Box purchase costs
S = Order costs
I = Inventory carrying costs
Q = Quantity ordered
Set solver parameters
 Recall

Green
cells are                 The blue
the                       cell
unknowns                  contains
the
objective
function
Red cells
contain the
constraints
Set solver parameters

Green cells
are the                   The blue
unknowns.                 cell
Delete the                contains
formula.                  the
objective
Red cells contain
function
the constraints, but
the only constraint is
non-negativity,
which is handled in
the Solver dialogue
NLP with Solver
and make sure the
checked. (Click on the
check box if it isn’t.)
 Click OK
 If the Solver Add-In is
not showing at all, plan
on working in the lab
Zone 1.
NLP with Solver

 Select the Tools..Solver menu item
 If the Standard Solver window appears,
NLP with Solver
 In the Premium Solver window, set
the solution method to Standard
GRG Non-linear
NLP with Solver
I. Click the options button

II. When the Solver Options window appears, you can include a non-
negativity constraint by checking Assume Non-Negative

III. Click OK.
NLP with Solver
Objective function
Select Max or Min
Unknown
NLP with Solver
NLP with Solver

 Try Waner 13.2 example 1 pg 783
 Focus on using Premium Solver NLP to
2. Enter objective function
formula
3. Set up Solver

 Set cell points to objective
function cell
 By changing variable points
to unknown cell
 Constraints have “used”
value to left of comparison
and “Available” value to right.
4. Check for Solver
“gotchas”
solver
 If needed, Min
selected
 Standard GRG
Nonlinear selected
4. Check for Solver
“gotchas”
 Under options,
assume non-
negative selected
4. Check for Solver
“gotchas”
have tried multiple
starting points
NLP with Solver