April 16, 2022

Case 4.1 Blending Aviation Gasoline at Jansen Gas, page 214 For the project, you will write a proposal for the case study and submit the

Case 4.1 Blending Aviation Gasoline at Jansen Gas, page 214

For the project, you will write a proposal for the case study and submit the final product. The final product will be an Excel spreadsheet used to create the model and either a Word document or a Power Point presentation. The final project will be graded not only on the accuracy of the quantitative solutions, but also the analytical approach used and the presentation of the results. Keep in mind that this course is designed for individuals interested in Business Management. As such, the final presentation should be appropriate for a presentation in a professional setting. It will be necessary to clearly explain the case study and present the results in a professional, yet easily understood manner.

Use this discussion forum to submit your case study and review others students' submissions. Please submit your Executive Summary (Abstract) and your Conclusions from your project. Review a minimum of one other case study report and provide comments / feedback to your classmate(s).

Student number 2- Discussion Post

Included is my presentation for Case Study 4.2 – Delinquent Accounts at GE Capital. I created a model to maximize revenue over the course of 4 months and sensitivity analyses to determine how various changes in the data affect total revenue.

Student Number 1-

My presentation was on case study 4.1 Blending Aviation Gasoline at Jansen Gas. I used a blending model that shows the optimal blending plan of four feedstocks producing three types of gas and maximizes total revenue. I altered the original by removing the constraint of Gas A >= Gas B and added a calculation of the difference in total revenues. A second altered model adds the Octane (medium TEL) to the feedstocks and calculates the difference in total revenue. A third altered model adds and altered max Reid level and max Reid level change input and then I ran a one-way analysis to show that there are no feasible solutions if each gas type required a reduction in the max Reid pressure. I did a model with similar changes but to the min octane ratings that showed that if Gas A was produced at a low TEL level the min octane rating could only increase to 91. Finally, the last model alters the value per gallon of feedstocks and price per gallon of the gas types then I ran a one-way analysis to prove 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.

Data

GM Capital
									DMM if account receives a letter Chris Albright: There is no 0-due row because a letter is never sent to a 0-due account.
									FromTo	0-due	1-due	2-due	3-due	Bad debt
									1-due	60%	10%	30%	0%	0%
Contact Methods	Time Required (Min):	Time Required (Hrs):							2-due	15%	15%	40%	30%	0%
Phone Call	40	0.67							3-due	0%	0%	20%	60%	20%
Letter	20	0.33							Bad debt	0%	0%	0%	0%	100%
No Contact	0	0.00
	Minutes	Hours
Time Available	300000	5000							DMM if account receives a phone call Chris Albright: There is no 0-due row because a phone call is never made to a 0-due account.
									Chris Albright: There is no 0-due row because a letter is never sent to a 0-due account.	FromTo	0-due	1-due	2-due	3-due	Bad debt
									1-due	70%	20%	10%	0%	0%
Average Monthly Payment	$ 10,000.00								2-due	30%	25%	30%	15%	0%
									3-due	0%	0%	50%	40%	10%
No of Accounts									Bad debt	0%	0%	0%	0%	100%
0-Due	40000
1-Due	4000
2-Due	4000								DMM for do-nothing action
3-Due	2000								FromTo	0-due	1-due	2-due	3-due	Bad debt
Bad Debt	0								0-due	90%	10%	0%	0%	0%
									1-due	20%	25%	55%	0%	0%
MONTH 1:									2-due	10%	10%	20%	60%	0%
	Send Letter	Phone Call	Do Nothing						3-due	0%	0%	10%	40%	50%
0-Due	0	0	40000	40000	=	40000			Bad debt	0%	0%	0%	0%	100%
1-Due	4000	0	0	4000	=	4000
2-Due	0	3500	500	4000	=	4000			Number of Accounts	0-due	1-due	2-due	3-due	Bad debt
3-Due	0	2000	0	2000	=	2000				40000	4000	4000	2000	0
Bad Debt	0	0	0	0	=	0			Earnings Matrix
Total:	80000	220000	0.00	300000.00	<=	300000			FromTo	0-due	1-due	2-due	3-due	Bad debt	Total:
									0-Due	$ 10,000.00	$ – 0	$ – 0	$ – 0	$ – 0	$ 10,000.00
MONTH 2:	Send Letter	Phone Call	Do Nothing						1-due	$ 20,000.00	$ 10,000.00	$ – 0	$ – 0	$ – 0	$ 30,000.00
0-Due	0	0	39500	39500	=	39500			2-due	$ 30,000.00	$ 20,000.00	$ 10,000.00	$ – 0	$ – 0	$ 60,000.00
1-Due	5050	0	275	5325	=	5325			3-due	$ 40,000.00	$ 30,000.00	$ 20,000.00	$ 10,000.00	$ – 0	$ 100,000.00
2-Due	0	3350	0	3350	=	3350			Bad debt	$ – 0	$ – 0	$ – 0	$ – 0	$ – 0	$ – 0
3-Due	0	1625	0	1625	=	1625
Bad Debt	0	0	200	200	=	200
Total:	101000	199000	0	300000	<=	300000
MONTH 3:	Send Letter	Phone Call	Do Nothing
0-Due	0	0	39640	39640	=	39640
1-Due	4995	366	0	5361	=	5361
2-Due	0	3484	0	3484	=	3484
3-Due	0	1153	0	1153	=	1153
Bad Debt	0	0	363	363	=	363
Total:	99900	200100	0	300000	<=	300000
MONTH 4:	Send Letter	Phone Call	Do Nothing
0-Due	0	0	39975	39975	=	39975
1-Due	4096	1312	0	5408	=	5408
2-Due	0	3157	0	3157	=	3157
3-Due	0	984	0	984	=	984
Bad Debt	0	0	478	478	=	478
Total:	81910	218090	0	300000	<=	300000
Revenue
	Send Letter	Phone Call	Do Nothing
0-Due	$ – 0	$ – 0	$ 9,000.00
1-Due	$ 13,000.00	$ 16,000.00	$ 6,500.00
2-Due	$ 11,500.00	$ 17,000.00	$ 7,000.00
3-Due	$ 10,000.00	$ 14,000.00	$ 6,000.00
Bad Debt	$ – 0	$ – 0	$ – 0
Total:				$ 2,009,988,625.00
Alternate Strategy (No Contact)		M1		M2		M3		M4
0-Due	40000	$ 360,000,000.00	37200	$ 334,800,000.00	34880	$ 313,920,000.00	32863	$ 295,767,000.00
1-Due	4000	$ 26,000,000.00	5400	$ 35,100,000.00	5390	$ 35,035,000.00	5228.5	$ 33,985,250.00
2-Due	4000	$ 28,000,000.00	3200	$ 22,400,000.00	3930	$ 27,510,000.00	4070.5	$ 28,493,500.00
3-Due	2000	$ – 0	3200	$ 19,200,000.00	3200	$ 19,200,000.00	3638	$ 21,828,000.00
Bad Debt	0		1000	$ – 0	2600	$ – 0	4200	$ – 0
Total:		$ 414,000,000.00	50000	$ 411,500,000.00	50000	$ 395,665,000.00	50000	$ 380,073,750.00	$ 1,601,238,750.00
Difference between initial contact strategy and alternate, no contact strategy:				$ 408,749,875.00

Data_STS

1	1
$C$11	$B$7
1	1
5000	5
6000	40
100	5

$E$65	$B$8
Hours Available	1
	5
	20
	5

	$E$65
	Time Required (Phone Call)
	Time Required (Letter)

STS_1

Oneway analysis for Solver model in Data worksheet			Sensitivity of Total_Revenue to Hours Available
Hours Available (cell $C$11) values along side, output cell(s) along top			Data for chart
	Total_Revenue	1	Total_Revenue
5000	$ 2,009,988,625.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,009,988,625.00	DIFFERENCE:
5100	$ 2,015,045,935.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,015,045,935.00	$ 5,057,310.00
5200	$ 2,019,240,100.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,019,240,100.00	$ 4,194,165.00
5300	$ 2,023,254,775.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,023,254,775.00	$ 4,014,675.00
5400	$ 2,026,730,200.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,026,730,200.00	$ 3,475,425.00
5500	$ 2,029,936,000.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,029,936,000.00	$ 3,205,800.00
5600	$ 2,033,141,800.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,033,141,800.00	$ 3,205,800.00
5700	$ 2,036,347,600.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,036,347,600.00	$ 3,205,800.00
5800	$ 2,039,416,600.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,039,416,600.00	$ 3,069,000.00
5900	$ 2,041,142,800.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,041,142,800.00	$ 1,726,200.00
6000	$ 2,042,869,000.00 s ward: Solver found a solution. All constraints and optimality conditions are satisfied.		$ 2,042,869,000.00	$ 1,726,200.00
NOTE:	The biggest revenue increase happens with an increase of 100 hours; after 100 hours, the revenue increases but to a lesser degree.

Sensitivity of Total_Revenue to Hours Available

5000 5100 5200 5300 5400 5500 5600 5700 5800 5900 6000 2009988625 2015045935 2019240100 2023254775 2026730 200 2029936000 2033141800 2036347600 2039416600 2041142800 2042869000

Hours Available ($C$11)

When you select an output from the dropdown list in cell $K$4, the chart will adapt to that output.

STS_2

Twoway analysis for Solver model in Data worksheet

Sensitivity of Total_Revenue to Time Required (Letter)

Sensitivity of Total_Revenue to Time Required (Phone Call)

Output and Time Required (Phone Call) value for chart

Output and Time Required (Letter) value for chart

Total_Revenue

Time Required (Phone Call) (cell $B$7) values along side, Time Required (Letter) (cell $B$8) values along top, output cell in corner

Output

Time Required (Phone Call) value

Output

Time Required (Letter) value

Total_Revenue