Create and run the Solver model - Microsoft Excel Tutorial

- [Instructor] After you have filled out a worksheet containing a transshipment problem you can define and run a model to find the optimal, or at least an optimal solution. In this movie, we'll define our constraints in solver and use the simplex engine to find an optimal solution. My sample file is 0204 solved. And you can find that in the chapter two folder of the exercise files collection. This worksheet contains data for demand and capacity for our wind farms which are the customers, distribution centers and then factories. Factories ship to distribution centers, DCS on to cities, or wind farms. We also have our inbound distances and outbound distance between each of the relevant pairs of entities. And then over on the right with the yellow colored cells we have the number of units that will be moving between factories and distribution centers and distribution centers to customers. And if we scroll down we also have conservation and flow constraints, making sure that we have as many units going in to a distribution center as go out and then on the left we have our inbound costs, outbound cost and total cost. So now let's go ahead and set up our solver model. So I'll go to the data tab of the ribbon and in the analyze group, click solver. our objective cell is total cost, which we want to minimize. So I'll make sure the cursor's inside objective and then click Cell C37. We want to minimize our costs, although for fun, it might be interesting to see how you could maximize the cost to meet all the other constraints, but for now we'll work on saving money. We want to change the inbound and outbound unit numbers. So I'll click the by changing variable cells collapse dialogue button and I will select cells H23 to J30. Then I'll scroll up. Then hold down the Control key and select cells H15 through J17. As with all other elements in Excel, you can hold down the Control key and select non-contiguous groups of cells. I will expand the dialogue box and there I see the variable cells. I'll select the solving method. This is a linear problem. So I'll click Simplex LP. And from here we can add our rules. So I click the add button and then I will drag the add constraint dialog box up so it is clear of the worksheet. First I want to make sure that each of the cities receives the amount that they have demanded. So I'll select cells K23 through K30 and then change the comparison operator to equal. And those will be constrained by the demand listed in cells D3 through D10, and I'll click add. Now we need to make sure that each factory stays within this capacity. So that would be the inbound at transport. So our factories are Albuquerque, Fort worth and Springfield. The total coming from them is K15 through K17 respectively. And those need to be less than or equal to the matching capacities and J3 through J5. Now click add. Next we need to ensure that we are sending positive numbers, or at least not negative numbers of units between our factories and distribution centers and also between the DCS and the customers. So first we'll do it for the factories. So I'll select cells H15 through J17. And those values need to be greater than or equal to zero, now click add. Then we need to do the same thing for outbound transport. So I'll scroll down select cells, H23 to J30. Those need to be greater than or equal to zero as well. So I'll click add. Next we need to ensure that each distribution center handles fewer items or at least no more items than their capacity. So I will select cells H31 through J31 and those need to be at less than or equal to their capacities, which are listed up here in cells H9 through J9. Now click add and we just have three more constraints to go. Two of them will ensure that we're only sending integer numbers of units. So I will select cells H15 to J17 and set them to integer. Click add and I will do the same for cells H23 to J30. Those need to be integer click add. And finally, we need to make sure that our conservation of flow constraints match up. So I'll scroll down and the cell references are for cells H34 through J34 and those must be equal to the outflow or the outbound volume, which is in cells H36 to J36 and click OK. And we're done, it's a lot of entering but hopefully we will get a satisfying solution to our model. So I'll click solve. We get a solution and I see that it is $162,413. So we have a fairly high inbound cost to get the units from the factories to the distribution centers. And we actually have the exact same cost as we had for our previous transportation problem. So we have found a solution and it is the lowest cost solution for the scenario. The question is whether it's a good solution. We will compare this transshipment problem to a regular one step transportation problem in the next movie.