Create and run the Solver model - Microsoft Excel Tutorial

- [Instructor] So far in this chapter, we have designed a worksheet that lets us calculate level of service constraints based on the distance between a warehouse and the wind farms they serve. In this movie we'll use what we've done so far to define and run a solver model to find the minimum cost solution that meets our level of service constraint. My sample file is 04_04_Solved and you can find it in the chapter four folder of the exercise files collection. This workbook is in the same state as the one we finished working on in the previous movie, so I'll move forward without review. We have everything in place for our solver model. So I'll go to the data tab and then over in the analyze group, click solver. And here we can set our objective cell. That is our total cost. So I will click the collapsed dialog button next to set objective and scroll down and click cell age 25 and then expand the dialog again. We want to find the minimum cost solution and we'll do that by changing our variable cells. So I will click the by changing variable cells collapse dialog button, select cells H15 through J22, and expand the dialog again. All right. That looks good. So I don't forget later, I will change from the GRG Nonlinear engine to Simplex LP because this is a simple linear program, and then I'll click add. Here, we can start adding our constraints. So I'll drag this dialog box up so I can see the entirety of the worksheet. The first constraint that I'll set is to make sure that our level of service is greater than or equal to our target. So I'll scroll down and the level of service that we calculate is in cell C40 and that needs to be greater than or equal to our target, which is in C39. And I'll click add. Next, we need to look at the units that we're sending. Those are in our range, H15 to J22. So we need to make sure that we only send integer numbers of units. So I will select cells H15, J22, and then click the comparison operator down arrow and click INT and click add. We also need to make sure that those values are non-negative, so greater than or equal to zero. So H15 to J22 again, greater than or equal to and then the constraint. I'll just type in zero and add. Next, we need to ensure that none of our distribution centers exceed their capacity. So we've calculated the number of items moving through each of them in H23 to J23. Those need to be less than or equal to the capacities which we copied to cells H9 to J9 and click add. Next, we need to ensure that we meet each city's demand. So I will select cells K15 to K22. And those need to be set equal to the demand that is identified in D3 through D10, and I'll click OK. Right, so there we have our model and I will click solve to run it. And Excel found the solution. Terrific. So I'll click OK. And take a look at the solution in the spreadsheet. Oh my goodness. That is ugly. The reason I say that is if we look at Manhattan, Kansas, and below, then we're getting units from only a single city and the same thing for Canton in the second row. If you look at Lawton, you see we get 325 from Amarillo and 100 from Tulsa. But if you look at Dodge City just above that, there are only three from Amarillo and 397 from Kansas City, and then Amarillo to Abilene is 72 and to Kansas City is 528. So you have some significant inefficiencies here even though it is the lowest cost solution. So let's see what happens if we relax our constraint a bit. So instead of say, 250 miles, let's make it 275. So I'll scroll down to C38 and type 275 and enter, and we need to resolve the problem. So I will go up to the data tab solver, click solve, solution. We knew we would get a solution because we had one at a tighter constraint. Click OK, scroll up, and that looks a lot more reasonable except we still have some fairly significant splits. We have a three-way split for Abilene and a two-way split for Wichita Falls. So let's do one more change and instead of 275 miles, let's make it 300. So in C38 I'll type 300 and go back up to solver and solve. Click OK. And it didn't change. So based on cost, this is about $6,000 less than our other solution. This might be the best way to go. This is where your judgment and expertise as a supply chain analyst comes in. If it makes sense to send trucks from all three of your distribution centers to Abilene, then go ahead and do it. However, if there's some way that you can change the capacity of one of the distribution centers, you might be able to save yourself money and also consolidate your shipping. So don't be afraid to play around with the parameters of the model even after you find a feasible solution.