StudentNumber-1-4.1BlendingAviationGasolineatJansenGas.xlsx
Case Study Presentations and Discussions
April 16, 2022Studentnumber2-Discussionpost-CaseStudy4.2.docx
April 16, 2022Jansen Gas
| Blending Aviation Gasoline at Jansen Gas | Range names used | ||||||||
| Blending_plan | =Sheet1!$B$18:$D$21 | ||||||||
| Data on Feedstocks | Value per gallon | Reid vapor pressure | Octane (low TEL) | Octane (high TEL) | Gallons_available__1000s | =Sheet1!$G$18:$G$21 | |||
| Alkylate | $4.50 | 5 | 98 | 107 | Gallons_produced_A | =Sheet1!$B$27 | |||
| CCG | $2.50 | 8 | 87 | 93 | Gallons_produced_B | =Sheet1!$D$27 | |||
| SRG | $2.25 | 4 | 83 | 89 | Gallons_sold__1000s | =Sheet1!$B$22:$D$22 | |||
| Isopentane | $2.35 | 20 | 101 | 108 | Gasoline_required__1000s | =Sheet1!$B$24:$D$24 | |||
| Leftover__1000s | =Sheet1!$H$18:$H$21 | ||||||||
| Data on Gasoline | Gas A | Gas B | Gas C | Max_Ried_vapor_pressure_allowed | =Sheet1!$H$30:$J$30 | ||||
| Gallons required (1000s) | 120 | 130 | 120 | Min_octane_required | =Sheet1!$H$26:$J$26 | ||||
| Price per gallon | $3.00 | $3.50 | $4.00 | Octane_level_obtained | =Sheet1!$H$24:$J$24 | ||||
| Min octane required | 90 | 97 | 100 | Ried_vapor_pressure_obtained | =Sheet1!$H$28:$J$28 | ||||
| Max Reid pressure | 7 | 7 | 7 | Total_revenue__Millions | =Sheet1!$B$30 | ||||
| TEL level | Low | High | High | Used | =Sheet1!$E$18:$E$21 | ||||
| Blending plan | Gas A | Gas B | Gas C | Used | Gallons available (1000s) | Leftover (1000s) | |||
| Alkylate | 37.13 | 34.31 | 68.56 | 140.00 | <= | 140 | 0.00 | ||
| CCG | 87.89 | 0.00 | 0.00 | 87.89 | <= | 130 | 42.11 | ||
| SRG | 4.90 | 73.46 | 61.64 | 140.00 | <= | 140 | 0.00 | ||
| Isopentane | 0.08 | 22.23 | 24.77 | 47.09 | <= | 110 | 62.91 | ||
| Gallons sold (1000s) | 130.00 | 130.00 | 154.97 | ||||||
| >= | >= | >= | Quality Constraints | Gas A | Gas B | Gas C | |||
| Gasoline required (1000s) | 120 | 130 | 120 | Octane level obtained | 11700.00 | 12610.00 | 15497.26 | ||
| >= | >= | >= | |||||||
| Production Constraint | Gas A | Gas B | Min octane required | 11700.00 | 12610.00 | 15497.26 | |||
| Gallons produced | 130.00 | >= | 130.00 | ||||||
| Ried vapor pressure obtained | 910.00 | 910.00 | 1084.81 | ||||||
| Objective to Maximize | <= | <= | <= | ||||||
| Total revenue (Millions) | $ 1,718.02 | Max Ried vapor pressure allowed | 910.00 | 910.00 | 1084.81 | ||||
Altered without A >= B
| Blending Aviation Gasoline at Jansen Gas | Range names used | ||||||||
| Altered_Total_revenue__Millions | ='Altered without A >= B '!$B$26 | ||||||||
| Data on Feedstocks | Value per gallon | Reid vapor pressure | Octane (low TEL) | Octane (high TEL) | Blending_plan | ='Altered without A >= B '!$B$17:$D$20 | |||
| Alkylate | $4.50 | 5 | 98 | 107 | Gallons_available__1000s | ='Altered without A >= B '!$G$17:$G$20 | |||
| CCG | $2.50 | 8 | 87 | 93 | Gallons_sold__1000s | ='Altered without A >= B '!$B$21:$D$21 | |||
| SRG | $2.25 | 4 | 83 | 89 | Gasoline_required__1000s | ='Altered without A >= B '!$B$23:$D$23 | |||
| Isopentane | $2.35 | 20 | 101 | 108 | Leftover__1000s | ='Altered without A >= B '!$H$17:$H$20 | |||
| Max_Ried_vapor_pressure_allowed | ='Altered without A >= B '!$H$29:$J$29 | ||||||||
| Data on Gasoline | Gas A | Gas B | Gas C | Min_octane_required | ='Altered without A >= B '!$H$25:$J$25 | ||||
| Gallons required (1000s) | 120 | 130 | 120 | Octane_level_obtained | ='Altered without A >= B '!$H$23:$J$23 | ||||
| Price per gallon | $3.00 | $3.50 | $4.00 | Ried_vapor_pressure_obtained | ='Altered without A >= B '!$H$27:$J$27 | ||||
| Min octane required | 90 | 97 | 100 | Total_revenue__Millions | ='Jensen Gas'!$B$29 | ||||
| Max Reid pressure | 7 | 7 | 7 | Used | ='Altered without A >= B '!$E$17:$E$20 | ||||
| TEL level | Low | High | High | ||||||
| Blending plan | Gas A | Gas B | Gas C | Used | Gallons available (1000s) | Leftover (1000s) | |||
| Alkylate | 34.29 | 52.63 | 53.09 | 140.00 | <= | 140 | 0.00 | ||
| CCG | 81.43 | 27.82 | 0.00 | 109.25 | <= | 130 | 20.75 | ||
| SRG | 4.29 | 87.98 | 47.73 | 140.00 | <= | 140 | 0.00 | ||
| Isopentane | 0.00 | 26.26 | 19.18 | 45.44 | <= | 110 | 64.56 | ||
| Gallons sold (1000s) | 120.00 | 194.69 | 120.00 | ||||||
| >= | >= | >= | Quality Constraints | Gas A | Gas B | Gas C | |||
| Gasoline required (1000s) | 120 | 130 | 120 | Octane level obtained | 10800.00 | 18885.31 | 12000.00 | ||
| >= | >= | >= | |||||||
| Objective to Maximize | Min octane required | 10800.00 | 18885.31 | 12000.00 | |||||
| Altered Total revenue (Millions) | $ 1,725.01 | ||||||||
| Difference w/o Gas A >= Gas B | $ 6,988.41 | Ried vapor pressure obtained | 840.00 | 1362.86 | 840.00 | ||||
| <= | <= | <= | |||||||
| Max Ried vapor pressure allowed | 840.00 | 1362.86 | 840.00 | ||||||
1. The constraint of Gas >= Gas B is costing the company $6,988.41 in lost revenue.
Altered Medium TEL
| Blending Aviation Gasoline at Jansen Gas | Range names used | ||||||||
| Altered_TEL_Total_revenue__Millions | ='Altered Medium TEL'!$B$29 | ||||||||
| Data on Feedstocks | Value per gallon | Reid vapor pressure | Octane (low TEL) | Octane (medium TEL) | Octane (high TEL) | Blending_plan | ='Altered Medium TEL'!$B$17:$D$20 | ||
| Alkylate | $4.50 | 5 | 98 | 102.50 | 107 | Gallons_available__1000s | ='Altered Medium TEL'!$G$17:$G$20 | ||
| CCG | $2.50 | 8 | 87 | 90.00 | 93 | Gallons_produced_A | ='Altered Medium TEL'!$B$26 | ||
| SRG | $2.25 | 4 | 83 | 86.00 | 89 | Gallons_produced_B | ='Altered Medium TEL'!$D$26 | ||
| Isopentane | $2.35 | 20 | 101 | 104.50 | 108 | Gallons_sold__1000s | ='Altered Medium TEL'!$B$21:$D$21 | ||
| Gasoline_required__1000s | ='Altered Medium TEL'!$B$23:$D$23 | ||||||||
| Data on Gasoline | Gas A | Gas B | Gas C | Leftover__1000s | ='Altered Medium TEL'!$H$17:$H$20 | ||||
| Gallons required (1000s) | 120 | 130 | 120 | Max_Ried_vapor_pressure_allowed | ='Altered Medium TEL'!$H$29:$J$29 | ||||
| Price per gallon | $3.00 | $3.50 | $4.00 | Min_octane_required | ='Altered Medium TEL'!$H$25:$J$25 | ||||
| Min octane required | 90 | 97 | 100 | Octane_level_obtained | ='Altered Medium TEL'!$H$23:$J$23 | ||||
| Max Reid pressure | 7 | 7 | 7 | Ried_vapor_pressure_obtained | ='Altered Medium TEL'!$H$27:$J$27 | ||||
| TEL level | Low | High | High | Total_revenue__Millions | ='Jensen Gas'!$B$29 | ||||
| Used | ='Altered Medium TEL'!$E$17:$E$20 | ||||||||
| Blending plan | Gas A | Gas B | Gas C | Used | Gallons available (1000s) | Leftover (1000s) | |||
| Alkylate | 32.05 | 54.86 | 53.09 | 140.00 | <= | 140 | 0.00 | ||
| CCG | 19.42 | 0.00 | 0.00 | 19.42 | <= | 130 | 110.58 | ||
| SRG | 52.88 | 39.38 | 47.73 | 140.00 | <= | 140 | 0.00 | ||
| Isopentane | 15.64 | 17.53 | 19.18 | 52.35 | <= | 110 | 57.65 | ||
| Gallons sold (1000s) | 120.00 | 111.78 | 120.00 | ||||||
| >= | >= | >= | Quality Constraints | Gas A | Gas B | Gas C | |||
| Gasoline required (1000s) | 120 | 130 | 120 | Octane level obtained | 10800.00 | 10842.20 | 12000.00 | ||
| >= | >= | >= | |||||||
| Production Constraint | Gas A | Gas B | Min octane required | 10800.00 | 10842.20 | 12000.00 | |||
| Gallons produced | 120.00 | >= | 111.78 | ||||||
| Ried vapor pressure obtained | 840.00 | 782.43 | 840.00 | ||||||
| Objective to Maximize | <= | <= | <= | ||||||
| Altered TEL Total revenue (Millions) | $ 1,643.13 | Max Ried vapor pressure allowed | 840.00 | 782.43 | 840.00 | ||||
| Difference w/medium TEL | $ (74,893.73) | ||||||||
2. The optimal revenue would decrease by $74,893.73 if Gas B was produced with a medium TEL level.
Altered Max Reid
| Blending Aviation Gasoline at Jansen Gas | Range names used | ||||||||
| Blending_plan | ='Altered Max Reid'!$B$17:$D$20 | ||||||||
| Data on Feedstocks | Value per gallon | Reid vapor pressure | Octane (low TEL) | Octane (high TEL) | Gallons_available__1000s | ='Altered Max Reid'!$G$17:$G$20 | |||
| Alkylate | $4.50 | 5 | 98 | 107 | Gallons_produced_A | ='Altered Max Reid'!$B$26 | |||
| CCG | $2.50 | 8 | 87 | 93 | Gallons_produced_B | ='Altered Max Reid'!$D$26 | |||
| SRG | $2.25 | 4 | 83 | 89 | Gallons_sold__1000s | ='Altered Max Reid'!$B$21:$D$21 | |||
| Isopentane | $2.35 | 20 | 101 | 108 | Gasoline_required__1000s | ='Altered Max Reid'!$B$23:$D$23 | |||
| Leftover__1000s | ='Altered Max Reid'!$H$17:$H$20 | ||||||||
| Data on Gasoline | Gas A | Gas B | Gas C | Max_Ried_vapor_pressure_allowed | ='Altered Max Reid'!$H$29:$J$29 | ||||
| Gallons required (1000s) | 120 | 130 | 120 | Min_octane_required | ='Altered Max Reid'!$H$25:$J$25 | ||||
| Price per gallon | $3.00 | $3.50 | $4.00 | Octane_level_obtained | ='Altered Max Reid'!$H$23:$J$23 | ||||
| Min octane required | 90 | 97 | 100 | Ried_vapor_pressure_obtained | ='Altered Max Reid'!$H$27:$J$27 | ||||
| Max Reid pressure | 7 | 7 | 7 | Max Reid Change | Total_revenue__Millions | ='Altered Max Reid'!$B$29 | |||
| Altered max Reid level | 7 | 7 | 7 | 0 | |||||
| TEL level | Low | High | High | Used | ='Altered Max Reid'!$E$17:$E$20 | ||||
| Blending plan | Gas A | Gas B | Gas C | Used | Gallons available (1000s) | Leftover (1000s) | |||
| Alkylate | 37.13 | 34.31 | 68.56 | 140.00 | <= | 140 | -0.00 | ||
| CCG | 87.89 | 0.00 | 0.00 | 87.89 | <= | 130 | 42.11 | ||
| SRG | 4.90 | 73.46 | 61.64 | 140.00 | <= | 140 | -0.00 | ||
| Isopentane | 0.08 | 22.23 | 24.77 | 47.09 | <= | 110 | 62.91 | ||
| Gallons sold (1000s) | 130.00 | 130.00 | 154.97 | ||||||
| >= | >= | >= | Quality Constraints | Gas A | Gas B | Gas C | |||
| Gasoline required (1000s) | 120 | 130 | 120 | Octane level obtained | 11700.00 | 12610.00 | 15497.26 | ||
| >= | >= | >= | |||||||
| Production Constraint | Gas A | Gas B | Min octane required | 11700.00 | 12610.00 | 15497.26 | |||
| Gallons produced | 130.00 | >= | 130.00 | ||||||
| Ried vapor pressure obtained | 910.00 | 910.00 | 1084.81 | ||||||
| Objective to Maximize | <= | <= | <= | ||||||
| Total revenue (Millions) | $ 1,718.02 | Max Ried vapor pressure allowed | 910.00 | 910.00 | 1084.81 | ||||
Altered Max Reid_STS
| 1 |
| $E$14 |
| 1 |
| 0 |
| 7 |
| 1 |
| $B$30 |
| Max Reid pressure decrease |
Jensen Gas (2)_STS
| 1 |
| $E$13 |
| 1 |
| 1 |
| 7 |
| 1 |
| $B$29 |
| Input |
Jensen Gas_STS
| 1 | 1 |
| $B$12 | $B$13 |
| 1 | 1 |
| 90 | 1 |
| 100 | 7 |
| 1 | 1 |
| $B$29 | $C$13 |
| Minimum required octane rating for Gas A | 1 |
| 1 | |
| 7 | |
| 1 | |
| $B$29 | |
| Max Reid vapor pressure | |
| Input2 |
STS_1
| Oneway analysis for Solver model in Altered Max Reid worksheet | Sensitivity of Total_revenue__Millions to Max Reid pressure decrease | |||||||||
| Max Reid pressure decrease (cell $E$14) values along side, output cell(s) along top | Data for chart | |||||||||
| Total_revenue__Millions | 1 | Total_revenue__Millions | ||||||||
| 0 | $ 1,718.02 Jeremy Murray: Solver found a solution. All constraints and optimality conditions are satisfied. |
1718.02 | ||||||||
| 1 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 2 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 3 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 4 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 5 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 6 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 7 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible |
Sensitivity of Total_revenue__Millions to Max Reid pressure decrease
012345671718.020000000
Max Reid pressure decrease ($E$14)
When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output.
3. There is no feasible solution if Jansen were to lower the maximum Reid vapor pressure levels on each gas type by the same amount.
STS_2
| Oneway analysis for Solver model in Jansen Gas worksheet | Sensitivity of Total_revenue__Millions to Minimum required octane rating for Gas A | |||||||||
| Minimum required octane rating for Gas A (cell $B$12) values along side, output cell(s) along top | Data for chart | |||||||||
| Total_revenue__Millions | 1 | Total_revenue__Millions | ||||||||
| 90 | $ 1,718.02 Jeremy Murray: Solver found a solution. All constraints and optimality conditions are satisfied. |
1718.02 | ||||||||
| 91 | $ 1,689.07 Jeremy Murray: Solver found a solution. All constraints and optimality conditions are satisfied. |
1689.07 | ||||||||
| 92 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 93 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 94 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 95 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 96 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 97 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 98 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 99 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible | ||||||||
| 100 | Not feasible Jeremy Murray: Solver could not find a feasible solution. |
Not feasible |
Sensitivity of Total_revenue__Millions to Minimum required octane rating for Gas A
909192939495969798991001718.021689.07000000000
Minimum required octane rating for Gas A ($B$12)
When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output.
4. Jansen could raise the minimum required octane rating to 91 for Gas A. Any further increase would result in no feasible solutions.
% increase model
| Blending Aviation Gasoline at Jansen Gas | |||||||||
| Data on Feedstocks | Value per gallon | Altered value per gallon | Reid vapor pressure | Octane (low TEL) | Octane (high TEL) | Percentage increase | |||
| Alkylate | $4.50 | $4.50 | 5 | 98 | 107 | 0% | |||
| CCG | $2.50 | $2.50 | 8 | 87 | 93 | ||||
| SRG | $2.25 | $2.25 | 4 | 83 | 89 | ||||
| Isopentane | $2.35 | $2.35 | 20 | 101 | 108 | ||||
| Data on Gasoline | Gas A | Gas B | Gas C | ||||||
| Gallons required (1000s) | 120 | 130 | 120 | ||||||
| Price per gallon | $3.00 | $3.50 | $4.00 | ||||||
| Altered price per gallon | $3.00 | $3.50 | $4.00 | ||||||
| Min octane required | 90 | 97 | 100 | ||||||
| Max Reid pressure | 7 | 7 | 7 | ||||||
| TEL level | Low | High | High | ||||||
| Blending plan | Gas A | Gas B | Gas C | Used | Gallons available (1000s) | Leftover (1000s) | |||
| Alkylate | 37.13 | 34.31 | 68.56 | 140.00 | <= | 140 | -0.00 | ||
| CCG | 87.89 | 0.00 | 0.00 | 87.89 | <= | 130 | 42.11 | ||
| SRG | 4.90 | 73.46 | 61.64 | 140.00 | <= | 140 | -0.00 | ||
| Isopentane | 0.08 | 22.23 | 24.77 | 47.09 | <= | 110 | 62.91 | ||
| Gallons sold (1000s) | 130.00 | 130.00 | 154.97 | ||||||
| >= | >= | >= | Quality Constraints | Gas A | Gas B | Gas C | |||
| Gasoline required (1000s) | 120 | 130 | 120 | Octane level obtained | 11700.00 | 12610.00 | 15497.26 | ||
| >= | >= | >= | |||||||
| Production Constraint | Gas A | Gas B | Min octane required | 11700.00 | 12610.00 | 15497.26 | |||
| Gallons produced | 130.00 | >= | 130.00 | ||||||
| Ried vapor pressure obtained | 910.00 | 910.00 | 1084.81 | ||||||
| Objective to Maximize | <= | <= | <= | ||||||
| Total revenue (Millions) | $ 1,718.02 | Max Ried vapor pressure allowed | 910.00 | 910.00 | 1084.81 | ||||
Jensen Gas % increase_STS
| 1 |
| $G$11 |
| 1 |
| 0 |
| 0.25 |
| 0.05 |
| $B$18:$D$21,$B$30 |
| Percentage increase |
STS_3
| Oneway analysis for Solver model in Jansen Gas % increase worksheet | Sensitivity of Total_revenue__Millions to Percentage increase | ||||||||||||||
| Percentage increase (cell $G$11) values along side, output cell(s) along top | Data for chart | ||||||||||||||
| Blending_plan_1 | Blending_plan_2 | Blending_plan_3 | Blending_plan_4 | Blending_plan_5 | Blending_plan_6 | Blending_plan_7 | Blending_plan_8 | Blending_plan_9 | Blending_plan_10 | Blending_plan_11 | Blending_plan_12 | Total_revenue__Millions | 13 | Total_revenue__Millions | |
| 0% | 37.13 Jeremy Murray: Solver found a solution. All constraints and optimality conditions are satisfied. |
34.31 | 68.56 | 87.89 | 0.00 | 0.00 | 4.90 | 73.46 | 61.64 | 0.08 | 22.23 | 24.77 | $ 1,718.02 | 1718.02 | |
| 5% | 37.13 Jeremy Murray: Solver found a solution. All constraints and optimality conditions are satisfied. |
34.31 | 68.56 | 87.89 | 0.00 | 0.00 | 4.90 | 73.46 | 61.64 | 0.08 | 22.23 | 24.77 | $ 1,803.92 | 1803.92 | |
| 10% | 37.13 Jeremy Murray: Solver found a solution. All constraints and optimality conditions are satisfied. |
34.31 | 68.56 | 87.89 | 0.00 | 0.00 | 4.90 | 73.46 | 61.64 | 0.08 | 22.23 | 24.77 | $ 1,889.82 | 1889.82 | |
| 15% | 37.13 Jeremy Murray: Solver found a solution. All constraints and optimality conditions are satisfied. |
34.31 | 68.56 | 87.89 | 0.00 | 0.00 | 4.90 | 73.46 | 61.64 | 0.08 | 22.23 | 24.77 | $ 1,975.73 | 1975.73 | |
| 20% | 37.13 Jeremy Murray: Solver found a solution. All constraints and optimality conditions are satisfied. |
34.31 | 68.56 | 87.89 | 0.00 | 0.00 | 4.90 | 73.46 | 61.64 | 0.08 | 22.23 | 24.77 | $ 2,061.63 | 2061.63 | |
| 25% | 37.13 Jeremy Murray: Solver found a solution. All constraints and optimality conditions are satisfied. |
34.31 | 68.56 | 87.89 | 0.00 | 0.00 | 4.90 | 73.46 | 61.64 | 0.08 | 22.23 | 24.77 | $ 2,147.53 | 2147.53 |
Sensitivity of Total_revenue__Millions to Percentage increase
05.000000074505806E-20.100000001490116120.150000005960464480.200000002980232240.251718.021803.921889.821975.732061.632147.5300000000002
Percentage increase ($G$11)
When you select an output from the dropdown list in cell $P$4, the chart will adapt to that output.
5. If all unit prices of the gas types and all unit values of the feedstocks increase by the same percentage, then the optimal blending plan remains the same.
