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

Evaluate a Solver solution - Microsoft Excel Tutorial

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

Evaluate a Solver solution

“

- [Instructor] In the previous movie, we completed a solver model for a transshipment problem, which was an extension of the transportation problem that we solved in chapter one. It's tempting to look at the numbers in the spreadsheet and accept them as the truth, but you must also use your judgment to determine whether the solution makes business sense. We'll take a look at such a scenario in this movie. The first sample file that I'll open is 02 05 transshipment, and you can find it in the chapter two folder of the exercise files collection. This is the result of our work from earlier in the chapter, and if I scroll down, we can see that the total cost here in cell C 37 is $162,413. The outbound cost from our distribution centers in Amarillo, Kansas City, and Tulsa is 85,445. And if you recall, that's exactly the outbound cost from our transportation problem in chapter one. So all we've done is add cost by moving our units from the factories to the distribution centers. So the question is, is there a better way to do this? For example, could we contract out find a different way of moving our units from the factories directly to our wind farms? I created a worksheet to solve that problem, and I also have it open on my computer, And that workbook is 0205 factory shipping. And as you can see, this reflects very closely the scenario from chapter one, where we went directly from a distribution center to a customer and ignore the factories as part of the transshipment. So here we have our same demand as before, and our factories this time have capacities that were listed in the previous movie. And if we scroll down, we can see that the total cost for this solution is $102,592.50, and we can compare that to the transshipment total of 162,413 that we found in the previous movie. So with this solution, we're saving almost $60,000. But now let's say that we have a long-term and very good relationship with our trucker, who takes the parts around for us. And if we go somewhere else, we might not be able to get 10 cents per mile. So the question is, what is the most we can pay per mile and still save, or at least break even on the transshipment total. To discover that rate, we can use goal seek. So I'll go to the data tab of the ribbon, click the What If Analysis button, and click Goal Seek. Depending upon the size of your monitor, you might not see What If Analysis, you might see Goal Seek as a separate button. So I click here, and we get the goal secret dialog box. And what we want to do is set cell age 25, which happens to already be selected. If not, for example, if I backspaced over it, I could make sure the cursor is in the set cell box, and click sell age 25, set that cell to a value, and we do need to type that in. So I'll click in the two value cell, and type one, six, two, four, one, three, no commas, just type it in directly. And we want to do that by changing the cost per mile. So click in by changing cell, click cell C 24, and click Okay. Great, Excel found a solution, and it looks like the cost per mile would be about 16 cents. I'll click Okay, and click sell C 24, which contains the cost per mile. And here we see it's actually about 15.83 cents per mile. And that gives us a negotiating target. If we can find a shipper that is willing to move our products for 15 cents a mile, then it becomes a close call. If it's 12 cents a mile, then it is a much easier decision to go with a new shipper and save money.

Contents