Transcription
Session #5Sensitivity AnalysisPage 1Sensitivity AnalysisWeekly supply of raw materials:6 Large Bricks8 Small BricksTableProfit 20/TableChairProfit 15/ChairProducts:
Session #5Sensitivity AnalysisPage 2Graphical SolutionMaximize Z ( 15)C ( 20)Tsubject toLarge Bricks:C 2T 6Small Bricks:2C 2T 8andC 0, T 0.
Session #5Sensitivity AnalysisPage 3Generating the Sensitivity ReportSolve the problem using the Solver:A1234567891011BCDEFGThe Lego Production ProblemProfitLarge BricksSmall BricksProduction QuantityTables 20.00Chairs 15.00Bill of Materials2122Tables2Chairs2Then, choose “Sensitivity” under Reports.Total Used6 8 Available68Total Profit 70.00
Session #5Sensitivity AnalysisPage 4The Sensitivity ReportA1234567891011BCDEFGThe Lego Production ProblemProfitLarge BricksSmall BricksProduction QuantityTables 20.00Chairs 15.00Bill of Materials2122Tables2Total Used6 8 Chairs2Available68Total Profit 70.00Adjustable CellsCell C 11 D 11NameProduction Quantity: TablesProduction Quantity: rge Bricks Total UsedSmall Bricks Total UsedFinalValue68ShadowPrice55ConstraintR.H. aintsCell E 7 E 8
Session #5Sensitivity AnalysisPage 5Net Profit from Tables 35A1234567891011BCDEFGTotal Used66 Available68The Lego Production ProblemProfitLarge BricksSmall BricksProduction QuantityTables 35.00Chairs 15.00Bill of Materials2122Tables3Chairs0Total Profit 105.00Adjustable CellsCell C 11 D 11NameProduction Quantity: TablesProduction Quantity: ient3515AllowableIncrease1E 302.5AllowableDecrease51E 30NameLarge Bricks Total UsedSmall Bricks Total UsedFinalValue66ShadowPrice17.50ConstraintR.H. Side68AllowableIncrease21E 30AllowableDecrease62ConstraintsCell E 7 E 8
Session #5Sensitivity AnalysisPage 6Using Solver Table to Investigate theEffect of Profit/Table on the FGTotal Used66 Available68The Lego Production ProblemProfitLarge BricksSmall BricksProduction QuantityProfit per Table 10 15 20 25 30 35 40C15Tables16 C11Tables 35.00Chairs 15.00Bill of 22000DChairs D11Total Profit 105.00Total Profit 105.00 60.00 60.00 70.00 80.00 90.00 105.00 120.00ETotal Profit G11Using Solver Table1. In the first column of the table, skip the first row and then enter the various trialvalues for the data cell that will be varied (the profit per table values rangingfrom 10 to 40 in B17:B23).2. In the first row of the table, enter equations referring to each output cell ofinterest (e.g., C11, D11, and G11 in C16:E16).3. Select the entire table (B16:E23) and choose Solver Table from the Tools menu.4. Specify the column input cell (the data cell that is being varied in the firstcolumn—the profit per table in cell C4 in this case), and click OK.For each trial value of the data cell in the first column, Solver is called on to re-solve theproblem, and the value of the output cells are filled into the table.
Session #5Sensitivity AnalysisPage 7Seven Large BricksA1234567891011BCDEFGTotal Used78 Available78The Lego Production ProblemProfitLarge BricksSmall BricksProduction QuantityTables 20.00Chairs 15.00Bill of Materials2122Tables3Chairs1Total Profit 75.00Adjustable CellsCell C 11 D 11NameProduction Quantity: TablesProduction Quantity: rge Bricks Total UsedSmall Bricks Total UsedFinalValue78ShadowPrice55ConstraintR.H. aintsCell E 7 E 8
Session #5Sensitivity AnalysisPage 8Nine Large BricksA1234567891011BCDEFGTotal Used88 Available98The Lego Production ProblemProfitLarge BricksSmall BricksProduction QuantityTables 20.00Chairs 15.00Bill of Materials2122Tables4Chairs0Total Profit 80.00Adjustable CellsCell C 11 D 11NameProduction Quantity: TablesProduction Quantity: nt2015AllowableIncrease1E 305AllowableDecrease51E 30NameLarge Bricks Total UsedSmall Bricks Total UsedFinalValue88ShadowPrice010ConstraintR.H. Side98AllowableIncrease1E 301AllowableDecrease18ConstraintsCell E 7 E 8
Session #5Sensitivity AnalysisPage 9Using Solver Table to Investigate theImpact of the Number of Large Bricks 5BCDEFGTotal Used68 Available68The Lego Production ProblemProfitLarge BricksSmall BricksProduction QuantityLarge Bricks2345678910Tables 20.00Chairs 15.00Bill of 234321000Total Profit 70.00Total Profit 70.00 30.00 45.00 60.00 65.00 70.00 75.00 80.00 80.00 80.00CDE15 Tables Chairs Total Profit16 C11 D11 G11Using Solver Table1. In the first column of the table, skip the first row and then enter the various trialvalues for the data cell that will be varied (the number of large bricks rangingfrom 2 to 10 in B17:B25).2. In the first row of the table, enter equations referring to each output cell ofinterest (e.g., C11, D11, and G11 in C16:E16).3. Select the entire table (B16:E23) and choose Solver Table from the Tools menu.4. Specify the column input cell (the data cell that is being varied in the firstcolumn—the number of large bricks in cell G7), and click OK.For each trial value of the data cell in the first column, Solver is called on to re-solve theproblem, and the value of the output cells are filled into the table.
Session #5Sensitivity AnalysisPage 10Two-Dimensional Solver TableA1234567891011121314151617181920BCDEFGTotal Used68 Available68HThe Lego Production ProblemProfitLarge BricksSmall BricksProduction QuantitySmall BricksTables 20.00Chairs 15.00Bill of Materials2122Tables2Chairs2 706789104 50 55 60 60 60Total Profit 70.005 55 60 65 70 75Large Bricks6 60 65 70 75 807 60 70 75 80 858 60 70 80 85 90C15 G11Using a Two-Dimensional Solver Table1. In the first row of the table, enter the various trial values for the first data cellthat will be varied (number of large bricks in D15:H15).2. In the first column of the table, enter the various trial values for the second datacell that will be varied (the number of small bricks in C16:C20).3. Enter an equations referring to the output cell of interest in the upper-left-handcorner of the table (e.g., G11 in cell C15).4. Select the entire table (C15:H20) and choose Solver Table from the Tools menu.5. Specify the row input cell (the data cell that is being varied in the first row) andthe column input cell (the data cell that is being varied in the first column), andclick OK.
Session #5Sensitivity AnalysisPage 11100% Rule for Simultaneous Changesin the Objective CoefficientsFor simultaneous changes in the objective coefficients, if the sum of the percentagechanges does not exceed 100%, the original solution will still be optimal.Adjustable CellsCell C 11 D 11NameProduction Quantity: TablesProduction Quantity: rge Bricks Total UsedSmall Bricks Total UsedFinalValue68ShadowPrice55ConstraintR.H. aintsCell E 7 E 8Examples: (Profit/Table 24)(Profit/Chair 13) (Profit/Table 25)(Profit/Chair 12) (Profit/Table 28)(Profit/Chair 18)
Session #5Sensitivity AnalysisPage 12100% Rule for Simultaneous Changesin the Right-Hand-SidesFor simultaneous changes in the right-hand-sides, if the sum of the percentagechanges does not exceed 100%, the shadow prices will still be valid.Adjustable CellsCell C 11 D 11NameProduction Quantity: TablesProduction Quantity: rge Bricks Total UsedSmall Bricks Total UsedFinalValue68ShadowPrice55ConstraintR.H. aintsCell E 7 E 8Examples: ( 1 Large Brick)( 2 Small Bricks) ( 1 Large Brick)(–1 Small Brick)
Session #5Sensitivity AnalysisPage 13Summary of Output from Computer SolutionChanging Cells:Final ValueThe value of the variable in the optimal solutionReduced CostIncrease in the objective function value per unitincrease in the value of a zero-valued variable (forsmall increases)—may be interpreted as the shadowprice for the nonnegativity constraint.AllowableIncrease/DecreaseDefines the range of the cost coefficients in theobjective function for which the current solution(value of the variables in the optimal solution) willnot change.Constraints:Final ValueThe usage of the resource in the optimal solution.Shadow priceThe change in the value of the objective function perunit increase in the right hand side of the constraint: Z (Shadow Price)( RHS)(Note: only valid if change is within the allowablerange for RHS values—see below.)ConstraintR.H. SideThe current value of the right hand side of theconstraint (the amount of the resource available).AllowableIncrease/DecreaseDefines the range of values of the RHS for whichthe shadow price is valid and hence for which thenew objective function value can be calculated.(NOT the range for which the current solution willnot change.)
3. Select the entire table (B16:E23) and choose Solver Table from the Tools menu. 4. Specify the column input cell (the data cell that is being varied in the first column—the profit per table in cell C4 in this case), and click OK. For each trial value of the data cell in
