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.