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 Quality Level per Barrel 10 S Barrels Available 5000 10000 Crude Oil 1 Crude Oil 2 Finished Products Required Quality Level Selling Price per Barrel Advertising Cost per Barrel Gasoline 8 $25 $0.20 Heating Oil 6 $20 $0.10 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. Gasoline Heating Oil Crude Oil 1 2500 2500 Crude Oil 2 1500 8500 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: 1. Indicate which of the constraints are binding by choosing from the dropdown menus. 2. If the number of available barrels of Crude Oil I 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. 3. 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. Once both Parts 1 and 2 are completed, save and upload your completed Template to the Module 11 Linear Programming II Homework folder. Chandler Blending Problem Parameters Monetary inputs Selling price/barrel Advertising cost/barrel Gasoline $25.00 $0.20 Heating oil $20.00 $0.10 Quality level per barrel of crudes Crude oil 1 10 Crude oil 2 Crude oil 1 Crude oil 2 Barrels available 5000 10000 5 Required quality level per barrel of product Gasoline Heating oil 8 6 Model Barrels used Barrels available Blending plan (barrels of crudes in each product) Gasoline Heating oil Crude oil 1 Crude oil 2 Constraints on quality Quality "points" obtained Gasoline Heating oil Quality "points" required Profit summary Gasoline Heating oil Profit/barrel Barrels sold Total profit Question 1 Status Constraint Quality "points" obtained Gasoline Quality "points" obtained Heating oil Crude oil 1 Barrels used Crude oil 2 Barrels used Binding Not Binding Question 2 If Barrels Available of Crude Oil 1 were to go up by 1, the optimal profit would Question 3 Crude Oil 1 Barrels Available 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 Quality Level per Barrel 10 S Barrels Available 5000 10000 Crude Oil 1 Crude Oil 2 Finished Products Required Quality Level Selling Price per Barrel Advertising Cost per Barrel Gasoline 8 $25 $0.20 Heating Oil 6 $20 $0.10 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. Gasoline Heating Oil Crude Oil 1 2500 2500 Crude Oil 2 1500 8500 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: 1. Indicate which of the constraints are binding by choosing from the dropdown menus. 2. If the number of available barrels of Crude Oil I 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. 3. 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. Once both Parts 1 and 2 are completed, save and upload your completed Template to the Module 11 Linear Programming II Homework folder. Chandler Blending Problem Parameters Monetary inputs Selling price/barrel Advertising cost/barrel Gasoline $25.00 $0.20 Heating oil $20.00 $0.10 Quality level per barrel of crudes Crude oil 1 10 Crude oil 2 Crude oil 1 Crude oil 2 Barrels available 5000 10000 5 Required quality level per barrel of product Gasoline Heating oil 8 6 Model Barrels used Barrels available Blending plan (barrels of crudes in each product) Gasoline Heating oil Crude oil 1 Crude oil 2 Constraints on quality Quality "points" obtained Gasoline Heating oil Quality "points" required Profit summary Gasoline Heating oil Profit/barrel Barrels sold Total profit Question 1 Status Constraint Quality "points" obtained Gasoline Quality "points" obtained Heating oil Crude oil 1 Barrels used Crude oil 2 Barrels used Binding Not Binding Question 2 If Barrels Available of Crude Oil 1 were to go up by 1, the optimal profit would Question 3 Crude Oil 1 Barrels Available