Hey everyone in this video we will see another use case of Excel.
This time we will solve the transportation problem a transportation problem is a common optimization
problem.
Well we have one set of suppliers and one set of destinations where there is demand of a particular
commodity.
Now the problem is that there is different cost of transportation per unit on different routes.
So transferring one unit from this factory to this show cost two point nine units of money.
And our agenda is to find out which factory will supply how much to which shop.
So that the total cost of transportation in code is leased.
Let me show you this example situation.
Suppose you are required to satisfy the demand of certain commodity at three different shops as one
as two industry with what is available at two different factories F1 and F2 the quantities available
at F1 and F2 are three units and six units respectively.
The demands add as one as two an extra year to Unit three unit and four units respectively as the total
supply is the same as the total demand.
Obviously there is at least one feasible solution to this problem.
Also as the number of factories is less than the number of shops at least one of the factories is applying
to more than one shop.
So let us first put this problem into a tablet format.
On the left I have just listed down all the constraint that I mentioned on the right.
There is a table in this table on the left.
We have factories F1 and F2 and on the top you can see shops s1 s2 and history.
The first value in this table will tell us how much unit will go from factory one to shop one.
The second value will tell us how many unit from factory one will go to shop too.
And this is the meaning of this table.
Each side is telling us which factory is sending how many units of commodity to which shop on the date
I am right written the constraint the factory one has a constraint of supplying maximum 3 unit factory
2 has a constraint of secured shop one is demanding to unit shop 2 is demanding 3 unit shop 3 is demanding
4 units.
So basically the sum of these two values should be two.
These two values should be three next to value should be four and if you go horizontally these three
value should be three.
And these two value should be six below.
I have made another table.
This table is giving us the transportation cost from each factory to each shop.
So if you want to transport one unit from F1 to S1 you'll be charged two point nine unit of money.
If you are going from EV to do S1 you'll be charged to 1 3 units of money per unit of commodity.
So the total cost of transportation will be the product of number of units you're sending from one factory
to their job multiplied by the cost of transportation per unit plus the units that you send from that
factory to another shop multiplied by the cost of transportation there and so on.
You add all these individual costs to get the total cost to get the total cost.
We have used a single formula called sum product.
This formula multiplies elements of two different mattresses and adds them simultaneously.
If you want to know more about such formulas there is a separate course on Microsoft Excel that we have
hosted on udemy the link for which you can find in the description below.
Now from these tables you can see our agenda is to minimise this cost and the sales in green color are
variable tools.
We want to change values in these sales so that the demand of all these shops is satisfied and we get
the minimum cost just to prove that there are multiple solutions.
Let us just by observation solve this problem.
Let us try to put values here to fulfill the demand of shops for disposal.
We will say to unit so that the demand of shop one is fulfilled.
Now if one has only one unit left because it has a limit of three unit and we have already sent two
units to shop one so it can only send one unit.
So we are sending one year to shop to now to fulfil shop to demand.
We have to send two units from factory two and we head will have to send for units from factory to to
shop three.
This is one solution and you can see since I have already applied the formula here it is giving us the
value that the total cost I really got is fifty four point four.
Now instead of sending two units from factory one if we decided to send only one unit from factory one.
So we have one unit from factory one and one from factory two.
Again for short two we are sending one unit from factory 1 and 2 from factory to for shop 3 also we
are sending one unit from factory one and w 3 from factory 2.
You can see still the total cost is forty seven point five.
Is there any solution for which I am sure that it is the minimum cost to solve such problems in excel.
We use goal seek goal take is available in the data menu and the solver I have solved here because I
have added it from the add into the back.
If you are not seeing this you go to defy option it go to options and go to the add an option manage
excel at it go.
Here you have to take this all over again if it is ending you will not be able to see this all over
option here take this.
Click on okay and you will get followed option to now let us go to the SSA sheet and solve this problem.
First thing I have done is we have added to sales it these sales will actually add the values in these
three sales.
This will add the values in these three sales and the next sale will add the values of these results
and the constraint that will give our solve it is that this value should actually be equal to the supply
that this factory can actually do.
Similarly for the shops we will add these two sales and to this bottom sell and will give the constraint
that this value the sum of those two should be equal to this constraint.
Now let's start typing is equal to some
of these three
know some from J seven to eleven
and this sale will have some of these six and seven
we can drag this also or we can continue to write it
let's drag it and this boredom sail is already containing this form love some product to apply some
product we laid is equal to some product and within brackets we first give the first metrics then after
a comma we will select the second metrics.
So now we will click solver option.
The objectives L is SDL that we want to optimize.
So the sale is J 17.
Now we want to minimize this because this is cost to us.
So we will select minimize by changing variable sales.
These are these cells that are marked in green.
These are the cells that lose value.
We can change.
Now we have to go to.
Now we have to add constraint to look at the first constraint as this value should be equal to J name.
You can see this first value cell reference.
This is the calculated value.
This is where you at apply these sum formula.
And this second box is deep constraint and this is the fixed constraint that we have received in the
problem statement.
So we'll click add here next we will select k 8.
It will take a 9 l 8 equal to a line
M6 equal to M7
index at
M7 is equal to entering
and at cost set of constraint is that all these values should be positive.
So we will select all these and will delight to be greater than equal to zero.
Another set of constraint is that all these values should be integral.
So it cannot have values like one point five or two one main.
So these should be in danger so religiously that these are integers and I think these are all these
sorts of constraint so you can't allow so you can see the objective is set to J 17 till we have to minimize
the sale by changing variable tables.
J six to seven.
And these are the list of conditions that we have now we have to select us all we matter because the
variables in this problem have a linear relationship amongst them.
That is why we'll be using these simplex linear programming solving method so let's click solve no.
You want to keep this all your solution so click on okay.
Now these values are actually failing by this all what we initially had some other values.
Now these values are given by a solver and we have a total cost of thirty eight point one.
If you remember initially we had something around 50 now this studied 1 one and this solution is the
optimal solution.
So this is how this all minimization and maximization problems using Excel and particularly using Solver
in the exit.
If you want to learn basics of Excel and some cool new tricks and graphs and excel you have a short
course on udemy the link for which you can find in the description below.
If you click this link you will get the course at a discounted price.
Also if you like this video don't forget to like Sharon subscribe.