An oil company has three refineries and three storage/distribution facilities for its crude oil. The refining capacities and deman for each refinery and distribution facility, in the millions of barrels, and the avaiable routes and transportation costs, in thousands of dollars per million barrels, are given in the diagram below.
Supply Refinery Routes Storage/Distribution Demand
200 1 X14 (40) 4 250
1 X15 (30) 5 220
1 X16 (50) 6 200
150 2 X24 (35) 4
2 X25 (45) 5
2 X26 (40) 6
350 3 X34 (20) 4
3 X35 (50) 5
3 X36 (45) 6
Using your Excel/Solver toolset, determine the most efficient (least Costly) route plan for getting crude oil from refineries to storage/distribution facilities. Create a data table section with all data from the formulation in individual cells. Create a model section with cells for decision variables, objective function, and LHS and RHS columns of constraints. Find the optimal routing solution with solver.