Assignment:
Linear Model and Optimal Solution
An oil company in Texas has three oil wells with capacities of 93, 88 and 95 thousand barrels per day respectively. The company also owns five refineries along the Gulf Coast, all of which have been operating at stable demand levels. Three pump stations have been built to move the oil from the wells to the refineries. Oil can flow from any one of the wells to any of the pump stations and from any one of the pump stations to any of the refineries. The company is looking to develop a minimum cost schedule for their systems. The refineries' requirements are as given the table 1.
The company's cost accounting system recognizes charges by the segment of pipeline that is used. These daily costs are given in tables 2 in thousands of dollars per thousand barrels.
|
|
|
|
To
|
|
|
|
|
|
Pump 1
|
Pump 2
|
Pump 3
|
Available
|
|
|
Well 1
|
1.52
|
1.60
|
1.40
|
93
|
|
From
|
Well 2
|
1.70
|
1.63
|
1.55
|
88
|
|
|
Well 3
|
1.45
|
1.57
|
1.30
|
95
|
|
|
|
|
|
|
|
|
|
|
From
|
|
|
To
|
|
Pump 1
|
Pump 2
|
Pump 3
|
Required
|
|
|
Refinery1
|
5.15
|
5.12
|
5.32
|
30
|
|
|
Refinery2
|
5.69
|
5.47
|
6.16
|
57
|
|
|
Refinery3
|
6.13
|
6.05
|
6.25
|
48
|
|
|
Refinery4
|
5.63
|
6.12
|
6.17
|
91
|
|
|
Refinery5
|
5.80
|
5.71
|
5.87
|
48
|
Questions:
1. Formulate a linear model to minimize the company's cost and put it in standard format. Clearly define decisions variables, objective function and constraints
2. Implement your formulation in Excel and use Solver to find the optimal solution. Provide answers for the minimum cost of providing oil to the refineries and the optimal amounts shipped across each segment (provide a snapshot of your Excel solution)
3. Which wells are used to the full capacity in the optimal solution
4. If pump 2 must be taken down for maintenance, what would be the new optimal solution and how much does this decision cost the company? Show how you would model this condition and provide a snapshot of your Excel solution.