Please answer the following problem using Solver and showing formulas in excel.
D LL 3 4 5 6 7 8 9 A E G . J K 1 Transportation Homework 2 Sunco Oil produces oil at two wells. Well 1 can produce up to 150.000 barrels per day, and well 2 can produce up to 200.000 barrels per day. It is possible to ship oil directly from the wells to Sunco's customers in Los Angeles and New York. Alternatively, Sunco could transport oil to the ports of Mobile or Galveston, and then ship it by tanker to New York or Los Angeles. Mobile and Galveston have no storage facilities, so everything shipped in to those ports must be shipped out as well. Los Angeles requires 160.000 barrels per day, and New York requires 140,000 barrels per day. The costs of shipping 1,000 barrels between various locations is given in the table below (a dash indicates shipments that are not allowed). A. Set up and solve the problem to minimize the total shipping cost for Sunco. (HINT 1: The minimum total shipping cost for the correct solution should be $7,500.) 10 (HINT 2: Model the requirements, supply capacities, and shipments in 1,000's of barrels.) 11 B. Explain the solution in terms of resulting decision variables. Why are some routes and nodes used and 12 others are not? 13 C. Format the problem to be presentation-ready. 14 15 Shipment Costs per 1,000 barrels 16 Welli We112 Mobile Galveston NY LA 17 Well 1 $10 $13 $25 $28 18 Well 2 $15 $12 $26 $25 19 From Mobile $6 $16 $17 20 Galveston $6 $14 $16 21 NY $15 22 LA $15 23 24 25 26 Well1 Wel12 Mobile Galvestor NY LA 27 Well 1 $10.00 $13.00 $25.00 $28.00 28 Well 2 $15.00 $12.00 $26.00 $25.00 29 Mobile $ 6.00 $16.00 $17.00 30 From Galveston $ 6.00 $14.00 $16.00 31 NY $15.00 32 LA $15.00 33 - D LL 3 4 5 6 7 8 9 A E G . J K 1 Transportation Homework 2 Sunco Oil produces oil at two wells. Well 1 can produce up to 150.000 barrels per day, and well 2 can produce up to 200.000 barrels per day. It is possible to ship oil directly from the wells to Sunco's customers in Los Angeles and New York. Alternatively, Sunco could transport oil to the ports of Mobile or Galveston, and then ship it by tanker to New York or Los Angeles. Mobile and Galveston have no storage facilities, so everything shipped in to those ports must be shipped out as well. Los Angeles requires 160.000 barrels per day, and New York requires 140,000 barrels per day. The costs of shipping 1,000 barrels between various locations is given in the table below (a dash indicates shipments that are not allowed). A. Set up and solve the problem to minimize the total shipping cost for Sunco. (HINT 1: The minimum total shipping cost for the correct solution should be $7,500.) 10 (HINT 2: Model the requirements, supply capacities, and shipments in 1,000's of barrels.) 11 B. Explain the solution in terms of resulting decision variables. Why are some routes and nodes used and 12 others are not? 13 C. Format the problem to be presentation-ready. 14 15 Shipment Costs per 1,000 barrels 16 Welli We112 Mobile Galveston NY LA 17 Well 1 $10 $13 $25 $28 18 Well 2 $15 $12 $26 $25 19 From Mobile $6 $16 $17 20 Galveston $6 $14 $16 21 NY $15 22 LA $15 23 24 25 26 Well1 Wel12 Mobile Galvestor NY LA 27 Well 1 $10.00 $13.00 $25.00 $28.00 28 Well 2 $15.00 $12.00 $26.00 $25.00 29 Mobile $ 6.00 $16.00 $17.00 30 From Galveston $ 6.00 $14.00 $16.00 31 NY $15.00 32 LA $15.00 33
