Using Solver for Non-Linear Programming

Document Sample
Using Solver for Non-Linear Programming Powered By Docstoc
					Using Solver for Non-
Linear Programming
(NLP)
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.
  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
  Select Tools..Add-ins
   and make sure the
   Solver Add-in is
   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,
   click the Premium button
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
   get the same answer.
1. Create spreadsheet
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”
               Using Premium
                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”
               In spreadsheet, you
                have tried multiple
                starting points
NLP with Solver
               Answer is 100, as
                shown in your text.

				
DOCUMENT INFO
Shared By:
Categories:
Tags:
Stats:
views:13
posted:9/9/2011
language:English
pages:20