develop a what-if model that will determine the optimal decision for a product-blending problem

Remember that your spreadsheet should be built containing formulas to calculate all values using the input parameters – hard-coding is NOT permitted! Additionally, the template is NOT to be modified in any way; adding or subtracting cells will be penalized.

Problem Statement

Suppose that you work for a petroleum products company and are responsible for deciding how many barrels of Gasoline and how many barrels of Heating Oil your unit will produce. Your objective to make as much profit as possible, but you are limited by how many barrels of Crude Oil you have available with which to make Gasoline and Heating Oil. To make matters worse, there are two grades of Crude Oil which must be mixed together to produce either Gasoline or Heating Oil, and both finished products (meaning Gasoline and Heating Oil) have different requirements for the minimum average quality level of the Crude Oil. The tables below summarize relevant information for both sets of raw materials and finished goods.

Crude Oil

Finished Products

Your goal is to determine the best blending plan that maximizes profit without violating any of the constraints. But first, a hint; rather than think of the quality level as a weighted average, consider that each barrel of finished goods will need at least the required quality level, so for example every barrel of Gasoline made will need 8 quality points, and thus to make 5 barrels of Gasoline, one must have 40 quality points. So, using 5 barrels of Crude Oil 1 is fine since that represents 50 quality points, but using 5 barrels of Crude Oil 2 won’t work since that’s only 25 quality points. A minimum middle ground would be 3 barrels of Crude Oil 1 and 2 barrels of Crude Oil 2, resulting in exactly the minimum of 40 quality points. So, to determine if your blending plan meets these requirements, you’ll need to calculate both how many quality points you need for a given number of produced barrels and how many quality points are provided by the barrels you’ve selected.

To reiterate, 3 barrels of Crude Oil 1 and 2 barrels of Crude Oil 2 provide a total of 40 quality points, which meets the requirement of Gasoline (5*8=40 quality points) and exceeds the requirement for Heating Oil (5*6=30 quality points).

Part 1

Begin by developing a what-if model using the template provided to represent the problem above, and don’t forget to include relation symbols between the constraints. To validate your model, enter a test solution according to the table below. If you have built the model correctly, you should find a Total Profit of $318,100.

Once you’re sure your what-if model is correct, create an optimization model in Solver to determine the optimal blending plan to maximize profit. Have Solver produce both the Answer and Sensitivity Reports.

Part 2

The worksheet “Interpretation” contains answer blanks to use when responding to the following interpretation questions:

• Indicate which of the constraints are binding by choosing from the dropdown menus.
• If the number of available barrels of Crude Oil 1 were increased by 1, how would this affect the optimal solution? Indicate if the optimal profit would improve or drop by choosing from the dropdown menu in cell B11, then enter the magnitude (absolute value) of change in C11.
• What is the largest number of available barrels of Crude Oil 1 where the relationship described in Question 2 remains true? Enter your response in C14.