excel

Model

Storing gas products in compartments
Unit shortage costs and penalty cost for violating shortage constraints						Range names used
Product	Cost/gallon					Amount	=Model!$C$13:$C$17
1	$10.00					Capacity	=Model!$E$13:$E$17
2	$8.00					Product	=Model!$B$13:$B$17
3	$6.00					Total_cost	=Model!$B$28
Shortage penalty	$100
Storing decisions
Compartment	Product	Amount		Capacity
1	2	2700.0	<=	2700
2	1	2800.0	<=	2800
3	2	1100.0	<=	1100
4	3	1677.8	<=	1800
5	3	3400.0	<=	3400
Shortages
Product	Amount Stored	Demand	Shortage	Max Shortage	Shortage Violation
1	2800.0	2900	100.0	900	0.0
2	3800.0	4000	200.0	900	0.0
3	5077.8	4900	0.0	900	0.0
Costs and penalties
Shortage cost	$2,600.00
Penalty cost	$0.00
Total cost	$2,600.00

This is a surprisingly difficult model for Evolutionary Solver. Note that the number of solutions for the range B13:B17 is "only" 35 = 243, but for each of these, Evolutionary Solver has to find the optimal amounts in the range C13:C17. We could solve this as a regular integer programming model (as in Chapter 6) except for the part about shortages, which we handle with IF functions. You should realize that instead of requiring that the shortages be no greater than the maximum shortages, we penalize them for doing so. Therefore, it could turn out that a shortage is more than the maximum shortage. This type of constraint is sometimes called a "soft" constraint, because we allow it to be violated -- at some cost. In contrast, we treat the capacity constraints in the usual way, as "hard" constraints. We don't let them be violated. See the extra file Loading Truck Linear for a linear version of this model, much like those in Chapter 6.

Model_STS

1
$E$15
1
300
1100
200
$B$13:$B$17,$C$13:$C$17,$B$28
Capacity 3

STS_1

Oneway analysis for Solver model in Model worksheet													Sensitivity of $B$28 to Capacity 3
Capacity 3 (cell $E$15) values along side, output cell(s) along top													Data for chart
	Product_1	Product_2	Product_3	Product_4	Product_5	Amount_1	Amount_2	Amount_3	Amount_4	Amount_5	$B$28	11	$B$28
300	3 Chris: Solver stopped at user's request.	1	2	3	2	2700.0	2800.0	300.0	1800.0	3400.0	$5,800.00		5800
500	3 Chris: Solver stopped at user's request.	1	2	3	2	2700.0	2800.0	500.0	1800.0	3400.0	$4,200.00		4200
700	3 Chris: Solver stopped at user's request.	1	2	3	2	2700.0	2800.0	691.8	1800.0	3382.9	$3,400.00		3400
900	3 Chris: Solver stopped at user's request.	1	2	3	2	2700.0	2800.0	900.0	1800.0	3266.2	$3,400.00		3400
1100	2 Chris: Solver stopped at user's request.	1	2	3	3	2700.0	2800.0	1100.0	1677.8	3400.0	$2,600.00		2600

Sensitivity of $B$28 to Capacity 3

300 500 700 900 1100 5800 4200 3400 3400 2600

Capacity 3 ($E$15)

When you select an output from the dropdown list in cell $N$4, the chart will adapt to that output.

There is nothing to prevent you from using SolverTable with Evolutionary Solver models, but they can take a long time to run. Note that if you click Stop when you see one of the "The maximum ... was reached" Solver messages (which was done for each of these problems -- see the comments in column B), SolverTable will report the best solution found to that point.

image1.png

image2.png