第五章线性规划的What-If

1、Table of ContentsChapter 5 (What-If Analysis for Linear Programming)Continuing the Wyndor Case Study (Section 5.2)5.2Changes in One Objective Function Coefficient (Section 5.3)5.35.9Simultaneous Changes in Objective Function Coefficients (Section 5.4)5.105.17Single Changes in a Constraint (Section 5

2、.5)5.185.23Simultaneous Changes in the Constraints (Section 5.6)5.245.26Wyndor (Before What-If Analysis)3456789101112BCDEFGDoorsWindowsUnit Profit$300$500HoursHoursUsedAvailablePlant 1102=4Plant 20212=12Plant 33218=18DoorsWindowsTotal ProfitUnits Produced26$3,600Hours Used Per Unit ProducedUsing the

3、 Spreadsheet to do Sensitivity Analysis3456789101112BCDEFGDoorsWindowsUnit Profit$200$500HoursHoursUsedAvailablePlant 1102=4Plant 20212=12Plant 33218=18DoorsWindowsTotal ProfitUnits Produced26$3,400Hours Used Per Unit ProducedThe profit per door has been revised from $300 to $200.No change occurs in

4、 the optimal solution.Using the Spreadsheet to do Sensitivity AnalysisThe profit per door has been revised from $300 to $500.No change occurs in the optimal solution.3456789101112BCDEFGDoorsWindowsUnit Profit$500$500HoursHoursUsedAvailablePlant 1102=4Plant 20212=12Plant 33218=18DoorsWindowsTotal Pro

5、fitUnits Produced26$4,000Hours Used Per Unit ProducedUsing the Spreadsheet to do Sensitivity AnalysisThe profit per door has been revised from $300 to $1,000.The optimal solution changes.3456789101112BCDEFGDoorsWindowsUnit Profit$1,000$500HoursHoursUsedAvailablePlant 1104=4Plant 2026=12Plant 33218=1

6、8DoorsWindowsTotal ProfitUnits Produced43$5,500Hours Used Per Unit ProducedUsing Solver Table to do Sensitivity Analysis345678910111213141516171819202122232425262728BCDEFGDoorsWindowsUnit Profit$300$500HoursHoursUsedAvailablePlant 1102=4Plant 20212=12Plant 33218=18DoorsWindowsTotal ProfitUnits Produ

7、ced26$3,600Unit ProfitTotalfor DoorsDoorsWindowsProfit26$3,600$100$200$300$400$500$600$700$800$900$1,000Hours Used Per Unit ProducedOptimal Units ProducedSelect these cells (B18:E28), before choosing the Solver Table.161718CDETotalDoorsWindowsProfit=DoorsProduced=WindowsProduced=TotalProfitOptimal U

8、nits ProducedUsing Solver Table to do Sensitivity Analysis16171819202122232425262728BCDEUnit ProfitTotalfor DoorsDoorsWindowsProfit26$3,600$10026$3,200$20026$3,400$30026$3,600$40026$3,800$50026$4,000$60026$4,200$70026$4,400$80043$4,700$90043$5,100$1,00043$5,500Optimal Units ProducedUsing the Sensiti

9、vity Report to Find the Allowable RangeAdjustable CellsFinalReducedObjectiveAllowableAllowableCellNameValueCostCoefficientIncreaseDecrease$C$12Units Produced Doors20300450300$D$12Units Produced Windows605001E+30300Graphical Insight into the Allowable RangeThe two dashed lines that pass through the s

10、olid 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.WD(2, 6) is optimal for 0 PD 750PD = 0 (Profit = 0 D + 500 W)PD = 300 (Profit = 300 D + 500 W)PD = 750 (Profit = 750 D + 500 W)Line ALine CLine B0246

11、2468Production rate for doorsProduction ratefor windowsFeasibleregionUsing the Spreadsheet to do Sensitivity AnalysisThe 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.3456789101112BCDEFGDoorsWindow

12、sUnit Profit$450$400HoursHoursUsedAvailablePlant 1102=4Plant 20212=12Plant 33218=18DoorsWindowsTotal ProfitUnits Produced26$3,300Hours Used Per Unit ProducedUsing the Spreadsheet to do Sensitivity AnalysisThe profit per door has been revised from $300 to $600.The profit per window has been revised f

13、rom $500 to $300.The optimal solution changes.3456789101112BCDEFGDoorsWindowsUnit Profit$600$300HoursHoursUsedAvailablePlant 1104=4Plant 2026=12Plant 33218=18DoorsWindowsTotal ProfitUnits Produced43$3,300Hours Used Per Unit ProducedUsing Solver Table to do Sensitivity Analysis34567891011121314151617

14、18192021BCDEFGHIDoorsWindowsUnit Profit$300$500HoursHoursUsedAvailablePlant 1102=4Plant 20212=12Plant 33218=18DoorsWindowsTotal ProfitUnits Produced26$3,600Total ProfitUnit Profit for Windows$3,600$100$200$300$400$500$300Unit Profit$400for Doors$500$600Hours Used Per UnitSelect these cells (C17:H21)

15、, before choosing the Solver Table.17C=TotalProfitUsing Solver Table to do Sensitivity Analysis161718192021BCDEFGHTotal ProfitUnit Profit for Windows$3,600$100$200$300$400$500$300$1,500$1,800$2,400$3,000$3,600Unit Profit$400$1,900$2,200$2,600$3,200$3,800for Doors$500$2,300$2,600$2,900$3,400$4,000$60

16、0$2,700$3,000$3,300$3,600$4,200Using Solver Table to do Sensitivity Analysis242526272829BCDEFGHUnits 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)$60

17、0(4, 3)(4, 3)(4, 3)(4, 3)(2, 6)25C=( & DoorsProduced & , & WindowsProduced & )The 100 Percent RuleThe 100 Percent Rule for Simultaneous Changes in Objective Function Coefficients: If simultaneous changes are made in the coefficients of the objective function, calculate for each change the percentage

18、 of the allowable change (increase or decrease) for that coefficient to remain within its allowable range. If the sum of the percentage changes does not exceed 100 percent, the original optimal solution definitely will still be optimal. (If the sum does exceed 100 percent, then we cannot be sure.)Gr

19、aphical Insight into 100 Percent Rule WD02462468Production rate for doorsProduction rate for windowsFeasible region10Objective function line now is Profit = $3150 = 525 D + 350 W since PD = $525, PW = $350.Entire line segment is optimal(4, 3)(2, 6)8The estimates of the unit profits for doors and win

20、dows change to PD = $525 and PW = $350, which lies at the edge of what is allowed by the 100 percent rule.Graphical Insight into 100 Percent RuleWhen the estimates of the unit profits for doors and windows change to PD = $150 and PW = $250 (half their original values), the graphical method shows tha

21、t the optimal solution still is (D, W) = (2, 6) even though the 100 percent rule says that the optimal solution might change. 02462468(2, 6)Feasible regionOptimal solutionProduction rate for doorsProduction rate for windowsProfit = $1800 = 150D + 250 W8WDUsing the Spreadsheet to do Sensitivity Analy

22、sisThe hours available in plant 2 have been increased from 12 to 13.The total profit increases by $150 per week.3456789101112BCDEFGDoorsWindowsUnit Profit$300$500HoursHoursUsedAvailablePlant 1101.667=4Plant 20213=13Plant 33218=18DoorsWindowsTotal ProfitUnits Produced1.6676.5$3,750Hours Used Per Unit

23、 ProducedUsing the Spreadsheet to do Sensitivity AnalysisThe 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).3456789101112BCDEFGDoorsWindowsUnit Profit$300$500HoursHoursUsedAvailablePlant 1100=4Plant 20

24、218=18Plant 33218=18DoorsWindowsTotal ProfitUnits Produced09$4,500Hours Used Per Unit ProducedUsing the Spreadsheet to do Sensitivity AnalysisThe hours available in plant 2 have been further increased from 18 to 20.The total profit does not increase any further.3456789101112BCDEFGDoorsWindowsUnit Pr

25、ofit$300$500HoursHoursUsedAvailablePlant 1100=4Plant 20218=20Plant 33218=18DoorsWindowsTotal ProfitUnits Produced09$4,500Hours Used Per Unit ProducedUsing Solver Table to do Sensitivity Analysis34567891011121314151617181920212223242526272829303132333435BCDEFGDoorsWindowsUnit Profit$300$500HoursHours

26、UsedAvailablePlant 1102=4Plant 20212=12Plant 33218=18DoorsWindowsTotal ProfitUnits Produced26$3,600Time AvailableTotalIncrementalin Plant 2 (hours)DoorsWindowsProfitProfit26$3,600442$2,200542.5$2,450$250643$2,700$25073.6673.5$2,850$15083.3334$3,000$150934.5$3,150$150102.6675$3,300$150112.3335.5$3,45

27、0$1501226$3,600$150131.6676.5$3,750$150141.3337$3,900$1501517.5$4,050$150160.6678$4,200$150170.3338.5$4,350$1501809$4,500$1501909$4,500$02009$4,500$0Hours Used Per Unit ProducedOptimal Units ProducedSelect these cells (B18:E35), before choosing the Solver Table.Using the Sensitivity ReportAdjustable

28、 CellsFinalReducedObjectiveAllowableAllowableCellNameValueCostCoefficientIncreaseDecrease$C$12Units Produced Doors20300450300$D$12Units Produced Windows605001E+30300ConstraintsFinalShadowConstraintAllowableAllowableCellNameValuePriceR.H. SideIncreaseDecrease$E$7Plant 1 Used2041E+302$E$8Plant 2 Used1

29、21501266$E$9Plant 3 Used181001866Graphical Interpretation of the Allowable Range 024624682 W = 6 Profit = 300 (4) + 500 (3) = $2,7002 W = 18 Profit = 300 (0) + 500 (9) = $4,5002 W = 12 Profit = 300 (2) + 500 (6) = $3,600(4, 3)(2, 6)Feasibleregion for original problemLine BLine A (D = 4)Line C (3 D +

30、 2 W = 18)10(0, 9)DWProduction rate for doorsProduction rate for windowsUsing the Spreadsheet to do Sensitivity AnalysisOne available hour in plant 3 has been shifted to plant 2.The total profit increases by $50 per week.3456789101112BCDEFGDoorsWindowsUnit Profit$300$500HoursHoursUsedAvailablePlant

31、1101.333=4Plant 20213=13Plant 33217=17DoorsWindowsTotal ProfitUnits Produced1.3336.5$3,650Hours Used Per Unit ProducedUsing Solver Table to do Sensitivity Analysis34567891011121314151617181920212223242526BCDEFGHDoorsWindowsUnit Profit$300$500HoursHoursUsedAvailablePlant 1102=4Total (Plants 2 & 3)Plant 20212=1230Plant 33218=18DoorsWin