Outline a transshipment problem in Excel - Microsoft Excel Tutorial

- [Instructor] Transshipment problems involve at least two steps, shipment from a primary source, such as a factory, to an intermediate stop, such as a distribution center, and from there, on to the final destination. In this movie, I will show you how to outline a transshipment problem in an Excel worksheet. My sample file is 02_02_Outline and you can find it in the Chapter02 folder of the Exercise Files collection. The data that we are given to solve this problem comes in several parts. The first is the demand for each of our eight wind farms. Next, we have the capacity for our distribution centers and then the capacity for the factories. We also have the totals for each of those elements. And we can see factory capacity, which is here, is greater than or equal to the distribution center capacity, which is here. And finally, we see that the distribution center capacity is greater than or equal to demand. So it is at least possible that solver will be able to find a solution for this problem. Below, I have two sets of mileage charts. For the inbound distance, which you can see here on the left, I have the distance in miles between the factories. So we have Albuquerque, Fort Worth, and Springfield and our three distribution centers, Amarillo, Kansas City, and Tulsa. And also inbound costs per mile of eight cents per unit per mile. Below that, we have the same data, except this time it's for outbound distance from the distribution center to the wind farms. And you can see that laid out here. And the table should look very familiar. We have our eight wind farms, the three distribution centers, and all of our distances. And it turns out that we have a difference in cost per mile. Inbound was eight cents a mile. Outbound is 10 cents a mile. So how do we proceed from here? Well, the first thing we need to do is to set up tables that we can use to change the number of units moving from each factory to distribution center and do the same thing for the distribution center to the wind farms. I'm currently near the bottom of the worksheet, so I will go ahead and work with the distribution centers and the wind farms. So in cell G21, I will type Outbound Transport and Enter. And I'll just format it in bold because it is a label. And then I'll copy the table or the array from the range B22 to E30. So I've got that selected. Control + C to copy. Click cell G22 and Control + V to paste. These cells that I just pasted will be part of the solution, part of the changing cells that change to create a solution. So I will select the cell range from H23 to J30. And I'll go up to the Home tab, and I will change the fill color to yellow. I use the color yellow to indicate a changing cell. Now I can move up and do the same thing for inbound. So I will copy the cell range B14 to E17. So I've got that selected, copy. And I will paste it, starting with cell G14. So Control + V there. And above that, I will type in cell G13, Inbound Transport and Enter. And here with cell G13 selected, I will Control + B to make it bold because it's a label. And same thing with the coloring. I will change the color of the fill for the range H15 to J17 to yellow. The final step that I will take in this movie is to change the distribution center and capacity data, which is currently in a column, to make it into a pair of rows. That way I can have Amarillo, Kansas City, and Tulsa lining up the same way it does in the Inbound Transport and Outbound Transport arrays. So I'll copy the range F2 to G5. So I'll copy that. Then I'll click in cell G8. Press Control + V to paste. But I want it to be in rows, which means that I need to transpose from columns to rows. So I'll click the Paste Options button, which appears to the bottom right of the pasted range. And under the Paste group in the palette, I'll click Transpose. And you can see that Amarillo, Kansas City, and Tulsa line up with the cities below. And we have the capacities available to work with.