5_RMC_What If Analysis - Microsoft Excel 14.0 Answer Report...

Microsoft Excel 14.0 Answer Report
Worksheet: [Week_5_RMC_What If Analysis.xlsx]Sheet1
Report Created: 8/25/2015 10:22:38 AM
Result: Solver found a solution. All Constraints and optimality conditions are satisfied.
Solver Engine
Engine: Simplex LP
Solution Time: 0.062 Seconds.
Iterations: 2 Subproblems: 0
Solver Options
Max Time Unlimited, Iterations Unlimited, Precision 0.000001, Use Automatic Scaling
Max Subproblems Unlimited, Max Integer Sols Unlimited, Integer Tolerance 1%, Assume NonNegative
Objective Cell (Max)
Cell Name Original Value Final Value
$D$5 Profit Total 1600 1600
Variable Cells
Cell Name Original Value Final Value Integer
$B$3 Solution Fuel add 25 25 Contin
$C$3 Solution Solvent Base 20 20 Contin
Constraints
Cell Name Cell Value Formula Status Slack
$D$8 Material 1 LHS 20 $D$8<=$F$8 Binding 0
$D$9 Material 2 LHS 4 $D$9<=$F$9 Not Binding 1
$D$10 Material 3 LHS 21 $D$10<=$F$10 Binding 0

Microsoft Excel 14.0 Sensitivity Report
Worksheet: [Week_5_RMC_What If Analysis.xlsx]Sheet1
Report Created: 8/25/2015 10:22:38 AM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$3 Solution Fuel add 25 0 40 20 16
$C$3 Solution Solvent Base 20 0 30 20 10
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$D$8 Material 1 LHS 20 33.333333333 20 1.5 6
$D$9 Material 2 LHS 4 0 5 1.000E+030 1
$D$10 Material 3 LHS 21 44.444444444 21 9 2.25

x1
x2
Fuel add Solvent Base
Solution 25 20
Total
Profit $ 40.00 $ 30.00 $ 1,600.00
Constraints LHS Signs RHS
Material 1 0.4 0.5 20 <= 20
Material 2 0 0.2 4 <= 5
Material 3 0.6 0.3 21 <= 21

Microsoft Excel 14.0 Sensitivity Report
Worksheet: [Week_5_RMC_What If Analysis.xlsx]Sheet2
Report Created: 8/25/2015 10:38:04 AM
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficient Increase Decrease
$B$3 Solution Fuel add 35 0 40 1.000E+030 10
$C$3 Solution Solvent Base 0 -5 15 5 1.000E+030
Constraints
Final Shadow Constraint Allowable Allowable
Cell Name Value Price R.H. Side Increase Decrease
$D$8 Material 1 LHS 14 0 20 1.000E+030 6
$D$9 Material 2 LHS 0 0 5 1.000E+030 5
$D$10 Material 3 LHS 21 66.666666667 21 9 21

x1
x2
Fuel add Solvent Base
Solution 35 0
Total
Profit $ 40.00 $ 15.00 1400
Constraints LHS Signs RHS
Material 1 0.4 0.5 14 <= 20
Material 2 0 0.2 0 <= 5
Material 3 0.6 0.3 21 <= 21
We have a new solution, C2, the coefficient for the Solvent base was decrease beyond the acceptable range of 20 to 50
The new solution, wants only Fuel Additive as products

Microsoft Excel 14.0 Sensitivity Report
Worksheet: [Week_5_RMC_What If Analysis.xlsx]RMC RHS Change Example