Clampett Oil purchases crude oil products from suppliers in Texas $($TX$),$ Oklahoma $($OK$),$ Pennsylvania $($PA$),$ and Alabama $($AL$),$ from which it refines four end$-$products: gasoline, kerosene, heating oil, and asphalt. Because of differences in the quality and chemical characteristics of the oil from the different suppliers, the amount of each end$-$product that can be refined from a barrel of crude oil varies depending on the source of the crude. Additionally, the amount of crude available from each source varies, as does the cost of a barrel of crude from each supplier. These values are summarized in the following table. For example, the first line of this table indicates that a barrel of crude oil from Texas can be refined into $2$ barrels of gasoline, $2\mathrm{.}8$ barrels of kerosene, $1\mathrm{.}7$ barrels of heating oil, or $2\mathrm{.}4$ barrels of asphalt. Each supplier requires a minimum purchase of at least $500$ barrels.

Raw Material Characteristics

Possible Production per Barrel

Crude Oils Barrels Available Gas Kero. Heat Asphalt Cost per Barrel Trucking Cost

TX $1,5002\mathrm{.}002\mathrm{.}801\mathrm{.}702\mathrm{.}40$ $$22$ $$1,500$

OK $2,0001\mathrm{.}802\mathrm{.}301\mathrm{.}751\mathrm{.}90$ $$21$ $$1,700$

PA $1,5002\mathrm{.}302\mathrm{.}201\mathrm{.}602\mathrm{.}60$ $$22$ $$1,500$

AL $1,8002\mathrm{.}102\mathrm{.}601\mathrm{.}902\mathrm{.}40$ $$23$ $$1,400$

The company owns a tanker truck that picks up whatever crude oil it purchases. This truck can hold $2,000$ barrels of crude. The cost of sending the truck to pick up oil from the various locations is shown in the column labeled Trucking Cost. The company's plans for its next production cycle specify $750$ barrels of gasoline, $800$ barrels of kerosene, $1,000$ barrels of heating oil, and $300$ barrels of asphalt to be produced. determine the purchasing plan that will allow the company to implement its production plan at the least cost.

b$.$Implement this model in a spreadsheet and solve it$.$

c$.$What is the optimal solution?

Parts b$/$c in a single excel worksheet

The Production per Barrel should be barrels required to produce a single barrel of output. $($For example, it take $2$ barrels of TX crude to make $1$ barrel of Gas$)$