(upbeat music)
- Hello, I am Professor Rick Jerz,
and I welcome you to this session titled,
the transportation model.
This is a very interesting business problem scenario
that involves moving materials from one location to another
and trying to figure out the least expensive way to do so.
This problem even occurs in our everyday lives.
Whenever you ship a package,
should you use the U.S. Postal Service, UPS, FedEx,
or some other shipper.
In this session,
I'm going to introduce you to the problem
and its general concepts.
In another session,
we are going to see how to solve this problem
using Microsoft Excel.
Let's get started.
I have opened up our Excel transportation model
which has the data
that I provided to you in the PowerPoints.
You should notice that in this model,
I have already included a dummy column.
This allows the model to be used
even when supply does not equal demand.
Typically we represent the transportation model
as cost cells in the upper portion of another cell.
In Excel, it is not possible to put a cell within a cell.
So instead, we're going to separate
the input data from the output data
using two different tables.
In this upper table,
we have the cost data represented in the middle
of these cells,
and the supply and demand data to the bottom and right.
Our bottom table is not yet complete.
We don't have the supply and demand totals,
nor do we have the cost calculations.
I will show you how to add these calculations.
Let me go ahead and do this right now.
First, I will put in the equations for total demand.
I can do this very quickly
by using the AutoSum tool on Home ribbon.
If this tool guesses incorrectly,
you will have to swipe through the cells
that you want to be totaled.
After I do this for one column,
I can drag through the other columns
and simply hit Ctrl + R,
which repeats this equation to the right,
Ctrl + D repeats an equation down.
I do know that this is a relative equation,
so Excel will recreate the equations properly
in the remaining cells.
I repeat this procedure for the supply totals
using this AutoSum button once again.
At this point, if you were wondering
whether or not the equations are correct,
put some very simple numbers into any of these middle cells,
like the number one or two,
and just verify that the totaling is being done correctly.
The lower right cell shows you that
supply is equal to demand.
I will now work on the cost calculations.
In the Albuquerque column,
I could set up the cost by typing,
equal, this cell, the cost,
times the quantity that we ship,
plus, and I might have to use parentheses
to make sure that my multiplications
are always done before my additions.
But I'm gonna show you another function in Microsoft Excel
that allows us to multiply and then add numbers.
It's called the SUMPRODUCT function.
I'll begin by typing equal, S-U-M-P,
and I see the SUMPRODUCT function showing.
I hit my Tab key to pick up this function,
I noticed that there are two arrays
that are needed for this function.
The first array are the cost,
the second array are the quantities.
I swipe these values and then I hit Enter, and we're done.
Wow, this is a nice function
whenever we need to multiply and add numbers together.
Before I go on, I want to prove to you
that this really is working.
I always use very simple numbers like one or two
to test my models.
I will put Number two in the Des Moines cell,
and notice that the cost is calculated to be 10.
This is the result of five times two, looks pretty good.
I can replicate this function to the right cells
by swiping through the cells to the right
and then hitting Ctrl + R.
Let me now add a total cost cell,
which is the sum of the four previous columns.
Oh, I'll change this to be highlighted in yellow,
and I will add the words Total Cost
just to remind us that that particular cell
represents the total cost of our solution.
Our Excel model is now complete,
but at this point,
we would have to manually make some guesses
as to what to put in each cell
to try to achieve the lowest cost.
If we do this manually,
we would also have to double-check
the supply and demand cells
to make sure that we have not exceeded any of these.
If we wanted to, we could use this model
to experiment with the Northwest corner rule
or the intuitive lowest-cost rule.
What I want to do is show you how to set this up
as a linear programming problem
using the Excel add-in called Solver.
Remember that since Solver is an add-in,
it must be turned on before we can use it.
Let me show you how.
Click on the File ribbon, then Excel options, then Add-ins,
then right down here, we wanna go to the Excel add-ins
and make sure that Solver is turned on or checked.
If this worked, you will see Solver show up
in the Data ribbon off to the right, looks pretty good.
If you are using Excel for the Mac,
you turn Solver on by clicking on the Tools menu,
then click on Add-ins, click on Solver, and OK.
Now we get to the fun part.
Let's use Solver to solve our problem.
From the Data ribbon, choose Solver.
That opens a pretty complex dialog box
that I have to fill in.
I'll show you how to fill in these various cells.
The set target cell is our total cost cell.
This text box picks up a reference
of wherever you happen to be when you start Solver.
I will delete this, and then go down
and specifically choose this total cost cell.
Next, we must choose whether we want the cell maximized,
minimized, or set to some value.
In our case, we want to minimize the total cost.
The next thing we have to do is to answer this
by changing cells dialog box.
I'm going to click inside this dialog box
so that my cursor is within it.
Now, which cells in our Excel model do we want to change?
Well, I'm gonna come down here
and I'm gonna start with this cell,
and click through all of these cells
that we're allowing to be changed.
The last thing that we must enter in our model
are the constraints for our problem.
This is the toughest part of doing linear programming
within Microsoft Excel.
In fact, it's the toughest thing to do
relative to linear programming
to figure out what are your proper constraints.
Watch me, and I'll show you what these constraints are.
As we go through these,
you'll see the logic of setting up constraints.
Okay, what I want to do is make sure I add some constraints.
What I want is for this total to be equal to this total.
Don't hit your Enter key or OK,
because we still have more constraints to enter.
Click Add, I want this cell to equal this cell,
and I need another one.
This cell is equal to this cell,
we have taken care of the row constraints,
but there are now column constraints.
This total has to be equal to this total.
Let me add another one.
This total has to equal this total.
Let's add another,
this total equals this total,
and this total equals this total.
Now, it may look to you like we're done,
but in linear programming,
you have to worry about whether or not
any of these cells down here could be negative.
In our case where we're shipping from one place to another,
a negative number
would mean that the destination is shipping to the source.
This doesn't make sense.
So I'm gonna add one more constraint,
which is actually quite common in linear programming,
this is called non-negativity.
I don't want any of these cells to be negative.
I can do this in one step.
I'm gonna swipe all of these cells,
all of these should be greater than or equal to zero.
I noticed one more interesting constraint
that I might want to add
when I was adding this non-negativity constraint.
I noticed an integer constraint.
And this would be important
if we are shipping discrete products, such as cars or tyres.
If our products could be split in half,
such as petroleum or paint,
we would not want to use this integer constraint.
But for our problems, I'm going to add this.
I now have all the constraints set, so I could hit OK.
You can see in this dialog box
that we've added all the constraints,
and Excel shows them to us.
You should review these,
just to make sure that they are correct.
You might notice that there is this choice
to make unconstrained variables non-negative.
We did the very same thing
by setting up another constraint
in the subject to constraints dialog box.
The last thing we want to make sure to do
is to select a solving method.
And this should be set to Simplex LP.
Now, hit the Solve button.
Excel solves this problem.
In this next dialog box,
click on, keep the solution, and we're done.
If I ever change the value in my Excel model,
I must rerun the Solver ad-in.
Remember with ad-ins, you get a static result.
So, if something changes, you need to rerun the ad-in.
When we do this,
Solver remembers how we filled out all of these text boxes.
Therefore, all I have to do is hit the Solve button,
and we now have a new solution.
If you ever have a problem
where supply does not equal demand,
you must put a value in this cell in the dummy column,
so that supply is always equal to demand.
Well, give this a try,
see if you can get this to work.
(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.
(upbeat music)