Table of Contents Chapter 6.8 (Sensitivity Analysis with Solver Table)

1
Table of ContentsChapter 6.8 (Sensitivity
Analysis with Solver Table)

• Continuing the Wyndor Case Study
• Changes in One Objective Function Coefficient
• Simultaneous Changes in Objective Function
  Coefficients
• Single Changes in a Constraint
• Simultaneous Changes in the Constraints

2
Wyndor (Before What-If Analysis)
3
Using the Spreadsheet to do Sensitivity Analysis
The profit per door has been revised from 300 to
200.No change occurs in the optimal solution.
4
Using the Spreadsheet to do Sensitivity Analysis
The profit per door has been revised from 300 to
500.No change occurs in the optimal solution.
5
Using the Spreadsheet to do Sensitivity Analysis
The profit per door has been revised from 300 to
1,000.The optimal solution changes.
6
Using Solver Table to do Sensitivity Analysis
7
Using Solver Table to do Sensitivity Analysis
8
Using the Sensitivity Report to Find the
Allowable Range
Allowable range(Solution stays the same)
The solution
9
Graphical Insight into the Allowable Range
The two dashed lines that pass through the solid
constraint boundary lines are the objective
function lines when PD (the unit profit for
doors) is at an endpoint of its allowable range,
0 PD 750.
10
Using the Spreadsheet to do Sensitivity Analysis
The profit per door has been revised from 300 to
450.The profit per window has been revised from
500 to 400.No change occurs in the optimal
solution.
11
Using the Spreadsheet to do Sensitivity Analysis
The profit per door has been revised from 300 to
600.The profit per window has been revised from
500 to 300.The optimal solution changes.
12
Using Solver Table to do Sensitivity Analysis
13
Using Solver Table to do Sensitivity Analysis
14
Using Solver Table to do Sensitivity Analysis
15
Using the Spreadsheet to do Sensitivity Analysis
The hours available in plant 2 have been
increased from 12 to 13.The total profit
increases by 150 per week.
16
Using the Spreadsheet to do Sensitivity Analysis
The hours available in plant 2 have been further
increased from 13 to 18.The total profit
increases by 750 per week (150 per hour added
in plant 2).
17
Using the Spreadsheet to do Sensitivity Analysis
The hours available in plant 2 have been further
increased from 18 to 20.The total profit does
not increase any further.
18
Using Solver Table to do Sensitivity Analysis
19
Using the Sensitivity Report
Usage of the resource(Left-hand-side of
constraint)
Allowable range(Shadow price is valid)
Increase in objective function value per unit
increase in right-hand-side (RHS)?Z (shadow
price)(?RHS)
20
Graphical Interpretation of the Allowable Range
21
Using the Spreadsheet to do Sensitivity Analysis
One available hour in plant 3 has been shifted to
plant 2.The total profit increases by 50 per
week.
22
Using Solver Table to do Sensitivity Analysis