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

5-2

Wyndor (Before What-If Analysis)

3456789

101112

B C D E F GDoors Windows

Unit Profit $300 $500Hours HoursUsed Available

Plant 1 1 0 2 <= 4Plant 2 0 2 12 <= 12Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 2 6 $3,600

Hours Used Per Unit Produced

5-3

Using the Spreadsheet to do Sensitivity Analysis

3456789

101112

B C D E F GDoors Windows

Unit Profit $200 $500Hours HoursUsed Available

Plant 1 1 0 2 <= 4Plant 2 0 2 12 <= 12Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 2 6 $3,400

Hours Used Per Unit Produced

The profit per door has been revised from $300 to $200.No change occurs in the optimal solution.

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

3456789

101112

B C D E F GDoors Windows

Unit Profit $500 $500Hours HoursUsed Available

Plant 1 1 0 2 <= 4Plant 2 0 2 12 <= 12Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 2 6 $4,000

Hours Used Per Unit Produced

5-5

Using the Spreadsheet to do Sensitivity Analysis

The profit per door has been revised from $300 to $1,000.The optimal solution changes.

3456789

101112

B C D E F GDoors Windows

Unit Profit $1,000 $500Hours HoursUsed Available

Plant 1 1 0 4 <= 4Plant 2 0 2 6 <= 12Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 4 3 $5,500

Hours Used Per Unit Produced

5-6

Using Solver Table to do Sensitivity Analysis

3456789

10111213141516171819202122232425262728

B C D E F GDoors Windows

Unit Profit $300 $500Hours HoursUsed Available

Plant 1 1 0 2 <= 4Plant 2 0 2 12 <= 12Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 2 6 $3,600

Unit Profit Totalfor Doors Doors Windows Profit

2 6 $3,600$100$200$300$400$500$600$700$800$900

$1,000

Hours Used Per Unit Produced

Optimal Units ProducedSelect these cells (B18:E28), before choosing the Solver Table.

161718

C D ETotal

Doors Windows Profit=DoorsProduced =WindowsProduced =TotalProfit

Optimal Units Produced

5-7

Using Solver Table to do Sensitivity Analysis

16171819202122232425262728

B C D EUnit Profit Totalfor Doors Doors Windows Profit

2 6 $3,600$100 2 6 $3,200$200 2 6 $3,400$300 2 6 $3,600$400 2 6 $3,800$500 2 6 $4,000$600 2 6 $4,200$700 2 6 $4,400$800 4 3 $4,700$900 4 3 $5,100

$1,000 4 3 $5,500

Optimal Units Produced

5-8

Using the Sensitivity Report to Find the Allowable Range

Adjustable CellsFinal Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease$C$12 Units Produced Doors 2 0 300 450 300$D$12 Units Produced Windows 6 0 500 1E+30 300

The solutionAllowable range

(Solution stays the same)

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

W

D

(2, 6) is optimal for 0 < PD < 750

PD = 0 (Profit = 0 D + 500 W)

PD = 300 (Profit = 300 D + 500 W)

PD = 750 (Profit = 750 D + 500 W)

Line A

Line C

Line B

0 2 4 6

2

4

6

8

Production rate for doors

Production ratefor windows

Feasibleregion

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

3456789

101112

B C D E F GDoors Windows

Unit Profit $450 $400Hours HoursUsed Available

Plant 1 1 0 2 <= 4Plant 2 0 2 12 <= 12Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 2 6 $3,300

Hours Used Per Unit Produced

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

3456789

101112

B C D E F GDoors Windows

Unit Profit $600 $300Hours HoursUsed Available

Plant 1 1 0 4 <= 4Plant 2 0 2 6 <= 12Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 4 3 $3,300

Hours Used Per Unit Produced

5-12

Using Solver Table to do Sensitivity Analysis

3456789

101112131415161718192021

B C D E F G H IDoors Windows

Unit Profit $300 $500Hours HoursUsed Available

Plant 1 1 0 2 <= 4Plant 2 0 2 12 <= 12Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 2 6 $3,600

Total Profit Unit Profit for Windows$3,600 $100 $200 $300 $400 $500$300

Unit Profit $400for Doors $500

$600

Hours Used Per Unit

Select these cells (C17:H21), before choosing the Solver Table.

17C

=TotalProfit

5-13

Using Solver Table to do Sensitivity Analysis

161718192021

B C D E F G HTotal Profit Unit Profit for Windows

$3,600 $100 $200 $300 $400 $500$300 $1,500 $1,800 $2,400 $3,000 $3,600

Unit Profit $400 $1,900 $2,200 $2,600 $3,200 $3,800for Doors $500 $2,300 $2,600 $2,900 $3,400 $4,000

$600 $2,700 $3,000 $3,300 $3,600 $4,200

5-14

Using Solver Table to do Sensitivity Analysis

242526272829

B C D E F G HUnits Produced (Doors, Windows) Unit Profit for Windows

(2, 6) $100 $200 $300 $400 $500$300 (4, 3) (4, 3) (2, 6) (2, 6) (2, 6)

Unit Profit $400 (4, 3) (4, 3) (2, 6) (2, 6) (2, 6)for Doors $500 (4, 3) (4, 3) (4, 3) (2, 6) (2, 6)

$600 (4, 3) (4, 3) (4, 3) (4, 3) (2, 6)

25C

="(" & DoorsProduced & ", " & WindowsProduced & ")"

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

3456789

101112

B C D E F GDoors Windows

Unit Profit $300 $500Hours HoursUsed Available

Plant 1 1 0 1.667 <= 4Plant 2 0 2 13 <= 13Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 1.667 6.5 $3,750

Hours Used Per Unit Produced

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

3456789

101112

B C D E F GDoors Windows

Unit Profit $300 $500Hours HoursUsed Available

Plant 1 1 0 0 <= 4Plant 2 0 2 18 <= 18Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 0 9 $4,500

Hours Used Per Unit Produced

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

3456789

101112

B C D E F GDoors Windows

Unit Profit $300 $500Hours HoursUsed Available

Plant 1 1 0 0 <= 4Plant 2 0 2 18 <= 20Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 0 9 $4,500

Hours Used Per Unit Produced

5-18

Using Solver Table to do Sensitivity Analysis

3456789

1011121314151617181920212223242526272829303132333435

B C D E F GDoors Windows

Unit Profit $300 $500Hours HoursUsed Available

Plant 1 1 0 2 <= 4Plant 2 0 2 12 <= 12Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 2 6 $3,600

Time Available Total Incrementalin Plant 2 (hours) Doors Windows Profit Profit

2 6 $3,6004 4 2 $2,2005 4 2.5 $2,450 $2506 4 3 $2,700 $2507 3.667 3.5 $2,850 $1508 3.333 4 $3,000 $1509 3 4.5 $3,150 $15010 2.667 5 $3,300 $15011 2.333 5.5 $3,450 $15012 2 6 $3,600 $15013 1.667 6.5 $3,750 $15014 1.333 7 $3,900 $15015 1 7.5 $4,050 $15016 0.667 8 $4,200 $15017 0.333 8.5 $4,350 $15018 0 9 $4,500 $15019 0 9 $4,500 $020 0 9 $4,500 $0

Hours Used Per Unit Produced

Optimal Units Produced

Select these cells (B18:E35), before choosing the Solver Table.

5-19

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)

Using the Sensitivity Report

Adjustable CellsFinal Reduced Objective Allowable Allowable

Cell Name Value Cost Coefficient Increase Decrease$C$12 Units Produced Doors 2 0 300 450 300$D$12 Units Produced Windows 6 0 500 1E+30 300

ConstraintsFinal Shadow Constraint Allowable Allowable

Cell Name Value Price R.H. Side Increase Decrease$E$7 Plant 1 Used 2 0 4 1E+30 2$E$8 Plant 2 Used 12 150 12 6 6$E$9 Plant 3 Used 18 100 18 6 6

5-20

Graphical Interpretation of the Allowable Range

0 2 4 6

2

4

6

8

2 W = 6 Profit = 300 (4) + 500 (3) = $2,700

2 W = 18 Profit = 300 (0) + 500 (9) = $4,500

2 W = 12 Profit = 300 (2) + 500 (6) = $3,600

(4, 3)

(2, 6)

Feasible

region for

original

problem

Line B

Line A (D = 4)

Line C (3 D + 2 W = 18)

10

(0, 9)

D

W

Production rate for doors

Production rate for windows

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

3456789

101112

B C D E F GDoors Windows

Unit Profit $300 $500Hours HoursUsed Available

Plant 1 1 0 1.333 <= 4Plant 2 0 2 13 <= 13Plant 3 3 2 17 <= 17

Doors Windows Total ProfitUnits Produced 1.333 6.5 $3,650

Hours Used Per Unit Produced

5-22

Using Solver Table to do Sensitivity Analysis

3456789

1011121314151617181920212223242526

B C D E F G HDoors Windows

Unit Profit $300 $500Hours HoursUsed Available

Plant 1 1 0 2 <= 4 Total (Plants 2 & 3)Plant 2 0 2 12 <= 12 30Plant 3 3 2 18 <= 18

Doors Windows Total ProfitUnits Produced 2.000 6 $3,600

Time Available Time Available Total Incrementalin Plant 2 (hours) in Plant 3 (hours) Doors Windows Profit Profit

2 6 $3,60012 18 2 6 $3,60013 17 1.333 6.5 $3,650 $5014 16 0.667 7 $3,700 $5015 15 0 7.5 $3,750 $5016 14 0 7 $3,500 -$25017 13 0 6.5 $3,250 -$25018 12 0 6 $3,000 -$250

Hours Used Per Unit Produced

Optimal Units Produced

Select these cells (C19:F26), before choosing the Solver Table.