Download - SDA 3E Chapter 14
-
8/4/2019 SDA 3E Chapter 14
1/29
2007 Pearson Education
Chapter 14: Solving andAnalyzing Optimization Models
-
8/4/2019 SDA 3E Chapter 14
2/29
Excel Solver Standard Solver (packaged with Excel)
Premium Solver (available on CD-ROM)
Can toggle between both versions
-
8/4/2019 SDA 3E Chapter 14
3/29
Premium Solver Solution procedures
Standard GRG Nonlinear used for solving nonlinear optimizationproblems
Standard Simplex LP used for solving linear and linear integeroptimization problems
Standard Evolutionary used for solving complex nonlinear andnonlinear integer problems
-
8/4/2019 SDA 3E Chapter 14
4/29
Differences in Options Dialog
Standard Premium
-
8/4/2019 SDA 3E Chapter 14
5/29
Click hereto addconstraints
Set cell references forobjective function and
decision variables
Product
Mix Model
ClickSolve
-
8/4/2019 SDA 3E Chapter 14
6/29
Add Constraint Dialog
-
8/4/2019 SDA 3E Chapter 14
7/29
Solver Results Dialog
Select reports to save
-
8/4/2019 SDA 3E Chapter 14
8/29
Possible Outcomes Unique optimal solution
Alternate optimal solution
Unbounded problem
Infeasible problem
-
8/4/2019 SDA 3E Chapter 14
9/29
Product Mix Model Solution
-
8/4/2019 SDA 3E Chapter 14
10/29
Slack and Binding Constraints Slackis the difference between the left- and right-
hand sides of a constraint when the optimal solution issubstituted for the variables.
A constraint is binding if the slack is zero. Example:
Amount of Component Used Amount Available Amount of Component Used + Amount of
Component Unused = Amount Available Slack = Amount of Component Unused
= Amount Available - Amount of Component Used
-
8/4/2019 SDA 3E Chapter 14
11/29
Answer Report
-
8/4/2019 SDA 3E Chapter 14
12/29
Sensitivity Report
-
8/4/2019 SDA 3E Chapter 14
13/29
Interpreting the Sensitivity
Report Reduced cost how much the objective
coefficient needs to change for a variable tobecome positive in the optimal solution
Allowable Increase/Decrease how much anindividual objective coefficient can changebefore the optimal values of the decision
variables will change Shadow price how much the value of theobjective function will change as the right-hand-side of a constraint is increased by 1.
-
8/4/2019 SDA 3E Chapter 14
14/29
Solver Limits Report Shows the lower limit and upper limit that each variable
can assume while satisfying all constraints and holding allof the other variables constant.
-
8/4/2019 SDA 3E Chapter 14
15/29
How Solver Creates Names in
Reports How you design your spreadsheet model will affect on
how Solver creates the names used in the outputreports.
Poor spreadsheet design can make it difficult orconfusing to interpret the Answer and Sensitivityreports.
Solver assigns names to target cells, changing cells,and constraint function cells by concatenating the textin the first cell containing text to the left of the cellwith the first cell containing text above it.
-
8/4/2019 SDA 3E Chapter 14
16/29
Example
-
8/4/2019 SDA 3E Chapter 14
17/29
Models With Lower and Upper
Bounds Bounded variables are listed in theAdjustable Cells section.
Reduced costs may be interpreted asshadow prices.
-
8/4/2019 SDA 3E Chapter 14
18/29
ExampleAdd constraint: G 400
-
8/4/2019 SDA 3E Chapter 14
19/29
Modeling Trick Using reduced costs as shadow
prices can be a bit confusing.
In your spreadsheet model,define a new set of cells for anydecision variables that haveupper or lower bound constraintsby referencing (not copying) theoriginal changing cells.
In the Solver model, use thisauxiliary variable cell to definethe bound constraint; that is,B18 B8.
-
8/4/2019 SDA 3E Chapter 14
20/29
Solver Results Using Auxiliary
Variable
-
8/4/2019 SDA 3E Chapter 14
21/29
Difficulties With Solver A poorly scaled modelone in which the parameters of the
objective and constraint functions differ by several orders ofmagnitude may cause round-off errors in internalcomputations or error messages such as The conditions for
Assume Linear Model are not satisfied. The values of thecoefficients in the objective function and constraints, as well asthe right hand sides, should not differ from each other by afactor of more than 1,000 or 10,000.
Remedies
Keep the solution that Solverfound and run Solveragainstarting from that solution. Solver also has a checkbox for Use Automatic Scaling that
can be used, especially if solver gives an error message thatlinearity is not satisfied.
-
8/4/2019 SDA 3E Chapter 14
22/29
Solving Integer Models Define decision variables as either int or bin in
the Add Constraint dialog
Set tolerance toZero in Integer
Options
-
8/4/2019 SDA 3E Chapter 14
23/29
Solving Nonlinear Models Select Standard GRG Nonlinear in Premium
Solver as the solution procedure
Sensitivity report is different for nonlinearmodels Reduced gradient is analogous to reduced
cost, but more difficult to interpret
Lagrange multipliers are similar to shadowprices, but give only approximate rates ofchange
-
8/4/2019 SDA 3E Chapter 14
24/29
Hotel Pricing Example
Sensitivity Report
-
8/4/2019 SDA 3E Chapter 14
25/29
Metaheuristics Used for difficult nonlinear problems
Premium Solver Standard Evolutionary
solution procedure Evolutionary Solver produces an Answer
Report a Population Report, which providesstatistics on the solutions encountered during
the search process, showing the best value,mean, standard deviation, maximum, andminimum values for each variable andconstraint function (excluding upper andlower bounds).
-
8/4/2019 SDA 3E Chapter 14
26/29
Example Population Report
-
8/4/2019 SDA 3E Chapter 14
27/29
Risk Analysis and Optimization Crystal Ballmay be used to conduct
post-optimality risk analysis to
understand the impact of uncertainty ofoptimization model parameters.
-
8/4/2019 SDA 3E Chapter 14
28/29
OptQuest: Combining
Optimization and Simulation OptQuestsearches for optimal solutions
within Crystal Ballsimulation model
spreadsheets. OptQuestis also designed to find
solutions that satisfy a wide variety of
constraints or a set of goals that youmay define.
-
8/4/2019 SDA 3E Chapter 14
29/29
OptQuest Procedure1. Create a Crystal Ballmodel of the decision problem.
2. Define the decision variables within Crystal Ball.
3. Invoke OptQuestfrom the Crystal Balltoolbar or the
corresponding menu.4. Create a new optimization file.
5. Select decision variables.
6. Specify constraints.
7. Select the forecast.
8. Modify OptQuestoptions.9. Solve the optimization problem.
10. Save the optimization files.
11. Exit OptQuest.