Question

28. Riverside Oil Company in eastern Kentucky produces regular and supreme gaso- line. Each barrel of regular sells for $21 and must have an octane rating of at least 90. Each barrel of supreme sells for $25 and must have an octane rating of at least 97 Each of these types of gasoline are manufactured by mxing different quantities of the following three inputs: Barrels Available Cost per Barrel Octane Rating in 1000s) Input $17.25 $15.75 $17.75 150 100 87 110 2 350 300 Riverside has orders for 300,000 barrels of regular and 450,000 barrels of supreme How should the company allocate the available inputs to the production of regular and supreme gasoline to maximize profits a. Formulate an LP model for this problem. b. Create a spreadsheet model for this problem, and solve it using Solver c. What is the optimal solution?

Answer 1

(a)

Define the decision variables as follows:

Rj = Barrels of the j-th input in Regular gasoline for j=1,2,3
Sj = Barrels of the j-th input in Supreme gasoline for j=1,2,3

[So, there are 3+3=6 decision variables]

Maximize Z = total profit = 21*(R1 + R2 + R3) + 25*(S1 + S2 + S3) - 17.25*(R1 + S1) - 15.75*(R2 + S2) + 17.75*(R3 + S3)
= 3.75 R1 + 5.25 R2 + 3.25 R3 + 7.75 S1 + 9.25 S2 + 7.25 S3

Subject to,

R1 + R2 + R3 <= 300000
S1 + S2 + S3 <= 450000

R1 + S1 <= 150000
R2 + S2 <= 350000
R3 + S3 <= 300000

(100 - 90) R1 + (87 - 90) R2 + (110 - 90) R3 >= 0 or, 10 R1 - 3 R2 + 20 R3 >= 0

(100 - 97) S1 + (87 - 97) S2 + (110 - 97) S3 >= 0 or, 3 S1 - 10 S2 + 13 S3 >= 0

Rj, Sj >= 0 for j=1,2,3

(b)

R1 S1 S2 S3 2 Barrels of Price 21 21 21 25 4 Cost 17.2515.75 17.75 17.25 15.75 17.75 Profit -c4-C5 D4-DS -E4-E5 F4F5 -G4-G5-14-H5l-sUNIPRODUCT($C$3.SH$3.Сб:H6) Capacity 1 1 Capacity -2 10 Capacity 3 -SUMPRODUCT(SCS3:SHS3,C8H8) 150000 -SUMPRODUCT(SCS3:SHS3,C9H9) 350000 -SUMPRODUCT(SCS3:SHS3,C10:H10) 300000 -SUMPRODUCT(SCS3:SHS3,C11 H1) 300000 -SUMPRODUCT(SCS3:SHS3,C12:H12) 450000 -SUMPRODUCT(SCS3:SHS3,C13H13) 0 -SUMPRODUCT(SCS3:SHS3,C14H14) 0 Demand - R 1 11 Demand-S 12 Octane R 10 20 13 14 Octane-S 10 13

Set Objective OMin OY Max Value Of: By Changing Variable Cells: $C$3:$H$3 Subject to the Constraints: SI$13:$I$14 SK$13:$K$14 Add Change Delete Reset All Load/Save Make Unconstrained Variables Non-Negative Select a Solving Method Simplex LP 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. Help Solve Close

(c)

Objective Cell (Max)
	Cell	Name	Original Value	Final Value
	$I$6	Profit	$0	$5,012,500
Variable Cells
	Cell	Name	Original Value	Final Value	Integer
	$C$3	Barrels of R1	0	150,000	Contin
	$D$3	Barrels of R2	0	95,652	Contin
	$E$3	Barrels of R3	0	54,348	Contin
	$F$3	Barrels of S1	0	0	Contin
	$G$3	Barrels of S2	0	254,348	Contin
	$H$3	Barrels of S3	0	195,652	Contin