(upbeat music)
- Hello, I am Professor Rick Jerz.
And I welcome you to this session titled
"The Transportation Model Using Excel."
In this session we're going to look
at an Excel model called the transportation model.
Historically, the transportation model was solved using
a mathematical technique called linear programming.
Excel includes some tools that can be used
to implement this technique.
I think that you are going to see
that this is one of the more difficult models
that we will be working with in this course.
But I think that you are also going to learn
a few new interesting techniques using Microsoft Excel
that you didn't know about.
And I think you're going to enjoy this model too.
Let's get started.
Here's the problem.
Assume that you have several factories located
throughout the United States.
And each of them have the capacity to produce
a certain number of units.
The production capacity may be different at each factory.
Also assume that you have several warehouses
that are located at different cities
throughout the United States,
each needing a certain number of units.
The issue is, from which factory
should you ship products to which warehouse?
At first it may not seem like this is much of a problem.
Just ship the units from any factory to any warehouse.
But consider this.
You call UPS and they say the cost
to ship from Des Moines to Albuquerque is $10 a package.
To ship from Evansville to Albuquerque might be 15.
And to ship from Fort Lauderdale
to Albuquerque might be $20.
So if you choose to ship products, let's say,
oh, from Fort Lauderdale to Albuquerque,
you might be incurring a greater cost
than if you were to ship from Des Moines to Albuquerque.
So the shipment cost differs
between factories and warehouses.
And our goal is to minimize the overall shipping cost.
Please realize that in this scenario,
we're talking about factories and warehouses.
But these are just generic facilities.
It could be shipping from warehouses to stores,
or something like that.
The real issue is how to satisfy all the demand centers
from the various sources.
And to do this at the lowest minimum cost.
Let's begin this problem by creating a table.
This table will show where we're shipping from
and where we are shipping to.
In the intersection of from locations to to locations,
let's enter the cost.
The cost can be determined
by calling different shipping agents.
For example, UPS or FedEx, or even the U.S. Postal System,
and asking them, "What is the cost
"to ship my product from one city to another city?"
Now let's create a transportation matrix.
In it we not only include the shipping cost,
but we have to show the warehouse requirements
and the factory capacities.
In this very simple scenario,
let's assume that supply equals demand.
If you look at this chart,
Albuquerque requires 300 units.
Des Moines only has a capacity to ship 100 units.
So let's do that and move on.
Evansville has a capacity of 300 units.
So the remaining 200 units that Albuquerque requires
can be satisfied from Evansville.
Moving on to Boston.
Boston requires 200 units.
Well, we have already used up two of the 300 units
of capacity that Evansville has.
So we would only be able to ship 100 units
from Evansville to Boston.
The remaining hundred units
would have to come from Fort Lauderdale.
Cleveland then gets serviced
from this Fort Lauderdale factory.
And we're done.
What does this cost?
Well, to figure out the total cost we
would have to multiply the units in each of the cells times
the shipping cost for every cell
where we have units being shipped, add it up,
and figure out the total cost.
But is this the very best we can do?
If you stare at this table a little bit,
you might be able to see another approach
to solving this problem.
For example, what you might do,
knowing that you want to minimize cost,
is to look at where our shipping cost is the lowest.
Notice that Des Moines to Cleveland
and Evansville to Cleveland has a shipping cost of three.
So another approach to this problem
is first trying to fulfill Cleveland's needs,
because this might provide the lowest shipping cost.
Cleveland needs 200 units.
Oh, we might choose to ship 100 units
from Des Moines to Cleveland.
And then the other 100 units from Evansville.
Then we could look at the next lowest cost,
which would be, oh let's see.
It looks like Evansville to Boston.
And use the remaining capacity from Evansville
to ship to Boston 200 units.
And so forth.
You might see that there are many approaches
to solving this problem.
Our first approach, starting in the upper left-hand corner,
and working downwards,
is actually referred to as the Northwest-Corner Rule.
The author shows that if we use this approach,
our total shipping costs will be $4200.
The question is, can we do any better than this?
In my second example, where I thought about using
the lowest shipping cost from one location to the other,
this is actually referred to
as the Intuitive Lowest-Cost Method.
You might see that if we use this approach,
the total cost is $4100,
which is lower than the Northwest-Corner Rule.
So it looks like we might be on to something.
The author actually gives us
one more method called the Stepping-Stone Method.
This method seems a little bit more complex.
I'll let you read the five steps
that the author has identified,
and see if you can make any sense of this approach.
You would probably have to take your time,
and watch what the author does
in applying each step of this method.
The author shows that using
this Stepping-Stone Method results
in a total cost of $4000.
A little bit better than either of the other two approaches,
but more complex.
We still don't know if we have the lowest possible cost.
This problem has been around for many years.
And the mathematicians actually came up
with a solution to this problem.
It involves a mathematical technique
called linear programming.
This has nothing to do with computer programming.
Now for many of you
who have not studied high-level mathematics,
this linear programming approach
is going to look pretty strange.
You begin with decision variables.
In our case, x one might be the amount of products shipped
from Des Moines to Albuquerque.
X two could be the amount of products shipped
from Des Moines to Boston, and so forth.
Next you need what's called an objective function.
Typically, an objective function is something
that you want to either minimize or maximize.
You have to create this objective function
using our variables.
In this equation, you cannot have any exponents
for any of the variables.
For example, you cannot have x one squared,
or x two cubed.
This is what makes linear programming be linear.
That all of the variables are to the power of one.
The last thing you need to identify
are all of the constraints
to shipping and receiving products.
One example in our problem is that Des Moines
cannot ship more than a hundred units to any factory,
because it is limited by its capacity of 100 units.
This is a constraint.
You have to be able to work through this whole matrix
and identify all possible constraints.
This is pretty tricky.
And let me tell you that typically,
using these kind of problem solving approaches
are mathematically demanding
and therefore limited to certain degrees of study.
For example, industrial engineering students study
linear programming and problem solving techniques like this.
Instead of exploring how to solve
the actual mathematical linear programming problem,
we're gonna take a look at how
to do this in Microsoft Excel.
I think you will enjoy the Excel approach
a lot more than learning
the linear programming problem solving technique.
(upbeat music)
Thanks for taking time to spend some moments with me.
I hope that you have enjoyed what I have shared with you.
If you ever have any questions, or need my help,
please let me know.