Managerial Decision Modeling with Spreadsheets

1
Managerial Decision Modeling with Spreadsheets

• Chapter 4
• Linear Programming Sensitivity Analysis

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.

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.

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 120R
• subject 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.

5
High Note Sound Company Problem Solution
6
Changes in Objective Function Coefficient
Impact of price change of Receivers
If 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)
7
Changes in Objective Function Coefficient
Impact of price change of Receivers
If receivers 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).
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

9
High Note Sound Company Answer Report
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.

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.

12
High Note Sound Company Sensitivity Report
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.

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.

15
Changes in Right-hand-side (RHS)
High Note Sound Company
Constraints Constraints  
    Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
D8 Electricians' Time 80.00 30.00 80.00 160.00 80.00
D9 Audio Technicians' Time 20.00 0.00 60.00 1E30 40.00

• 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.

16
Change in RHS of Nonbinding Constraint
Constraints Constraints  
    Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
D8 Electricians' Time 80.00 30.00 80.00 160.00 80.00
D9 Audio Technicians' Time 20.00 0.00 60.00 1E30 40.00

• 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).

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.

18
Change in Objective Function Coefficient (OFC)
High Note Sound Company
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 technicians 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.

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 (1E30) indicates
  if 50 is not attractive enough to make CD any
  price below it will not make it attractive either!

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 technicians 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.

22
Change in Objective Function Coefficient (OFC)

• Allowable Increase and Decrease for each Stereo
  Receiver
• Allowable Increase - infinity (1E30) 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.

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 7
Non-electronic Components 2 2 4 3 4,500 5
Assembly time (hours) 1 1 3 2 2,500 10
Selling price (per unit) 70 80 150 110
24
Anderson Electronics LP Formulation

• Objective maximize profit
• 29 V 32 S 72 T 54 D
• subject 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.
25
Excel Solver Answer Report
Target Cell (Max) Target Cell (Max) Target Cell (Max)  
  Cell Name Original Value Final Value  
  F8 Profit 0.00 69,400.00  
   
Adjustable Cells Adjustable Cells Adjustable Cells  
  Cell Name Original Value Final Value  
  B5 Solution value VCR 0.00 0.00  
  C5 Solution value Stereo 0.00 380.00  
  D5 Solution value TV 0.00 0.00  
  E5 Solution value DVD 0.00 1060.00  
   
Constraints Constraints Constraints  
  Cell Name Cell Value Formula Status Slack
  F10 Electronic comp 4700.00 F10ltH10 Binding 0.00
  F11 Non-electronic comp 3940.00 F11ltH11 Not Binding 560.00
  F12 Assembly time 2500.00 F12ltH12 Binding 0.00
26
Excel Solver Sensitivity Report
Adjustable Cells Adjustable Cells  
    Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
B5 Solution value VCR 0.00 -1.00 29.00 1.00 1E30
C5 Solution value Stereo 380.00 0.00 32.00 40.00 1.67
D5 Solution value TV 0.00 -8.00 72.00 8.00 1E30
E5 Solution value DVD 1060.00 0.00 54.00 10.00 5.00
Constrains Constrains  
    Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
F10 Electronic comp 4700.00 2.00 4700.00 2800.00 950.00
F11 Non-electronic comp 3940.00 0.00 4500.00 1E30 560.00
F12 Assembly time 2500.00 24.00 2500.00 466.67 1325.00
27
Excel Solver Sensitivity Report

• Adjustable 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
  (3240)
• 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
  (5410)

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).

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).

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).

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).

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 lt
  1 
• Since sum does not exceed 1, information
  provided in sensitivity report is valid to
  analyze impact of changes.

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).

34
4.5 Simultaneous Changes In OFC Values

• Anderson 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 isSum of ratios 3 / 5 8
  / 40 0.80 lt 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.

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)?

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.

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.

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 resources
  availability to allowable decrease for that
  resource. 
• Sum of ratios 5/950 4/560 4/1325
  0.015 lt 1
• Profit loss if the resources are used for each
  HTS5 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.

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.

40
Revised Excel Layout
Anderson Electronics
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 lt-- Revenue lt-- Revenue lt-- Revenue lt-- Revenue
Cost price per unit 41 48 78 56 95 77,600 lt-- Cost lt-- Cost lt-- Cost lt-- Cost
Profit 29 32 72 54 80 69,400 lt-- Objective lt-- Objective lt-- Objective lt-- Objective
Constraints         Cost
Electronic comp 3 4 4 3 5 4700.00 lt lt 4700 7
Non-electronic comp 2 2 4 3 4 3940.00 lt lt 4500 5
Assembly time 1 1 3 2 4 2500.00 lt lt 2500 10
LHS Sign Sign RHS
41
Revised Excel Solver Sensitivity Report
Adjustable Cells Adjustable Cells  
    Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
B5 Solution value VCR 0.00 -1.00 29.00 1.00 1E30
C5 Solution value Stereo 380.00 0.00 32.00 40.00 1.67
D5 Solution value TV 0.00 -8.00 72.00 8.00 1E30
E5 Solution value DVD 1060.00 0.00 54.00 10.00 5.00
F5 Solution value HTS 0.00 -26.00 80.00 26.00 1E30
Constraints Constraints  
    Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
G10 Electronic comp 4700.00 2.00 4700.00 2800.00 950.00
G11 Non-electronic comp 3940.00 0.00 4500.00 1E30 560.00
G12 Assembly time 2500.00 24.00 2500.00 466.67 1325.00
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
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

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 lt-- Objective lt-- Objective
Constraints      
Daily dosage 1 1 1 1 36.00 gt 36
Chemical X 3 4 8 10 280.00 gt 280
Chemical Y 5 3 6 6 205.75 gt 200
Chemical Z 10 25 20 40 1050.00 lt 1050
LHS Sign RHS
45
Solver Answer Report
Burn-Off Diet Drink
Target Cells Target Cells  
Cell Name Original Value Final Value  
F6 Cost (cents) 0.000 130.625  
 
Adjustable Cells Adjustable Cells  
Cell Name Original Value Final Value  
B5 Number of ounces Ingr A 0.000 10.250  
C5 Number of ounces Ingr B 0.000 0.000  
D5 Number of ounces Ingr C 0.000 4.125  
E5 Number of ounces Ingr D 0.000 21.625  
 
Constraints Constraints  
Cell Name Cell Value Formula Status Slack
F11 Chemical Z 1050.000 F11ltH11 Binding 0.000
F8 Daily dosage 36.000 F8gtH8 Binding 0.000
F9 Chemical X 280.000 F9gtH9 Binding 0.000
F10 Chemical Y 205.750 F10gtH10 Not Binding 5.750
46
Solver Sensitivity Report
Adjustable Cells Adjustable Cells  
    Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
B5 Number of ounces Ingr A 10.250 0.000 4.000 3.500 2.500
C5 Number of ounces Ingr B 0.000 5.688 7.000 1E30 5.688
D5 Number of ounces Ingr C 4.125 0.000 6.000 15.000 2.333
E5 Number of ounces Ingr D 21.625 0.000 3.000 3.800 1E30
 
Constraints Constraints  
    Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
F11 Chemical Z 1050.000 -0.238 1050.000 47.143 346.000
F8 Daily dosage 36.000 3.750 36.000 16.500 1.278
F9 Chemical X 280.000 0.875 280.000 41.000 11.000
F10 Chemical Y 205.750 0.000 200.000 5.750 1E30
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.

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 (615).
• When the cost of ingredient C is between this
  range, the current corner point solution remains
  optimal.

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 (28041).

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 (105047.143).

51
Simultaneous Changes In Parameter Values

• Burn-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 lt 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).

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.