Formulate the problem as an optimization model and solve using Excel Solver
Coffeyville Resources is a refinery located in Nebraska. The refinery buys pre-processed crude oil from other countries and final-processes this crude oil into other products here in the U.S. Coffeyville can final-process up to 100,000 barrels of crude oil per day into gasoline, diesel fuel, kerosene, asphalt base, and residual oil. The assumption is that fractional production values are acceptable. All products can be sold directly, other than residual oil, which must be blended with kerosene to generate heavy oil (composed of 10% kerosene and 90% residual oil) and light oil (composed of 20% kerosene and 80% residual oil). The refinery must comply with a contract that establishes a minimum and maximum number of barrels produced per day of each product. Table 1 shows these quantities and the selling price of each product.
Table 1: Selling Price & Minimum and Maximum Number of Barrels per Day
Product Selling Price ($/barrel) Minimum Production (barrels/day) Maximum Production (barrels/day)
Gasoline $3.50 13,000 20,000
Diesel Fuel $2.10 5,000 10,000
Kerosene $3.30 15,000 20,000
Asphalt Base $3.10 10,000 25,000
Heavy Oil $2.50 10,000 20,000
Light Oil $2.80 12,000 20,000
Coffeyville can buy crude oil from three different countries, which have daily availabilities and purchase prices presented in Table 2. It is observed that at least 10,000 barrels per day must be purchased from Saudi Arabia. Furthermore, each country is required to pre-process the number of barrels of crude oil purchased daily according to the percentages described in Table 2. Pre-processing each barrel of crude oil purchased from Kuwait, Saudi Arabia, and Libya costs $0.90, $1.00, and $1.20, respectively, for Coffeyville.
Table 2: Purchase Price, Percentage Pre-processed by each Country for each Product, and Availability
Country Price ($/barrel) Availability (barrels/day) Percentage Pre-processed by each Country for each Product
Kuwait $1.10 70,000 10% Diesel, 10% Asphalt, 60% Residual
Saudi Arabia $1.50 100,000 15% Diesel, 10% Gasoline, 15% Kerosene, 15% Asphalt, 45% Residual
Libya $1.80 50,000 20% Diesel, 10% Gasoline, 20% Kerosene, 20% Asphalt, 30% Residual
