0:00

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.

Â 1:07

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.

Â 2:06

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.

Â 2:19

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.

Â 3:36

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.

Â 4:17

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.

Â 5:03

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.

Â 6:32

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.

Â 7:17

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

Â 9:20

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.

Â 10:37

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.

Â 11:22

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.

Â 11:43

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.

Â 12:04

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,

Â 13:05

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.

Â 14:05

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.

Â 14:59

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.

Â 15:28

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.

Â 15:59

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.

Â 16:31

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.

Â 16:56

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.

Â 17:36

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.

Â 17:55

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.

Â 18:17

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.

Â 19:35

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.

Â