Solving Transportation or Distribution Problems using Excel Solver

It is well known that companies not only manufacture products at different locations but also ship their products to the customer at different locations. Distribution managers need to allocate and ship merchandise in the most efficient and cost-effective manner. This kind of problem is called Transportation problem. I will discuss how to use Excel Solver to solve the Transportation problem. If you are not familiar with Excel solver, you can read one of my previous articles – How to solve LP using Microsoft Solver.

Case 1: Minimize total shipping cost while meeting requirements

Suppose that northern, central, and southern California each uses 100 billion gallons of water each day. Also, assume that northern California and central California have 120 billion gallons of water available, whereas southern California has 40 billion gallons of water available. The cost of shipping 1 billion gallons of water between the three regions is as follows:

Northern Central Southern
Northern $5,000 $7,000 $8,000
Central $7,000 $5,000 $6,000
Southern $8,000 $6,000 $5,000

What is the cheapest way to deliver the quantity of the drug each region needs?

Our objective, in this case, is to compute the total shipping cost and minimize it. The total cost can be expressed as follows:

(Number of billion gallons of water sent from Northern to Northern)*(Cost per billion gallons of sending water from Northern to Northern) + (Number of billion gallons of water sent from Northern to Central)*(Cost per billion gallons of sending water from Northern to Central) + (Number of billion gallons of water sent from Northern to Southern)*(Cost per billion gallons of sending water from Northern to Southern) + …… + (Number of billion gallons of water sent from Southern to Central)*(Cost per billion gallons of sending water from Southern to Central) + (Number of billion gallons of water sent from Southern to Southern)*(Cost per billion gallons of sending water from Southern to Southern)

Read More: Deal with Sequencing Problems Using Excel Solver!

If we translate the above model into an excel formula, it will not only cost us a lot of time but also expose us to a high risk of making a mistake. In order to make computation easier, I put the costs of shipping 1 billion gallons of water between those three regions were in the range C3:E5. And shipments for each supply point to each region were put in cell range C8:E10. These two ranges are of the same dimensions and therefore SUMPRODUCT function can be used to compute total cost here. The formula used in this case to compute total cost is “=SUMPRODUCT (C3: E5, C8: E10)” which was entered into cell G3.

So far, we have determined our objective and by changing cells. What we need to do now is to determine the explicit or implicit constraints. One constraint is that water received by each region should be equal to or greater than 100 billion gallons of water. This constraint was put in range C12:E14. The formula in cell C12 is “=SUM (C$8: C$10)”. The formula in cell D12 and E12 were copied from that in C12. Another one is that water sent from each region must be equal to or less than a billion gallons of water available. We use the formula “=SUM (C8: E8)” to compute the total billion gallons of water sent from Northern California. By copying and pasting this formula into cells G9 and G10, we can get the amount of water sent from Central or Southern too.

Solving transportation problem using Excel solver image 1

Figure 1.1

Now let’s click on the Data tab and then click on Solver in the Analysis group to open the Solver Parameters dialog box.

Solving transportation problem using Excel solver image 2

Figure 1.2 [click on the image to get a full view]

Fill the dialog box as shown in Figure 1.3. Total shipping cost is computed by adding together the terms changing cell*constant. Two explicit constraints are created by comparing sums of changing cells with a constant. You can see that both our objective and constraints can be represented by a linear relationship. Therefore, our model is a linear model, and the Simplex LP engine was selected. Obviously, shipments must be positive and that’s the reason why we selected the Make Unconstrained Variables Non-Negative check box.
Solving transportation problem using Excel solver image 3

Figure 1.3 [click on the image to get a full view]

After clicking on Solve and Ok, Excel returned below optimal solution. The minimum cost of meeting requirements is $1,580,000 and can be achieved by applying the below schedule:
  1. 100 billion gallons of water sent from Northern to Northern and 20 billion gallons of water sent from Northern to Sothern
  2. 100 billion gallons of water sent from Central to Center and 20 billion gallons of water sent from Central to Sothern
  3. Southern keep all available water for its own use
    Solving transportation problem using Excel solver image 4

    Figure 1.4

Case 2: Maximize after-tax profit with limited production capacity

A company produces and sells drugs at several locations. The decision of where to produce goods for each sales location can have a huge impact on profitability. Suppose that we need to produce drugs at six locations and sell to customers in six areas. The tax rate and variable production cost depending on where the drug is produced. The sales price of each drug depends on where the drug is sold.

Sold1 Sold2 Sold3 Sold4 Sold5 Sold6
Sales price $45 $40 $38 $36 $39 $34
Tax rate 31% 40% 20% 40% 35% 18%
Variable production cost $8 $7 $6 $9 $7 $7

Each of the six plants can produce up to 6 million units per year. The annual demand for your product in each location is as follows:

Sold1 Sold2 Sold3 Sold4 Sold5 Sold6
1000000 2000000 3000000 4000000 5000000 6000000

The unit shipping cost depends on the plant where the product is produced and where the product is sold:

Sold1 Sold2 Sold3 Sold4 Sold5 Sold6
Plant1 $3 $4 $5 $6 $7 $8
Plant2 $5 $2 $6 $9 $10 $11
Plant3 $4 $3 $1 $6 $8 $6
Plant4 $5 $5 $7 $2 $5 $5
Plant5 $6 $9 $6 $5 $3 $7
Plant6 $7 $7 $8 $9 $10 $4

How can you maximize after-tax profit with your limited production capacity?

Since tax rate, variable production cost, and sales price relate to the location where the drugs are produced or sold, we can simplify the problem by computing profit for each plant and then add them together. Below Figure 2.1 shows how to arrange data. Shipping costs were filled into range C3:H8. The number of drugs sent from plants to locations was entered into range C13:H18. Formula “=SUM (C13: C18)” was typed into cell C20 to calculate the number of drugs received at location 1. By copying the formula into cells from D20 through H20, we can get all the amounts of drugs received at other locations. Formula “=SUM (C13: H13)” was used to compute how many drugs are produced in plant 1. To compute how many drugs are produced in other plants, the formula in cell J13 was copied into range J14: J18.

Read More: Solving simultaneous linear equations in Excel using Solver

I put the sales price in the range C9:H9 because it depends on where the drug is sold. There are entered into the same row so that I can use the SUMPRODUCT function to compute gross revenue. Similarly, tax rate and variable production cost were listed in the same column because they depend on where the drugs are produced and the plant names are in one column.

The formula in Figure 2.1 was entered into cell L3 to compute after-tax profit for Plant1. Thanks to the good data arrangement, I can copy this formula into cells L4 through L8 to calculate after-tax profit for other plants. And finally by adding the profits together using the formula “=SUM (L3: L8)”, we can get total profit which is our objective.

Solving transportation problem using Excel solver image 5

Figure 2.1 [click on the image to get a full view]

Open the Solver Parameters dialog box and fill it as Figure 2.2. Both the two explicit constraints can be represented in a linear relationship and thus I selected the Simplex LP engine. The first constraint is the drugs received at each location should be equal to or greater than demands. This can be described using “$C$20:$H$20 >= $C$22:$H$22”. Another constraint is that drugs produced cannot be greater than the capacity which can be represented by “$J$13:$J$18 <= $L$13:$L$18”.
Solving transportation problem using Excel solver image 6

Figure 2.2

After clicking Solve and Ok, Excel returned the following solution. The maximum after-tax profit can be as much as $332,630,000. All six plants produce 6,000,000 drugs. For example, Drugs produced in Plant 1 should be sold only at location 1. Drugs produced in Plant 2 will be sold at location 1 and location 2.

Solving transportation problem using Excel solver image 7

Figure 2.3 [click on the image to get a full view]

Read more…

Excel Solver with a Case Study [Rate Your Sports Team]

Sequencing problem using Johnson’s algorithm of scheduling n-jobs on 2-machines [Sol]

Download working file

Download the working file from the link below.

Zhiping Yan

I am from China and this photo was taken in a classical garden. There are many similar gardens in China, attracting a lot of visitors every year, especially in spring and summer. I was major in Biotechnology. But I took a job as a SAS programmer because I prefer programming. Besides SAS, I also learned Excel VBA in my spare time. It is fantastic to be able to manipulate data, files and even to interact with the internet via programming. This will save me a lot of time. I am keen to learn new things.

2 Comments
  1. How to solve data envelopment analysis problems using Excel Solver?

Leave a reply

ExcelDemy
Logo