Chapter 4
Linear Optimization:
Sensitivity Analysis
Part 2Chapter 4
Linear Optimization:
Sensitivity Analysis
Part 2DECISION MODELING
DECISION MODELING
WITH
WITH
MICROSOFT EXCEL
MICROSOFT EXCEL
Copyright 2001 Prentice Hall Publishers andSolverTable.xla
SolverTable.xla is a DataTable-like macro to re-optimize and tabulate an LP model after each change in its parameters.
Similar to Excel’s DataTable, SolverTable knows how to re-Solve the LP model for each change before tabulating any results.
SolverTable can also tabulate the information in the Solver Sensitivity Report.
Sensitivity
Analysis
Sensitivity
Analysis
Using SolverTable Using SolverTableSensitivity
Analysis with SolverTable
Sensitivity
Analysis with
SolverTable To begin using SolverTable, open the SimpleOakProd.xls
workbook.
Open the add-in file SolverTable.xla. Click
on the resulting Enable Macros button.
SolverTable will install itself and be available as a
To illustrate SolverTable, start with the Simplified Oak Products model.
RHS Ranging with SolverTable
First a range of RHS parameter values for the
constraint are entered as data in a column (or a row).
Now, highlight the table by click-dragging and
In the resulting dialog, specify the cell location of the Long Dowel constraint’s RHS in the Input Column Cell edit field.
Click OK to run Solver on the model for each Long Dowels constraint RHS value (in this case for 11 optimizations).
SolverTable tabulates the requested model results referenced in the table’s columns.
Here are the corresponding GLP pictures of the Oak Products model for the Long Dowels Starting
Inventory amounts (L).
L = 1350
Sweeping the values of L from 400 to 1350 causes the feasible region to expand until the Long
SolverTable can mimic DataTable 2 to tabulate
simultaneous variations in two parameters, with the restriction that only one output cell can be tabulated.
To illustrate, a range of parameter values for the inventory constraint RHS values for both Long and Short Dowels will be analyzed.
Using the Oak Products model, start by setting up the table, in this case with a range of parameter values for both parameters.
Now, click on Tools – SolverTable and in the resulting dialog, specify cell $F$7 as the Input Row Cell and
$F$6 as the Input Column Cell.
SolverTable will run Solver on the model for each paired combination of Long and Short Dowels
constraint RHS values (108 optimizations in this case), and for each run, tabulate the single Profit result referenced in the table’s upper left corner.
Sensitivity
Analysis
Sensitivity
Analysis
Objective Function Objective Function Coefficient Ranging Coefficient Ranging with SolverTable with SolverTableSimilar to ranging an RHS, first set up a table with values of the objective
As before, click on Tools – SolverTable and in the resulting dialog, specify cell $B$3 as the Input Column Cell.
Here are the results of the SolverTable analysis. Notice that the objective function coefficients for profit per Captain chair are the coefficient values at which the LP solution changes (as shown by the
Here are the corresponding GLP pictures of the Oak Products model for the Captain objective function coefficient values (V). Note how the corner point solution changes abruptly for critical values of V.
V = 99999
Sweeping the values of V from 0 to 99999 causes the objective function to rotate from horizontal to nearly vertical in slope.
Sensitivity
Analysis
Sensitivity
Analysis
Technical Coefficient Technical CoefficientRanging with SolverTable
Ranging with SolverTable
SolverTable can be used to investigate alternative production technologies. Suppose Oak Products were to consider the
option of strengthening or slightly weakening a Mate chair by increasing or decreasing the
number of long dowels it uses.
Let’s examine the economic effects of reducing the number of long dowels per Mate chair from
Here is the resulting solution:
As before, first set up a table in Excel and run
SolverTable. In the SolverTable dialog, specify $C$6 (no. of Mates in the Long Dowel constraint) as the Input Column Cell.
Increasing the number of long dowels per Mate chair from 4 to 6 reduces the optimal number of Mates to produce (with an associated increase in Captains), with a net reduction in Profit.
Reducing the number of long dowels per Mate chair from 4 to 2 also reduces the optimal number of Mates
Sensitivity
Analysis
Sensitivity
Analysis
Eastern Steel Example
Eastern Steel Example
Ore from four different locations is blended to make a steel alloy.
Each ore contains three essential elements (A, B, and C) that must appear in the final blend at minimum threshold levels.
Find the cost-minimizing blend by solving the
following LP model (Ti = fraction of a ton of ore
Min 800T1 + 400T2 + 600T3 + 500T4 s.t. 10T1 + 3T2 + 8T3 + 2T4 > 5 (requirement on A) 90T1 + 150T2 + 75T3 + 175T4 > 100 (requirement on B) 45T1 + 25T2 + 20T3 + 37T4 > 30 (requirement on C) T + T + T + T = 1 (blend condition)
Eastern Steel Symbolic Model
Here is the Excel spreadsheet:
The Reduced Cost of any particular decision variable is defined to be the amount by which the coefficient of that variable in the objective function would have to change in order to have a positive optimal value for that variable.
The Reduced Cost of a decision variable (whose
optimal value is currently zero) is the rate (per unit amount) at which the objective value is hurt as that variable is “forced into” a previously optimal solution.
Sensitivity
Analysis
Sensitivity
Analysis
Sensitivity Report Sensitivity Report Interpretation for Interpretation for Alternative LP Models Alternative LP ModelsIn this example, the Friendly Loan
Company has an annual $15 million loan budget. Profit is generated by the
annual interest income from three types of loans:
Real Estate (First Mortgage; 7%)
Furniture Loans (12%) Signature Loans (15%)
In addition, Friendly requires at least 60% First Mortgage loans and no more than 10% Signature loans.
Here is the spreadsheet model:
Note how compact the model is. The constraints are immediately adjacent to the quantities they
affect and are custom formatted to include the
Using Solver, specify the parameters and solve the model.
The resulting Solver analysis shows that all $15 million will be loaned out ($9 million into First Mortgage Loans, $1.5 million into Signature loans, and $4.5 million into Furniture loans).
The annual Total interest income will be
$1,395,000 with an average return of 9.3%. All three constraints are binding.
Here is the Sensitivity Report for the model:
The Shadow Price of .12 indicates that a 12% return can be achieved on any budget increase.
Verify Solver’s Sensitivity Report by typing a new budget limit into the spreadsheet and Solving.
Notice that the Avg. Return for this model is still 9.3%. This indicates that the marginal return for the extra $5 million is actually 9.3% and not 12% as indicated by the previous Sensitivity Analysis.
The Sensitivity Analysis for this model shows a shadow price of .12 (12%), the same as the
previous model.
And the resulting Sensitivity Analysis from Solver:
Note the presence of 3 constraints and the correct Shadow Price of 9.3%.
Now that we have looked at both spreadsheet models (the compact model vs. the recommended LP
model), it would seem that they give different results.
However, both models are completely correct, and neither Sensitivity Report contains any
errors.
To understand the differences, look at simple upper and lower bounds.
Sensitivity
Analysis
Sensitivity
Analysis
Simple Upper and
Simple Upper and
Lower Bounds
The time and memory requirements for Solver to optimize a model are determined primarily by the size of the coefficient matrix of cells making up the LHS of the set of constraints.
The size of the constraint coefficient matrix is proportional to the product of the number of decision variables and constraints. This size effects speed of optimization.
In addition to nonnegativity constraints,
Solver allows any upper or lower constraint
bounds directly on the decision variables to be honored without actually considering them as constraints.
However, the only sensitivity information available for any simple upper and lower bound constraints are their shadow prices. Solver places any non-zero shadow price on an upper or lower bound constraint into the Reduced Cost column next to the relevant decision variable.
The Reduced Cost numbers for Solver LP models containing simple upper and lower bounds are the shadow prices for whichever bound, if any, is binding on that decision
The table below gives values the Reduced Cost shadow price entry may have in Solver models containing simple upper and lower bounds.
Value of Decision
Variable at Optimality Reduced Cost Entry,Maximization Model Reduced Cost Entry,Minimization Model Lower Bound (>) Binding Zero or Negative Shadow Zero or Positive Shadow
Price Price
Upper Bound (<) Binding Zero or Positive Shadow Zero or Negative Shadow
Price Price
Solver invokes its special bounding procedure whenever it sees “Changing Cells” cell
references in the Subject to the Constraints:
box of the Solver Parameters dialog.
Solver will not evoke this procedure if the upper or lower bound on any decision
variable is specified indirectly on the worksheet.
This “indirect reference” can be achieved by the use of some intervening formula, such as the SUMPRODUCT formula.
Although the shadow price given in the two different models was correct, the interpretation of that price was incorrect.
Sensitivity
Analysis
Sensitivity
Analysis
Shadow Price Shadow Price Interpretation InterpretationRemember, a shadow price is the change in the LP’s OV per unit of change in a given constraint’s
RHS value holding all other data, including the
So, for example, the correct interpretation of the shadow price of .120 should be :
Holding the Loan Limit RHS’s for
Signature and First Mortgage loans at their original dollar amount bounds
of $1500 and $9000, respectively, the improvement in the objective
function value is .12 for each additional budget dollar.
The use of simple upper and lower bounds and the use of formulas on RHS’s of LP formulations can lead to more compact and managerially