Question

10) You are hired as a consulting engineer to a small chemical plant. The plant needs to produce 10,000 liters per day of a liquid with a maximum contaminant concentration of 100 mg/l. There are four suppliers of liquid that can be mixed together to produce the final fluid. Each source sells liquid at a different cost and with different contaminant concentrations. Each supplier has a maximum volume that can be supplied per day. You need to determine how much liquid should be ordered from each supplier to produce 10,000 liters of liquid with no more than 100 mg/l contaminant concentration at the lowest price. The following table summarizes requirements for production (also in Excel).

Use Excel and show all formulas and steps used.

	Source 1	Source 2	Source 3	Source 4
Cost($/L)	$0.50	$0.80	$1.20	$1.50
Max Supply (L/day)	10,000	4,000	3,000	1,500
Concentration (mg/L)	142	105	78	62

Constraints

Constraints
Conc <=	100 mg/L
Supply =	10,000 L

Answer 1

Let,

x1 = Quantity (in litre) to be ordered from Supplier 1
x2 = Quantity (in litre) to be ordered from Supplier 2
x3 = Quantity (in litre) to be ordered from Supplier 3
x4 = Quantity (in litre) to be ordered from Supplier 4

Objective is to minimize cost so objective function = Min 0.5x1+0.8x2+1.2x3+1.5x4

Subject to,

x1+x2+x3+x4 = 10000 (litres to be ordered)

Max supply available constraints for suppliers

x1 <= 10000
x2 <= 4000
x3 <= 3000
x4 <= 1500

Concentration constraint

(142x1+105x2+78x3+62x4)/(x1+x2+x3+x4) <= 100

or, 42x1+5x2-22x3-38x4 <= 0

x1,x2,x3,x4 >= 0 (Non-negativity constraint)

Solving in excel solver we get,

x1 = Quantity (in litre) to be ordered from Supplier 1 = 2000
x2 = Quantity (in litre) to be ordered from Supplier 2 = 4000
x3 = Quantity (in litre) to be ordered from Supplier 3 = 3000
x4 = Quantity (in litre) to be ordered from Supplier 4 = 1000

Minimized cost = 9300

x1 X2 X3 2000 Objective function 4000 3000 1000 9300 5 litresto be ordered 1 10000 10000 10000 4000 3000 1500 2000 4000 3000 1000 7Max supply available 8 constraints for suppliers 42 38 10 Concentration constraint

Solver Parameters SES15 Set Objective To O Max By Changing Variable Cells: 0 alue Of: $B$3:$E$3 Subject to the Constraints: Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a SolvingSimplex LP Metho: Options Solving Method Select the GRG Nonlinear engine for Solver Problems that are smooth nonlinear. Select the LP Simplex engine for linear Solver Problems, and select the Evolutionary engine for Solver problems that are non-smooth. Solve Close