Question
Please show excel screenshots thank you Problem 2: (30 points) Clampett Oil purchases crude oil products from suppliers in Texas (TX), Oklahoma (OK), Pennsylvania (PA),
Please show excel screenshots thank you
Problem 2: (30 points) 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 below. 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.8 barrels of kerosene, 1.7 barrels of heating oil, or 2.4 barrels of asphalt. Each supplier requires a minimum purchase of at least 500 barrels. 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 companys 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.
TX 1,500 2.00 2.80 1.70 2.40 $22 $1,500 OK 2,000 1.80 2.30 1.75 1.90 $21 $1,700 PA 1,500 2.30 2.20 1.60 2.60 $22 $1,500 AL1,800 2.10 2.60 1.90 2.40 $23 $1,400
Create a spreadsheet model for the above problem and solve it using Excel Solver.
Step by Step Solution
There are 3 Steps involved in it
Step: 1
The question requires the creation and solution of a spreadsheet model using Excel Solver Unfortunately I cannot produce Excel screenshots directly bu...Get Instant Access to Expert-Tailored Solutions
See step-by-step solutions with expert insights and AI powered tools for academic success
Step: 2
Step: 3
Ace Your Homework with AI
Get the answers you need in no time with our AI-driven, step-by-step assistance
Get Started