Download Optimizing Oil Distribution: Model Understanding & Finding Cheapest Demand Satisfaction and more Study notes Business Management and Analysis in PDF only on Docsity!
Distribution Management (Oil Pipeline)
Distribution Planning (Oil)
Agenda:
- Understanding the Model
- Questions
- Setting up the Model
- Shortest Path
- Forcing Through a Node
- Satisfying All Demand
- Opening New Facilities
- Large Data Sets 1. Understanding the Model As always the first thing to do when setting up your models is to make sense of what information we are given. If we don't understand the information we are given we can't create a model to find the missing information. We are working through the oil transportation model that was introduced in the lectures. We have a supply of oil, an some oil pipes connecting (arcs) locations (nodes) which will allow us to satisfy the demand of oil.
What does the graph say for this model? "Problem Data" worksheet
- There is demand at nodes 4 & 5
- Already some oil at nodes 1 & 2 (possibly in inventory)
- All arcs flow in one direction
- Different costs associated with each arc
- Different fixed costs associated with purchasing/producing more oil at various nodes (this information will not be used for the first few questions) 2. Questions:
- What is the cheapest way to get from node 1 to 5?
- The company is concerned with the deterioration of the equipment at the node There are some new options available but the company would first like to test out the equipment before implementing the equipment at every node. A testing facility has been set up at node 3 and all oil must pass through this node. Wha is the cheapest way to go from nodes 1 to 5 now with this testing in place?
- The test results were not as positive as we would have liked and therefore the transportation system is remaining as it was before the testing was implemented. Using only the oil currently in the system (no new production) what is the cheapest way to satisfy all the demand shown?
- Now there is a possibility of opening a new facility in node 3. To open this facili will cost $4 and each unit of oil produced will cost $1. As well, there is another company that has a plant with extra production room and is willing to sell us th oil provided we pay a premium price for both the use of the facility (Flat rate o $25) and the actual production per unit ($13). Do you want to take advantage either option? 3. Setting up the model To set up the model, we will use two Excel functions that are quite compact in the sense that there is little to type, the formulas are quite short, there are few seperate calculations done: SUMPRODUCT and SUMIF. We have seen SUMPRODUCT in previou labs and it will help us to compute the pumping (transportation) costs, production costs and fixed costs. SUMIF will help us compute the flow in and flow out of each of the nodes. As always, if you are looking for an indepth description of any formula use please refer to the Excel help tool bar.
In the worksheet titled "Template" you will find the data for the first few problems an additional information in the worksheet titled "Problem Data". Notice how the pipes (arcs) are represented in pairs of nodes (From: x, To: y) in columns B and C. Column A consists of the decision variable, that is, the number of units that will flow from no x to node y. Column D expresses the cost per unit for using the arc and column E is the capacity for each arc.
4. Question 1: cheapest way from node 1 -> 5
Manual approach: What are all the different ways oil can travel from node 1 to 5?
- 1 -> 2 -> 5 which costs ($12 + $13 = $25)
- 1 -> 2 -> 5 which costs ($14 + $14 = $28)
- 1 -> 2-> 3-> 5 which costs ($12 + $8 + $14 = $34)
NOTE: The SUMIF does not make your model nonlinear. Dispite the "IF" part, the function only performs a value look-up and adds the numbers that match the look-up value. If you're still curious, please see the discussion forum for a detailed explanatio of why this works.
We also need to calculate the Flow In + Supply and the Flow Out + Demand because what we have available in a node cannot be less than what we use in a node when we're determining the distribution.
Lastly, we need to determine the pumping costs for the model which we use the SUMPRODUCT formula for as shown below:
The last thing to do before going to solver is to put in the demand and supply amoun that will allow us to find the shortest path. As was mentioned before, this is using a supply and demand of 1 at both ends of our trip - at node 1 and node 5. As these arc are 1-way we need to have the supply at node 1 and the demand at node 5.
Solver: Now we are ready to minimize the costs associated with finding the most cost efficien way of getting from one node to another.
Target cell: minimize pumping costs Changing cells: flows along each arc Constraint: Flow in + Supply >= Flow out + Demand Flows along any arc <= Flow Capacity of each arc Options: Linear Model, Non-Negative, Automatic Scaling
the column headers (A3:E3), select tools -> Filter -> AutoFilter.
Then there will be a small triangle in the bottom right of each cell that has a drop do menu. While in the pump drop down menu, select custom -> is greater than and put
- You can filter for more than one condition, and more than one column. You know you have used autofilter when the row numbers change to blue. When you are done and want to look at all the data again, select Data -> Filter -> AutoFilter and it will deselect the autofilter option and you will see all of the data again.
Note: If you copy from the 'autofilter' selection, and only want to paste the sections you see, on some older versions of Excel you have to paste special values or else you will paste everything in between as well.
7. Question 4 - Opening new plants (using binary constraints) Now we have been looking at opening a facility in node 3. As well, we have an option given to us by another producer who isn't producing to their maximum capicity and i looking for some extra cash.
Now we have 2 more decisions to make with this question: Do we open (yes/no -> binary), and if we open, how much we will produce. Even though we don't technically open the facility that we purchase the extra oil from in node 4 because the nature of the option available we can model it like we do operate. There is a fixed cost associated with even using the option and the company also charges us per unit of o they produce.
The 'Open' column is a binary variable because there are only 2 possible options: open, or not. If it is a 0, we do not open the plant. If it is a 1 that means 'yes' open t facility. The maximum production is the number of units of oil we could produce. Remember though that we are only able to use this capacity if we are actually open. This is why we have the 'upper bound' column to ensure that we don't use supply tha we don't technically have. This is how we keep the model linear.
You then link the upper bound with the amount to produce in your constraints, and a what you produce in a plant to your flow in + supply column as to produce will add to your supply.
Finally you need to calculate the additional costs associated with opening a new plan which is the fixed and production costs. Both are calculated using the SUMPRODUCT and it is the total of costs that is the new objective cell that you are minimizing. You also add the changing binary cells and production cells to your solver (remember to adjust your tolerance to 0 now that you are using binary constraints!).
8. Large Data Sets
Tips/Tricks/Reminders for handeling large data sets. This is just a reminder of some the ways you can manage larger data sets. If you are confused about one of the suggestions, ask a TA at the end of the lab, during extra help in the labs, or come in during Morgan's or Jen's lab hours to get a refresher.
- Using the split screen on the scroll (also, using the duel moniters to spread out model over two screens)
- use ctl + alt to move around large amounts of data and select large sections over the split screen
- just 'double clicking' to propogate down only works if there are no breaks in th data, and you propogate beside the column with data in it. To get around this, have your formula in the top cell, while holding shift select your range you wan to propogate down and then hold ctl + D - this will fill down. As well, if you wa to fill across the 'Row' use ctl + R.
- When you are de-bugging DO NOT start with the first formula as you will not catch the propgation errors that might be happening - possibly due to incorrect absolute referencing
