Riverside Oil Company in eastern Kentucky produces regular and supreme gasoline. 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 mixing different quantities of the following three inputs:

Input______Cost/barrel__Oct rating__Barrels avbl (in 1000s)
1_________$17.25__100__150
2_________$15.75__87__350
3_________$17.75__110__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 if they want to maximize profits?

I just need to figure out the MAX and constraints.

The constraints and the objective function can be obtained from the instructions and data.

First start off with defining the variables, x1,x2,x3 for the quantity (in 1000's of barrels) of each type of gasoline available for regular product, and x4,x5,x6 for supreme.

Since the company is buying, the quantities x1,x2,... cannot be negative. So constraints are:

x1≥0 (input 1 for regular)
...
x4≥0 (input 1 for supreme)
...

The availability tells us that:
x1+x4≤150
x2+x5≤350
...

The order quantity requirements (in 1000's of barrels)
x1+x2+x3=300
...

The octane requirement is such that the weighted average of the octane level must be greater than 90 and 97 for each type of gasoline. The sums (x1+x2+x3) and (x4+x5+x6) are 300 and 450 respectively:
(100x1+87x2+110x3)/300≥90
...

Profit is selling price - cost, so the objective function is:
P(x1,x2,x3,x4,x5,x6)
=300*$21-($17.25x1+$15.75x2+$17.75x3)
+...

Can you take it from here?

Yes, thank you so much.

You're welcome!

That's great formular. Could you create a spreadsheet model for this problem and solve it using Solver?

Is there a spreadsheet model to complete this on excel?

To determine the optimal allocation of inputs to maximize profits, we need to set up a linear programming problem. In this case, the objective is to maximize the profit, and the constraints are based on the availability of inputs and the desired octane ratings.

Let's define the decision variables:
Let X1 represent the number of barrels of regular gasoline (in thousands) produced.
Let X2 represent the number of barrels of supreme gasoline (in thousands) produced.

The objective function, which represents the profit, can be expressed as:
Profit = 21X1 + 25X2

Now let's set up the constraints:

1. Octane rating constraint for regular gasoline:
For each barrel of regular gasoline, the octane rating must be at least 90. Using the available inputs, the octane rating can be formulated as:
1 (input) * X1 + 2 (input) * X2 ≥ 90X1

2. Octane rating constraint for supreme gasoline:
For each barrel of supreme gasoline, the octane rating must be at least 97. Using the available inputs, the octane rating can be formulated as:
3 (input) * X1 + 2 (input) * X2 ≥ 97X2

3. Availability constraint for input 1:
The total amount of input 1 used should not exceed its availability. This can be expressed as:
1 (input) * X1 + 3 (input) * X2 ≤ 150 (availability)

4. Availability constraint for input 2:
The total amount of input 2 used should not exceed its availability. This can be expressed as:
2 (input) * X1 + 2 (input) * X2 ≤ 350 (availability)

5. Availability constraint for input 3:
The total amount of input 3 used should not exceed its availability. This can be expressed as:
3 (input) * X1 + 0 (input) * X2 ≤ 300 (availability)

6. Non-negativity constraint:
The number of barrels produced cannot be negative, so X1 and X2 should be greater than or equal to zero.

Combining all these constraints, you have the following linear programming problem:

Maximize: Profit = 21X1 + 25X2

Subject to:
X1 + 3X2 ≤ 150 (Octane rating constraint for regular gasoline)
3X1 + 2X2 ≥ 97X2 (Octane rating constraint for supreme gasoline)
X1 + 3X2 ≤ 150 (Availability constraint for input 1)
2X1 + 2X2 ≤ 350 (Availability constraint for input 2)
3X1 + 0X2 ≤ 300 (Availability constraint for input 3)
X1, X2 ≥ 0 (Non-negativity constraint)

By solving this linear programming problem, you can find the optimal allocation of inputs to maximize profits for Riverside Oil Company in eastern Kentucky.