Q7.14 Solution:
Let X 1 = number of air conditioner to be produced X 2 = number of large fans produced
Maximize profit
Z = $25 X 1 + $15 X 2
Subject to 3 X 1+2 X 2≤ 240
(available wiring)
2 X 1+ 1 X 2≤ 140
(available drilling)
X 1, X 2 ≥ 0
(non-negativity)
Graphic solution 3 X 1+2 X 2 = 240,
X 1 = 0, X 2=120 X 2 = 0, X 1 = 80
2 X 1+ X 2 = 140,
X 1 = 0, X 2= 140 X 2 = 0, X 1 = 70
Isoprofit line method Z = 1800,
X 1 = 0, X 2 = 120 X 2 = 0, X 1 = 72
Compare corner points Point a: X 1=0, X 2=0
Z = $ 2 5 * 0 + $ 15 * 0
= $0
Point b: X 1 = 0, X 2=120
Z = $25*0+ $15*25
= $1800
Optimal solution:
X 1=40, X 2=60
Z = $25 $25×40 + $1 $15×60 5×60
= $19 $1900 00
Q7.17 Solution:
Let B = numbe numberr of benc benches hes to to be produ produced ced T = number of picnic tables to be produced Max profit
Z = $9B + $20T
Subject to 4B + 6T ≤ 1200
(available labor hours)
10B + 35T ≤ 3500 (available stock) B, T ≥ 0
(non-negativity)
Graphic solution 4B + 6T = 1200,
B = 0, T = 20 200 T = 0, B = 300
10B + 35T 35T = 3500, 3500, B = 0, T = 100 T = 0, B = 350 Isoprofit line method Z = 1800,
B = 0 , T = 90 T = 0, B = 200
Compare corner points Point a: B = 0, T = 100
Z = $9*0 + $20*100
Point b: B = 262.5, T = 25
Z = $9*262.5 + $20*25 =$2862.5
Point c: B =3000, T = 0
Z = $ 9 *3 0 0 + $ 2 0 * 0
Optimal solution:
B = 262.5, T = 25 Z = $9×262.5 + $20×25 = $2862.5 Q8.2
= $ 20 00
= $ 2 7 00
T be dollars invested in Thompson Electronics U be dollars invested in United Aerospace P be dollars invested in Palmer Drugs H be dollars invested in Happy Days Nursing Homes Maximize return
Z = 0.053L + 0.068T + 0.049U + 0.084P + 0.118H
Subject to 1.
Municipal bonds L ≥ 0.2(L+T+U+P+H) 0.8L−0.2T−0.2U−0.2P−0.2H ≥ 0
2.
Combination of electronics, aerospace, and drugs T+U+P ≥ 0.4(L+T+U+P+H)
3.
−0.4L+0.6T+0.6U+0.6P −0.4H ≥ 0
Nursing home as percent of bonds H ≤ 0 .5L
4.
H−0.5L ≤ 0
Fund to be invested L+T+U+P+H ≤ 250,000
5.
Non-negativity L, T, U, P, H ≥ 0
(b)
Solved by Excel: Heinlein and Krampf Brokerage
L T U Los Angeles Thompson United $ invested Return
$50,000.00 5.30%
$0.00 6.80%
1 -0.5 -0.4 0.8
1 0 0.6 -0.2
P Palmer
H Happy Days
$0.00 $175,000.00 $25,000.00 4.90% 8.40% 11.80%
$20,300.00 <- Objective
Constraints
Total Nursing Electr, Aero, Drug Muni Bonds
1 0 0.6 -0.2
1 0 0.6 -0.2
1 1 -0.4 -0.2
Total returns = $20,300
$50,000 invested in Los Angeles municipal bonds(L = 50,000)
250000.0 0.0 75000.0 0.0
<= <= >= >=
250000 0 0 0
LHS
Sign
RHS
No investment in Thompson Electronics and United Aerospace (T, U = 0) $175,000 invested in Palmer Drugs (P =175,000) $25,000 invested in Happy Days Nursing Homes (H = 25,000)
Q8.3 Solution:
(for i = 1,2,3,4,5,6) Minimize staff size Z = X1 +X2 +X3 +X4+X5 +X6 Subject to 1.
2.
Number of waiters and busboys required for each period X6 + X1 ≥ 3
(3am – 7am)
X1 + X2 ≥ 12
(7am – 11am)
X2 + X3 ≥ 16
(11am – 3pm)
X3 + X4 ≥ 9
(3pm – 7pm)
X4 + X5 ≥ 11
(7pm – 11pm)
X5 + X6 ≥ 4
(11pm – 3am)
Non-negativity Xi ≥ 0 (for i = 1,2,3,4,5,6)
11pm
3am 4
11 3
7 pm
7am 9
12 16
3pm
Q8.4
11am
Let A be the number of pounds of oat product per horse each day G be the number of pounds of enriched grain per horse each day M be the number of pounds of mineral product per horse each day Minimize cost Z = 0.09A +0.014G + 0.17M Subject to 1.
Min daily requirement on ingredients 2A + 3G + 1M
≥6
(ingredient A)
0.5A+ 1G + 0.5M
≥2
(ingredient B)
3A + 5G + 6M
≥9
(ingredient C)
1A + 1.5G + 2M
≥8
(ingredient D)
0.5A + 0.5G + 1.5M ≥ 5
(ingredient E)
2. Maximum feed per day A+G+M≤ 6
3. Non-negativity A, G, M ≥ 0 Solved by computer: Battery Park Stable
Number of Pounds Cost
A G Oat Grain 1.333 0.000 $0.09 $0.14
M Mineral 3.333 $0.17
0.6867
1 0.5 6 2 1.5 1
6.0 2.3 24.0 8.0 5.7 4.7
>= >= >= >= >= <=
6 2 9 8 5 6
LHS
Sign
RHS
<- Objective
Constraints:
Ingredient A Ingredient B Ingredient C Ingredient D Ingredient E Max feed
2 0.5 3 1 0.5 1
3 1 5 1.5 0.5 1
Total Cost = $0.6867
The optimal daily mix consists of 1.3333 pounds of oat product, no enriched 3.3333 pounds of mineral product per house each day.
grain and
Q8.14 Solution: (LP Formulation) (a)
Let X ij be the acres of crop i planted on parcel j (for i = W: wheat, A: alfalfa, B: barley) (for j =1 : SE, 2: N, 3: NW, 4: W, 5: SW) Profit from Wheat Maximize profit
Z= $2(50) ( X W1 + X W2 + X W3 + X W4 + X W5) Profit from Alfalfa
+ $40(1.5) ( X A1 + X A2 + X A3 + X A4 + X A5) + $50(2.2) ( X B1 + X B2 + X B3 + X B4 + X B5)
Profit from Barley
Subject to: 1.
Irrigation limits (in term of acre-feet): 1.6 X W1 + 2.9 X A1 + 3.5 X B1 ≤ 3,200
(acre-feet in SE)
1.6 X W2 + 2.9 X A2 + 3.5 X B2 ≤ 3,400
(acre-feet in N)
1.6 X W3 + 2.9 X A3 + 3.5 X B3 ≤ 800
(acre-feet in NW)
1.6 X W4 + 2.9 X A4 + 3.5 X B4 ≤ 500
(acre-feet in W)
1.6 X W5 + 2.9 X A5 + 3.5 X B5 ≤ 600
(acre-feet in SW)
1.6( X W1 + X W2 + X W3 + X W4 + X W5) + 2.9( X A1 + X A2 + X A3 + X A4 + X A5) +3.5( X B1 + X B2 + X B3 + X B4 + X B5)
≤
7,400 (Total water acre-feet)
2. Sales limited (in term of acres): X W1 + X W2 + X W3 + X W4 + X W5 ≤ 2,200
(110,000 bushels/ 50 bushels per acre)
X A1 + X A2 + X A3 + X A4 + X A5 ≤ 1,200
(1,800 tons/1.5 tons per acre)
X B1 + X B2 + X B3 + X B4 + X B5 ≤ 1,000
(2,200 tons/2.2 tons per acre)
3.
Area availability (in term of acres): X W1 + X A1 + X B1
≤
2,000
(acre in SE)
X W2 + X A2 + X B2
≤
2,300
(acre in N)
X W3 + X A3 + X B3
≤
600
(acre in NW)
X W4 + X A4 + X B4
≤
1,100
(acre in W)
X W5 + X A5 + X B5
≤
500
(acre in SW)
4. Non-negativity
Margaret Black’s Farmland
X11
X12
X13
X14
X15
X21
X22
X23
X24
X25
X31
X32
X33
X34
X35
Wheat Wheat Wheat Wheat Wheat Alfalfa Alfalfa Alfalfa Alfalfa Alfalfa Barley Barley Barley Barley Barley SE N NW W SW SE N NW W SW SE N NW W SW Number of acres 1762.5 437.5 Profit $100 $100
0.0 $100
0.0 $100
0.0 $100
131.0 $60
0.0 $60
0.0 $60
0.0 $60
0.0 $60
0.0 $110
771.4 228.6 0.0 $110 $110 $110
0.0 $110 $337,862.07 <-- Objective
Constraints:
SE acreage N acreage NW acreage W acreage SW acreage Wheat acreage Alfalfa acreage Barley acreage SE water N water NW water W water SW water Total water
1
1 1
1 1
1
1
1 1
1
1
1
1
1 1
1.6
1 1
1
1
1
1
1 3.5 2.9
1.6
1.6
1
1.6
1
2.9
2.9
2.9
2.9
3.5 2.9 2.9
3.5
(b)
Total Profit: $337,862.07
Plant 1,762.5 acres of wheat in SE parcel
Plant 437.5 acres of wheat in N parcel ( X W2 = 437.5)
Plant 131 acres of alfalfa in SE parcel ( X A1 = 131)
Plant 771.4 acres of barley in N parcel ( X B2 = 771.4)
Plant 228.6 acres of barley in NW parcel
Multiple optimal solution exist (c)
Yes, need only 500 more water-feet.
1
3.5 2.9
1.6 1.6
1
3.5 2.9
1.6 1.6
1
1
2.9 1.6
1.6
1 1
( X W1 = 1,762.5)
( X B3 = 228.6)
3.5
3.5
3.5
3.5 3.5
1893.5 1208.9 228.6 0.0 0.0 2200.0 131.0 1000.0 3200.0 3400.0 800.0 0.0 0.0 7400.0
<= <= <= <= <= <= <= <= <= <= <= <= <= <=
2000 2300 600 1100 500 2200 1200 1000 3200 3400 800 500 600 7400
LHS
Sign
RHS
(b)
Total Profit: $337,862.07
Plant 1,762.5 acres of wheat in SE parcel
Plant 437.5 acres of wheat in N parcel ( X W2 = 437.5)
Plant 131 acres of alfalfa in SE parcel ( X A1 = 131)
Plant 771.4 acres of barley in N parcel ( X B2 = 771.4)
Plant 228.6 acres of barley in NW parcel
Multiple optimal solution exist
( X W1 = 1,762.5)
( X B3 = 228.6)
(c)
Yes, need only 500 more water-feet.
Q8.12 Solution: (a)
Let
I be the number of units of internal modems produced per week E be the number of units of external modems produced per week C be the number of units of circuit boards produced per week
F be the number of units of floppy disk drives modems produced per week H be he number of units of hard drives produced per week M be the number of units of memory boards produced per week Maximize profit = Revenue – material cost – test cost Max
Revenue
Z = 200I + 120E + 180C + 130F + 430H + 260M − 35I – 25E – 40C – 45F – 170H – 60M −( −( −(
15 60 12 60 18 60
Material cost
) (7I + 3E + 12C + 6F + 18H + 17M) ) (2I + 5E + 3C + 2F + 15H + 17M)
Test cost (in terms of minutes)
) (5I + 1E + 3C + 2F + 9H + 2M)
( i.e. 161.35I + 92.95E + 135.5C + 82.5F +249.8H + 191.75M ) Subject to
1.
Time limit (in terms of minutes)
7I + 3E + 12C + 6F + 18H + 17M ≤ 7,200 2I + 5E + 3C + 2F + 15H + 17M
≤
7,200
(= 120*60mins) (Test device 1) (= 120*60mins) (Test device 2)
5I + 1E + 3C + 2F + 9H + 2M ≤ 6,000 2.
(= 100*60mins) (Test device 3)
Non-negativity I, E, C, F, H, M ≥ 0
(b) Quitmeyer Electronics
I E C Internal External Circuit modems modems boards 0.00 $180 $40 $4.50
F Floppy drives 0.00 $130 $45 $2.50
H M Hard Memory drives boards
Solution value Selling price per unit Material cost per unit Labor cost per unit
496.55 1241.38 $200 $120 $35 $25 $3.65 $2.05
0.00 $430 $170 $10.20
0.00 $260 $60 $8.25
Profit
$161.35 $92.95 $135.50 $82.50 $249.80 $191.75 $195,504.83 <-- Objective
$248,275.86 <-- Revenue $48,413.79 <-- Matl Cost $4,357.24 <-- Labor Cost
Constraints
Cost
Test device 1 Tets device 2 Test device 3
7 2 5
3 5 1
12 3 3
6 2 2
18 15 9
17 17 2
Total Profit = $195,504.83
Produce 496.55 internal modems (I = 496.55)
Produce 1,241.38 external modems (E = 1,241.38)
7200.00 7200.00 3724.14
<= <= <=
7200 7200 6000
LHS
Sign
RHS
Do not produce any circuit boards, floppy drives, hard drives and memory boards (C,F,H,M=0)
(c) Microsoft Excel 10.0 Answer Report Worksheet: [P3-10.xls]P3-10
Target Cell (Max) Cell
$H$9 Profit
Name
Original Value
$0.00
Final Value
$195,504.83
$15 $12 $18
Adjustable Cells Cell
$B$5 $C$5 $D$5 $E$5 $F$5 $G$5
Name
Original Value
Solution value Internal modems Solution value External modems Solution value Circuit boards Solution value Floppy drives Solution value Hard drives Solution value Memory boards
Final Value
0.00 0.00 0.00 0.00 0.00 0.00
496.55 1241.38 0.00 0.00 0.00 0.00
Constraints Cell
Name
Cell Value
$H$11 Test device 1 $H$12 Tets device 2 $H$13 Test device 3
Formula
Status
Slack
7200.00 $H$11<=$J$11 Binding 0 7200.00 $H$12<=$J$12 Binding 0 3724.14 $H$13<=$J$13 Not Binding 2275.862069
Microsoft Excel 10.0 Sensitivity Report Worksheet: [P3-10.xls]P3-10 Report Created: 29/1/2004 11:31:03
Adjustable Cells Cell
$B$5 $C$5 $D$5 $E$5 $F$5 $G$5
Name
Solution value Internal modems Solution value External modems Solution value Circuit boards Solution value Floppy drives Solution value Hard drives Solution value Memory boards
Final Reduced Objective Value Cost Coefficient
496.55 1241.38 0.00 0.00 0.00 0.00
0.00 0.00 -138.64 -57.44 -221.73 -269.86
161.35 92.95 135.5 82.5 249.8 191.75
Allowable Increase
Allowable Decrease
55.53333333 69.40833333 310.425 23.8 138.637931 1E+30 57.44137931 1E+30 221.7275862 1E+30 269.8586207 1E+30
Constraints Cell
$H$11 Test device 1 $H$12 Tets device 2 $H$13 Test device 3
Name
Final Value
Shadow Constraint Price R.H. Side
7200.00 7200.00 3724.14
21.41 5.74 0.00
Allowable Increase
Allowable Decrease
7200 2869.565217 2880 7200 4800 5142.857143 6000 1E+30 2275.862069
The value of an additional minute in test device 1 is $ 21.41, test device 2 is 5.74 and test device 3 is 0. Therefore, Quitmeyer Electronics should no more than 2869.56 minutes to test device 1, 4800 minutes to test device 2 only. Q4.13 Solutions:
LP model
Let T be number of 1-minute TV spots selected each week. N be number of full-page daily newspaper ads selected each week. P be number of 30-second prime-time radio spots selected each week. A be number of 1-minute afternoon radio spots selected each week.
Maximize audience coverage
Z = 5000 T + 8500 N + 2400 P +2800 A
subject to: T ≤ 12
(maximum TV spots/week)
N ≤ 5
(maximum newspaper ads/week)
P ≤ 24
(maximum
30-second
radio
spots/week) A ≤ 20
(maximum 1-minute radio spots/week)
800T + 925N + 290P + 380A ≤ 8000
(budget)
P +A≥ 5
(min radio spots contracted)
290P + 380A ≤ 1800
(max dollars spent on radio)
T, N, P, A ≥ 0
(non-negativity)
Use the Sensitivity Report for this LP model to answer the following questions. Each question is independent of the others. Win Big Gambling Club
T
N
P
A
TV spots Newspape Part-time Afternoon r radio radio Number of Units
1.97
5.00
6.21
0.00
Audience
5000
8500
2400
2800
67240.3 <-objective
Constraints
Max TV
1
Max Newspaper
1
Max Prime-Time Radio
1
Max Afternoon Radio Budget Max Radio $ Min Radio Spots
1 $800.00
$925.00
1.97 <=
12
5.00 <=
5
6.21 <= 0.00 <=
25 20
$290.00 $380.00 8000.00 <= $8,000 $290.00 $380.00 1800.00 <= $1,800 1 1 6.21 >= 5 LHS
Adjustable Cells
Sign
RHS
Cell
Name
$B$5 $C$5 $D$5 $E$5
Number of Units TV spots Number of Units Newspaper ads Number of Units Prime-time radio spots Number of Units Afternoon radio spots
Final Value
1.97 5.00 6.21 0.00
Reduced Cost
Objective Coefficient
Allowable Increase
Allowable Decrease
0.00 0.00 0.00 -344.83
5000.00 8500.00 2400.00 2800.00
1620.69 1E+30 1E+30 344.83
Shadow Price
Constraint R.H. Side
Allowable Increase
Allowable Decrease
5000.00 2718.75 263.16 1E+30
Constraints Cell
$F$1 4 $F$8 $F$9 $F$1 0 $F$11 $F$1 2 $F$1 3
Name
Final Value
Min Radio Spots
6.21
0.00
5.00
1.21
1E+30
Max TV Max Newspaper Max Prime-Time Radio
1.97 5.00 6.21
0.00 2718.75 0.00
12.00 5.00 25.00
1E+30 1.70 1E+30
10.03 5.00 18.79
Max Afternoon Radio Budget
0.00 8,000.00
0.00 6.25
20.00 8000.00
1E+30 8025.00
20.00 1575.00
Max Radio $
1,800.00
2.03
1800.00
1575.00
350.00
Microsoft Excel 10.0 Answer Report Worksheet: [LP_2.xls]media
Target Cell (Max) Cell
$F$6
Name
Original Value
Audience
Final Value
0.00
67240.30
Adjustable Cells Cell
$B$5 $C$5 $D$5 $E$5
Name
Original Value
Number of Units TV spots Number of Units Newspaper ads Number of Units Prime-time radio spots Number of Units Afternoon radio spots
Final Value
0.00 0.00 0.00 0.00
1.97 5.00 6.21 0.00
Constraints Cell
$F$14 $F$8 $F$9 $F$10 $F$11 $F$12 $F$13
Name
Cell Value
Min Radio Spots Max TV Max Newspaper Max Prime-Time Radio Max Afternoon Radio Budget Max Radio $
Formula
6.21 $F$14>=$H$14 1.97 $F$8<=$H$8 5.00 $F$9<=$H$9 6.21 $F$10<=$H$10 0.00 $F$11<=$H$11 $8,000.00 $F$12<=$H$12 $1,800.00 $F$13<=$H$13
Status
Not Binding Not Binding Binding Not Binding Not Binding Binding Binding
Slack
1.21 10.03125 0 18.79310345 20 0 0
Microsoft Excel 10.0 Sensitivity Report Worksheet: [LP_2.xls]media
Adjustable Cells Final Value
Reduced Objective Cost Coefficient
Cell
Name
$B$5
Number of Units TV spots
1.97
0.00
$C$5 $D$5 $E$5
Number of Units Newspaper ads Number of Units Prime-time radio spots Number of Units Afternoon radio spots
5.00 6.21 0.00
0.00 0.00 -344.83
Allowable Increase
5000 1620.689655
Allowable Decrease
5000
8500 1E+30 2718.75 2400 1E+30 263.1578947 2800 344.8275862 1E+30
Constraints Cell
$F$14 $F$8 $F$9 $F$10 $F$11 $F$12 $F$13
Name
Min Radio Spots Max TV Max Newspaper Max Prime-Time Radio Max Afternoon Radio Budget Max Radio $
Final Value
6.21 1.97 5.00 6.21 0.00 $8,000.00 $1,800.00
Shadow Price
0.00 0.00 2718.75 0.00 0.00 $6.25 $2.03
Constraint R.H. Side
Allowable Increase
Allowable Decrease
5 1.206896552 1E+30 12 1E+30 10.03125 5 1.702702703 5 25 1E+30 18.79310345 20 1E+30 20 8000 8025 1575 1800 1575 350
$C$5 $D$5 $E$5
Number of Units Newspaper ads Number of Units Prime-time radio spots Number of Units Afternoon radio spots
5.00 6.21 0.00
0.00 0.00 -344.83
8500 1E+30 2718.75 2400 1E+30 263.1578947 2800 344.8275862 1E+30
Constraints Cell
$F$14 $F$8 $F$9 $F$10 $F$11 $F$12 $F$13
Name
Min Radio Spots Max TV Max Newspaper Max Prime-Time Radio Max Afternoon Radio Budget Max Radio $
Final Value
6.21 1.97 5.00 6.21 0.00 $8,000.00 $1,800.00
Shadow Price
0.00 0.00 2718.75 0.00 0.00 $6.25 $2.03
Constraint R.H. Side
Allowable Increase
Allowable Decrease
5 1.206896552 1E+30 12 1E+30 10.03125 5 1.702702703 5 25 1E+30 18.79310345 20 1E+30 20 8000 8025 1575 1800 1575 350
(a) Spending $200 more on radio advertising is within the allowable increase (1575). The shadow price would remain unchanged. The audience coverage would increase the by(=2.03*200) to 67646.3 (b) No. Since we are already placing 6.21 radio spots, this contractual agreement is not a binding constraint. (c) The optimal solution will not change since 3100 audience reached per ad (increase of 300 of the objective function coefficient value) is within the allowable increase (344.83) The audience reached for each afternoon radio spot would have to increase to at least 3144.83(=2800+344.83) in order for these spot to become attractive. (refer to reduce cost definition)
(a) Spending $200 more on radio advertising is within the allowable increase (1575). The shadow price would remain unchanged. The audience coverage would increase the by(=2.03*200) to 67646.3 (b) No. Since we are already placing 6.21 radio spots, this contractual agreement is not a binding constraint. (c) The optimal solution will not change since 3100 audience reached per ad (increase of 300 of the objective function coefficient value) is within the allowable increase (344.83) The audience reached for each afternoon radio spot would have to increase to at least 3144.83(=2800+344.83) in order for these spot to become attractive. (refer to reduce cost definition) (d) Currently
5000
audience
reached
per
TV
spot.
(the
objective
function
coefficient=5000). Optimal number of TV spots would remain unchanged if the number of audience reach per TV spot is between 0 and 6620.69. Obviously, if the objective function coefficient is 0, it is not worthwhile to use any TV spots. Q4.16 Solutions
Battery Park Stable
Number of Pounds Cost
A Oat 1.333 $0.09
G Grain 0.000 $0.14
M Mineral 3.333 $0.17
$0.69
2 0.5 3 1 0.5 1
3 1 5 1.5 0.5 1
1 0.5 6 2 1.5 1
6.0 2.3 24.0 8.0 5.7 4.7
>= >= >= >= >= <=
6 2 9 8 5 6
LHS
Sign
RHS
<- Objective
Constraints:
Ingredient A Ingredient B Ingredient C Ingredient D Ingredient E Max feed
Microsoft Excel 9.0 Answer Report Worksheet: [P4-16.xls]P3-4
Target Cell (Min) Cell
$E$6
Name
Original Value
Cost
$0.00
Final Value
$0.69
Adjustable Cells Cell
$B$5 $C$5 $D$5
Name
Original Value
Number of Pounds Oat Number of Pounds Grain Number of Pounds Mineral
0.000 0.000 0.000
Final Value
1.333 0.000 3.333
Constraints Cell
$E$13 $E$8 $E$9 $E$10 $E$11 $E$12
Name
Cell Value
Max feed Ingredient A Ingredient B Ingredient C Ingredient D Ingredient E
Formula
4.67 $E$13<=$G$13 6.00 $E$8>=$G$8 2.33 $E$9>=$G$9 24.00 $E$10>=$G$10 8.00 $E$11>=$G$11 5.67 $E$12>=$G$12
Status
Not Binding Binding Not Binding Not Binding Binding Not Binding
Slack
1.333 0.000 0.333 15.000 0.000 0.667
Microsoft Excel 9.0 Sensitivity Report Worksheet: [P4-16.xls]P3-4
Adjustable Cells Cell
$B$5 $C$5 $D$5
Name
Number of Pounds Oat Number of Pounds Grain Number of Pounds Mineral
Final Reduced Objective Value Cost Coefficient
1.33 0.00 3.33
0.000 0.005 0.000
0.09 0.14 0.17
Allowable Increase
0.003 1E+30 0.010
Allowable Decrease
0.005 0.005 0.125
Constraints Cell
$E$13 $E$8 $E$9 $E$10 $E$11 $E$12
Name
Max feed Ingredient A Ingredient B Ingredient C Ingredient D Ingredient E
Final Shadow Constraint Value Price R.H. Side
4.67 6.00 2.33 24.00 8.00 5.67
0.000 0.003 0.000 0.000 0.083 0.000
6.00 6.00 2.00 9.00 8.00 5.00
Allowable Increase
1E+30 4.000 0.333 15.000 4.000 0.667
Allowable Decrease
1.333 2.000 1E+30 1E+30 0.800 1E+30
(a) The decrease of $0.01 per pound is outside the allowable decrease (0.005). Therefore the optimal solution will change. We can resolve the LP model to determine the new cost.
(b) The constraints prescribing the minimum daily requirement for ingredient A and ingredient D are binding (i.e 2A +3G +1M ≥ 6 & 1A +0.5G +2M ≥ 8 ) For each additional unit of ingredient A required in the mix, the cost will increase by $0.0033. For each additional unit of ingredient D required in the mix, the cost will increase by $0.0833. (c) A 20% decrease in the cost of mineral implies the cost will decrease by $0.034 (=0.17*0.2), which is less the allowable decrease (0.125). The optimal solutions remain unchanged. The revised cost will be decrease by 0.1133 (=0.034*3.3333) to $0.5734 (=0.6867-0.1133) (d) The price of oats can fluctuate between $0.085 and $0.0933 per pound for the current solution to remain optimal.
Q4.18 Solutions:
Quitmeyer Electronics I E C Internal External Circuit modems modems boards Solution value Selling price per unit Material cost per unit Labor cost per unit Profit
F Floppy drives
H M Hard Memory drives boards
496.55 1241.38 0.00 0.00 0.00 0.00 $200 $120 $180 $130 $430 $260 $248,275.86 <-- Revenue $35 $25 $40 $45 $170 $60 $48,413.79 <-- Matl Cost $3.65 $2.05 $4.50 $2.50 $10.20 $8.25 $4,357.24 <-- Labor Cost $161.35 $92.95 $135.50 $82.50 $249.80 $191.75 $195,504.83 <-- Objective
Constraints
Test device 1 Tets device 2 Test device 3
Adjustable Cells
Cost
0.117 0.033 0.083
0.050 0.083 0.017
0.200 0.050 0.050
0.100 0.033 0.033
0.300 0.250 0.150
0.283 0.283 0.033
120.00 120.00 62.07
<= <= <=
120 120 100
LHS
Sign
RHS
$15 $12 $18
Cell
Name
Final Value
Reduced Cost
Objective Coefficient
$B$5
Solution value Internal modems
496.55
0.00
161.35
$C$5 $D$5 $E$5
Solution value External modems Solution value Circuit boards Solution value Floppy drives
1241.38 0.00 0.00
0.00 -138.64 -57.44
92.95 135.5 82.5
$F$5
Solution value Hard drives
0.00
-221.73
249.8
$G$5
Solution value Memory boards
0.00
-269.86
191.75
Allowable Increase 55.5333333 3 310.425 138.637931 57.4413793 1 221.727586 2 269.858620 7
Allowable Decrease 69.40833333 23.8 1E+30 1E+30 1E+30 1E+30
Constraints Cell $H$1 1 $H$1 2 $H$1 3
Name
Final Value
Shadow Price
Constraint R.H. Side
Test device 1
120.00
1284.52
120
Test device 2
120.00
344.69
Test device 3
62.07
0.00
Allowable Increase
Allowable Decrease
120
47.8260869 6 80
48 85.71428571
100
1E+30
37.93103448
(a) Four products (Circuit boards, Floppy drives, Hard drives, Memory boards) The reduced costs indicate the minimum amount by which the profit contributions of these products must increase before they would be included in the production mix. For example, the profit contribution of a circuit board should increase by at least $138.64 (from the current $ 135.5 to at least $274.14) before it becomes a viable product.
(b) The current production plan required only 3724.14 minutes of the available 6000 minutes on test device 3. Also, taking 2100 minutes (=35 hours*60) on test device 3 (decrease of 2100) is within the allowable decrease (2275.86). The optimal solution would not be affected.
(c) Since the additional of 1200 minutes (= 20 hours*60) on test device 1 is within the allowable increase (2275.86), the shadow price remains unchanged. (i.e. for each additional minutes of time on test device 1 increase, the profit would increase by $21.41)
Refer to Q8.12, the time on test device 1 cost only $0.25 per minutes (i.e. $15 per hour). The 20 additional hours at a cost of $25 per hour will therefore increase the profit by $25692 (= $21.41*60*20) less than the cost of $500(= will be $220,696.83 ($195,504.83 + $21.41 – $500).
$25 60
*60*20). The new profit
Q7.14
The Electrocomp Corporation manufactures two electrical products: air conditioners and large fans. The assembly process for each is similar in that both require a certain amount of wiring and drilling. Each air conditioner takes 3 hours of wiring and 2 hours of drilling. Each fan must go through 2 hours of wiring and 1 hour of drilling. During the next production period, 240 hours of wiring time are available and up to 140 hours of drilling time may be used. Each air conditioner sold yields a profit of $25. Each fan assembled may be sold for a $15 profit. Formulate and solve this LP production mix situation to find the best combination of air conditioners and fans that yields the highest profit. Use the corner point graphical approach.
Q7.17
The Outdoor Furniture Corporation manufactures two products, benches and picnic tables, for use in yards and parks. The firm has two main resources: its carpenters (labor force) and a supply of redwood for use in the furniture. During the next production cycle, 1,200 hours of labor are available under a union agreement. The firm also has a stock of 3,500 feet of good-quality redwood.
Each bench that Outdoor Furniture produces
requires 4 labor hours and 10 feet of redwood; each picnic table takes 6 labor hours and 35 feet of redwood. Completed benches will yield a profit of $9 each, and tables will result in a profit of $20 each. How many benches and tables should Outdoor Furniture produce to obtain the largest possible profit? Use the graphical LP approach.
Q8.2
( Investment decision problem ) The Heinlein and Krampf Brokerage firm has just been instructed by one of its clients to invest $250,000 for her money obtained recently through the sale of land holdings in Ohio. The client has a good deal of trust in the investment house, but she also has her own ideas about the distribution of the funds being invested. In particular, she requests that the firm selects whatever stocks and bonds they believe are well rated, but within the following guidelines: 1.
Municipal bonds should constitute at least 20% of the investment.
2.
At least 40% of the funds should be placed in a combination of electronics
firms, aerospace firms, and drug manufacturers. 3.
No more than 50% of the amount invested in municipal bonds should be
placed in a high-risk, high-yield nursing home stock.
Subject to these restraints, the client’s goal is to maximize projected return on investments. The analysts at Heinlein and Krampf, aware of these guidelines, prepare a list of high-quality stocks and bonds and their corresponding rates of return. Investment Projected Rate of Return (%) Los Angeles municipal bonds 5.3 Thompson Electronics, Inc. 6.8 United Aerospace Corp. 4.9 Palmer Drugs 8.4 Happy Days Nursing Homes 11.8 (a) Formulate this portfolio selection problem using LP. (b) Solve this problem.
Q8.3
( Restaurant work scheduling problem ) The famous Y. S. Chang Restaurant is open 24 hours a day. Waiters and busboys report for duty at 3am, 7am, 11am, 3pm, 7pm, or 11pm, and each works an 8-hour shift. The following table shows the minimum number of workers needed during the six periods into which the day is divided.
Chang’s
scheduling problem is to determine how many waiters and busboys should report for work at the start of each time period to minimize the total staff required for one day’s operation. (Hint: Let X i equal the number of waiters and busboys beginning work in time period i, where i = 1, 2, 3, 4, 5, 6) Period Time 1 3am – 7am 2 7am – 11am 3 11am – 3pm 4 3pm – 7pm 5 7pm – 11pm 6 11pm – 3am Time period X 1 X 2 X 3 X 4
3am-7am
Number of Waiters & Busboys Required 3 12 16 9 11 4
7am-11am 11am-3pm
3pm-7pm
7pm-11pm 11pm-3am
X 5 X 6 Min requirement
3
12
16
9
11
4
Q8.4
( Animal feed mix problem ) The Battery Park Stable feeds and houses the horses used to pull tourist-filled carriages through the streets of Charleston’s historic waterfront area. The stable owner, an ex-racehorse trainer, recognizes the need to set a nutritional diet for the horses in his care. At the same time, he would like to keep the overall daily cost of feed to a minimum. The feed mixes available for the horses’ diet are an oat product, a highly enriched grain, and a mineral product. Each of these mixes contains a certain amount of five ingredients needed daily to keep the average horse healthy.
The table shows these
minimum requirements, units of each ingredient per pound of feed mix, and costs for the three mixes. In addition, the stable owner is aware that an overfed horse is a sluggish worker. Consequently, he determines that 6 pounds of feed per day is the most that any horse needs to function properly. Formulate this problem and solve for the optimal daily mix of the three feeds. Diet Requirement (Ingredients)
A B C D E Cost / lb Q8.14
Oat Product (Units / lb)
2 0.5 3 1 0.5 $0.09
Feed Mix Enriched Grain Mineral Product (Units / lb) (Units / lb)
3 1 5 1.5 0.5 $0.14
1 0.5 6 2 1.5 $0.17
Minimum Daily Requirement (Units) 6 2 9 8 5
( Agricultural Production Planning ) Margaret Black’s family owns five parcels of farmland broken into a southeast sector, north sector, northwest sector, west sector, and southwest sector. Margaret is involved primarily in growing wheat, alfalfa, and barley crops and is currently preparing her production plan for next year. The Pennsylvania Water Authority has just announced its yearly water allotment, with the Black farm receiving 7,400 acre-feet. Each parcel can only tolerate a certain amount of irrigation per growing season, as specified in the following table:
Parcel Southeast North Northwest West Southwest
Area (Acres) 2,000 2,300 600 1,100 500
Water Irrigation Limit (Acre-Feet) 3,200 3,400 800 500 600
Each of Black’s crops needs a minimum amount of water per acre, and there is a projected limit on sales of each crop. Crop data follow:
Crop Maximum Sales Water Needed per Acre (Acre-Feet) Wheat 110,000 bushels 1.6 Alfalfa 1,800 tons 2.9 Barley 2,200 tons 3.5 Margaret’s best estimate is that she can sell wheat at a net profit of $2 per bushel,
alfalfa at $40 per ton, and barley at $50 per ton. One acre of land yields an average of 1.5 tons of alfalfa and 2.2 tons of barley. The wheat yield is approximately 50 bushels per acre. (a) Formulate Margaret’s production plan. (b) What should the crop plan be, and what profit will it yield? (c) The Water Authority informs Margaret that for a special fee of $6,000 this year, her farm will qualify for an additional allotment of 600 acre-feet of water. How should she respond? Q8.12
( High tech production problem) Quitmeyer Electronics Incorporated manufactures the following six micro-computer peripheral devices: internal modems, external modems, graphics circuit boards, floppy disk drives, hard disk drives, and memory expansion boards. Each of these technical products requires time, in minutes, on three types of electronic testing equipment, as shown in the table.
Internal External Circuit Modem Modem Board Test device 7 3 12 1 Test device 2 5 3 2
Floppy Drives 6
Hard Drives 18
Memory Boards 17
2
15
17
Test device 3
5
1
3
2
9
2
The first two test devices are available 120 hours per week. The third (device 3) requires more preventive maintenance and may be used only 100 hours each week. The market for all six computer components is vast, and Quitmeyer Electronics believes that it can sell as many units of each product as it can manufacture. The table that follows summarizes the revenues and material costs for each product:
Device Revenue per Unit Sold ($) Internal Modem 200 External Modem 120 Graphics Circuit Board 180 Floppy Disk Drives 130 Hard Disk Drives 430 Memory Expansion Boards 260
Material Cost per Unit ($) 35 25 40 45 170 60
In addition, variable labor costs are $15 per hour for test device 1, $12 per hour for test device 2, and $18 per hour for test device 3.
Quitmeyer Electronics wants to
maximize its profits. (a) Formulate this problem as an LP model. (b) Solve the problem by computer. What is the best product mix? (c) What is the value of an additional minute of time per week on test device 1? Test device 2? Test device 3? Should Quitmeyer Electronics add more test device time? If so, on which equipment? *4.13
( Media Selection ) The Win Big Gambling Club promotes gambling junkets from a large Midwestern city to casinos in the Bahamas. The club has budgeted up to $8,000 per week for local advertising. The money is to be allocated among four promotional media: TV spots, newspaper ads, and two types of radio advertisements. Win Big’s goal is to reach the largest possible high-potential audience through the various media.
The
following table presents the number of potential gamblers reached by making use of an advertisement in each of the four media. It also provides the cost per advertisement placed and the maximum number of ads that can be purchased per week. Medium Audience Reached Cost per per Ad Ad ($) TV spot (1 minute) 5,000 800 Daily newspaper (full-page ad) 8,500 925 Radio spot (30 seconds, prime time) 2,400 290 Radio spot (1 minute, afternoon) 2,800 380
Maximum Ads per Week 12 5 25 20
Win Big’s contractual arrangements require that at least five radio spots be placed each week. To ensure a broad-scoped promotional campaign, management also insists that no more than $1,800 be spent on radio advertising every week. (a) What would be the impact if management approves spending $200 more on radio advertising each week? (b) Would it help Win Big if it can get out of the contractual agreement to place at least five radio spots each week? (c) The radio station manager agrees to run the afternoon radio spots during some of their more popular programs. He thinks this will increase the audience reached per ad to 3,100. Will this change the optimal solution? Why or why not? (d) There is some uncertainty in the audience reached per TV spot. For what range of values for this OFC will the current solution remain optimal?
*4.16
Consider Battery Park Stables’ animal feed problem first presented in Q8.4. Use Solver to create the Answer and Sensitivity Reports for this LP problem. Now answer the following questions using these reports. Each question is independent of the others. (a) If the price of grain decreases by $0.01 per pound, will the optimal solution change? (b) Which constraints are binding?
Interpret the shadow price for the binding
constraints. (c) What would happen to the total cost if the price of mineral decreased by 20% from its current value? (d) For what price range of oats is the current solution optimal?
*4.18
Consider Quitmeyer Electronics’ product mix problem first presented in Q8.12. Use Solver to create the Answer and Sensitivity Reports for this LP problem. Now answer the following questions using these reports. Each question is independent of the others. (a) Interpret the reduced costs for the products that are not currently included in the optimal production plan. (b) Another part of the corporation wants to take 35 hours of time on test device 3. How does this affect Quitmeyer’s optimal solution? (c) Quitmeyer has the opportunity to obtain 20 additional hours on test device 1 at a cost of $25 per hour. Is this deal worthwhile?