Explore
For Enterprise
Join for Free
Log In

Гуманитарные науки и искусства
Бизнес
Компьютерные науки
Наука о данных
Информационные технологии
здоровье
Математика и логика
Личное развитие
Естественные и технические науки
Социальные науки
Изучение языков
Степени
Сертификаты

Полный обзор Coursera

Обзор
Поиск
Для организаций
Войти
Присоединиться бесплатно

Network Optimization Example

Loading...

Operations Analytics

Пенсильванский университет

4.7 (оценок: 3,190) | Зарегистрировано учащихся: 67K

Курс 2 из 5 — Бизнес-аналитика Специализация

This course is designed to impact the way you think about transforming data into better decisions. Recent extraordinary improvements in data-collecting technologies have changed the way firms make informed and effective business decisions. The course on operations analytics, taught by three of Wharton’s leading experts, focuses on how the data can be used to profitably match supply with demand in various business settings. In this course, you will learn how to model future demand uncertainties, how to predict the outcomes of competing policy choices and how to choose the best course of action in the face of risk. The course will introduce frameworks and ideas that provide insights into a spectrum of real-world business challenges, will teach you methods and software available for tackling these challenges quantitatively as well as the issues involved in gathering the relevant data. This course is appropriate for beginners and business professionals with no prior analytics experience.

Просмотреть программу курса

Получаемые навыки

Simulation, Mathematical Optimization, Solver, Decision Tree

Рецензии

4.7 (оценок: 3,190)

5 stars
2,376 ratings
4 stars
660 ratings
3 stars
123 ratings
2 stars
19 ratings
1 star
12 ratings

GC

Sep 16, 2018

The topics is very much related to what I'm doing and I'm surprised that this course leads me to unchartered territory that brings me a lot of ways on how to solve day to day operations problem.

JJ

Jan 30, 2018

Very useful and very informative. Loved the predictive and prescriptive part of this learning. Good knowledge to get into more advanced learning. Much much better than customer analytics course.

Из урока

Prescriptive Analytics, Low Uncertainty

In this module, you'll learn how to identify the best decisions in settings with low uncertainty by building optimization models and applying them to specific business challenges. During the week, you’ll use algebraic formulations to concisely express optimization problems, look at how algebraic models should be converted into a spreadsheet format, and learn how to use spreadsheet Solvers as tools for identifying the best course of action.

How to Build an Optimization Model16:45

Optimizing with Solver31:02

Network Optimization Example20:00

(Optional) Week 2 Review20:55

(Optional) Solver on Mac3:19

(Optional) Solver in Google Sheets4:20

Преподаватели

Senthil Veeraraghavan
Associate Professor of Operations, Information and Decisions
Sergei Savin
Associate Professor of Operations, Information and Decisions
Noah Gans
Anheuser-Busch Professor of Management Science, Professor of Operations, Information and Decisions

Текст видео

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.

Ознакомьтесь с нашим каталогом

Присоединяйтесь бесплатно и получайте персонализированные рекомендации, обновления и предложения.

Coursera делает лучшее в мире образование доступным каждому, предлагая онлайн-курсы от ведущих университетов и организаций.

© Coursera Inc., 2019 Все права защищены.

Загрузить из App Store

Загрузить в Google Play

Coursera
О проекте
Руководство
Карьера
Каталог
Сертификаты
Сертификаты MasterTrack™
Степени
Для организаций
Для правительственных организаций

Сообщество
Learners
Partners
Developers
Beta Testers
Translators

На связи
Блог
Facebook
LinkedIn
Twitter
Instagram
YouTube
Технический блог

Еще
Условия
Конфиденциальность
Помощь
Доступность
Пресса
Контакты
Справочник
Филиалы