Class 8 Linear Optimization LP models: Sensitivity analysis
For the first 17 pages, see the notes from Class 7. They were included there for anyone who wanted to attack all of PS 4 before Friday.
1
Class 8: Sensitivity analysis and more complex problems
Sensitivity report
M icro so ft E xcel 10.0 S en sitiv ity R ep o rt W o rksh eet: [D iam o n d _T iren ew .xls]F o rm u latio n R ep o rt C reated : 9/12/2005 1:44:05 P M A djustable C ells C ell $G $8 $G $9 N am e R egular (000's/w k ) P rem ium (000's/w k ) F in al V alu e 2 9 R ed u ced C o st 0 0 O b jectiv e C o efficien t 8 10 Allo w ab le In crease 37 0.6667 Allo w ab le D ecrease 0.5 8.2222
Be sure that you have enough accuracy. Sometimes a zero is actually .001
C onstraints C ell $C $22 $C $23 $C $19 $C $15 $C $16 N am e R egular N onnegativity P rem ium N onnegativity P rem ium P roduced for C ontract M olding H ours U sed F inishing H ours U sed F in al V alu e 2 9 9 36 42 S h ad o w P rice 0 0 0 0.0667 2.4667 C o n strain t R .H . S id e 0 0 3 36 42 Allo w ab le In crease 2 9 6 60 30 Allo w ab le D ecrease 1E +30 1E +30 1E +30 15 20
2
Class 8: Sensitivity analysis and more complex problems
If you don’t click “assume linear model”, the sensitivity analysis will give you the shadow price just at that point… Called the Lagrange multiplier.
Microsoft Excel 11.0 Sensitivity Report Worksheet: [Diamond_Tirepost.xls]Formulation Report Created: 9/26/2007 4:57:31 PM
Adjustable Cells Cell Name $G$8 Regular (000's/wk) $G$9 Premium(000's/wk) Constraints Cell $C$22 $C$23 $C$19 $C$15 $C$16 Name Regular Nonnegativity Premium Nonnegativity Premium Produced for Contract Molding Hours Used Finishing Hours Used Final Lagrange Value Multiplier 2 0 9 0 9 0 36 0.066666667 42 2.466666667 Final Value 2 9 Reduced Gradient 0 0
3
Class 8: Sensitivity analysis and more complex problems
Note that in most non-linear objective functions with constraints, the change in the objective as you change a parameter changes continuously.
As an example: Here, profit is a function of X but X is constrained to be less than (the capacity) 20. If when this constraint moves up to 21, profit increases by 3, then when it moves from 21 to 22, profit increases by less than 3 (e.g. maybe by 2.5).
profit
20
4
Class 8: Sensitivity analysis and more complex problems
X
Sensitivity analysis: The top table.
5
Class 8: Sensitivity analysis and more complex problems
Sensitivity report with explicit nonnegativity constraints in spreadsheet
A d ju s ta b le C e lls C e ll $G $8 $G $9 N am e R e g u la r (0 0 0 's /w k ) P re m iu m (0 0 0 's /w k ) F in a l V a lu e 2 9 R educed C ost 0 0 O b je c tiv e C o e ffic ie n t 8 10 A llo w a b le In c re a s e 37 0 .6 6 6 7 A llo w a b le D e c re a s e 0 .5 8 .2 2 2 2
• The first two columns are related. • The last three columns are related. • But the second column doesn‟t relate to the last three columns. • Don‟t try to connect them in your minds! • Put a line between them.
6
Class 8: Sensitivity analysis and more complex problems
Sensitivity Analysis: Adjustable Cells (see top table)
Last 3 columns
The third column is the coefficient on each choice variable in the objective function. What if….. The profit margin for a Regular tire increases from 8 to 9? The last two columns, the allowable increases and decreases, refer to the amounts that the “objective coefficient” can increase or decrease ….. and still have the optimal choices (of the choice variables) stay the same. The reading calls it the “range of optimality”
7
Class 8: Sensitivity analysis and more complex problems
Example of Allowable Increase/Decrease of Adjustable Cells
The coefficient on Regular is now 8, with an allowable increase of 37 and an allowable decrease of 0.5. If the coefficient on Regular is 8 increased to 9, you‟d still make R=2 P=9. Your profits would increase, however, by $1 („000) per Regular Tire, or by $1 x 2 = $1 („000). If the coefficient on Regular changed to anything between 7.5 (8 -.5) and 45 (8+37), it would still be best to make 2 Regular and 9 Premium. If the coefficient on Regular changed beyond this range, you‟d you‟d no longer want to make R=2 P=9. You‟d have to redo Solver (or find new solution in some other way.)
8
Class 8: Sensitivity analysis and more complex problems
More Questions on Diamond Tire
How much can the profit per regular tire increase (decrease) before the optimal product mix changes? For an additional fixed administrative cost of $25,000 per week, Ed Shea can use a different vendor and lower costs per premium tire by $1. Should he do it? What would happen to profits, and how much R and P should they then make? Can you tell from this output? In the long run, if it costs $2(000) more to add a weekly hour to either or both departments, should you? What if there were a worldwide rubber shortage that limited Diamond Tire to a total of 10 („000) tires? What would happen to profits, and how much R and P should they make? Can you tell from this output?
9
Class 8: Sensitivity analysis and more complex problems
One more term…. Reduced cost
Optional!!
10
Class 8: Sensitivity analysis and more complex problems
A helpful definition
A simple constraint A simple constraint is a constraint that limits a single choice variable to be: greater than or equal to some number OR less than or equal to some number Examples: P >= 3 R >= 0 X <= 8
11
Class 8: Sensitivity analysis and more complex problems
Reduced cost
The reduced cost of a choice variable..
….is just the shadow price of some simple constraint about that choice variable but it doesn‟t not tell you exactly which simple constraint but without the allowable increase/decrease of the shadow price
You never need to use the reduced cost if you have made the spreadsheet correctly so that all constraints are listed in the bottom panel.
Class 8: Sensitivity analysis and more complex problems
12
Review of concepts in Answer and Sensitivity Reports
A binding constraint prevents you from further increasing your objective (so the constraint hold with equality) Slack is the extra, left over, unused amount -- when a constraint is not binding. The shadow price is the change in the objective function from increasing the right hand side of the constraint by +1 The allowable increase/decrease of the constraint (bottom table of sensitivity report) is the range that the right side of the constraint can change without changing the SHADOW PRICE. Outside this range, a different set of constraints are binding (To see this, think of moving the constraint line on the graph!) The objective coefficient is the number before the choice variable in the objective function. The allowable increase/decrease of the objective coefficient (top table of sensitivity report) is the range that the objective coefficient on that choice variable can change without changing the best choices.
13
Class 8: Sensitivity analysis and more complex problems
More Examples
14
Class 8: Sensitivity analysis and more complex problems
Steps to approach optimization problems
1. What is the name/idea of my objective? 2. What are the choice variables? 3. What is single equation for the objective? The equation should have the choice variables and parameters only? 4. What is the name/idea of each constraint? 5. What are the equations for each constraint? These should only have choice variables and parameters in them. (Be sure the right hand side and left hand side of each constraint are in the same units.)
15
Class 8: Sensitivity analysis and more complex problems
Samuelson/Marks S1
S1. An electronics firm has production plants in Oregon and Tennessee. It ships its products overseas from three ports: Los Angeles, New Orleans, and New York. Transportation costs between plants and seaports are as follows: Los Angeles $14 $24 New Orleans $26 $10 New York $30 $12
Oregon Tennessee
The maximum capacity of the Oregon plant is 9,000 tons; the capacity of the Tennessee plant is 10,000 tons. The minimum daily quantities shipped overseas from Los Angeles, New Orleans, and New York are 5,000, 7,000, and 6,000 tons, respectively.
16
Class 8: Sensitivity analysis and more complex problems
Formulate this model, find graphic solution (Samuelson/Marks Q5)
An athlete carefully watches her intake of calcium, protein and calories. Her breakfast consists of milk and cereal: Milk (1 oz) Cereal (1 oz)
Calcium Protein Calories Price
2 2 6 $.10
2 6 2 $.15
She seeks a diet that supplies at least 50 units of calcium, 90 units of protein, and 66 calories at minimum cost.
17
Class 8: Sensitivity analysis and more complex problems
Answers are on the next pages
Do NOT look at them until you‟ve done the problem yourself!
18
Class 8: Sensitivity analysis and more complex problems
Oregon Plant
Cost = $30
Cost = $14 Cost = $26
Cost = $12
NY
LA
Cost =$24
Tenn. Plant
Cost = $10 NO
19
Class 8: Sensitivity analysis and more complex problems
Formulation: Transport problem
Choose ORLA TNLA ORNO TNNO ORNY TNNY to minimize Costs= 14 ORLA + 24 TNLA + 26 ORNO +10 TNNO + 30 ORNY + 12 TNNY subject to: (capacity) ORLA+ORNO+ORNY<= 9 Oregon TNLA+TNNO+TNNY<= 10 Tennessee (demand) ORLA+TNLA>=5 ORNO+TNNO>=7 ORNY+TNNY>=6 (non-neg) ORLA, TNLA, ORNO,TNNO,ORNY, TNNY >=0 where ORLA are 1000 tons shipped from Oregon to LA etc. and Costs are in $1000.
20
Class 8: Sensitivity analysis and more complex problems
A 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 O B J E C T IV E C o s ts C H O IC E V A R IA B L E S O re g o n -L A O re g o n -N O O re g o n -N Y T e n n e s s e e -L A T e n n e s s e e -N O T e n n e s s e e -N Y C O N S T R A IN T S
B = S U M (E 5 :E 1 0 ) Q u a n tity 5 3 0 0 4 6
C
D
E
U n it C o s t 14 26 30 24 10 12
T o ta l C o s t = B 5 *D 5 = B 6 *D 6 = B 7 *D 7 = B 8 *D 8 = B 9 *D 9 = B 1 0 *D 1 0
U sed O re g o n c a p a c ity = S U M (B 5 :B 7 ) T e n n e s s e e c a p a c ity = S U M (B 8 :B 1 0 ) M in im u m s h ip p in g re q u ire m e n ts D e liv e re d LA =B5+B8 NO =B6+B9 NY =B7+B10 N o n n e g a tivity O R LA =B5 ORNO =B6 ORNY =B7 T N LA =B8 TNNO =B9 TNNY =B10
<= <=
C a p a c ity 9 10 Needs 5 7 6 0 0 0 0 0 0
>= >= >= >= >= >= >= >= >=
21
Class 8: Sensitivity analysis and more complex problems
Answers
An athlete carefully watches her intake of calcium, protein and calories. Her breakfast consists of milk and cereal: Milk (1 oz) Cereal (1 oz)
Calcium Protein Calories Price
2 2 6 $.10
2 6 2 $.15
She seeks a diet that supplies at least 50 units of calcium, 90 units of protein, and 66 calories at minimum cost.
Choose M, C to Minimize 10 M + 15 C Subject to 2 M + 2 C >= 50 2 M + 6 C >= 90 6 M + 2 C >=66 M >= 0 C >= 0
22
where M=oz of milk, C of cereal
Calcium min Protein min Calories minimum non-negativity
Class 8: Sensitivity analysis and more complex problems
45
Protein
Milk
25
Calcium
11
Calories
15
23
25
33
Cereal
Class 8: Sensitivity analysis and more complex problems
45
Protein
Milk
25
Calcium
11
Calories
Min. profit using objective line 15 (optional)
24
25
33
Cereal
Class 8: Sensitivity analysis and more complex problems