This assignment is similar to Case 4.1 from the text book. However, the question to answer are slightly different. As such, refer to the information within this document. Answer the questions on the attached template. Ensure that you have all question answer on the Answer sheet of the template. Also, provide the model and work to substantiate your answers on each work sheet of the template.

Jansen Gas creates three different types of aviation gasoline labeled A, B and C. It does this by blending four different feedstocks to create the gasoline blends. The four feedstocks are Alkylate, Catalytic Cracked Gasoline (CCG), Straight Run Gasoline (SRG) and Isopentane. Jansens production manager, Dave Wagner, has compiled the data on feed stocks and gas types in tables 1 and 2. Table 1 lists the availabilities and values of the feedstocks as well as their key chemical properties, Reid Vapor Pressure and Octane rating. Table 2 list the gallons required, the prices, and chemical requirements of the three gas types

Table 1: Data on Feedstocks

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

Table 2: Data on Gasoline

Gasoline Type	A	B	C
Gallons Required (1000s)	120	130	120
Price Per Gallon	$3.00	$3.5	$4.00
Max Reid Pressure	7	7	7
Min Octane	90	97	100
TEL Level	Low	High	High

Note that each feedstock can have either a low or high level of TEL. The TEL level affects only the octane rating. For example, Gasoline A is always made with a Low TEL level where-as gas types B and C are made with a high TEL level

As indicated in table 7, each gasoline type has two requirements; a maximum allowable Reid Vapor Pressure and a minimum required octane rating. In addition to these requirements, the company wants to ensure that the amount of Gasoline A produced is at least as large as the amount of gasoline B produced.

Dave believes that Jansen can sell all of the gasoline it produces at the given prices. If any feedstocks are left over, they can be sold at values indicated in table 1. Dave wants to find a blending plan that meets all the requirements and maximizes the revenue from selling gasoline and left over feedstock.

1. To help Dave with this problem, develop an LP optimization model then use excel solver to find the optimal blending plan that maximizes revenue. This solution will be what is known as the Base model and is the starting point. Once the base model is completed, develop other models to answer the following questions. Note each of these questions are variations from the base model. As such, use the base model as the starting point to answer each question separately. Note also that each question 1 through 5 is independent and based off the original optimum solution. In other words, they are not continuations. Each question (1 through 5) represents independent changes to the original model.

1. Dave is not sure that the side constraint of at least as much gasoline A as Gasoline B is necessary. What is this constraint costing the company? That is, how much more revenue could Jansen earn if this constraint were ignored?
2. Dave consults the chemical experts, and they suggest that Gasoline A could be produced with a medium level of TEL. The octane ratings for each feedstock with this medium level would be halfway between their low and high TEL octane ratings. Would this be a better option (what is the maximum revenue for this option)?
3. Because of regulations Jansen may have to increase Reid Vapor Pressure maximum value on each gasoline type (by the same amount). Conduct a sensitivity analysis to the base model by varying Max Reid Pressure requirement from 7 to as high as 15 (in increments of 1). Determine the maximum Reid Vapor Pressure value ascertained and the associated revenue. Show a plot and data table of varying read pressure to the revenue and amount of gasoline (Total of A, B and C) produced. Lasty, what is the minimum value of revenue when varying Reid Vapor Pressure maximum value that results lowest revenue.
4. Dave believes that the minimum required octane rating for gasoline A is too low. He would like to know how much this minimum rating could be increased before there would be no feasible solution (still assuming that Gasoline A uses the low TEL level). What is the highest value that this minimum octane rating could be increased to before no feasible solution is ascertained? What is the associated revenue with this minimum octane value?
5. Dave suspects that only the relative prices matter in the optimal blending plan. Specifically, he believes that if all unit prices of the gasoline types and all unit values of the feedstocks increase by the same percentage, then the optimal blending plan will remain the same. Is he correct? To test this show a plot and data table of resulting revenue and total gas produced contrasted to increasing unit prices of the gasoline types and all unit values of the feedstocks increase by the same percentage from 0% to 10%.