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

Change parameters by hand - Microsoft Excel Tutorial

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

Start my 1-month free trial Buy for my team

Change parameters by hand

“

- [Instructor] Testing the results of your solver models helps you learn about your business. In this movie, I will show you how to change values by hand to answer important questions about your business and how it performs. My sample file is, 06_01_ChangeValues, and you can find it in the Chapter 6 folder of the exercise files collection. In this workbook, I have a transportation problem, and it's a straightforward transportation problem, so no distribution centers or second steps. And I have my demand here in column D, and the other constraints and the solution here in yellow. Over to the right, I have the current solution, which I have labeled as Previous Demand and Previous Solution, and that's so I have my original values on the same worksheet as the model that I'm changing. It doesn't have to be there. If you don't have room, you can always print it out on paper or have it on a tablet or a laptop. But in this case, because my screen is large enough, I have them side by side. With that in mind, let's go ahead and start making some changes. First, I will increase the demand from Sweetwater, which is currently a 600, to 700. So, in cell D10, I'll replace the existing value with 700, press Enter, and we're still 75 units below our capacity, so we will be able to get a solution. I'll go to the Data tab of the ribbon, and then click Solver to run it, click Solve, and click OK. And we get our solution. So it looks like there was a shift of 100 units from Amarillo to Abilene. So we've gone down to 300 from 400 in the original. And then that has been taken up by Kansas City, which went from 175 to 275. And I don't see any other changes except for Sweetwater, which has gone from 600 to 700. I'll make another change by taking Sweetwater back to 600. So in D10, type 600 and Enter. And I will go up to Abilene, which is in D3, and change it from 600 to 400. And go back to Solver, click Solve, click OK. And let's take a look at our solution. So for Abilene, we no longer need the excess from Kansas City and Tulsa, and that also meant that we could shift 25 units from Tulsa to Canton. And Kansas City to Canton has gone down to 325. And it doesn't look like there have been any other changes. So it looks like we got a little bit of an increase in efficiency by reducing the demand in Abilene and adding it on to Canton. Now see what happens when we make both changes. So I will leave Abilene at 400 and change Sweetwater back to 700, and Enter, then the Solver, and Solve, and OK. And here we have 375 and 25, and everything else is the same except for Sweetwater, which has gone back up. Now let's push things a little bit closer to our total capacity. So I'll go up to cell D5 and change Canton from 350 to 500. That takes the demand to 3375, and that still takes us below our total capacity, but we're closer than we were. So I'll go back up to Solver, click that, click Solve, click OK. And our solution doesn't look like it has changed. We have Amarillo getting 375 and 25. Canton has gone up to 500, but the sources haven't changed. And all of the other values, including the 700, which was the increase in Sweetwater, remain as they were. So it appears that our original solution is pretty robust. There are only small changes if we make changes to demand that stay well within our capacity. The question of course, is what would happen if you make much bigger changes.

Contents