Hi, I'm Sergei Savin. Welcome back to the second week of Operations Analytics course. We're about to start session three of this week. In session two, we have learned how to set up and solve optimization problems using Solver. As an example, we have used a resource location problem. In this session, we will look at another frequently encountered business setting where the optimization tool kit can be used to make the best decisions. It is a network setting, a setting where demand and supply are spread across a network of locations, and the goal is to make sure that the demand and supply match at the lowest possible cost. Let's have a look. In section two, we used Zooter example to set up and solve a resource allocation problem. In this session, we will look at a different kind of optimization, a problem where optimization involves a network of locations. Here's the business context we're going to analyze. Keystone Dry Goods Logistics is a company that moves goods for its customers. This particular customer needs Keystone to transport powdered drink. The network of locations that client owns include three warehouses and three distribution centers. Here are the warehouses, here are the distribution centers, so this is the complete network. From each warehouse, Keystone must transport out a certain amount of powdered drink. For example, out of the Chicago warehouse it must move exactly 20 tons. So here's an illustration of what must be shipped out. 15 tons out of Los Angeles, 20 tons out of Chicago, and 30 tons out of New York City. Each distribution center has a minimum requirement for the amount it must receive. For example, at least 10 tons must be shipped to the Denver distribution center. So here's the picture illustrating these two types of requirements. Requirements for exact amounts that must be shipped out of warehouses, and the minimum requirements for the amounts to be shipped into the distribution centers. Now shipping is costly. Here are the costs that Keystone will incur when shipping one ton of powdered drink from each warehouse to each distribution center. For example, if it tries to ship 10 tons from New York City to Austin, it will incur a shipping cost of $132 for each ton, and the total cost will be 132 times 10. $1,320. So here's the problem Keystone is trying to solve. It is trying to find out the cheapest way to transport the goods while making sure that certain amounts are moved from warehouses, and some minimum amounts are delivered to each distribution center. So let's cast this problem formulation into three pieces. What kind of decisions must Keystone make? The amounts to ship from each warehouse to each distribution center. What objective does Keystone have? It wants to minimize the shipping cost. What kind of constraints does it face? There are supply constraints at the warehouses and the demand constraints at the distribution centers. We can specify decision variables, objective function, and constraints. Let's start with decision variables. The company needs to determine how much to ship from each warehouse like Los Angeles, Chicago and New York to each distribution center, Denver, Washington D.C. and Austin. So let's assign one variable, X LD to stand for the amount sent from Los Angeles to Denver, and another one to describe the amount shipped from Los Angeles to Austin. And the third one, to denote the amount shipped from Los Angeles to Washington, DC. Let's add three more variables to designate shipments from Chicago to Denver, Austin, and Washington, D.C. And let's do the same for shipments from New York City to Denver, Austin and Washington, D.C. So we have 3 plus 3 plus 3, 9 decision variables. So what about the objective? Keystone tries to select the decision variable values to make the total shipping cost as small as possible. So if I try to ship XLD tons from Los Angeles to Denver, what are the cost implications of such a decision? Okay, I ship from Los Angeles to Denver and it'll cost me $105.00 per ton. So shipping XLD tons from Los Angeles to Denver creates a cost contribution of 105 times XLD. And shipping XLA tons from Los Angeles to Austin creates a cost contribution of 135 times XLA. In the same way, shipping XLW tons from Los Angeles to Washington, D.C., creates a cost contribution of 153 Times XLA. So the objective function has three contributions from shipments originating in Los Angeles, three more from shipments that originate in Chicago, and three more from shipments that come from New York. Now the objective function must be minimized in the presence of supply constraints at the warehouses, and demand constraints at the distribution centers. Okay, here's a reminder of what the supply constraints are. Keystone must ship exact amounts from each warehouse. For example, all shipments from the Los Angeles warehouse either to Denver or Washington D.C., or Austin, must stood up to 15 tons. So the supply constraint for the Los Angeles warehouse looks like this. In the same way, all shipments from Chicago must add up to 20 tons. Finally, all shipments from New York must add up to 30 tons. So to summarize, we have supply constraints for Los Angeles, Chicago, and New York. What about the demand constraints? Here's a reminder of what the requirements for each distribution center are. Let's start with Denver distributions center. Denver must receive at least ten tons, so here's how this constraint will look like. And the Austin center must receive at least 13 tons. Finally, the Washington, DC distribution center must receive at least 20 tons. Here's the summary of demand constraints. We have one for Denve, one for Austin, and one for Washington D.C. To put it all together, we have an algebraic model with nine variables, and we have expressed the objective function, the total shipping cost, in terms of these nine variables. We have also put together expressions for the supply constraints. And the demand constraints. We must also ensure that shipping quantities are non-negative. Now, we do not have to restrict the shipping quantities to integer values, since it is possible to ship fractional amounts. For example, 12.5 tons. Now that we have formulated an algebraic model for the Keystone problem, We can go to Excel, set up a spreadsheet formulation of this model, and find the optimal shipping plan using solver. Let's do it now. We have prepared for you a template called keystone_0. Go ahead use it and follow the steps we go through in setting up the model. Okay, here is our Excel template. Keystone_0. As you can see, the spreadsheet contains the data for the keystone example but none of the formulas that we will need to find the best solution. As in the example, we must convert our algebraic optimization model into a spreadsheet formulation and in order to accomplish this task we will define on the spreadsheet three components of an optimization model. Decision variables and objective function and constraints. Let's start with the decision variables. In this problem the decision variables are the amounts of powder drink to ship between each warehouse and each distribution center. There are three warehouses and three distribution centers so we have nine decision variables. In our template we have nine cells B12 to D14 that have the header shipping quantities let's use those cells for the values of our decision variables just like we did in the zooter example. Let's put in some trial values in those cells say lets put ten in each of those nine cells. Okay, shipping ten times from each warehouse to each distribution center certainly is not a feasible solution. For example, the L.A. warehouse Would need to ship out the total of 30 tons and it only has 15 but that's okay for now. Those numbers we have put into each of the decision variables so they're just trial values and the solver will be able to change those values later. Okay we have defined the decision variable cells. Let's color them in blue and make the font bold. Just like we did for the decision variable cells in the example. Next the objective function cell, in this example the objective is the total shipping cost and we would like to minimize this objective. As the template suggests let us use the cell F3 as the objective functions out. So, how do we calculate the total shipping resulting from the shipping plan and the decision variable self. We should multiply each of nine shipping quantities by the corresponding shipping costs from the cells b6 d8 and then add those nine products. This all sounds like a sum product of our decision variables and the corresponding cost values. Let's see how we can use the sum product formula in this case. In cell F3 we type in SUMPRODUCT of our decision variable cells B 12 d 14 and the corresponding cost values, B 6, D 8. The result is, according to Excel, $11,570. So, we have the objective function cell. Let's change the form to bold and red as we did for the objective function in the Zooder case. Now we move to the constraints. In the Keystone problem, there are six main constraints. Three supply constraints, one for each warehouse, and three minimum demand constraints, one for each distribution center. Let's start with the supply constraint. The first supply constraint states that the total amount shipped from the LA warehouse must be exactly equal to 15 tons. Let's go to the cell E12 and calculate the total amount shipped from the LA warehouse under our trial shipping plan. We need to sum the shipping amounts from the LA warehouse to each distribution center. So, we'll put in sum B12, ship to Denver, C12 Austin and D12 Washington. The result is 30 tons. Now we can do the same calculation for the remaining two warehouses. We just copy and paste the formula in the cell E12 to the cells E13 and E14. As you can see under the trial shipping plan we're considering the amount shipped from each warehouse is 30 tons that is certainly not feasible but let's worry about this later. Next we deal with the minimum demand constraints. For each distribution center there is a minimum amount that it must receive. Let us start with the Denver distribution center and let us calculate in the cell B 15. The the total amount this distribution will receive under the current shipping plan. We are summing the shipping quantities that go to Denver from each warehouse, SUM of B12, B13, and B14. And the answer is 30 tons. We can do the same calculation for the remaining two distribution centers. All we have to do is to copy and paste the formula in B15 to the cells C15 and D15. This is, for example, what we have now in D15. It sums the shipping quantities that go to Washington from LA, Chicago, and New York City. We have specified the decision variables, the objective function, and the constraints and we are ready to call the solver. We go to data, we click on solver. Let's first show the solver where the objective function is. It is in the cell F 3. And this time, we would like to minimize that function. In other words, we would like to make the shipping cost as small as possible. Next, we specify where our decision variable are. They're located in the cells, B12 though D14. Finally the constraints. The supply constraint state that whatever amounts which was to ship, the total shipping amount from each warehouse, must be exactly equal to the amount we have specified. So, we click add and tell the solver that the number is in the cells E 12 through E 14 must be exactly equal to the numbers in the cells G12, G14. The minimum demand constraints state that each distribution center can not get less than what is required, thus we click Add again and state that numbers in the cell B15, D15 must be greater than or equal to the value specified in the cells B17, D17. We do not need to specify that the shipped amounts are integer. We can ship 9.4 tons between a particular warehouse and a particular distribution center if we choose to, which have the non-negativity of the decision variable selection. We leave the solver method as it is. The GRG now linear, and we click Solve. Here's our optimal solution. Here's the picture of the optimized spreadsheet. As we can see, the lowest cost that can be achieved here is $7485. Solver is a useful tool for learning the optimization techniques using problems with small numbers of variables and constraints. However, real size problems represent a serious challenge for Solver. Fortunately, there's a number of commercially available optimization tools that can tackle large problems. The good news is that, whether you're using Solver or a commercial optimization tool, you will still have to work with decision variables, objective function, and constraints. Here's a link to a recently compiled list of commercial optimization tools. A journal called Interfaces publishes articles on actual business applications of analytics techniques. Interfaces' articles is a great source of practical knowledge on how to identify analytic opportunities, how to build the models, and how to implement them. Here we've picked two examples. The first article is about optimization of profits from refinery operations at Chevron, one of the world's largest integrated energy companies. The second is about applying analytics tools, including optimization, at one of the world's leading fashion retailers, Zara. Please keep in mind that, in practice, analytics projects may often involve multiple analytics tools. In particular, optimization will need to be combined with descriptive and predictive analytics tools, like forecasting. Often the best course of action must be charted in settings with significant uncertainty. We will look at such settings in Week Three. We're at the end of Week Two of operations and analytics course. This week we have focused on an optimization toolkit that allows us to find the best course of action in business settings with lower levels of uncertainty. We have used an algebraic formulation to create a concise way of expressing any optimization problem by specifying its decision variables, objective function, and constraints. We have looked at how algebraic models should be converted to a spreadsheet format, and how Solver can be used as a tool for identifying the best decisions. We have covered two examples of how an optimization toolkit can be applied to different business contexts, resource allocation and network optimization. In order to help you master the optimization concepts and get ready for Week Two's homework, we have prepared a video review session where we cover optimization examples similar to the ones covered in our sessions. This video review session is completely optional, and if you feel comfortable with the optimization examples you have seen so far, you can move on to the practice problems. We provide you with two practice problems with solutions. One particular reason you may want to look at the practice problems is that the types of questions we're asking you to answer are very similar to the questions you will see on Week Two of schoolwork. We hope you will find these additional materials useful when you work on the homework questions. So, what's next? Remember the news fender problem of Week One? It was also about making the best decision, but in a business setting where the impact of any course of action cannot be identified with certainty. In order to understand how to make the best decisions in such settings, we must first understand how to evaluate and compare decisions under uncertainty. This will be the subject of Week Three of our course. See you all next week.
