An oil company produces two types of gasoline, G1 and G2, from two types of

An oil company produces two types of gasoline, G1 and G2, from two types of crude oil, C1 and C2. G1 is allowed to contain up to 4% impurities, and G2 is allowed to contain up to 3% impurities. G1 sells for $48 per barrel, whereas G2 sells for $72 per barrel. Up to 4200 barrels of G1 and up to 4300 barrels of G2 can be sold. The cost per barrel of each crude, their availability, and the level of impurities in each crude are listed in the file FinalQuestion4.xlsx Download FinalQuestion4.xlsx. Before blending the crude oil into gas, any amount of each crude can be “purified” for a cost of $3.00 per barrel. Purification eliminates half of the impurities in the crude oil.
A. Determine how to maximize profit.
B. Use SolverTable to determine how an increase in the availability of C1 affects the optimal profit. C. Use SolverTable to determine how an increase in the availability of C2 affects the optimal profit. D. Use SolverTable to determine how a change in the profitability of G2 changes profitability and the types of gas produced.
Producing gasoline
Data on crudes
Crude 1 Crude 2
Cost per barrel $36 $48
Impurity percentage 10% 2%
Availability 5000 4500
Purification cost per barrel $3.00
Percent impurities eliminated 50%
Data on types of gas
Unit price Max impurity Max sales
Gas 1 $48 4% 4200
Gas2 $72 3% 4300
Blending and purification decisions (barrels of each crude into each type of gas)
Unpurified Purified
Crude 1 Crude 2 Crude 1 Crude 2 Total sold Max sales
Gas 1
Gas2
Constraints on crude availability
Used
Available
Impurity constraints (in barrels)
Gas 1
Gas2
Cost of buying crudes
Cost of purification
Revenue from selling gas
Profit
Image transcription text
AutoSaveOnOF FinalQuestion . Last Modified: 35m ago P Search File Home
Insert Draw Page Layout Formulas Data Review View Developer Help Acrobat Po
Calibri 11 AA E 18 Wrap Text General Merge & Center % Uindia …
Show more