5-56
Variable Cells
Final Reduced Objective Allo w able Allo w able
Cell Name Valu e Cost Coefficient Increase Decrease
$B$7 Acres Planted Soybeans 450 0 70 1E+ 30 8. 4
$C$7 Acres Planted Corn 30 060 8. 4 1E+ 30
$D$7 Acres Planted W heat 100 0 40 17.15 1E+ 30
$B$27 New Livestock Cows 0 -53 700 53 1E+ 30
$C$27 New Livestock Hens 0 -0.857 3.5 0.857 1E+ 30
$B$36 Hours Worked W &S 1063 0 5 57.3 0.915
$C$36 Hours W orked S&F 1364 0 5.5 34.5 0.930
Constraints
Final Shado w Constraint Allo w able Allo w able
Cell Name Valu e Price R.H. Side Increase Decrease
$C$7 Acres Planted Corn 30 -8.4 0 450 30
$D$7 Acres Planted W heat 100 -24.15 0 450 100
$D$23 Cost of New Livestock Totals $0 $0 20000 1E+ 30 20000
$B$28 Total Livestock Cows 30 042 1E+ 30 12
$C$28 Total Livestock Hens 2000 0 5000 1E+ 30 3000
$E$39 W&S Hours Total 4000 5 4000 1E+ 30 1063
$E$40 S&F Hours Total 4500 5.5 4500 1E+ 30 1364
$E$41 Acreage Total 640 57.3 640 974.29 450
d) The allowable range for the value per acre planted of soybeans is 61.6 to ∞.
37
e) Drought
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
A B C D E F G
Plantings Planting
Soybeans Corn W heat T otals
W &S Hours Required 1 0.9 0.6 117.8
S&F Hours Required 1.4 1.2 0.7 143.733
Net Value -$10 -$15 $0 -$630
Acres Planted 0 42 133.33 175.333
>= >=
42 133.33
1 0.05
acre/cow acre/hen
Liv estock Livestock
Cows Hens Totals
Hours Required per Month 10 0.05 553.333333
Grazing Land Required 2 0 84
Net Annual Cash Income $850 $4 $47,033
Beginning Value (Current Livestock) $35,000 $5,000
Decrease in Value per Year 10% 25%
End Value (Current Livestock) $31,500 $3,750 $35,250 Investment
Fund
Cost of New Livestock $1,500 $3 $20,000 <= $20,000
End Value (New Livestock) $1,350 $2 $17,700
Current Livestock 30 2,000
New Livestock 12 667
Total Livestock 42 2,667
<= <=
Barn/House Limits 42 5,000
Neighboring Farm Work Neighbor
W &S S&F T otals
W age $5 $5.50 $8,510.47
Flood
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
37
38
39
40
41
42
43
44
45
46
47
A B C D E F G
Plantings Planting
Soybeans Corn W heat T otals
W &S Hours Required 1 0.9 0.6 460.4
S&F Hours Required 1.4 1.2 0.7 600.533
Net Value $15 $20 $10 $9,787
Acres Planted 0 422.6667 133.33 556
>= >=
42 133.33
1 0.05
acre/cow acre/hen
Liv estock Livestock
Cows Hens Totals
Hours Required per Month 10 0.05 553.333333
Grazing Land Required 2 0 84
Net Annual Cash Income $850 $4 $47,033
Beginning Value (Current Livestock) $35,000 $5,000
Decrease in Value per Year 10% 25%
End Value (Current Livestock) $31,500 $3,750 $35,250 Investment
Fund
Cost of New Livestock $1,500 $3 $20,000 <= $20,000
End Value (New Livestock) $1,350 $2 $17,700
Current Livestock 30 2,000
New Livestock 12 667
Total Livestock 42 2,667
<= <=
Barn/House Limits 42 5,000
Neighboring Farm Work Neighbor
W &S S&F T otals
W age $5 $5.50 $4,285.07
Totals Plantings Livestock Neighbor Total Available
W &S Hours 460.4 3,320 220 4,000 <= 4,000
S&F Hours 600.5333 3,320 579 4,500 <= 4,500
Acreage 556 84 0 640 <= 640
Net Income $9,787 $47,033 $4,285 $61,105
End of Year Value $52,950 $52,950
Leftover Investment F und $0 $0
Living Expenses -$40,000
Total Monetary Worth $74,055
In a flood, the model predicts (under the optimal solution) that the family’s monetary
Early Frost
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
A B C D E F G
Plantings Planting
Soybeans Corn W heat T otals
W &S Hours Required 1 0.9 0.6 537
S&F Hours Required 1.4 1.2 0.7 736
Net Value $50 $40 $30 $26,700
Acres Planted 450 30 100.00 580
>= >=
30 100.00
1 0.05
acre/cow acre/hen
Liv estock Livestock
Cows Hens Totals
Hours Required per Month 10 0.05 400
Grazing Land Required 2 0 60
Net Annual Cash Income $850 $4 $34,000
Beginning Value (Current Livestock) $35,000 $5,000
Decrease in Value per Year 10% 25%
End Value (Current Livestock) $31,500 $3,750 $35,250 Investment
Fund
Cost of New Livestock $1,500 $3 $0 <= $20,000
End Value (New Livestock) $1,350 $2 $0
Current Livestock 30 2,000
New Livestock 0 0
Total Livestock 30 2,000
<= <=
Barn/House Limits 42 5,000
Neighboring Farm Work Neighbor
W &S S&F T otals
W age $5 $5.50 $12,817.00
Hours W orked 1063 1364 2427.000
Totals Plantings Livestock Neighbor Total Available
W &S Hours 537 2,400 1,063 4,000 <= 4,000
S&F Hours 736 2,400 1,364 4,500 <= 4,500
Acreage 580 60 0 640 <= 640
Net Income $26,700 $34,000 $12,817 $73,517
End of Year Value $35,250 $35,250
Leftover Investment F und $20,000 $20,000
Living Expenses -$40,000
Total Monetary Worth $88,767
5-60
Drought and Early Frost
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
A B C D E F G
Plantings Planting
Soybeans Corn W heat T otals
W &S Hours Required 1 0.9 0.6 97.8
S&F Hours Required 1.4 1.2 0.7 120.4
Net Value -$15 -$20 –$10 –$1,840
Acres Planted 0 42 100.00 142
>= >=
42 100.00
1 0.05
acre/cow acre/hen
Liv estock Livestock
Cows Hens Totals
Hours Required per Month 10 0.05 520
Grazing Land Required 2 0 84
Net Annual Cash Income $850 $4 $44,200
Beginning Value (Current Livestock) $35,000 $5,000
Decrease in Value per Year 10% 25%
End Value (Current Livestock) $31,500 $3,750 $35,250 Investment
Fund
Cost of New Livestock $1,500 $3 $18,000 <= $20,000
End Value (New Livestock) $1,350 $2 $16,200
Current Livestock 30 2,000
New Livestock 12 0
Total Livestock 42 2,000
<= <=
Barn/House Limits 42 5,000
Neighboring Farm Work Neighbor
W &S S&F T otals
W age $5 $5.50 $10,838.80
Hours W orked 782.2 1259.6 2041.800
Totals Plantings Livestock Neighbor Total Available
W &S Hours 97.8 3,120 782 4,000 <= 4,000
S&F Hours 120.4 3,120 1,260 4,500 <= 4,500
Acreage 142 84 0 226 <= 640
Net Income -$1,840 $44,200 $10,839 $53,199
End of Year Value $51,450 $51,450
Leftover Investment F und $2,000 $2,000
Living Expenses -$40,000
Total Monetary Worth $66,649
5-61
Flood and Early Frost
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
A B C D E F G
Plantings Planting
Soybeans Corn W heat T otals
W &S Hours Required 1 0.9 0.6 183.6
S&F Hours Required 1.4 1.2 0.7 219.8
Net Value $10 $10 $5 $1,623
Acres Planted 0 37.33333 250.00 287.333
>= >=
37.33333 250.00
1 0.05
acre/cow acre/hen
Liv estock Livestock
Cows Hens Totals
Hours Required per Month 10 0.05 623.333333
Grazing Land Required 2 0 74.6666667
Net Annual Cash Income $850 $4 $52,983
Beginning Value (Current Livestock) $35,000 $5,000
Decrease in Value per Year 10% 25%
End Value (Current Livestock) $31,500 $3,750 $35,250 Investment
Fund
Cost of New Livestock $1,500 $3 $20,000 <= $20,000
End Value (New Livestock) $1,350 $2 $16,650
Current Livestock 30 2,000
New Livestock 7.333333 3,000
Total Livestock 37.33333 5,000
<= <=
Barn/House Limits 42 5,000
Neighboring Farm Work Neighbor
W &S S&F T otals
W age $5 $5.50 $3,353.10
Hours W orked 76.39999 540.2 616.600
Totals Plantings Livestock Neighbor Total Available
W &S Hours 183.6 3,740 76 4,000 <= 4,000
S&F Hours 219.8 3,740 540 4,500 <= 4,500
Acreage 287.3333 74.66667 0 362 <= 640
Net Income $1,623 $52,983 $3,353 $57,960
End of Year Value $51,900 $51,900
Leftover Investment F und $0 $0
Living Expenses -$40,000
Total Monetary Worth $69,860
5-62
f)
Family’s monetary worth at year’s end if the scenario is actually:
Opt. Sol. Used
Good
Drought
Flood
Early Frost
Drought&EF
Flood&EF
Good Weather
$99,367
$57,117
$70,417
$88,767
$53,717
$67,367
Drought
$76,348
$67,864
$70,668
$74,174
$66,321
$69,581
Flood
$94,962
$57,929
$74,055
$85,175
$54,482
$69,162
Early Frost
$99,367
$57,117
$70,417
$88,767
$53,717
$67,367
Drought & E.F.
$75,009
$67,859
$70,329
$73,169
$66,649
$69,409
Flood & E.F.
$80,476
$67,676
$71,483
$77,230
$64,990
$69,860
Answers will vary. No solution is clearly best. The Good Weather solution is the
riskiest, with the highest upside and downside. The Flood solution appears to be a good
g and h)
The expected net value for each of the crops is calculated as follows:
Soybeans: ($70)(0.4) + (–$10)(0.2) + ($15)(0.1) + ($50)(0.15) + (–$15)(0.1) +
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
A B C D E F G
Plantings Planting
Soybeans Corn W heat T otals
W &S Hours Required 1 0.9 0.6 511.8
S&F Hours Required 1.4 1.2 0.7 700
Net Value $34.00 $27.50 $20.75 $17,306
Acres Planted 414 42 100.00 556
>= >=
42 100.00
1 0.05
acre/cow acre/hen
Liv estock Livestock
Cows Hens Totals
Hours Required per Month 10 0.05 520
Grazing Land Required 2 0 84
Net Annual Cash Income $850 $4 $44,200
Beginning Value (Current Livestock) $35,000 $5,000
Decrease in Value per Year 10% 25%
End Value (Current Livestock) $31,500 $3,750 $35,250 Investment
Fund
Cost of New Livestock $1,500 $3 $18,000 <= $20,000
End Value (New Livestock) $1,350 $2 $16,200
Current Livestock 30 2,000
New Livestock 12 0
Total Livestock 42 2,000
<= <=
Barn/House Limits 42 5,000
Neighboring Farm Work Neighbor
W &S S&F T otals
W age $5 $5.50 $5,581.00
Hours W orked 368.2 680 1048.200
Totals Plantings Livestock Neighbor Total Available
W &S Hours 511.8 3,120 368 4,000 <= 4,000
S&F Hours 700 3,120 680 4,500 <= 4,500
Acreage 556 84 0 640 <= 640
Net Income $17,306 $44,200 $5,581 $67,087
End of Year Value $51,450 $51,450
Leftover Investment F und $2,000 $2,000
Living Expenses -$40,000
Total Monetary Worth $80,537
This model predicts that the family’s monetary worth at the end of the coming year will
5-64
Variable Cells
Final Reduced Objective Allowable Allowable
Cell Name Value Cost Coefficien t Increase Decrease
$B$7 Acres Planted Soybeans 414 0 34 7.5 0.4
$C$7 Acres Planted Corn 42 0 27.5 4.9 22.5
$D$7 Acres Planted W heat 100 0.00 20.75 0.4 1E+ 30
$B$27 New Livestock Cows 12 0 700 1E+ 30 22.5
$C$27 New Livestock Hens 0 0 3.5 0.02 1E+ 30
$B$36 Hours W orked W &S 368.2 0 5 0.389 0.071
$C$36 Hours W orked S&F 680 0 5.5 0.395 0.075
Constraints
Final Shadow Con straint Allo wable Allowable
Cell Name Value Price R.H. Side In crease Decrease
$C$7 Acres Planted Corn 42 -4.9 0 414 42
$D$7 Acres Planted W heat 100.00 -7.40 0 414 100
$D$23 Cost of New Livestock Totals $18,000 $0 20000 1E+ 30 2000
$B$28 Total Livestock Cows 42 22.5 42 1.333 12
$C$28 Total Livestock Hens 2,000 0 5000 1E+ 30 3000
$E$39 W &S Hours T otal 4,000 5 4000 1E+ 30 368.2
$E$40 S&F Hours Total 4,500 5 4500 1E+ 30 680
$E$41 Acreage Total 640 21.3 640 368.2 414
i) The shadow price for the investment constraint is zero, indicating that additional
investment funds will not increase their total monetary worth at all. Thus, it is not
$0.40 or decrease any amount without changing the optimal solution. The expected net
value for soybeans and wheat should be estimated most carefully.
The solution is sensitive to decreases in the expected value of soybeans and increases in
the expected value of wheat. If the cumulative decrease in the expected value of
50
51
52
53
54
55
56
A B C D E F G
Total Monetary W orth Wheat Expected Net Value
$80,537 $18.75 $19.75 $20.75 $21.75 $22.75
$32.00 $79,509 $79,629 $79,788 $79,976 $80,217
Soybeans Expected $33.00 $79,923 $80,023 $80,143 $80,308 $80,505
Net Value $34.00 $80,337 $80,437 $80,537 $80,657 $80,829
$35.00 $80,751 $80,851 $80,951 $81,051 $81,171
$36.00 $81,165 $81,265 $81,365 $81,465 $81,565
k) Answers will vary.
5-65
5.4 a)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
A B C D E F G
Data: Percentage Percentage Percentage
in 6th in 7th in 8th Bussing Cost ($/Student)
Area Grade G rade Grade School 1 School 2 School 3
1 32% 38% 30% $300 $0 $700
2 37% 28% 35% – $400 $500
3 30% 32% 38% $600 $300 $200
4 28% 40% 32% $200 $500 –
5 39% 34% 27% $0 – $400
6 34% 28% 38% $500 $300 $0
Solu tion: Number of Students Assigned Total Number of
School 1 School 2 School 3 F rom Area Students
Area 1 0 450 0 450 = 450
Area 2 0 422.22 177.78 600 = 600
Area 3 0 227.78 322.22 550 = 550
Area 4 350 0 0 350 = 350
Area 5 366.67 0 133.33 500 = 500
Area 6 83.33 0 366.67 450 = 450
Total In School 800 1,100 1,000
<= <= <= Total
Capacity 900 1,100 1,000 Bussing
Cost
$555,556
Grad e Co nstraints:
240 330 300 30% of total in school
<= <= <=
6th G raders 269.33 368.56 339.11
7th G raders 288.00 362.11 300.89
8th G raders 242.67 369.33 360.00
<= <= <=
288 396 360 36% of total in school
Range Name Cells
BussingCost E4:G 9
Capacity B22:D22
NumberOfStudents G14:G 19
PercentageInGrade B4:D9
Solution B14:D19
TotalBussingCost G24
TotalFromArea E14:E19
TotalInSchool B20:D20
12
13
14
15
16
17
18
19
E
Total
From Area
= SUM(B14:D14)
= SUM(B15:D15)
= SUM(B16:D16)
= SUM(B17:D17)
= SUM(B18:D18)
= SUM(B19:D19)
21
22
23
24
G
Total
Bussing
Cost
=SUMPRODUCT(BussingCost,Solution)
5-66
b)
Variable Cells
F in al Redu ced O b jective Allow able Allow able
Cell Name Value Co st Coefficien t In crease Decrease
$B$14 Area 1 School 1 0 177.778 300 1E+ 30 177.778
$C$14 Area 1 School 2 450 0 0 177.778 1E+ 30
$D$14 Area 1 School 3 0 266.667 700 1E+ 30 266.667
$B$15 Area 2 School 1 0 –800.000 0 1E+ 30 800.000
$C$15 Area 2 School 2 422.22 0 400 34.211 4.545
$D$15 Area 2 School 3 177.78 0 500 4.545 34.211
$B$16 Area 3 School 1 0 11.111 600 1E+ 30 11.111
$C$16 Area 3 School 2 227.78 0 300 4.545 34.211
$D$16 Area 3 School 3 322.22 0 200 34.211 7.692
$B$17 Area 4 School 1 350 0 200 366.667 2.08E+ 17
$C$17 Area 4 School 2 0 366.667 500 1E+ 30 366.667
$D$17 Area 4 School 3 0 -433.333 0 1E+ 30 433.333
$B$18 Area 5 School 1 366.67 0 0 16.667 108.333
$C$18 Area 5 School 2 0 233.333 0 1E+ 30 233.333
$D$18 Area 5 School 3 133.33 0 400 108.333 16.667
$B$19 Area 6 School 1 83.33 0 500 33.333 166.667
$C$19 Area 6 School 2 0 200 300 1E+ 30 200
$D$19 Area 6 School 3 366.67 0 0 166.667 33.333
Constraints
F in al Sh adow Co nstrai nt Allow able Allow able
Cell Name Value Price R.H. Sid e Increase Decrease
$B$30 8th G raders <= 242.67 0.00 0 1E+ 30 45.333
$C$30 8th Graders <= 369.33 0.00 0 1E+ 30 26.667
$D$30 8th Graders <= 360.00 –6666.67 0 5.333 0.667
$B$20 Total In School School 1 800 0 900 1E+ 30 100
$C$20 Total In School School 2 1,100 -178 1100 36.364 3.774
$D$20 Total In School School 3 1,000 -144 1000 42.105 3.883
$B$28 6th G raders <= 269.33 0.00 0 29.333 1E+ 30
$C$28 6th Graders <= 368.56 0.00 0 38.556 1E+ 30
$D$28 6th Graders <= 339.11 0.00 0 39.111 1E+ 30
$B$28 6th G raders <= 269.33 0.00 0 1E+ 30 18.667
$C$28 6th Graders <= 368.56 0.00 0 1E+ 30 27.444
$D$28 6th Graders <= 339.11 0.00 0 1E+ 30 20.889
$B$29 7th G raders <= 288.00 0.00 0 48 1E+ 30
$C$29 7th Graders <= 362.11 0.00 0 32.111 1E+ 30
$D$29 7th Graders <= 300.89 0.00 0 0.889 1E+ 30
$B$29 7th G raders <= 288.00 -2777.78 0 0.258 2.909
$C$29 7th Graders <= 362.11 0.00 0 1E+ 30 33.889
$D$29 7th Graders <= 300.89 0.00 0 1E+ 30 59.111
$B$30 8th G raders <= 242.67 0.00 0 2.667 1E+ 30
$C$30 8th Graders <= 369.33 0.00 0 39.333 1E+ 30
$D$30 8th Graders <= 360.00 0.00 0 60 1E+ 30
$E$14 Area 1 F rom Area 450 177.778 450 3.774 36.364
$E$15 Area 2 F rom Area 600 577.778 600 3.774 36.364
$E$16 Area 3 F rom Area 550 477.778 550 3.774 36.364
$E$17 Area 4 F rom Area 350 311.111 350 72.727 6.452
$E$18 Area 5 F rom Area 500 –55.556 500 12.903 145.455
$E$19 Area 6 F rom Area 450 277.778 450 3.226 36.364
5-67
c) The bussing cost from area 6 to school 1 can increase $33.33 before the current optimal
solution would no longer be optimal. The new solution with a 10% increase ($50) is
shown below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
A B C D E F G
Data: Percentage Percentage Percentage
in 6th in 7th in 8th Bussing Cost ($/Student)
Area Grade G rade Grade School 1 School 2 School 3
1 32% 38% 30% $300 $0 $700
2 37% 28% 35% – $400 $500
3 30% 32% 38% $600 $300 $200
4 28% 40% 32% $200 $500 –
5 39% 34% 27% $0 – $400
6 34% 28% 38% $550 $300 $0
Solution: Number of Students Assigned T otal Number of
School 1 School 2 School 3 From Area Students
Area 1 0 450 0 450 = 450
Area 2 0 600 0 600 = 600
Area 3 72.73 50 427.27 550 = 550
Area 4 350 0 0 350 = 350
Area 5 318.18 0 181.82 500 = 500
Area 6 59.09 0 390.91 450 = 450
Total In School 800 1,100 1,000
<= <= <= Total
Capacity 900 1,100 1,000 Bussing
Cost
$559,318
Grad e Con straints:
240 330 300 30% of total in school
<= <= <=
6th G raders 264.00 381.00 332.00
7th G raders 288.00 355.00 308.00
8th G raders 248.00 364.00 360.00
<= <= <=
288 396 360 36% of total in school
d) The bussing cost from area 6 to school 2 can increase any amount and the optimal
solution from part (a) will still be optimal.
e) If the bussing costs increase 1% from area 6 to all the schools, then:
Percentage of allowable increase for school 1 used = ($505 – $500) / $33.33 = 15%.
5-68
If the bussing costs increase 10% from area 6 to all schools, the new solution is:
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
A B C D E F G
Data: Percentage Percentage Percentage
in 6th in 7th in 8th Bussing Cost ($/Student)
Area Grade G rade Grade School 1 School 2 School 3
1 32% 38% 30% $300 $0 $700
2 37% 28% 35% – $400 $500
3 30% 32% 38% $600 $300 $200
4 28% 40% 32% $200 $500 –
5 39% 34% 27% $0 – $400
6 34% 28% 38% $550 $330 $0
Solution: Number of Students Assigned T otal Number of
School 1 School 2 School 3 From Area Students
Area 1 0 450 0 450 = 450
Area 2 0 600 0 600 = 600
Area 3 72.73 50 427.27 550 = 550
Area 4 350 0 0 350 = 350
Area 5 318.18 0 181.82 500 = 500
Area 6 59.09 0 390.91 450 = 450
Total In School 800 1,100 1,000
<= <= <= Total
Capacity 900 1,100 1,000 Bussing
Cost
$559,318
Grad e Con straints:
240 330 300 30% of total in school
<= <= <=
6th G raders 264.00 381.00 332.00
7th G raders 288.00 355.00 308.00
8th G raders 248.00 364.00 360.00
<= <= <=
288 396 360 36% of total in school
f) The shadow price for school 1 is zero. Thus, adding a temporary classroom at school 1
would not save any money, and thus would not be worthwhile.
5-69
h) The following combinations do not violate the 100% rule:
Portables to
add
to school 2
Portables to
add
to school 3
100%-rule calculation
1
0
(20/36) + (0/42) = 55.6%
0
1
(0/36) + (20/42) = 47.6%
0
2
(0/36) + (40/42) = 95.23%
Each combination yields the following total savings
Portables to
add
to school 2
Portables to
add
to school 3
Bussing Cost Savings
Lease
Cost
Total
Savings
1
0
($177.78)(20) = $3555.60
$2500
$1055.60
0
1
($144.44)(20) = $2888.80
$2500
$388.80
0
2
($144.44)(40) = $5777.60
$5000
$777.60
Of these combinations, adding one portable to school 2 is best in terms of minimizing
total cost. The spreadsheet solution is shown below.
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
24
25
26
27
28
29
30
32
A B C D E F G
Data: Percentage Percentage Percentage
in 6th in 7th in 8th Bussing Cost ($/Student)
Area Grade Grade Grade School 1 School 2 School 3
1 32% 38% 30% $300 $0 $700
2 37% 28% 35% – $400 $500
3 30% 32% 38% $600 $300 $200
4 28% 40% 32% $200 $500 –
5 39% 34% 27% $0 – $400
6 34% 28% 38% $500 $300 $0
Solutio n: Number of Students Assigned Total Number of
School 1 School 2 School 3 From Area Students
Area 1 0 450 0 450 = 450
Area 2 0 520 80 600 = 600
Area 3 0 150 400 550 = 550
Area 4 350 0 0 350 = 350
Area 5 340 0 160 500 = 500
Area 6 90 0 360 450 = 450
Grad e Co nstrain ts:
234 336 300 30% of total in school
<= <= <=
6th G raders 261.20 381.40 334.40
