Question

A refinery in Southern Louisiana is in the business of producing regular and premium
unleaded gasoline. Based on its experience, light and heavy crude oil have to be combined in
the ratio of 1 to 2 and 3 to 2, respectively, for regular and premium gas. Market price of light
crude is $0.3/gallon and $0.2/gallon for heavy crude oil. The objective is to minimize the
total production cost of regular and premium gasoline. Management wants to satisfy the
market demand of 6 million gallons of regular and 10 million gallons of premium gasoline per
period. Formulate the problem as a linear program and obtain the optimal solution using the
Solver Program in Excel. Hint: Define the decision variables as XLR = millions of gallons of
light crude going into regular gas, etc

Answer 1

Let

XLR = millions of gallons of light crude going into regular gas

XHR = millions of gallons of hevy crude going into regular gas

XLP = millions of gallons of light crude going into premium gas

XHP = millions of gallons of hevy crude going into premium gas

The above are the decision variables.

The cost of producing the required amount of regular gas using XLR m-gallons of light crude and XHR m-gallons of  heavy crude is (in million dollars)

The cost of producing the required amount of premium gas using XLP m-gallons of light crude and XHP m-gallons of  heavy crude is (in million dollars)

The total production cost (in million dollars) is

The objective is to minimize this cost and hence the above is the objective function

Now the constraints

light and heavy crude oil have to be combined in the ratio of 1 to 2 for regular gas

• 

light and heavy crude oil have to be combined in the ratio of 3 to 2 for premium gas

• 

Management wants to satisfy the market demand of 6 million gallons of regular gasoline per period.

Since XLR m-gallons of light crude and XHR m-gallons of  heavy crude when combined produces (XLR+XHR) million gallons of regular gas (no loss during blending) we need

• 

Management wants to satisfy the market demand of 10 million gallons of premium gasoline per period.

Since XLP m-gallons of light crude and XHP m-gallons of  heavy crude when combined produces (XLP+XHP) million gallons of regular gas (no loss during blending) we need

• 

The LP model that we want to solve is

Minimize

s.t.

• 
• 
• 
• 
• 

Prepare the following sheet

get this

set up the solver using data--->solver

get this

ans: The optimum solution to minimize the cost is

mix 2 million gallons of light crude and 4 million gallons of heavy crude to get 6 million gallons of regular gas and

mix 6 million gallons of light crude and 4 million gallons of heavy crude to get 10 million gallons of premium gas

The optimum cost is $4 million