It's a Gasser!

Here is a petroleum blending model that is a simplified version of LP models used at refineries.

Three kinds of gasoline are to be blended from four different feedstocks of oil. The three kinds of gasoline are differentiated based upon the minimum level of octane that each averages.

REG-87 must have at least an octane rating of 87, REG-89 must have at least an octane rating of 89, and PREM-93 must have at least an octane rating of 93. Also, individually, each type of gas must maintain a weighted average of vapor pressure not to exceed 12.7 units. (Both the octane number/rating and vapor pressure are weighted averages).

For the next production period, you wish to make at least 200 bbl of REG-87, and at least 100 bbl of both REG-89 and PREM-93 individually. There is no upper limit on the amount of REG-87 to make, but you should make no more than 350 bbl of REG-89 and 200 bbl of PREM-93

Your goal in the production model is to maximize net contribution. REG-87 contributes $100/bbl, REG-89 $110/bbl, and PREM-93 $135/bbl. The cost of each of the four feedstocks is shown below in the attribute table (along with their Octane numbers and vapor pressure, and the maximum amount of the feed stock available).

A couple of other restrictions in production of the gasoline - REG-87 cannot use any oil from FS3 (feedstock 3). Also, no more than 35% of any product can be made up from any one feedstock. The appropriate data for the feedstocks (referred to as FS1 through FS4).

Feedstocks

REQUIREMENT: Use matrix approach to implement and solve this 'blending' problem in Excel.