Blending at
Chandler Oil
I. Overview and Objective
Chandler
Oil has two types of oil that they sell.
The first is gasoline and the other is heating. These products are produced by blending
together two crude oils. A quality
rating is assigned to each of the crude oils, which will be referred to as
crude oil 1 and crude oil 2. Each
barrel of crude oil 1 has a quality level of 10 and each barrel of crude oil 2
has a rating of 5. Gasoline must have
an average quality level of at least 8, whereas heating oil must have an
average of at least 6. Gasoline sells
for $25 per barrel and heating oil sells for $20. The advertising cost to sell one barrel of gasoline is $0.20 and
for heating oil it is $0.10. Demand for
the two oils is unlimited and Chandler of course wants to maximize profit.
MAXIMIZATION
II. Variables
|
Variable |
How Measured |
Related to |
|
Selling
price/ barrel |
Dollars |
price per
barrel sold |
|
Advertising
cost/ barrel |
Dollars |
advertising
cost per barrel sold |
|
Quality
level |
units
from 1-10 |
rating
given to crude oils assigning quality |
|
Barrels
sold |
Barrels |
total
barrels sold of each product |
|
Barrels
used |
Barrels |
total
barrels used of each crude oil |
|
Barrels
available |
Barrels |
amount of
barrels that Chandler can use |
|
Revenue |
Dollars |
barrels
sold*price per barrel |
|
Advertising
cost |
Dollars |
advertising
cost*# of barrels sold |
|
Profit |
Dollars |
revenue -
advertising cost |
III. Mathematical
Formulation
The
objective of this model is to maximize the variable profit. As you can see above the basic formula for
profit is: Profit
= revenue – advertising cost.
This maximization must be while obeying certain constraints:
- The inputs must
all be positive numbers. This is
because, there will never be a negative amount of barrels available or sold.
- The amount of barrels
available of crude oil 1 and crude oil 2 are limited. There is only 5000 barrels of crude oil 1 available and 10000 of
crude oil two. This can be represented
by:
# of barrels of crude oil 1
<= 5000
# of barrels of crude oil 2
<= 10000
- The finished products
must have a certain average quality level in the mixture. Gasoline must have at least an average of 8,
and heating oil must have at least 6.
This can be represented by:
average quality level in
gasoline mixture >= 8
average quality level in
heating oil mixture >= 6
IV. Development of
Spreadsheet Model
1. Monetary and Quality inputs
This is the input portion of the model. The price and advertising cost per barrel of
gasoline and heating oil is entered here.
The quality levels for both crude oils are also included in this
section. The last part deals with the
constraint on the average quality levels for the finished products.
|
Monetary
inputs |
Gasoline |
Heating oil |
|
Selling
price/barrel |
$25.00 |
$20.00 |
|
Advertising
cost/barrel |
$0.20 |
$0.10 |
|
|
|
|
|
Quality
level per barrel of crudes |
|
|
|
Crude oil
1 |
10 |
|
|
Crude oil
2 |
5 |
|
|
|
|
|
|
Required
quality level per barrel of product |
||
|
|
Gasoline |
Heating oil |
|
|
8 |
6 |
2. Barrels of Crude Oil
The barrels of crude oil sold are listed by the
barrels available. This makes sure that
the constraint on the barrels is obeyed.
The number of barrels sold is also listed for each product in this
section.
|
Blending
plan (barrels of crudes in each product) |
|
|
|
||
|
|
Gasoline |
Heating oil |
Barrels used |
|
Barrels available |
|
Crude oil
1 |
3000 |
2000 |
5000 |
<= |
5000 |
|
Crude oil
2 |
2000 |
8000 |
10000 |
<= |
10000 |
|
Barrels
sold |
5000 |
10000 |
|
|
|
3. Quality Achieved
The constraints on quality are listed
with the actual level of quality for each product.
|
Constraints
on quality |
|
|
|
|
Gasoline |
Heating oil |
|
Quality
"points" obtained |
40000 |
60000 |
|
|
>= |
>= |
|
Quality
"points" required |
40000 |
60000 |
4. Monetary Summary
This section gives the total revenue,
advertising cost and profit.
|
Monetary
summary |
|
|
Revenue |
$325,000 |
|
Advertising
cost |
$2,000 |
|
Profit |
$323,000 |
V. Results
Along
with the spreadsheet model an answer report and sensitivity analysis will aid
in interpretation.
Answer
Report
|
Target
Cell (Max) |
|
|
|
|
||
|
|
Cell |
Name |
Original Value |
Final Value |
|
|
|
|
$B$30 |
Profit |
$323,000 |
$323,000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Adjustable
Cells |
|
|
|
|
||
|
|
Cell |
Name |
Original Value |
Final Value |
|
|
|
|
$B$17 |
Crude oil
1 Gasoline |
3000 |
3000 |
|
|
|
|
$C$17 |
Crude oil
1 Heating oil |
2000 |
2000 |
|
|
|
|
$B$18 |
Crude oil
2 Gasoline |
2000 |
2000 |
|
|
|
|
$C$18 |
Crude oil
2 Heating oil |
8000 |
8000 |
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
|
Constraints |
|
|
|
|
||
|
|
Cell |
Name |
Cell Value |
Formula |
Status |
Slack |
|
|
$D$17 |
Crude oil
1 Barrels used |
5000 |
$D$17<=$F$17 |
Binding |
0 |
|
|
$D$18 |
Crude oil
2 Barrels used |
10000 |
$D$18<=$F$18 |
Binding |
0 |
|
|
$B$23 |
Quality
"points" obtained Gasoline |
40000 |
$B$23>=$B$25 |
Binding |
0 |
|
|
$C$23 |
Quality
"points" obtained Heating oil |
60000 |
$C$23>=$C$25 |
Binding |
0 |
This
report includes the value for the target cell and the adjustable cells at the
optimal solution. These fall under the
final value column. The bottom table
gives information on the variables under constraint. In this case all of the variables are binding, meaning that if
the constraint was changed the optimal value can be improved. The slack for each variable is 0, which
indicates that Chandler is producing to capacity.
Sensitivity
Analysis
|
Adjustable
Cells |
|
|
|
|
|
||
|
|
|
|
Final |
Reduced |
Objective |
Allowable |
Allowable |
|
|
Cell |
Name |
Value |
Cost |
Coefficient |
Increase |
Decrease |
|
|
$B$17 |
Crude oil
1 Gasoline |
3000 |
0 |
24.8 |
58.16666667 |
8.166666667 |
|
|
$C$17 |
Crude oil
1 Heating oil |
2000 |
0 |
19.9 |
8.166666667 |
58.16666667 |
|
|
$B$18 |
Crude oil
2 Gasoline |
2000 |
0 |
24.8 |
87.25 |
6.125 |
|
|
$C$18 |
Crude oil
2 Heating oil |
8000 |
0 |
19.9 |
6.125 |
14.54166667 |
|
|
|
|
|
|
|
|
|
|
Constraints |
|
|
|
|
|
||
|
|
|
|
Final |
Shadow |
Constraint |
Allowable |
Allowable |
|
|
Cell |
Name |
Value |
Price |
R.H. Side |
Increase |
Decrease |
|
|
$D$17 |
Crude oil
1 Barrels used |
5000 |
30 |
5000 |
10000 |
2500 |
|
|
$D$18 |
Crude oil
2 Barrels used |
10000 |
17 |
10000 |
10000 |
6666.666667 |
|
|
$B$23 |
Quality
"points" obtained Gasoline |
40000 |
-2 |
0 |
5000 |
20000 |
|
|
$C$23 |
Quality
"points" obtained Heating oil |
60000 |
-2 |
0 |
10000 |
6666.666667 |
This
analysis illustrates how sensitive each variable is in relation to the
outcome. In the first table, the focus
is on the objective coefficient. As
long as the coefficient remains between the allowable increase and decrease
then Chandler should continue to produce the same amount. In other words, your recommended value will
remain optimum. The second table is an
analysis on the constraints. The shadow
price is effect one unit of each variable will have on the optimal solution.
To
illustrate a sensitivity analysis preformed on a variable two tables are
included:
|
Sensitivity
of profit and outputs sold to the selling price of gasoline |
||||
|
Price of gasoline |
Gasoline |
Heating oil |
Profit |
|
|
|
$B$19 |
$C$19 |
$B$30 |
Increase |
|
20 |
0 |
15000 |
$298,500 |
|
|
25 |
5000 |
10000 |
$323,000 |
$24,500 |
|
30 |
5000 |
10000 |
$348,000 |
$25,000 |
|
35 |
5000 |
10000 |
$373,000 |
$25,000 |
|
40 |
5000 |
10000 |
$398,000 |
$25,000 |
|
45 |
5000 |
10000 |
$423,000 |
$25,000 |
|
50 |
5000 |
10000 |
$448,000 |
$25,000 |
|
55 |
5000 |
10000 |
$473,000 |
$25,000 |
|
60 |
8333 |
0 |
$498,333 |
$25,333 |
|
65 |
8333 |
0 |
$540,000 |
$41,667 |
|
70 |
8333 |
0 |
$581,667 |
$41,667 |
|
75 |
8333 |
0 |
$623,333 |
$41,667 |
|
80 |
8333 |
0 |
$665,000 |
$41,667 |
The
price of gasoline is increased in increments of 5 from $20 to $80. The barrels sold of each product are listed
to achieve the optimal solution. An
analysis of profit is included with each increase in price. Two things are interesting. First, as the price increases Chandler will
produce more barrels of gasoline and less heating oil. Second, the profit never decreases.
|
Sensitivity
of profit and outputs sold to the availability of crude 1 |
|
|||
|
Availability of crude 1 |
Gasoline |
Heating oil |
Profit |
|
|
|
$B$19 |
$C$19 |
$B$30 |
Increase |
|
2000 |
0 |
10000 |
$199,000 |
|
|
3000 |
1000 |
12000 |
$263,600 |
$64,600 |
|
4000 |
3000 |
11000 |
$293,300 |
$29,700 |
|
5000 |
5000 |
10000 |
$323,000 |
$29,700 |
|
6000 |
7000 |
9000 |
$352,700 |
$29,700 |
|
7000 |
9000 |
8000 |
$382,400 |
$29,700 |
|
8000 |
11000 |
7000 |
$412,100 |
$29,700 |
|
9000 |
13000 |
6000 |
$441,800 |
$29,700 |
|
10000 |
15000 |
5000 |
$471,500 |
$29,700 |
|
11000 |
17000 |
4000 |
$501,200 |
$29,700 |
|
12000 |
19000 |
3000 |
$530,900 |
$29,700 |
|
13000 |
21000 |
2000 |
$560,600 |
$29,700 |
|
14000 |
23000 |
1000 |
$590,300 |
$29,700 |
|
15000 |
25000 |
0 |
$620,000 |
$29,700 |
|
16000 |
26000 |
0 |
$644,800 |
$24,800 |
|
17000 |
27000 |
0 |
$669,600 |
$24,800 |
|
18000 |
28000 |
0 |
$694,400 |
$24,800 |
|
19000 |
29000 |
0 |
$719,200 |
$24,800 |
|
20000 |
30000 |
0 |
$744,000 |
$24,800 |
The
availability of crude oil 1 is increased in increments of 1000 ranging from
2000 to 20000. Again the effect on
gasoline and heating barrels produced is illustrated. The profit does increase but at a decreasing rate. Here again the amount of gasoline produced
is increased while the amount of heating oil decreases.
VI. DSS Guidelines
Chandler
could use this model to view their maximum profit under any constraints that
are described in the model. They can
see the effect on profit when an input is changed. With no demand variable it will be difficult, because more times
than not they will be producing at capacity.