Chapter 19 – Linear Programming
13. Formulate the model:
A = Quantity of Product A
B = Quantity of Product B
C = Quantity of Product C
A
B
C
Revenue
$80
$90
$70
Cost
Mat’l #1
2
x $ 5 = $10
1
x $ 5 = $ 5
6 x $ 5 = $30
Mat’l #2
3
x $ 4 = $12
5
x $ 4 = $20
Labor
3.2
x $10 = $32
1.5
x $10 = $15
2 x $10 = $20
Total
$54
$40
$50
Profit
$26
$50
$20
Maximize 26A + 50B + 20C (profit)
Subject to:
C1: Matl 1
2A
+ 1B
+ 6C
200 lb.
C2: Matl 2
3A
+ 5B
300 lb.
C3: Labor
3.2A
+ 1.5B
+ 2.0C
150 hr.
C4: A Output %
2/3A
– 1/3B
– 1/3C
0
C5: Ratio
2A
– 3B
= 0
C6: A Order
A
5
A, B, C 0
Notes on constraint development:
C4: Output of A should be less than or equal to 1/3 of the total produced.
A ≤ 1/3(A + B + C)
A ≤ 1/3A + 1/3B + 1/3C
A – 1/3A – 1/3B – 1/3C ≤ 0
2/3A – 1/3B – 1/3C ≤ 0
C5: The ratio of units of A to B should be 3 to 2.
=
2A = 3B
2A – 3B = 0
Chapter 19 – Linear Programming
19–52
The Excel Solver solution is shown below:
Formulas used:
Cell
Formula
B16
=(B4*B8)+(C4*C8)+(D4*D8)
B17
=(B4*B9)+(C4*C9)
B18
=(B4*B10)+(C4*C10)+(D4*D10)
B19
=(B4*B11)+(C4*C11)+(D4*D11)
B20
=(B4*B12)+(C4*C12)
B21
=(B4*B13)
F4
=(B4*B7)+(C4*C7)+(D4*D7)
Chapter 19 – Linear Programming
19–53
Solver Setup
Chapter 19 – Linear Programming
19–55
Formulas used:
Cell
Formula
B15
=(B4*B8)+(C4*C8)+(D4*D8)
B16
=(B4*B9)+(E4*E9)
B17
=(B4*B10)+(F4*F10)
B18
=(B4*B11)+(C4*C11)+(G4*G11)
I4
=(B4*B7)+(C4*C7)+(D4*D7)+(E4*E7)+(F4*F7)+(G4*G7)
Chapter 19 – Linear Programming
19–56
Solver Setup
19–57
15. a. As shown in the Excel Solver solution below, the C1: Machine and C3: Materials
constraints are binding.
b. As shown in the Sensitivity Report below, the range of optimality for the objective
function coefficient of Product 3 is from 15 – 1.5 to 15 +21 = 13.5 to 36. Therefore, an
increase from 15 to 22 would not change the value of the decision variables. However, the
not change either because the shadow price is 0 in the Sensitivity Report. However, there
will be 10 hours less slack. Thus, the new value of slack for C2: Labor would be 46 labor
hours.
e. If the manager decided that as many as 20 units of Product 2 could be produced (instead
of 16), the decision variables and the objective function would not change. The C4:
Product 2 constraint is not binding.
f. To determine if the changes are within the range for multiple changes, we first compute
the ratio of the amount of each change to the end of the range in the same direction.
Using the Sensitivity Report below, we see that the Allowable Increase for Product 1 =
$10.2, for Product 2 = $2, and for Product 3 = $21. The amount of increase for the profit
per unit of each product is $1. The ratios are:
Product 1: $1 / $10.2 = .098
19–58
Formulas used:
Cell
Formula
B14
=(B$4*B8)+(C$4*C8)+(D$4*D8)
B15
=(B$4*B9)+(C$4*C9)+(D$4*D9)
B16
=(B$4*B10)+(C$4*C10)+(D$4*D10)
B17
=C4*C11
F4
=(B4*B7)+(C4*C7)+(D4*D7)
Chapter 19 – Linear Programming
19–59
Solver Setup
Chapter 19 – Linear Programming
19–60