Download - Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis
![Page 1: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/1.jpg)
Managerial Decision Modeling with Spreadsheets
Chapter 4
Linear Programming Sensitivity Analysis
![Page 2: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/2.jpg)
2
Learning Objectives• Understand, using graphs, impact of changes in
objective function coefficients, right-hand-side values, and constraint coefficients on optimal solution of a linear programming problem.
• Generate answer and sensitivity reports using Excel's Solver.
• Interpret all parameters of reports for maximization and minimization problems.
• Analyze impact of simultaneous changes in input data values using 100% rule.
• Analyze impact of addition of new variable using pricing-out strategy.
![Page 3: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/3.jpg)
3
4.1 Introduction
• Optimal solutions to LP problems have been examined under deterministic assumptions.
• Conditions in most real world situations are dynamic and changing.
• After an optimal solution to problem is found, input data values are varied to assess optimal solution sensitivity.
• This process is also referred to as sensitivity analysis or post-optimality analysis.
![Page 4: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/4.jpg)
4
4.2 Sensitivity Analysis Using Graphs High Note Sound Company • Manufactures quality CD players and stereo receivers.
• Each product requires skilled craftsmanship.
• LP problem formulation:
Objective: maximize profit = $50C + $120Rsubject to
2C + 4R 80 (Hours of electricians' time available)
3C + R 60 (Hours of audio technicians' time available)
C, R 0 (Non-negativity constraints)
Where:
C = number of CD players to make.
R = number of receivers to make.
![Page 5: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/5.jpg)
5
High Note Sound Company Problem Solution
![Page 6: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/6.jpg)
6
Changes in Objective Function Coefficient
Impact of price change of ReceiversIf unit profit per stereo receiver (R) increased from $120 to $150, is corner point a still the optimal solution? YES ! But Profit is $3,000 = 0 ($50) + 20 ($150)
![Page 7: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/7.jpg)
7
Changes in Objective Function Coefficient
Impact of price change of Receivers
If receiver’s profit coefficient changed from $120 to $80, slope of isoprofit line changes causing corner point (b) to become optimal. But Profit is $1,760 = 16 ($50) + 12 ($80).
![Page 8: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/8.jpg)
8
4.3 Sensitivity Analysis Using Solver
• In Answer Report• Final Values: objective function, decision
variables.• Binding and nonbinding constraints• Slack: Unused resource.
• In Sensitivity Report• Adjustable Cells:
– Objective Function Coefficients– Reduced Cost– Allowable Changes
• Constraints: – Shadow Price – Allowable Changes
![Page 9: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/9.jpg)
9
High Note Sound Company Answer Report
![Page 10: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/10.jpg)
10
High Note Sound Company Answer Report
• Resources available:
– 80 hours of electricians’ time.
– 60 hours of audio technicians’ time.
• Final Values in table reveal optimal solution requires:
– all 80 hours of electricians’ time.
– Only 20 hours of audio technicians’ time.
• Binding and Non-binding Constraints:
– Electricians’ time constraint is binding.
– Audio technicians’ time constraint is non-binding.
• 40 unused hours of audio technicians’ time are referred to as slack.
![Page 11: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/11.jpg)
11
Sensitivity Report
• Sensitivity report has two distinct components.
(1) Table titled Adjustable Cells
(2) Table titled Constraints.
• Tables permit one to answer several "what-if"
questions regarding problem solution.
• Consider a change to only a single input data value.
• Sensitivity information does not always apply to
simultaneous changes in several input data values.
![Page 12: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/12.jpg)
12
High Note Sound Company Sensitivity Report
![Page 13: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/13.jpg)
13
Changes in Constraint Right-hand-side (RHS)
• Primary information is provided by Shadow Price
• Shadow Price is change in optimal objective function value for one unit
increase in RHS.
• The shadow price is positive for binding constraints and is zero for nonbinding
constraints.
![Page 14: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/14.jpg)
14
Changes in Right-hand-side (RHS)
RHS of Binding Constraint -
• If RHS of non-redundant constraint changes, size
of feasible region changes.
– If size of region increases, optimal objective
function improves.
– If size of region decreases, optimal objective
function worsens.
• Relationship expressed as Shadow Price.
![Page 15: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/15.jpg)
15
Changes in Right-hand-side (RHS)
• In case of electrician hours, shadow price is $30.
• For each additional hour of electrician time that firm can increase profits by $30.
• The range of RHS for electrician time with a shadow price of $30 is (0, 240).
• How to calculate shadow price and range? Excel.
High Note Sound Company
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$D$8 Electricians' Time 80.00 30.00 80.00 160.00 80.00
$D$9 Audio Technicians' Time 20.00 0.00 60.00 1E+30 40.00
![Page 16: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/16.jpg)
16
Change in RHS of Nonbinding Constraint
• In case of audio technicians’ time, shadow price is zero.– Audio technicians’ time has 40 unused hours. – No interest in acquiring additional hours of resource. – Allowable increase for RHS value is infinity.
• Allowable decrease for RHS value is 40. – Once 40 hours is lost (current unused portion, or slack) of audio
technicians’ time, resource also becomes binding. – Any additional loss of time will clearly have adverse effect on
profit.
• The range of RHS for audio technicians’ time with a shadow price of $0 is (20, infinite).
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$D$8 Electricians' Time 80.00 30.00 80.00 160.00 80.00
$D$9 Audio Technicians' Time 20.00 0.00 60.00 1E+30 40.00
![Page 17: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/17.jpg)
17
Change in Objective Function Coefficient (OFC)
Adjustable Cells• Reduced Cost value - shows the difference between the
marginal contribution of a decision variable and the
marginal worth of the resources it uses.
– Objective Function Coefficients
– Shadow Prices and Resources Used
• Allowable Increase and Allowable Decrease – the limits
to which the objective function coefficient of a decision
variable can be changed without affecting the optimality
of the current solution.
![Page 18: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/18.jpg)
18
Change in Objective Function Coefficient (OFC)
High Note Sound Company
![Page 19: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/19.jpg)
19
Change in Objective Function Coefficient (OFC)
Reduced Cost for each CD player
• The marginal contribution is the objective coefficient
$50.
• The marginal worth of the resources used:
– Resources Used: 2 hours of electrician time and 3 hours of
audio technician’s time.
– Shadow Prices: $30 for per hour of electrician time and $0 for
per hour of audio technician time.
– Marginal Cost: 2 x $30 + 3 x $0 = $60.
• Reduced Cost: $60 - $50 = $10
• Current value is 0. If one makes 1, firm will lose $10.
![Page 20: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/20.jpg)
20
Change in Objective Function Coefficient (OFC)
Allowable Increase and Decrease for the coefficient
of CD players
• Allowable Increase - indicates if the price of CD
players increases by $10, one will profit by making
additional CDs.
• Allowable Decrease – infinity (1E+30) indicates if $50
is not attractive enough to make CD – any price below
it will not make it attractive either!
![Page 21: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/21.jpg)
21
Change in Objective Function Coefficient (OFC)
Reduced Cost for each Stereo Receiver
• The marginal contribution is the objective coefficient
$120.
• The marginal worth of the resources used:
– Resources Used: 4 hours of electrician time and 1 hours of
audio technician’s time.
– Shadow Prices: $30 for per hour of electrician time and $0 for
per hour of audio technician time.
– Marginal Cost: 4 x $30 + 0 x $0 = $120.
• Reduced Cost: $120 - $120 = $0.
![Page 22: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/22.jpg)
22
Change in Objective Function Coefficient (OFC)
Allowable Increase and Decrease for each Stereo
Receiver
• Allowable Increase - infinity (1E+30) indicates if $120
is profitable enough to make receiver – any price above
it will also be profitable.
• Allowable Decrease – $20 indicates if the price of
receivers drops below than $100, it is not optimal to
produce 20 receivers and no CDs.
![Page 23: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/23.jpg)
23
4.4 Sensitivity Analysis For A Larger Maximization Example
• Anderson Electronics Considering producing four potential products: VCRs, stereos, televisions (TVs), and DVD players:
Profit per unit:
VCR Stereo TV DVD
$29 $32 $72 $54
VCR Stereo TV DVD Supply Cost
Electronic Components 3 4 4 3 4,700 $7Non-electronic Components 2 2 4 3 4,500 $5Assembly time (hours) 1 1 3 2 2,500 $10Selling price (per unit) $70 $80 $150 $110
![Page 24: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/24.jpg)
24
Anderson Electronics LP Formulation
Objective: maximize profit =
$29 V + $32 S + $72 T + $54 Dsubject to
3 V + 4 S + 4 T + 3 D 4700 (Electronic components)
2 V + 2 S + 4 T + 3 D 4500 (Non-electronic components)
1 V + 1 S + 3 T + 2 D 2500 (Assembly time in hours)
V, S, T, D 0
Where: V = number of VCRs to produce. S = number of Stereos to produce. T = number of TVs to produce. D = number of DVD players to produce.
![Page 25: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/25.jpg)
25
Excel Solver Answer ReportTarget Cell (Max)
Cell Name Original Value Final Value
$F$8 Profit $0.00 $69,400.00
Adjustable Cells
Cell Name Original Value Final Value
$B$5 Solution value VCR 0.00 0.00
$C$5 Solution value Stereo 0.00 380.00
$D$5 Solution value TV 0.00 0.00
$E$5 Solution value DVD 0.00 1060.00
Constraints
Cell Name Cell Value Formula Status Slack
$F$10 Electronic comp 4700.00 $F$10<=$H$10 Binding 0.00
$F$11 Non-electronic comp 3940.00 $F$11<=$H$11 Not Binding 560.00
$F$12 Assembly time 2500.00 $F$12<=$H$12 Binding 0.00
![Page 26: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/26.jpg)
26
Excel Solver Sensitivity Report
Adjustable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$5 Solution value VCR 0.00 -1.00 29.00 1.00 1E+30
$C$5 Solution value Stereo 380.00 0.00 32.00 40.00 1.67
$D$5 Solution value TV 0.00 -8.00 72.00 8.00 1E+30
$E$5 Solution value DVD 1060.00 0.00 54.00 10.00 5.00
Constrains
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$F$10 Electronic comp 4700.00 2.00 4700.00 2800.00 950.00
$F$11 Non-electronic comp 3940.00 0.00 4500.00 1E+30 560.00
$F$12 Assembly time 2500.00 24.00 2500.00 466.67 1325.00
![Page 27: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/27.jpg)
27
Excel Solver Sensitivity ReportAdjustable Cells
Non Zero value decision variables, Stereos and DVDs:Produce 380 Stereos with unit profit of $32. • Decision should not change as profit is between $31.33 and
$72: Objective Coefficient – Allocable Decrease ($32 - $1.67)
and Objective Coefficient – Allocable Increase ($32+$40)
Produce 1060 DVDs with unit profit of $54. • Decision should not change as profit is between $49 and
$64: Objective Coefficient – Allocable Decrease ($54 - $5)
and
Objective Coefficient – Allocable Increase ($54+$10)
![Page 28: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/28.jpg)
28
Excel Solver Sensitivity Report
Zero value decision variables, VCRs and TVs:
Produce 0 VCRs with unit cost of $1.00 (Reduced Cost).
• Decision to make 0 should not change as profit is below $29 –
but should change over $30:
Objective Coefficient – Allocable Decrease ($29 - infinity) and
Objective Coefficient – Allocable Increase ($29 + $1).
Produce 0 TVs with unit cost of $8.00 (Reduced Cost).
• Decision to make 0 should not change as profit is below $72 –
but should change over $80:
Objective Coefficient – Allocable Decrease ($72 - infinity) and
Objective Coefficient – Allocable Increase ($72 + $8).
![Page 29: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/29.jpg)
29
Excel Solver Sensitivity Report
Constraints
Nonzero Shadow Prices:
• Electronic Components, Shadow price $2
– Each additional unit of electronic components will allow
Anderson to increase its profit by $2.
– The shadow price is $2 for RHS between (3750, 7500).
RHS - Allocable Increase (4700 + 2800) and
RHS - Allocable Decrease (4700 - 950).
![Page 30: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/30.jpg)
30
Excel Solver Sensitivity Report
Constraints
Nonzero Shadow Prices:
• Assembly Time, Shadow price $24
– Each additional hour of assembly time will allow Anderson
to increase its profit by $24.
– The shadow price is $24 for RHS between (1175, 2966.67).
RHS - Allocable Increase (2500 + 466.67) and
RHS - Allocable Decrease (2500 - 1325).
![Page 31: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/31.jpg)
31
Excel Solver Sensitivity Report
Constraints
Zero Shadow Price:
• Non-electronic components, Shadow price $0
– Nonbinding constraint, 560 units of unused resources
– The shadow price is $0 for RHS between (3940, infinite).
RHS - Allocable Increase (infinite) and
RHS - Allocable Decrease (4500 - 560).
![Page 32: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/32.jpg)
32
4.5 Simultaneous Changes Using the 100% Rule Possible to analyze impact of simultaneous changes on optimal
solution only under specific condition: (Change / Allowable change) 1
• If decrease RHS from 4,700 to 4,200 units in electronic component, allowable decrease is 950.
The ratio is: 500 / 950 = 0.5263• If increase 200 hours (from 2,500 to 2,700) in assembly
time, allowable increase is 466.67.
The ratio is: 200 / 466.67 = 0.4285• The sum of these ratios is:
Sum of ratios = 0.5263 + 0.4285 = 0.9548 < 1
Since sum does not exceed 1, information provided in sensitivity report is valid to analyze impact of changes.
![Page 33: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/33.jpg)
33
4.5 Simultaneous Changes In Parameter Values
Anderson Electronics
• Decrease of 500 units in electronic component availability
reduces size of feasible region and causes profit to
decrease.
– Magnitude of decrease is $1,000 (500 units x $2 per unit).
• Increase of 200 hours of assembly time results in larger
feasible region and net increase in profit.
– Magnitude of increase is $4,800 (200 hours x $24 per hour).
• Net impact of both changes simultaneously is an increase
in profit by $3,800 ( $4,800 - $1,000).
![Page 34: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/34.jpg)
34
4.5 Simultaneous Changes In OFC ValuesAnderson Electronics• What is impact if selling price of DVDs drops by $3
per unit and at same time selling price of stereos increases by $8 per unit?
• For current solution to remain optimal, allowable decrease in DVD players is $5, while allowable increase in OFC for stereos is $40. – Sum of ratios is:
Sum of ratios = $3 / $5 + $8 / $40 = 0.80 < 1 – $3 decrease in profit per DVD player causes total profit to
decrease by $3,180 (i.e., $3 x 1,060). – $8 increase in unit profit of each stereo results in total
profit of $3,040 (i.e., $8 x 380). • Net impact is a decrease in profit of only $140 to a
new value of $69,260.
![Page 35: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/35.jpg)
35
4.6 Pricing-out New Variables
• Information given in sensitivity report can be used to study impact of introduction of new decision variables (products).
• For example:
– If problem is re-solved with a new product in model, will it be recommend that a new product be made?
– Or, will it be recommend that a new product not be made, and continue making same products (that is, stereos and DVD players)?
![Page 36: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/36.jpg)
36
Could Anderson Electronics Propose a New Product?
Anderson Electronics
• Anderson Electronics considers a new product, home-theater system (HTS). Could the company propose this new product?
• Answer to such question involves a procedure called pricing-out.
![Page 37: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/37.jpg)
37
Pricing-Out Procedure
Home-Theater System (HTS)
• Requires:– 5 units of electronic components
– 4 units of non-electronic components
– 4 hours of assembly time.
• Selling price: $175 per unit.
• The actual cost is 5 x $7 + 4 x $5 + 4 x 10 = $95.
• The net profit is $175 - $95 = $80.
![Page 38: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/38.jpg)
38
Pricing-out procedure
Home-Theater System (HTS)
• Resources required to make this player:– No longer available to meet existing production plan (380 stereos and
1060 DVD players) for $69,400 total profit.
• Checking validity of the 100% Rule:
Calculate ratio of reduction in each resource’s availability to allowable decrease for that resource.
Sum of ratios = 5/950 + 4/560 + 4/1325 = 0.015 < 1
• Profit loss if the resources are used for each HTS:5 x shadow price of electronic components +
4 x shadow price of non-electronic components +
4 x shadow price of assembly time
or 5 x $2 + 4 x $0 + 4 x $24 = $106.
![Page 39: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/39.jpg)
39
Pricing-out procedure
Home-Theater System (HTS) • Profit contribution of each HTS has to at least make
up shortfall in profit. • OFC for HTS must be at least $106 in order for
optimal solution to have non-zero value. • The unit profit of HTS is $80. Therefore, Anderson
Electronics should not propose this new product.
![Page 40: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/40.jpg)
40
Revised Excel Layout
V S T T H
VCR Stereo TV DVD HTS
Solution value 0.00 380 0.00 1060 0.00
Selling price per unit $70 $80 $150 $110 $175 $147,000 <-- Revenue
Cost price per unit $41 $48 $78 $56 $95 $77,600 <-- Cost
Profit $29 $32 $72 $54 $80 $69,400 <-- Objective
Constraints Cost
Electronic comp 3 4 4 3 5 4700.00 <= 4700 $7
Non-electronic comp 2 2 4 3 4 3940.00 <= 4500 $5
Assembly time 1 1 3 2 4 2500.00 <= 2500 $10
LHS Sign RHS
Anderson Electronics
![Page 41: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/41.jpg)
41
Revised Excel Solver Sensitivity Report
Adjustable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$5 Solution value VCR 0.00 -1.00 29.00 1.00 1E+30
$C$5 Solution value Stereo 380.00 0.00 32.00 40.00 1.67
$D$5 Solution value TV 0.00 -8.00 72.00 8.00 1E+30
$E$5 Solution value DVD 1060.00 0.00 54.00 10.00 5.00
$F$5 Solution value HTS 0.00 -26.00 80.00 26.00 1E+30
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$G$10 Electronic comp 4700.00 2.00 4700.00 2800.00 950.00
$G$11 Non-electronic comp 3940.00 0.00 4500.00 1E+30 560.00
$G$12 Assembly time 2500.00 24.00 2500.00 466.67 1325.00
![Page 42: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/42.jpg)
42
4.7 Sensitivity Analysis - Minimization Example
Burn-Off Diet Drink
• Plans to introduce miracle drink that will magically burn fat away.
Ingredient A Ingredient B Ingredient C Ingredient D Requirement
Chemical X 3 4 8 10 At least 280 units
Chemical Y 5 3 6 6 At least 200 units
Chemical Z 10 25 20 40 At most 1,050 units
Cost per ounce
4 cents 7 cents 6 cents 3 cents
![Page 43: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/43.jpg)
43
Burn-Off Diet Drink LP Formulation
Objective: minimize daily dose cost in cents.
4A + 7B + 6C + 3D
Subject to
A + B + C + D 36 (Daily dose requirement)
3A + 4B + 8C + 10D 280 (Chemical X requirement)
5A + 3B + 6C + 6D 200 (Chemical Y requirement)
10A + 25B + 20C + 40D 1050 (Chemical Z max limit)
A, B, C, D 0
![Page 44: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/44.jpg)
44
Excel Solution
A B C D
Ingr A Ingr B Ingr C Ingr D
Number of ounces 10.250 0.000 4.125 21.625
Cost (cents) 4 7 6 3 130.625 <-- Objective
Constraints
Daily dosage 1 1 1 1 36.00 >= 36
Chemical X 3 4 8 10 280.00 >= 280
Chemical Y 5 3 6 6 205.75 >= 200
Chemical Z 10 25 20 40 1050.00 <= 1050
LHS Sign RHS
![Page 45: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/45.jpg)
45
Solver Answer Report Burn-Off Diet Drink
Target Cells
Cell Name Original Value Final Value
$F$6 Cost (cents) 0.000 130.625
Adjustable Cells
Cell Name Original Value Final Value
$B$5 Number of ounces Ingr A 0.000 10.250
$C$5 Number of ounces Ingr B 0.000 0.000
$D$5 Number of ounces Ingr C 0.000 4.125
$E$5 Number of ounces Ingr D 0.000 21.625
Constraints
Cell Name Cell Value Formula Status Slack
$F$11 Chemical Z 1050.000 $F$11<=$H$11 Binding 0.000
$F$8 Daily dosage 36.000 $F$8>=$H$8 Binding 0.000
$F$9 Chemical X 280.000 $F$9>=$H$9 Binding 0.000
$F$10 Chemical Y 205.750 $F$10>=$H$10 Not Binding 5.750
![Page 46: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/46.jpg)
46
Solver Sensitivity Report
Adjustable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$5 Number of ounces Ingr A 10.250 0.000 4.000 3.500 2.500
$C$5 Number of ounces Ingr B 0.000 5.688 7.000 1E+30 5.688
$D$5 Number of ounces Ingr C 4.125 0.000 6.000 15.000 2.333
$E$5 Number of ounces Ingr D 21.625 0.000 3.000 3.800 1E+30
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$F$11 Chemical Z 1050.000 -0.238 1050.000 47.143 346.000
$F$8 Daily dosage 36.000 3.750 36.000 16.500 1.278
$F$9 Chemical X 280.000 0.875 280.000 41.000 11.000
$F$10 Chemical Y 205.750 0.000 200.000 5.750 1E+30
![Page 47: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/47.jpg)
47
Change in Objective Function Coefficient (OFC)
Nonzero Reduced Cost: Ingredient B
• The reduced cost of ingredient B is $5.688.
– Each ounce of ingredient B used to make the drink will
cause the total cost per daily dosage to increase by 5.688
cents.
– The current cost of ingredient B is 7 cents. If the cost of
ingredient B is lower by 5.688 cents, then it becomes cost-
effective to use this ingredient.
– When the cost of ingredient B is above 1.312 cents (=7-
5.688), the current corner point solution remains optimal.
![Page 48: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/48.jpg)
48
Change in Objective Function Coefficient (OFC)
Zero Reduced Cost: Ingredient C
• The reduced cost of ingredient C is $0.
– The current cost of ingredient C is 6 cents per
ounce. The range for the cost coefficient of this
ingredient is between 3.667 cents (=6-2.333) and
21 cents (=6+15).
– When the cost of ingredient C is between this
range, the current corner point solution remains
optimal.
![Page 49: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/49.jpg)
49
Changes in Right-hand-side (RHS)
Nonzero Shadow Price: Chemical X
• The shadow price of chemical X is 0.875.
• For each additional unit of chemical X required to
be present in the drink, the total cost will increase
by 0.875 cents.
• The shadow price remains to be 0.875 if the
requirement for chemical X is between 269 units
(=280-11) and 321 units (=280+41).
![Page 50: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/50.jpg)
50
Changes in Right-hand-side (RHS)
Nonzero Shadow Price: Chemical Z
• The shadow price of chemical Z is -0.238.
• Each unit increase in the maximum limit allowed
for chemical Z will reduce the total cost by 0.238
cents.
• The shadow price remains to be -0.238 if the
maximum limit is between 704 units (=1050-346)
and 1097.143 units (=1050+47.143).
![Page 51: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/51.jpg)
51
Simultaneous Changes In Parameter ValuesBurn-Off can decrease the minimum requirement for chemical
X by 5 units provided the maximum limit allowed for chemical Z is reduced by 50 units.
• The sum of each proportion of change to allowable change is
5/11 + 50/346 = 0.399 < 1• Since sum does not exceed 1, information provided in
sensitivity report is valid to analyze impact of changes. • The reduced cost from the change in chemical X is
0.875 x 5 = 4.375 cents.• The reduced cost from the change in chemical Z is
0.238 x 50 = 11.9 cents.• The net impact is an increase in total cost of 7.525 cents
(=11.9-4.375).
![Page 52: Managerial Decision Modeling with Spreadsheets Chapter 4 Linear Programming Sensitivity Analysis](https://reader033.vdocuments.us/reader033/viewer/2022061404/56649eb55503460f94bbdeaf/html5/thumbnails/52.jpg)
52
Summary• Sensitivity analysis used by management to answer
series of “ what-if ” questions about LP model inputs. • Tests sensitivity of optimal solution to changes:
– Profit or cost coefficients, and
– Constraint RHS values.
• Explored sensitivity analysis graphically (with two decision variables).
• Discussed interpretation of information:– In answer and sensitivity reports generated by Solver.
– In reports used to analyze simultaneous changes in model parameter values.
– Determine potential impact of new variable in model.