Create and run the Solver model - Microsoft Excel Tutorial

- [Instructor] After you implement linking constraints to allow solver to open facilities or keep them closed you can define your solver model to find a solution to the problem. In this movie, I will use the sample file 03_04_Solved. And you can find that in the chapter three folder of the exercise files collection. I'm continuing on with the worksheet that we created at the end of the last movie. So I will dive right in and create the solver model. So I'll go up to the data tab on the ribbon and then in the analyzed group, I'll click solver. And then I can work in the solver parameters dialogue box. Our objective cell is the total cost. So if I scroll down, I can collect cell C 28 and we want to minimize our total cost. And we want to do that by changing two sets of variable cells the on-off zero one and the number of units that are going from each distribution center to each city. So I'll click the by changing variable cells, collapse dialogue button and then I'll select cells H 10 through J 10 hold down the control key and select H 15 through J 15. Those are all of the cells that I've marked yellow as changing. So I will click the expand dialog box button and now I can start adding my constraints. So click the add button and I'll move the add constraint dialog box off of the worksheet so I can see everything that I have or at least potentially if I scroll around. And the first constraint will be the binary constraint. So I'll select cells H 10 through J 10. And in the comparison operator box, I will click bin which is short for binary, then I'll click add. Next I want to make sure that we are only sending integer numbers of units. So I'll select cells H 15 through J 22. And the comparison there will be integer. So I'll click add. I want to make sure we don't send negative numbers of units between cities and distribution centers. So I'll select the same cells again, H 15 to J 22 and those values must be greater than or equal to zero. And click add. Next we need to make sure that we do not exceed the capacity of any of our distribution centers. The total number of units from each distribution center appears in the cells H 23 to J 23. So those must be less than or equal to their capacities. And we'll find that up above in cells H nine through J nine. So I'm leaving the less than or equal to comparison operator. And I'm selecting H nine through J nine and click add. Next we need to make sure that we meet the demand of all of our wind farms so that we'll have the total number shipped to each city down in outbound transport. And that's K 15 to K 22, which must be equal to the demand values for each of those cities. And those are in cells D three through D 10. I'll click add, we have one more constraint and that is the linking constraint that we have at the bottom of the worksheet. So I'll scroll down and remember that the calculations in cells, H 26 through J 26 which I'll select here must be less than or equal to the constraint of zero. And I have those values added here H 28 through J 28, right. I'll go ahead and click okay. And those are all of our constraints. Finally, I need to switch the solving method from nonlinear to linear or simplex. So I'll click the solving method down arrow, select simplex, LP and click solve and solver found a solution. So we have, okay. All right. So let's see what's open and what isn't. It looks like Amarillo is closed and Fort Worth and Tulsa are open. And we see that we have total cost of $98,717.50. And if we wanted to we could change some of our assumptions about the model. For example, if we change the fixed cost of Amarillo to zero, just a possible future scenario and rerun the model by going to solver on the data tab and clicking solve, takes a moment to think about it. And we see that we get a different solution. This time Tulsa is turned off. However, if we set all opening costs to zero so I'll do that and click solver. Solve again. And here we have our solution and we have all of them open and we have a solution that is very similar although with changed demand to what we saw in chapter one when we worked on the transportation problem. So as you can see, this is a very flexible worksheet. And if you have distribution centers that have fixed costs associated with them, or similar resources you can use an on-off or linking constraint model to determine the best way to open and close those facilities.