Oil Blending Problem

Objective: The objective is to gain experience solving a "blending" linear program. This means not only are ingredients being blended, but the properties in the final blend must meet requirements that are proportional to the amounts put in$.$

Instructions: Solve the problem in Excel using Solver. This problem can be solved as a non$-$linear problem $($Gradient method, rather than Simplex$),$ but for full credit, model it as a linear program. This requires converting nonlinear constraints to linear constraints, as we did in class. You do not need to write out the LP formulation, but you may find it beneficial to do so$.$

Submit to Canvas by saving this file with your $\_[$lastname$]$ in the filename, e$.$g$.,$ "Week $2$ HW Oil Blending$\_$Hoffman.xlsx$"$

Guidance:

$(*)$ Self$-$check: your optimal objective function values should be in the range $\backslash$$$3700-\backslash$$$4000$

$(*)$ To answer each of the questions $($a$),($b$),($c$),$ modify the demand constraints by changing the inequality. That is$,$ consider whether you should be using or $=.$ Once you solve the problem for question $($a$),$ you can copy the entire model $($worksheet$)$ over to another worksheet and modify it to answer question $($b$).$ Similar for $($c$).$ Or$,$ have just one worksheet, and just keep modifying the constraints in the original model.

$(*)$ To compute per$-$unit profit on for decision variable, it is sales price of the blend it is going into minus the cost of the ingredient. So$,$ the cost always stays with the ingredient, but the sales price depends on the which blend it contibutes to$.$ Problem: Oil Blending $($Source: Excercise $2\mathrm{.}5$ of textbook, Optimization Modeling, by Kenneth Baker$)$

An oil company produces three blends of oil: Regular, Multigrade, and Supreme. Each bleand of oil is composed of one or more of four crude stocks, each having a different lubrication index. The cost, supply, and lubrication values for the four crude stocks are shown in the table at right.

Each blend of oil must meet a minimum standard for a lubrication index. The lubcrication index requirements, selling price, and demand for each blend are shown in the table at right. For instance, Regular blend must have a minimum lubrication index of $25,$ sells for $\backslash$$$8\mathrm{.}50$ per barrel, and is in demand for $2,000$ barrels per day.

Determine a daily production plan that maximes total profit, assuming that production can be either sold or else stored at negligible cost.

The daily demand figures are subject to alternative interpretations. Investigate the following:

$($a$)$ The daily demands represent potential sales. In other words, the model should contain demand ceilings $($upper limits$).$ What is the optimal profit?

$($b$)$ The daily demands are strict obligations. In other words, the model should contain demand constraints that are met precisely. What is the optimal profit?

$($c$)$ The daily demands represent minimum sales commitments, but all output can be sold. In other words, the model should permit production to exceed the daily commitments. What is the optimal profit?