please submit the answer in excel fileAn 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 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. Determine how to maximize profit. Use SolverTable to determine how an increase in the availability of C1 affects the optimal profit. Use SolverTable to determine how an increase in the availability of C2 affects the optimal profit. Use SolverTable to determine how a change in the profitability of G2 changes profitability and the types of gas produced. Data 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

please submit the answer in excel fileAn 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...

please submit the answer in excel file

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

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.

Determine how to maximize profit.

Use SolverTable to determine how an increase in the availability of C1 affects the optimal profit.

Use SolverTable to determine how an increase in the availability of C2 affects the optimal profit.

Use SolverTable to determine how a change in the profitability of G2 changes profitability and the types of gas produced.

Data Producing gasoline Data on crudes Crude 1Crude 2 Cost per barrel$36$48 Impurity percentage10%2% Availability50004500 Purification cost per barrel$3.00 Percent impurities eliminated50% Data on types of gas Unit priceMax impurityMax sales Gas 1$484%4200 Gas2$723%4300 Blending and purification decisions (barrels of each crude into each type of gas) UnpurifiedPurified Crude 1Crude 2Crude 1Crude 2Total soldMax 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

copy-of-finalquestion4-eajk5g2n.xlsx

Answered Same DayDec 13, 2022

Answer To: please submit the answer in excel fileAn oil company produces two types of gasoline, G1 and G2, from...

Rochak answered on Dec 13 2022

31 Votes

Data
    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    3594    0    606    0    4200    <=    4200
    Gas2    0    0    4300    0    4300    <=    4300
    Constraints on crude availability
    Used
    Available
    Impurity constraints (in barrels)
    Gas 1    168
    Gas2    129
    Cost...

SOLUTION.PDF

please submit the answer in excel fileAn 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...

Answer To: please submit the answer in excel fileAn oil company produces two types of gasoline, G1 and G2, from...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment