Riverside Oil Company in eastern Kentucky produces regular and supreme gasoline. Each barrel of regular sells for $21 and must have an octane rating of at least 90. Each barrel of supreme sells for $25 and must have an octane rating of at least 97. Each of these types of gasoline are manufactured by mixing different quantities of the following three inputs:
Input______Cost/barrel__Oct rating__Barrels avbl (in 1000s)
1_________$17.25__100__150
2_________$15.75__87__350
3_________$17.75__110__300
Riverside has orders for 300,000 barrels of regular and 450,000 barrels of supreme. How should the company allocate the available inputs to the production of regular and supreme gasoline if they want to maximize profits?
I need help with setting up LP model on excel for this problem
To set up a linear programming (LP) model in Excel for this problem, we need to define some decision variables, objective function, and constraints.
Decision Variables:
Let's define the following decision variables:
R = Number of barrels of regular gasoline to produce (in 1000s)
S = Number of barrels of supreme gasoline to produce (in 1000s)
Objective Function:
The objective is to maximize profit. The profit for each type of gasoline can be calculated by subtracting the cost of inputs used from the selling price. Therefore, the objective function can be defined as:
Profit = 21R + 25S
Constraints:
1. Octane Rating Constraint:
Since each barrel of regular gasoline must have an octane rating of at least 90, and each barrel of supreme gasoline must have an octane rating of at least 97, we can define the octane rating constraint as follows:
100R + 87S >= 90,000 (to satisfy the requirement for regular gasoline)
110R + 87S >= 97,000 (to satisfy the requirement for supreme gasoline)
2. Available Inputs Constraint:
The available quantities of each input should not be exceeded. Using the given data, we can define the available inputs constraint as follows:
17.25R + 15.75S <= 150,000 (for input 1)
17.75R + 15.75S <= 350,000 (for input 2)
17.75R + 22S <= 300,000 (for input 3)
3. Demand Constraint:
The total barrels produced (R + S) should not exceed the demand for regular and supreme gasoline. We can define this constraint as:
R + S <= 750
Now, you can enter these formulas and constraints into the Excel spreadsheet and use the Solver add-in to solve the LP model and maximize the profit.
1. Set up the cells for decision variables, objective function, and constraints.
2. Define the objective cell as the sum of the profit.
3. Define the changing cells as the decision variables (R and S).
4. Enter the coefficients for the objective function (selling prices) and constraints (coefficients of decision variables).
5. Enter the right-hand side values (octane rating requirements and available quantities).
6. Define the constraints as "greater than or equal to" type.
7. Set the Solver to maximize the objective by changing the decision variables, subject to the defined constraints.
8. Run Solver to find the optimal solution (values of R and S) that maximize the profit.
By solving the LP model using the Solver in Excel, you will get the optimal allocation of inputs (R and S) that maximizes the profit for Riverside Oil Company.