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.
Network Optimization Example
Loading...
From the course by University of Pennsylvania
Operations Analytics
оценки
From the lesson
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.
Meet the Instructors
Senthil VeeraraghavanAssociate Professor of Operations, Information and Decisions
The Wharton School
Sergei SavinAssociate Professor of Operations, Information and Decisions
The Wharton School
Noah GansAnheuser-Busch Professor of Management Science, Professor of Operations, Information and Decisions
The Wharton School
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., 2018 Все права защищены.
