Using Solver for Non-Linear Programming (NLP)

1
Using Solver for Non-Linear Programming (NLP)
2
NLP with Solver

• Requires Microsoft Excel
• Requires Premium Solver, which is located on your
  student disk.
• Requires a spreadsheet model that needs to be
  optimized.
• Well use our EOQ model as an example.

3
The Model
(Note the nonlinear objective!)
D Annual demand C Box purchase costs S
Order costs I Inventory carrying costs Q
Quantity ordered
4
The Model
Make sure cell formulas are correct.
D Annual demand C Box purchase costs S
Order costs I Inventory carrying costs Q
Quantity ordered
5
Set solver parameters

• Recall

Green cells are the unknowns
The blue cell contains the objective function
Red cells contain the constraints
6
Set solver parameters
Green cells are the unknowns. Delete the formula.
The blue cell contains the objective function
Red cells contain the constraints, but the only
constraint is non-negativity, which is handled in
the Solver dialogue
7
NLP with Solver

• Select Tools..Add-ins and make sure the Solver
  Add-in is checked. (Click on thecheck box if
  it isnt.)
• Click OK
• If the Solver Add-In is not showing at all, plan
  on working in the lab Zone 1.

8
NLP with Solver

• Select the Tools..Solver menu item
• If the Standard Solver window appears, click the
  Premium button

9
NLP with Solver

• In the Premium Solver window, set the solution
  method to Standard GRG Non-linear

10
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.
11
NLP with Solver
Objective function
Select Max or Min
Unknown
12
NLP with Solver
13
NLP with Solver

• Try Waner 13.2 example 1 pg 783
• Focus on using Premium Solver NLP to get the same
  answer.

14
Create spreadsheet
15
2. Enter objective function formula
16
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.

17
4. Check for Solver gotchas

• Using Premium solver
• If needed, Min selected
• Standard GRG Nonlinear selected

18
4. Check for Solver gotchas

• Under options, assume non-negative selected

19
4. Check for Solver gotchas

• In spreadsheet, you have tried multiple starting
  points

20
NLP with Solver

• Answer is 100, as shown in your text.