Excel Solver Sensitivity Analysis PDF
Document Details
Uploaded by CheaperMemphis
McMaster University
Tags
Summary
This document contains an Excel-generated sensitivity report for a linear programming problem. It details the results of a Solver analysis, including objective cell values, variable cell values, and constraints. The output includes final values, reduced costs, shadow prices, constraint values, and allowable increases/decreases. Data on various parameters such as maximum chairs, minimum tables, carpentry hours, and painting hours are present. The report identifies binding and non-binding constraints of the optimization problem.
Full Transcript
# Microsoft Excel 16.0 Answer Report ## Worksheet: [Unsolved-Sensitivity Analysis.xlsx]Original Problem ### Report Created: 10/30/2024 8:49:04 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. ## Solver Engine Engine: Simplex LP Solution Time: 0.016 Secon...
# Microsoft Excel 16.0 Answer Report ## Worksheet: [Unsolved-Sensitivity Analysis.xlsx]Original Problem ### Report Created: 10/30/2024 8:49:04 AM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. ## Solver Engine Engine: Simplex LP Solution Time: 0.016 Seconds. Iterations: 3 Subproblems: 0 ## Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative ## Objective Cell (Max) | Cell | Name | Original Value | Final Value | |---|---|---|---| | $D$6 | Profit | $0.00 | $4,040.00 | ## Variable Cells | Cell | Name | Original Value | Final Value | Integer | |---|---|---|---|---| | $B$5 | Number of units Tables | 0 | 320 | Contin | | $C$5 | Number of units Chairs | 0 | 360 | Contin | ## Constraints | Cell | Name | Cell Value | Formula | Status | |---|---|---|---|---| | $D$10 | Maximum Chairs | 360 | 360$D$10<=$F$10 | Not Binding | | $D$11 | Minimum Tables | 320 | 320$D$11>=$F$11 | Not Binding | | $D$8 | Carpentry Hours | 2,400 | 2,400$D$8<=$F$8 | Binding | | $D$9 | Painting Hours | 1,000 | 1,000$D$9<=$F$9 | Binding | # Microsoft Excel 16.0 Sensitivity Report ## Worksheet: [Unsolved-Sensitivity Analysis.xlsx]Original Problem ### Report Created: 10/30/2024 8:49:05 AM ## Variable Cells | Cell | Name | Final Value | Reduced Cost | Objective Coefficient | Allowable Increase | |---|---|---|---|---|---| | $B$5 | Number of units Tables | 320 | 0 | 7 | 3 | | $C$5 | Number of units Chairs | 360 | 0 | 5 4.3333333333 | | | | | | | | ## Constraints | Cell | Name | Final Value | Shadow Price | Constraint R.H. Side | Allowable Increase | |---|---|---|---|---|---| | $D$10 | Maximum Chairs | 360 | 0 | 450 | 1E+030 | | $D$11 | Minimum Tables | 320 | 0 | 100 | 220 | | $D$8 | Carpentry Hours | 2400 | 0.6 | 2400 | 225 | | $D$9 | Painting Hours | 1000 | 2.6 | 1000 | 600 | ## Allowable Decrease - 3.25 - 1.5 ## Allowable Decrease - 90 - 1E+030 - 900 - 150 # Flair Furniture Company ## T ## Tables ## C ## Chairs | | | | |:---|:---|:---| | Number of units | 320 | 360 | | Profit | $7.0 | $5.0 | $4,040.00 | ## Constraints: - Carpentry Hours: 3 4 2,400 <= 2,400 - Painting Hours: 2 1 1,000 <= 1,000 - Maximum Chairs: 0 1 360 <= 450 - Minimum Tables: 1 0 320 >= 100 - LHS Sign RHS ## LP Formulation Maximize 7T+5C Subject to - 3T+4C≤2,400 (carpentry time) - 2T+ C≤1,000 (painting time) - C≤450 (maximum chairs allowed) - T≥100 (minimum tables required) - T, C≥0 (nonnegativity) # Microsoft Excel 16.0 Answer Report ## Worksheet: [Solved-Sensitivity Analysis1.xlsx]Original Problem‐RHS1700 ### Report Created: 2024-10-31 1:57:14 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. ## Solver Engine Engine: Simplex LP Solution Time: 0.031 Seconds. Iterations: 2 Subproblems: 0 ## Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative ## Objective Cell (Max) | Cell | Name | Original Value | Final Value | |---|---|---|---| | $D$6 | Profit | $5,340.00 | $5,600.00 | ## Variable Cells | Cell | Name | Original Value | Final Value | Integer | |---|---|---|---|---| | $B$5 | Number of units Tables | 720 | 800 | Contin | | $C$5 | Number of units Chairs | 60 | 0 | Contin | ## Constraints | Cell | Name | Cell Value | Formula | Status | |---|---|---|---|---| | $D$10 | Maximum Chairs | 0 | 0$D$10<=$F$10 | Not Binding | | $D$11 | Minimum Tables | 800 | 800$D$11>=$F$11 | Not Binding | | $D$8 | Carpentry Hours | 2,400 | 2,400$D$8<=$F$8 | Binding | | $D$9 | Painting Hours | 1,600 | 1,600$D$9<=$F$9 | Not Binding | # Microsoft Excel 16.0 Sensitivity Report ## Worksheet: [Solved‐Sensitivity Analysis1.xlsx]Original Problem‐RHS1700 ### Report Created: 2024-10-31 1:57:15 PM ## Variable Cells | Cell | Name | Final Value | Reduced Cost | Objective Coefficient | Allowable Increase | |---|---|---|---|---|---| | $B$5 | Number of units Tables | 800 | 0 | 7 | 1E+030 | | $C$5 | Number of units Chairs | 0 | -4.3333333333 | 5 4.3333333333 | ## Constraints | Cell | Name | Final Value | Shadow Price | Constraint R.H. Side | Allowable Increase | |---|---|---|---|---|---| | $D$10 | Maximum Chairs | 0 | 0 | 450 | 1E+030 | | $D$11 | Minimum Tables | 800 | 0 | 100 | 700 | | $D$8 | Carpentry Hours | 2400 | 2.3333333333 | 2400 | 150 | | $D$9 | Painting Hours | 1600 | 0 | 1700 | 1E+030 | ## Allowable Decrease - 3.25 - 1E+030 ## Allowable Decrease - 450 - 1E+030 - 2100 - 100 # Flair Furniture Company ## T ## Tables ## C ## Chairs | | | | |:---|:---|:---| | Number of units | 800 | 0 | | Profit | $7.0 | $5.0 | $5,600.00 | ## Constraints: - Carpentry Hours: 3 4 2,400 <= 2,400 - Painting Hours: 2 1 1,600 <= 1,700 - Maximum Chairs: 0 1 0 <= 450 - Minimum Tables: 1 0 800 >= 100 - LHS Sign RHS ## LP Formulation Maximize 7T+5C Subject to - 3T+4C≤2,400 (carpentry time) - 2T+ C≤1,000 (painting time) - C≤450 (maximum chairs allowed) - T≥100 (minimum tables required) - T, C≥0 (nonnegativity) The right-hand side of this constraint has been changed to 1700, which exceeds the allowable increase. As a result, the shadow price is no longer valid (it was 2.6 in the original problem). # Microsoft Excel 16.0 Answer Report ## Worksheet: [Solved-Sensitivity Analysis1.xlsx]Original Problem_T 15 ### Report Created: 2024-10-31 1:58:18 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. ## Solver Engine Engine: Simplex LP Solution Time: 0.016 Seconds. Iterations: 2 Subproblems: 0 ## Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative ## Objective Cell (Max) | Cell | Name | Original Value | Final Value | |---|---|---|---| | $D$6 | Profit | $7,500.00 | $7,500.00 | ## Variable Cells | Cell | Name | Original Value | Final Value | Integer | |---|---|---|---|---| | $B$5 | Number of units Tables | 500 | 500 | Contin | | $C$5 | Number of units Chairs | 0 | 0 | Contin | ## Constraints | Cell | Name | Cell Value | Formula | Status | |---|---|---|---|---| | $D$10 | Maximum Chairs | 0 | 0$D$10<=$F$10 | Not Binding | | $D$11 | Minimum Tables | 500 | 500$D$11>=$F$11 | Not Binding | | $D$8 | Carpentry Hours | 1,500 | 1,500$D$8<=$F$8 | Not Binding | | $D$9 | Painting Hours | 1,000 | 1,000$D$9<=$F$9 | Binding | # Microsoft Excel 16.0 Sensitivity Report ## Worksheet: [Solved-Sensitivity Analysis1.xlsx]Original Problem_T 15 ### Report Created: 2024-10-31 1:58:18 PM ## Variable Cells | Cell | Name | Final Value | Reduced Cost | Objective Coefficient | Allowable Increase | |---|---|---|---|---|---| | $B$5 | Number of units Tables | 500 | 0 | 15 | 1E+030 | | $C$5 | Number of units Chairs | 0 | -2.5 | 5 | 2.5 | ## Constraints | Cell | Name | Final Value | Shadow Price | Constraint R.H. Side | Allowable Increase | |---|---|---|---|---|---| | $D$10 | Maximum Chairs | 0 | 0 | 450 | 1E+030 | | $D$11 | Minimum Tables | 500 | 0 | 100 | 400 | | $D$8 | Carpentry Hours | 1500 | 0 | 2400 | 1E+030 | | $D$9 | Painting Hours | 1000 | 7.5 | 1000 | 600 | ## Allowable Decrease - 5 - 1E+030 ## Allowable Decrease - 450 - 1E+030 - 900 - 800 # Flair Furniture Company ## T ## Tables ## C ## Chairs | | | | |:---|:---|:---| | Number of units | 500 | 0 | | Profit | $15.0 | $5.0 | $7,500.00 | ## Constraints: - Carpentry Hours: 3 4 1,500 <= 2,400 - Painting Hours: 2 1 1,000 <= 1,000 - Maximum Chairs: 0 1 0 <= 450 - Minimum Tables: 1 0 500 >= 100 - LHS Sign RHS ## LP Formulation Maximize 7T+5C Subject to - 3T+4C≤2,400 (carpentry time) - 2T+ C≤1,000 (painting time) - C≤450 (maximum chairs allowed) - T≥100 (minimum tables required) - T, C≥0 (nonnegativity) The coefficient of T in the objective function has been changed to 15, making a case where the reduced cost is not zero. In the sensitivity report, we see that the reduced cost for chairs is -2.5. To achieve a value greater than 0 for chairs in the optimal solution, its coefficient needs to improve by at least the amount of the reduced cost. Therefore, if the coefficient becomes any more than 5 + |-2.5| = 7.5, the number of chairs would exceed 0 (in other words, C would enter the optimal solution). See the next worksheet for an example where we change the coefficient to 8. # Microsoft Excel 16.0 Answer Report ## Worksheet: [Solved‐Sensitivity Analysis1.xlsx]Original Problem‐T15,C8 ### Report Created: 2024-10-31 2:04:30 PM Result: Solver found a solution. All Constraints and optimality conditions are satisfied. ## Solver Engine Engine: Simplex LP Solution Time: 0.016 Seconds. Iterations: 3 Subproblems: 0 ## Solver Options Max Time Unlimited, Iterations Unlimited, Precision 0.000001 Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative ## Objective Cell (Max) | Cell | Name | Original Value | Final Value | |---|---|---|---| | $D$6 | Profit | $7,680.00 | $7,680.00 | ## Variable Cells | Cell | Name | Original Value | Final Value | Integer | |---|---|---|---|---| | $B$5 | Number of units Tables | 320 | 320 | Contin | | $C$5 | Number of units Chairs | 360 | 360 | Contin | ## Constraints | Cell | Name | Cell Value | Formula | Status | |---|---|---|---|---| | $D$10 | Maximum Chairs | 360 | 360$D$10<=$F$10 | Not Binding | | $D$11 | Minimum Tables | 320 | 320$D$11>=$F$11 | Not Binding | | $D$8 | Carpentry Hours | 2,400 | 2,400$D$8<=$F$8 | Binding | | $D$9 | Painting Hours | 1,000 | 1,000$D$9<=$F$9 | Binding | # Microsoft Excel 16.0 Sensitivity Report ## Worksheet: [Solved‐Sensitivity Analysis1.xlsx]Original Problem‐T15,C8 ### Report Created: 2024-10-31 2:04:30 PM ## Variable Cells | Cell | Name | Final Value | Reduced Cost | Objective Coefficient | Allowable Increase | |---|---|---|---|---|---| | $B$5 | Number of units Tables | 320 | 0 | 15 | 1 | | $C$5 | Number of units Chairs | 360 | 0 | 8 | 12 | ## Constraints | Cell | Name | Final Value | Shadow Price | Constraint R.H. Side | Allowable Increase | |---|---|---|---|---|---| | $D$10 | Maximum Chairs | 360 | 0 | 450 | 1E+030 | | $D$11 | Minimum Tables | 320 | 0 | 100 | 220 | | $D$8 | Carpentry Hours | 2400 | 0.2 | 2400 | 225 | | $D$9 | Painting Hours | 1000 | 7.2 | 1000 | 600 | ## Allowable Decrease - 9 - 0.5 ## Allowable Decrease - 90 - 1E+030 - 900 - 150 # Flair Furniture Company ## T ## Tables ## C ## Chairs | | | | |:---|:---|:---| | Number of units | 320 | 360 | | Profit | $15.0 | $8.0 | $7,680.00 | ## Constraints: - Carpentry Hours: 3 4 2,400 <= 2,400 - Painting Hours: 2 1 1,000 <= 1,000 - Maximum Chairs: 0 1 360 <= 450 - Minimum Tables: 1 0 320 >= 100 - LHS Sign RHS ## LP Formulation Maximize 7T+5C Subject to - 3T+4C≤2,400 (carpentry time) - 2T+ C≤1,000 (painting time) - C≤450 (maximum chairs allowed) - T≥100 (minimum tables required) - T, C≥0 (nonnegativity)
