using solver to solve a minimization lp + interpretation of output bsad 30 dave novak source:...
TRANSCRIPT
Using Solver to solve a minimization LP+ interpretation of output
BSAD 30
Dave Novak
Source: Anderson et al., 2013 Quantitative Methods for Business 12th edition – some slides are directly from J. Loucks © 2013 Cengage Learning
Overview
Focus on section 7.5 Solving the minimization LP using MS
Solver and interpreting output Surplus variables Standard formulation of LP
Setting up the problem
Go to Excel choose the “Data” tab and then click on Solver
The Solver Screen pops up and we will need to reference the cells for our problem:Set Objective refers to the OF in B4Click the Min Radio button as this is a
minimization problem
Setting up the problem
By Changing Variable Cells refers to our two variables (cells B1 and B2)
Subject to the Constraints: refers to our constraints for the problem. We will add each one separately. Select “Add” in the Add Constraint Menu choose cell B8 for the left-hand-side “Cell Reference” for constraint #1 and choose cell C8 for the right-hand-side “Cell Reference” for constraint #1
Setting up the problem
Make sure the Make Unconstrained Variables non-negative check box is checked (this ensures that X1 and X2 are non-negative). We cannot produce a negative number of either decision variable
And using the Pull-down menu make sure that Select Solving Method is Simplex LP
Select SOLVE
Setting up the problem
The cells in your spreadsheet model should change and cell B1 should now = 3.2, cell B2 should now = 0.8, and cell B4 should = 17.6
Using the Solver Results Menu that pops up on your screen highlight both “Answer” and “Sensitivity” on the right-hand-side under Reports and click OK
Minimization example from last class We solved this graphically
ObjectiveFunction
“Regular”Constraints
Non-negativity Constraints
Min 5x1 + 2x2
s.t. #1) 2x1 + 5x2 > 10
#2) 4x1 - x2 > 12
#3) x1 + x2 > 4
x1, x2 > 0
Minimization problem
#2) 4x1 - x2 > 12#2) 4x1 - x2 > 12
#1) 2x1 + 5x2 > 10#1) 2x1 + 5x2 > 10
x1x1
Feasible Region6
5
4
3
2
1
#3) x1 + x2 > 4#3) x1 + x2 > 4
1 2 3 4 5 6
x2x2
Objective functionObjective function
Setting up the problem
Download, save, and then open the Excel template file from the URL on the course schedule
X1 (# of units of product 1)X2 (# of units of product 2)
Objective Function (Maximize Profit) 0
Constraints LHS RHSST:1) Constraint#1 0 102) Constraint #2 0 123) Constraint #3 0 4
Answer reportObjective Cell (Min)
Cell Name Original Value Final Value$B$4 Objective Function (Maximize Profit) 0 17.6
Variable CellsCell Name Original Value Final Value Integer
$B$1 X1 (# of units of product 1) 0 3.2 Contin$B$2 X2 (# of units of product 2) 0 0.8 Contin
ConstraintsCell Name Cell Value Formula Status Slack
$B$10 3) Constraint #3 LHS 4 $B$10>=$C$10 Binding 0$B$8 1) Constraint#1 LHS 10.4 $B$8>=$C$8 Not Binding 0.4$B$9 2) Constraint #2 LHS 12 $B$9>=$C$9 Binding 0
(16/5, 4/5)
Answer Report
Excel Solver uses the term “slack” when referring to any non-binding ≥ or ≤ constraintsNon-binding constraints do not restrict the
feasible region at the optimal solution point
Slack and surplus variables
Recall that slack and surplus variables are added to the standard form LP whenever there is an inequality
Less-than or equal to constraints ≤ require a SLACK variable that is added to the LHS of the constraint so that we can set the LHS = the RHS
Slack and surplus variables
Greater-than or equal to constraints ≥ require a SURPLUS variable that is subtracted from the LHS of the constraint so that we can set the LHS = the RHS
Equality constraints do not require either a slack or surplus variable in standard form
Slack and surplus variables
Surplus variables describe EXCESS quantity or the amount over the RHS of the constraint that is being used (associated with ≥)It is feasible to use less
Slack variables describe IDLE resources or the amount of the RHS of the constraint that is not being used (associated with ≤)It is feasible to use more
Answer Report
Constraint #1 is a non-binding constraint and has “surplus” = 0.4 (although Excel uses the term slack for both ≥ and ≤)
Constraint #1 does not directly affect the optimal solution – it is a redundant, non-binding constraint
Because this is a minimization problem, and the constraints are ≥, this lets us know that at the optimal point (3.2, 0.8) we exceed the RHS of constraint #1 by 0.4 units
Magnified view of extreme points
#2) 4x1 - x2 > 12#2) 4x1 - x2 > 12
#1) 2x1 + 5x2 > 10#1) 2x1 + 5x2 > 10
#3) x1 + x2 > 4#3) x1 + x2 > 4
Optimal A (3.2, 0.8)This point is restricted or bound by constraints #2 and #3, but NOT by constraint #1
B (3.33, 0.67)
Surplus in non-binding constraint #1at optimal point
Answer Report
Binding constraints let us know that all the resources associated with those specific constraints are used (there is no excess and there is no shortage)
The constraint “binds” the problem, and all resources are used – there is no slack or surplus
Standard form of the LP
Standard form of the LP involves adding slack or surplus variables to the mathematical model so the problem can be solved using strict equalities For each ≥ constraint, there should be a
“surplus” variable (denoted by Si)
Surplus variables have a coefficient of 0 in the OF
Surplus variables have a coefficient of 1 in the constraints
Slack or surplus? (I just randomly
created this, so don’t solve it)
ObjectiveFunction
“Regular”Constraints
Non-negativity Constraints
Min 10x1 + 25x2
s.t. #1) 2x1 + 5x2 > 15
#2) 4x1 - x2 > 20
#3) x1 + x2 ≤ 7
#4) 2x1 = 15
x1, x2 > 0
Standard form of the LP
You could think of slack/surplus variables as “placeholder” variables that hold the numeric difference between the LHS and RHS of any inequality
In an equality constraint, the RHS MUST EQUAL the LHS, so there are no slack/surplus variables