Microsoft Excel 14.0 Sensitivity Report Variable Cells Final Reduced Objective A
ID: 399519 • Letter: M
Question
Microsoft Excel 14.0 Sensitivity Report
Variable Cells
Final
Reduced
Objective
Allowable
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
Decrease
$B$4
Units Producted Brand 1
8.4
0
2
2
0.5
$C$4
Units Producted Brand 2
4.8
0
3
1
1.5
Constraints
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$D$9
Ingredient C Left Hand Side
4.2
0
1.5
2.7
1E+30
$D$7
Ingredient A Left Hand Side
90
0.24
90
45
30
$D$8
Ingredient B Left Hand Side
48
0.2
48
24
13.5
a. For the optimal solution, how much should we buy for the brand 1 and brand 2?
b. How much ingredient A is used in actual?
c. What is max upper limit of ingredient B as the shadow price remains as 0.2?
d. Will the cost change if the ingredient C changes to 3 kg?
Microsoft Excel 14.0 Sensitivity Report
Variable Cells
Final
Reduced
Objective
Allowable
Allowable
Cell
Name
Value
Cost
Coefficient
Increase
Decrease
$B$4
Units Producted Brand 1
8.4
0
2
2
0.5
$C$4
Units Producted Brand 2
4.8
0
3
1
1.5
Constraints
Final
Shadow
Constraint
Allowable
Allowable
Cell
Name
Value
Price
R.H. Side
Increase
Decrease
$D$9
Ingredient C Left Hand Side
4.2
0
1.5
2.7
1E+30
$D$7
Ingredient A Left Hand Side
90
0.24
90
45
30
$D$8
Ingredient B Left Hand Side
48
0.2
48
24
13.5
Explanation / Answer
Ans a)
For the optimal solution, we should buy 8.4 units of Brand 1 and 4.8 units of Brand 2.
Ans b)
90 units of ingredient A are used in actual. It can be seen from the final value column of the constraints table.
Ans c)
Ingredient B has an allowable increase of 24 at a shadow price of 0.2. Hence the max upper limit of ingredient B is the sum of the final value of ingredient B and the allowable increase, i.e. 48 + 24 = 72 units.
Ans d)
The allowable decrease of ingredient C is 1E+30 or 1030 and the shadow price is 0. Hence if the ingredient C changes to 3 kg, the value of the objective function (cost) will not change.
This is because a decrease of 1.2 kg in the final value of ingredient C falls within the allowable decrease.