Case study analysis

Case 4.1 Blending Aviation Gasoline at Jansen Gas

Student: Jeremy Murray

Course: Quantitative Methods for Management

Instructor: Roger Preece

Date: 11 Apr 2022

Table of Contents

Executive Summary

Background

Problem Statement

Methodology

Analysis

Results

Conclusion

Recommendations

References

Executive Summary

Case study 4.1 Blending Aviation Gasoline at Jansen Gas involves developing a production plan for three types of aviation fuel that satisfy all company and governmental constraints while maximizing revenue. The known data for the feedstocks and gasoline types will be input into an Excel spreadsheet to create a linear programming blending model. The model enables the use of Excel’s Solver add-in to find the optimal blending solution while calculating the optimal revenue.

Additionally, it examines how the following circumstances impact the optimal revenue:

If the company were to remove the constraint for Gas A >= Gas B.

If the company produced gas B at a medium TEL level.

If government regulations require the company to lower the maximum Reid Vapor level for all gas types.

Executive Summary (Cont.)

Furthermore, if the production of Gas A contains a low TEL level, how much could the company increase the minimum octane rating before there is no feasible solution.

Finally, the study will answer whether the optimal blending plan would remain the same if all feedstock unit values and gas type unit prices were raised equally.

The study will show the optimal blending plan and revenue. It also will show how the company is losing revenue by maintaining the Gas A >= Gas B constraint. On the other hand, the company is earning more income by not producing Gas B at a medium TEL level. Furthermore, the study shows there will be no feasible solution if an equal decrease in maximum Reid pressure levels is required across Gas types. The company would only be able to increase the octane rating of Gas A produced at a Low TEL level by one before there is no feasible solution. Finally, there would be no change to the optimal blending plan and revenue if all feedstock unit values and gas type unit prices were raised equally.

Background

The objective is to develop a production plan for three types of aviation fuels labeled

Gas A, B, and C.

The fuel types are created by blending four feedstocks:

Alkylate, Catalytic Cracked Gasoline, Strait Run Gasoline, and Isopentane.

Inputs for feedstocks:

gallons available, value per gallon, Reid vapor pressure, and Octane rating for Low and High TEL.

Data for the gasoline types A, B, and C:

gallons required, price per gallon, Maximum Reid Vapor pressure, Minimum Octane Rating, and required TEL level.

Background (Cont.)

Constraints:

Produce Gas A at only the Low TEL level and Gas B and C only at the high TEL level.

The amount of Gas A made must be greater than or equal to Gas B

All the gas types should be greater than or equal to the minimum Octane rating

All the gas types should be less than or equal to the maximum Reid Vapor pressure required.

All amounts produced should be greater than or equal to the required amount.

The number of gallons of feedstock used should be less than or equal to the amount available.

Problem Statements

Develop a production plan for three types of aviation fuel that satisfy all company and governmental constraints while maximizing revenue.

Additionally, examine how the following circumstances impact the optimal revenue:

Removal of the constraint for Gas A >= Gas B.

Produce gas B at a medium TEL level.

If government regulations require the equal decrease to maximum Reid Vapor Pressure levels for all gas types.

Furthermore, if the production of Gas A contains a low TEL level, how much could the company increase the minimum octane rating before there is no feasible solution.

Finally, the study will answer whether the optimal blending plan would remain the same if all feedstock unit values and gas type unit prices were raised equally.

Methodology

Create a linear programming blending model using an Excel spreadsheet.

Input known data for the feedstocks and gasoline types extracted from Table 4.6 & 4.7.

Input required constraints that were previously discussed.

Note. Data tables 4.6 & 4.7 were reproduced from p.214 Winston, W. L., & Albright, S. C. (2019). Practical management science

(6th ed.). Cengage.

Methodology (Cont.)

The model enables using Excel’s Solver add-in to find the optimal blending solution while concurrently calculating the optimal revenue.

Additionally, it allows the use of Excel’s SolverTable add-in to run What-if analysis to evaluate possible outcomes for fluctuating conditions. For example, different values of max Reid pressure.

Analysis & Results

The data analysis with all constraints in place is shown in the optimal blending plan in Table 1. The total revenue equated to $1,718,020.

Constraints:

Gas A Low TEL level and Gas B and C high TEL level.

Gas A >= Gas B

All the gas types >= Min Octane rating

All the gas types <= Max Reid Vapor pressure required.

All amounts produced >= required amount.

Gallons of feedstock used <= amount available.

Analysis & Results (Cont.)

The data analysis after removing the constraint of Gas A >= Gas B produced the results in Table 2. The table shows increased use of feedstocks CCG and Isopentane. A decreased production of Gas A and Gas C with increased output of Gas B. The removal of the Gas A >= Gas B constraint resulted in a total revenue gain of $6,988.41.

Analysis & Results (Cont.)

An analysis for producing Gas B with a medium TEL level resulted in an increase in leftover feedstock CCG, and production of Gas B did not meet the minimum required amount. As a result, the optimal revenue would decrease by $74,893.73.

Using the Excel SolverTable add-in, a One-way analysis revealed no feasible solutions if Jansen were required to lower each gas type’s maximum Reid Vapor Pressure levels by equal amounts.

A second One-way analysis indicated Jansen could raise the minimum required octane rating to 91 for Gas A produced with a Low TEL level. However, any further increase would result in no feasible solutions.

A third One-way analysis demonstrated that 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.

Conclusion

Case study 4.1 Blending Aviation Gasoline at Jansen Gas developed a production plan for three types of aviation fuel that satisfy all company and governmental constraints, resulting in a maximized revenue of $1,718,020. Additional analysis showed that removing the restriction for production of Gas A greater than or equal to Gas B resulted in a total revenue gain of $6,988.41. Further investigation into producing Gas B with a medium TEL level showed a loss in revenue of $74,893.73. Additional analysis reveals that producing each gas type with reduced maximum Reid Vapor Pressure levels of equal amounts is not feasible. Additionally, the minimum octane rating for Gas A made with a Low TEL level can only be raised to 91 before there are no viable solutions. Finally, the analysis demonstrated that 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.

Recommendations

The recommendation is to remove the constraint requiring the production of Gas A greater than or equal to Gas B to increase total revenue by $6,988.41. Additionally, continue to require the production of Gas A with a Low TEL level and Gas B and C with a High TEL level. Finally, maintain the minimum octane ratings and maximum Reid Vapor pressure level at the current ratings.

References

Winston, W. L., & Albright, S. C. (2019). Practical management science (6th ed.). Cengage.

Table of Contents

Sheet1

Table 4.6 Data on Feedstock
Feedstock	Alkylate	CCG	SRG	Isopentane
Gallons available (1000s)	140	130	140	110
Value per gallon	$4.50	$2.50	$2.25	$2.35
Reid vapor pressure	5	8	4	20
Octane (low TEL)	98	87	83	101
Octane (high TEL)	107	93	89	108

Sheet1

Table 4.7 Data on Gasoline
Gasoline	A	B	C
Gallons required (1000s)	120	130	120
Price per gallon	$3.00	$3.50	$4.00
Max Reid pressure	7	7	7
Min octane	90	97	100
TEL level	Low	High	High

Sheet1

Table 2.
Optimal blending plan without Gas A >= Gas B constraint.
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
Note: Data is extracted from Excel file 4.1 Blending Aviation Gasoline Jeremy