In this article, we will discuss 2 cases for **solving transportation or distribution problems using the Excel Solver**. It is well known that companies manufacture products at different locations and ship their products to customers at various locations. Distribution managers need to allocate and ship merchandise in the most efficient and cost-effective manner. This kind of problem is called the **Transportation **problem. Today, we will discuss **2 **cases. Using these cases, you can easily use Solver for transportation or distribution problems in Excel.

## Download Practice Workbook

You can download the practice workbook from here.

## 2 Suitable Cases of Solving Transportation or Distribution Problems Using Excel Solver

Today, we will discuss two cases of solving transportation or distribution problems using **Excel ****Solver**. In **Case 1**, we will try to minimize the total shipping cost while meeting requirements. And in **Case 2**, we will maximize after-tax profit with limited production capacity. You can see a quick view of **Case 1 **below.

### Case 1: Minimize Total Shipping Cost While Meeting Requirements

In the first case, we will try to minimize the total shipping cost while meeting all requirements using **Solver **in Excel. For that purpose, we need to understand the problem first. 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 **60 **billion gallons of water available. The cost of shipping **1 **billion gallons of water between the three regions is as follows:

We need to find the cheapest way to deliver the amount of water 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)**

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. So, let’s follow the steps to see how we can implement an easier formula and use **Solver **to find the minimum shipping cost.

**STEPS:**

- First of all, we need to create a dataset to enter the shipments for each supply point to each region.
- Here, the
**range C10:E12**depicts that. These are the “**By Changing Cells**” that we will use inside the**Solver**tool.

- Secondly, we need to assign cells for the
**Objective**and**Constraints**. - In our case,
**Cell G5**is the**Objective**cell that will hold the minimum shipping cost. - Similarly,
**range C15:D17**represents**Constraint 1**and**range G10:I12**denotes**Constraint 2**. **Constraint 1**says the**Demand**for water in each region should be equal to or greater than**100**gallons. After minimizing, we will get the**Received**amount of water in the**range C12:C14**.- On the other hand,
**Constraint 2**denotes that northern and central California have the capacity of water equal to or less than**120**gallons whereas southern California has the capacity equal to or less than**60**gallons of water. After minimizing, we will get the gallons of water in the**range G10:G12**that should be**SENT**.

- In the following step, we need to type the formula below in
**Cell G5**to get the**Total Shipping Cost**:

`=SUMPRODUCT(C5:E7,C10:E12)`

In this formula, we have used **the SUMPRODUCT function**. In order to find the total cost, we put the costs of shipping **1 **billion gallons of water between those three regions in cell **range C5:E7**. And shipments for each supply point to each region were put in cell **range C10:E12**. These two ranges are of the same dimensions and therefore **SUMPRODUCT **function can be used to compute the total cost here.

- So far, we have determined our
**Objective**and the “**By Changing Cells**”. - Now, we need to apply equations for the constraints.
- As
**Constraint 2**states that water sent from each region must be equal to or less than the gallons of water available, we can use the formula below in**Cell G10**to compute the total gallons of water sent from**Northern California**:

`=SUM(C10:E10)`

- Drag down the
**Fill Handle**up to**Cell G12**to get the amount of water sent from**Central**and**Southern California**.

Here, we have used **the SUM function **to find the resultant of the **range C10:E10**.

- On the other hand,
**Constraint 1**is that water received by each region should be equal to or greater than**100**billion gallons of water. - This constraint was put in the
**range C15:E17**and the formula below is used to count the**Received**amount in**Cell C15**:

`=SUM(C$10:C$12)`

- Now, drag the
**Fill Handle**to the right up to**Cell E15**to copy the formula.

- After inserting the formulas, go to the
**Data**tab and select**Solver**from the**Analyze**section. It will open the**Solver Parameters**box.

- Fill the dialog box as shown in the below figure.
**Total Shipping Cost**is computed by adding together the terms changing cell*constant in**Cell G5**. So, “**Set Objective**” is**G5**.- After that, select
**Min**and type**range C10:E12**in the “**By Changing Variable Cells**” field. - Two explicit constraints are created by comparing sums of changing cells with a constant. We have inserted the constraints using the
**Add**option. - 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. - After that, click on the
**SOLVE**option.

- Again, click
**OK**in the**Solver Results**box.

- Finally, you will find the requirements and total shipping cost automatically.

The minimum cost of meeting requirements is $**1,580,000 **and we can achieve it by applying the following schedule:

- We can send
**100**billion gallons of water from**Northern to Northern**and**20**billion gallons of water from**Northern to Southern**. - Also, we can transfer
**100**billion gallons of water from**Central to Central**and**20**billion gallons of water from**Central to Southern.** - Southern keeps all available water for its own use.

### Case 2: Maximize After-Tax Profit with Limited Production Capacity

In the second case, we will minimize the after-tax profit with limited production capacity with the **Solver **add-in. To illustrate the example, we will use the problem below:

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 plants **and sell them to customers in **six areas**. The “**tax** **rate**” and “**variable production** **cost**” depend on the place of production. The “**sales price**” of each drug depends on the selling place. Also, each of the six plants can produce up to **6 **million units per year. You can see the **annual demand **for the products in each area in the problem overview below. The unit shipping cost depends on the plant and area. You can find the details in the overview below.

We need to find the maximum after-tax profit with the limited production capacity.

Let’s follow the steps below to see how we maximize the profit using the **Solver **tool in Excel.

**STEPS:**

- Firstly, we need to create a dataset for computation.
- Since
**tax rate**,**variable production cost**, and**sales price**relate to the location of production or sale, we can simplify the problem by computing profit for each plant and then adding them together. - You can see the rearranged dataset with the
**Profit**column. Here, we will count the profit for each plant.

- Secondly, insert cells for “
**By Changing Variable Cells**” in the**range C14:H19**. These cells will contain the number of drags sent from**Plants**to the**Areas**. - Also, one constraint states that the capacity of the plants is less than
**6**million units. We insert that constraint in the**range J4:L19**.

- Again, the second constraint denotes the amount of the
**Received**and**Demand**units. It is in the**range C21:H23**like the picture below.

- Now, to compute the after-tax
**Profit**, type the formula below in**Cell K5**:

`=(SUMPRODUCT(C14:H14,$C$11:$H$11)-J14*I5-SUMPRODUCT(C14:H14,C5:H5))*J5`

- Press
**Enter**and drag down the**Fill Handle**up to**Cell K10**. - Then, in
**Cell K11**, type the formula below:

`=SUM(K5:K10)`

- After that, type the formula below in
**Cell J14**to count the number of drugs sent from plants to locations:

`=SUM(C14:H14)`

- Hit
**Enter**and drag the**Fill Handle**down to**Cell J19**.

- To find the
**Received**number of drugs, type the formula below in**Cell C21**:

`=SUM(C14:C19)`

- Press
**Enter**and drag the**Fill Handle**to the right to**Cell H21**.

- In the following step, navigate to the
**Data**tab and select**Solver**from the**Analyze**section. It will open the**Solver Parameters**box.

- In the
**Solver Parameters**box, fill the dialog box as shown in the below figure. - Here, we computed
**Total Profit**by adding the profit of each plant. So, “**Set Objective**” is**K11**. - After that, select
**Max**and type**range C14:H19**in the “**By Changing Variable Cells**” field. - Now, insert the constraints using the
**Add**option. - The first constraint is that each location should receive drugs equal to or greater than the demands. We can describe it using “
**$C$21:$H$21 >= $C$23:$H$23**“. - Another constraint is that the produced drugs cannot be greater than the capacity. You can write it using “
**$J$14:$J$19****<= $L$14:$L$19**”. - You can see that both our objective and constraints can have a linear relationship. Therefore, our model is a linear model, and we need to select the
**Simplex LP**engine. - Obviously, shipments must be positive and that’s the reason why we selected the
**Make Unconstrained Variables Non-Negative**check box. - After that, click on the
**SOLVE**option.

- Again, click
**OK**to proceed.

- As a result, you will see the maximum profit amount in
**Cell K11**.

- Also, you will get the number of drugs that you need to send to locations using constraints in the
**range C14:H19**.

Here, all six plants should produce **6 **million units of drugs to get a maximum profit of $**332,630,000**.

## Conclusion

In this article, we have demonstrated **2 **cases of **Solving Transportation or Distribution Problems Using Excel Solver**. I hope this article will help you to perform your tasks efficiently. Furthermore, we have also added the practice book at the beginning of the article. To test your skills, you can download it to exercise. Also, you can visit **the ExcelDemy website** for more articles like this. Lastly, if you have any suggestions or queries, feel free to ask in the comment section below.

How to solve data envelopment analysis problems using Excel Solver?

Raja, any working file on that?