Welcome to Highline BI 348 class video number 60.
Hey, if you want to download this Excel workbook BI 348
Chapter 8 and follow all along, click on the link
below in the video.
Hey, we're talking in linear programming
and using Excel Solver to find an optimal solution.
Now in this video, we're going to do a transportation problem.
And this is called a network graph
that gives us all the information we
need to figure out the minimum cost
to ship from these locations to these locations.
Now here is our graph.
Production plants, this will be the supply side.
And these are called origin nodes.
We have a plant and Oakland, Portland, and Seattle.
Now if we're producing items in these three locations,
we need to look at the supply constraints, that
means the maximum amount for each one of the locations
that they can produce.
So in Oakland, the max they can produce
is 5,075, Portland 6,150, and Seattle 2,725.
Now these arrows are called arcs,
and the numbers are the transportation cost per unit
to ship from Oakland to Rapid City, Oakland to Spokane.
So we have our transportation costs and our arcs,
and then over here, this is the demand side.
Now these are distribution centers in Spokane, Las Vegas,
Coeur d'Alene, and Rapid City.
And on this side, we have the demand constraints.
Now this is the exact amount that Spokane has ordered,
and is 6,150 Spokane ordered.
Las Vegas ordered 4,125, Coeur d'Alene
ordered 2,050, and Rapid City 1,625.
Now the question is how do we pick the right route
to get the right number of items to each one of the locations?
That's where linear programming and Excel Solver
will come in handy.
Now the trick is, we're going to build a cross tabulated table
like we've done so many times in this class already.
Oakland, Portland, Seattle will be the row headers.
Spokane, Las Vegas, Coeur d'Alene and Rapid City
will be the column headers.
Now I went ahead and put origin or production cities
as row headers, distribution or destination cities
as column headers.
Now we're going to define the decision variable
as number of units shipped.
The origin, that's going to be the row.
The destination, that's going to be the column.
Now I went ahead and put in x 1 1, x 2 1, x 3 1.
Any particular x, that means this x, that
means number of units from Portland to Coeur d'Alene
will be in row 2 column 3.
All right, I'm going to copy this, Control C,
and then paste Control V. And I'm
going to put my dummy data in the active cell.
I'm going to type the number one and then Control Enter
to populate everything from that active cell
through the highlighted range.
Those will be the number of units shipped
from any particular location.
Right now we're shipping one from Seattle to Coeur d'Alene.
These are the decision variables that Excel Solver
will determine for us.
Now we have to put the shipping costs
in, so I'm going to highlight the same range here, Control C.
And in A16, I'm going to Control V.
I'm going to change this label, Shipping Cost Per Units.
Now I'm going to right click and use the mini toolbar.
I do not want this red.
I do not want this bold, delete.
Now I look from this chart, and very carefully I
have to enter the correct values.
Now I'm actually going to highlight in advance
and use the Tab key.
The Tab key will move the active cell.
And when it gets to the end, it will move to the next row.
So I'm looking for Oakland.
I need 3.5, 2.5, 6.75, and 7 along the first row-- 3.5, tab,
2.5, tab, 6.75, tab, 7, tab.
I continue on.
Now I need to get the Portland shipping cost per unit.
So in the active cell 6, tab, 5.5, tab, 2.25, tab, 3.25, tab.
Now many get Seattle shipping cost per unit, 2.5, tap, 5,
tap, 3.75, tab, 5.25, tab.
Now notice that we set this problem up differently
than we did an earlier videos.
We had a whole separate section for our constraints.
But guess what, I'm going to notice that the constraints all
have to do with the units on the supply and the demand side.
So guess what, here's Oakland.
When I add it up and get a total right there,
I have to make sure that it is less than or equal to 5,075.
Similarly, here's the destination.
On the demand side, that total right
there has to be exactly equal to 6,500.
So I'm going to highlight the actual dummy data, the number
of units to ship, with an empty cells to the right in below
and use Alt equals to add them up.
That's a quick way to get your sum function
in all the right places.
Now I'm going to add some labels and formatting.
All right, now that I have the supply
side totals and the demand side totals,
I need to get the actual constraints.
Now I'm going to type these in.
These are the constraints for supply.
So I'm going to look over here for Oakland, it's 5,075.
I'm going to look over here for Portland, it's 6,150.
For Seattle I'm going to look down here, 2,025.
Looks like I have one too many.
All right, we'll do the same thing for the demand.
Here's Spokane total, and I need to compare it to the 6,150.
So I type 6,150, 4,125 for Las Vegas, 2,025 for Coeur d'Alene,
1,625 for Rapid City.
Now before we do anything else, I
want to look at-- we could do this longhand right here.
The objective function is simply each one
of the number of units times the shipping cost per unit,
add those up.
That's the total cost we're trying to minimize.
That's the objective function.
Constraints, notice we use the sum function
to add up all of the items delivered
to each one of the locations and put our comparative operator.
Down here on the demand side, we added up
the individual decision variables
and set them equal to the demanded amount.
Now I'm going to scoot this down out of the way.
And I want to be explicit like we were in earlier videos.
We're actually going to have to enter
each one of these constraint functions
in and compare it to this.
Now in earlier videos, we put the comparative operators right
in the cells, and I'm going to do the same thing here
because I want to see visually on the spreadsheet what
the constraint functions are.
Now in the active cell, I'm going
to type a lead apostrophe and an equal sign
because each one of these totals for demand
has to equal exactly the demanded amount.
And if I were just to type an equal
without the lead apostrophe Excel
would think it's a formula.
So when I Control Enter to populate all those,
there it is.
When we get to our solver dialog box objective function,
comparative operator, right side constraint amount.
Let's do the same thing over here.
All of these actual total amount have
to be less than the maximum that that location can produce.
So all of these, active cell, apostrophe, less than
or equal to the maximum amount, Control Enter.
Now we still need our objective function.
And notice that's a big long one, but it's no problem.
In earlier videos, we saw how we can use the sum product
function to take a range of values,
multiply it by a second range of values
that have the same exact dimension,
multiply, and then add them.
So over here, all right, I have a cell
for our objective function, and I listed
the goal, which is to minimize.
All right, you ready?
Equals sum product, array 1.
Hey, these are all of our decision variables
that Solver is going to figure out
what the right amount for each one should be to minimize cost.
That's array 1, and notice 3 rows by 4 columns, comma.
Sum product doesn't work when it's multiplying and then
adding arrays unless they're the exact same dimensions, 3
rows, 4 columns.
That means number of units times shipping cost per unit Oakland
to Spokane, all the way through to the very last one.
Number of units for Seattle to Rapid City times
shipping cost per unit, ready?
Close parentheses and Enter.
And there we are, we've set up objective functions and all
of our constraint functions with our right hand side
constraints.
Now we can use Solver to get our optimal solution
for total cost.
In fact, I'm going to put total costs.
All right, you ready?
Let's click in that cell with the objective function, Data,
Solver, up at the top, Set Objective.
That is our objective function.
Our objective is to minimize.
Decision Variables, here they are.
Make Unconstrained Variables Non-negative,
that's our non-negativity constraint requirement
for decision variables.
Simplex LP, that'll do our linear programming.
Let's click Add.
Now the first one-- and in earlier videos
we had an objective function, an operator, and a right hand side
constraint-- but anytime you have
a bunch of objective functions and right hand constraints,
and it's the same exact operator between all of them,
you can simply, in cell reference and constraint,
enter the entire range.
I'm going to enter the four objective functions,
change the operator to equal sign.
Notice these equal signs have nothing
to do with the solution.
That's just for us, visually, on the spreadsheet
to see what we're doing and remind ourselves.
We've got the right operator in our Add Constraint dialog box,
the right hand side constraints, boom there we go.
Click Add.
Now we get our supply side, those
are the constraint functions.
Our operator, that's correct.
Right hand side constraints, there they are.
Now this is our second set of constraints, but we
actually, when I click Add, we have a third constraint here.
And we are going to tell the decision variables that we only
want those to come out as integers.
So by doing this, we'll only get whole units
because we're not going to be able to ship partial units
from one place to another.
Now we can click Add.
Now I click Cancel.
There is my set objective function.
There is our goal, our decision variables,
our set of constraints.
We got our settings correct.
I'm going to click solve.
And just like that, Solver found a solution.
I'm going to click OK, and there is our optimal solution.
All right, so in this video, we saw
how to use a network graph to get our information
for a transportation problem where
we needed to minimize costs.
All right, next video, we'll actually
see a finance example where we want
to maximize portfolio returns.
All right, we'll see you next video.