In this video, I will be solving this transportation LP problem in Excel.
The plants are the origins The customers are the destinations
Here are the objective coefficients: they’re usually shipping costs per unit
but they could also be profits per unit.
Here are the capacities at the plants.
And here are the demands by the customers.
I’m going to copy the matrix and paste down here.
I’ll delete the objective coefficients and shade the cells yellow.
These cells will contain the values of the decision variables at optimal solution.
That is, how many units to ship from each plant to each customer.
Next, I’m going to move demand down two rows
and move Supply to the right two columns I’m going to create a column for actual
supply here.
For Plant 1, I’m going to do =SUM
all the units shipped from plant 1 to the 4 customers.
I’m going to centre, copy it down and shade the cells blue.
I’m going to place the supply inequality sign here (for visual purposes only)
For Demand, I’ll place actual demands on this row.
=SUM the demand for customer 1
Copy it to other customers Highlight it blue
For the sign, I’ll type the apostrophe before the equal sign
so Excel knows it’s not a formula.
Equality signs are used here because total demand is less than total supply.
If total supply is less than total demand, we will switch the signs.
Next, I’ll place the objective function value here.
It will be total cost if the coefficients are costs
and total profit if they are profits.
We’ll be multiplying the objective coefficients by the decision variables
and adding the result.
Hence, we use =SUMPRODUCT Select the coefficients (comma)
Then select the decision variables.
ENTER I’m going to shade it green.
The setup is now complete.
To solve it using Solver, click the Data tab If Solver is already loaded, you should see
it in the ribbon on the right here.
If it’s not there, right-click on the ribbon and click
Customize the ribbon Click Addins
Then click Go Select the Solver add-in box
And click OK Now, on clicking the Data tab,
you should now see Solver on the right up here.
Click on the Solver command.
For the objective, I’ll select the green OFV cell.
I can choose Max if this is a maximization problem.
But let’s start with Minimization The changing cells will be the yellow cells.
To add the constraints, click Add
For the Supply constraint, select the blue cells for the left side
Select less or equal for the sign and for the right side, select the Supply
values Click Add
For Demand left side, select the blue cells Change the sign to equality
and select the Demand values for the right side
Click OK Ensure this nonnegativity box is checked.
Select Simplex LP in this dropdown and click Solve
And here is the solution.
I’m going to select Answer here to generate the answer report
and click OK.
Clicking on the Answer Report tab We see the objective function value here
the amount to ship from each plant to each customer here
we can also see the total amount at each node here
and that Plant 2 has a slack or unused capacity of 120 units.
Now suppose these coefficients are profits per unit
Click Solver; the last work is already saved.
Select Max Click Solve
And that’s the maximization result I’m going to click Cancel to revert to the
previous solution.
Now suppose Plant 2 cannot ship to Customer 2.
If we solve this as a Maximization problem, the coefficient for this cell will be treated
as zero, and on solving, we find that no shipment is
made from plant 2 to customer 2 because the profit of zero is too small.
But if this were costs, and we solve as a Minimization problem,
we see that it ships from Plant 2 to Customer 2.
This is because the program sees a cost of zero here
and couldn’t pass on the opportunity to ship there for free.
To correct this, we can either enter a very large shipping
cost amount for that cell to discourage shipping along that route,
or we can simply set the value of the decision variable to zero.
We do the latter by adding an extra constraint.
For the left side, click the variable cell Change the sign to equality
And for the right side, simply type zero.
click OK.
That’s our new constraint in the middle.
Click Solve You can see here that units shipped from plant
2 to customer 2 is now zero.
And that’s it for this video.
Thanks for watching.