Share
Explore BrainMass

Linear programming Problems using excel

Sunco Oil Co. manufactures three types of gasoline: Gas 1, Gas 2 and Gas 3. Each type is produced by blending three type of crude oil: Crude 1, Crude 2 and Crude 3. The sales price per barrel of gasoline and the purchase price per barrel of crude oil is given in the following table:
Gasoline Type Gas Selling Price Per Barrel Crude Oil Type Crude Oil Purchase Price Per Barrel
1 $70 1 $45
2 $60 2 $35
3 $50 3 $25

Sunco can purchase up to 5,000 barrels of each type of crude oil each day.
The three types of gasoline differ in their octane rating and sulfur content.
The crude oil blended to create Gas 1 must have an average octane rating of at least 10 and contain at most 1% sulfur. The crude oil blended to create Gas 2 must have an average octane rating of at least 8 and contain at most 2% sulfur. The crude oil blended to create Gas 3 must have an average octane rating of at least 6 and contain at most 1% sulfur.
The octane rating and sulfur content of the three types of crude oil is given in the following table:
Crude Oil Type Octane Rating Sulfur Content (%)
1 12 0.5
2 6 2.0
3 8 3.0

It costs $4 to transform one barrel of crude oil into one barrel of gasoline, and Sunco's refinery can produce up to 14,000 barrels of gasoline per day. Sunco has the following contractual obligations to its customers that must be met:
Gasoline Type Barrels / Day
1 3,000
2 2,000
3 1,000

Sunco also has the option of advertising to stimulate demand for its gasoline products. Each dollar spent daily on advertising a particular type of gasoline increases the daily demand for that type of gasoline by 10 barrels.
Create and solve a linear program which maximizes Sunco's daily profits. What are the optimum decisions, i.e. the barrels of crude oil used to create the gasoline and the advertising dollars spent on stimulating the demand for gasoline?
---
(See attached file for full problem description)

Attachments

Solution Summary

Sets up and solves a Linear programming problem using excel.

$2.19