From the course: Excel Supply Chain Analysis: Solving Transportation Problems

Create and run the Solver model - Microsoft Excel Tutorial

From the course: Excel Supply Chain Analysis: Solving Transportation Problems

Create and run the Solver model

“

- In the previous movie, we created summary formulas that Solver can use as part of its process to find the lowest cost solution for a transportation problem. In this movie, we will create and run the solver model based on current values. My sample file's 01_04_Solved, and you can find it in the chapter one folder of the exercise files collection. We're continuing the work that we did earlier in the chapter, but just as a quick review, we have demand information for eight wind farms and items are coming from a series of three distribution centers, which have the capacity listed there. I copied the capacity for the distribution centers from columns to rows to make it easier to work with it in combination with the outbound transport matrix that I have below. To calculate total cost, we are multiplying the distance of each route, so in this case Amarillo to Abilene is 270 miles, by the number of items moving on that route. And the corresponding cell would be in H15. We find the sum of all of the element-wise multiplications, multiply by cost per mile, and that gives us our total cost. With all this in place, we can create our solver model. So I'll go up to the data tab of the ribbon, and in the analyze group click Solver. Our objective cell is the total cost, so I will click cell H25. We want to minimize the cost and we're going to be changing the variable cells that represent the number of units that are moving. So I'll click the By Changing Variable Cells collapsed dialogue button, select cells H15 through J22, expand the dialogue, and it looks good. Our solving method will be simplex LP. If you see non-linear than just click the down arrow and click simplex from the list that appears. And from here, we can start adding our constraints. So I'll click add and I will drag the Add Constraint dialog box up so we can see the entirety of the worksheet. First, we need to make sure that we send integer numbers of parts. In other words, we can't send half of a replacement part. So I will select cells H15 through J22, click the comparison operator button here in the middle and click I-N-T. And as we can see that constraint is integer, and click add. We also want to send non-negative numbers of parts. In other words, we can't send minus three replacement parts. So I'll select the same cells again, H15 to J22, greater than or equal to zero, and add. We also need to abide by the capacity of our distribution centers, so that means the total of items that we send, which are found in H23 to J23 must be less than or equal to, which we have here, the values in H9 to J9. And you can see that is why I did a transplants paste on the values from G2 to H5. So that looks good, I'll click add. And finally, we need to ensure that we meet customer demand. So that means the total going to each customer or each city, which is found in K15 to K22 must be equal to the values indicating demand, which is in D3 to D10. I believe that's it, so I'll click okay. Everything looks good, I'll click solve, and solver comes up with a solution. And we see here that every city is receiving items from exactly one distribution center, except for Abilene, Texas. And there we have 400 from Amarillo, 175 from Kansas City, and 25 from Tulsa. So even though that looks a little strange, it is, in fact, the cheapest solution or at least a cheapest solution. When you solve this sort of problem it's possible that there are multiple solutions with the same total cost. In this case, this is the one that Excel settled on, and it is a perfectly valid solution to our problem.

Contents