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**

We will try to minimize the total shipping cost while meeting all requirements using **Solver **in Excel. Suppose northern, central, and southern California each uses **100 **billion gallons of water each day. 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 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)**

Follow the steps to see how we can use **Solver **to find the minimum shipping cost.

**STEPS:**

- Create a dataset to enter the shipments for each supply point to each region.
- The
**range C10:E12**depicts that. These are the “**By Changing Cells**” that we will use inside the**Solver**tool.

- We need to assign cells for the
**Objective**and**Constraints**. **Cell G5**is the**Objective**cell that will hold the minimum shipping cost.**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**After minimizing, we will get the**Received**amount of water in the**range C12:C14**.**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**.

- Enter the formula below in
**Cell G5**to get the**Total Shipping Cost**:

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

We have used **the SUMPRODUCT function**.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**. 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.

- We have determined our
**Objective**and the “**By Changing Cells**”. - 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**.

We have used **the SUM function **to find the resultant of the **range C10:E10**.

**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)`

- Drag the
**Fill Handle**to the right up to**Cell E15**to copy the formula.

- Go to the
**Data**tab and select**Solver**from the**Analyze.**It will open the**Solver Parameters**box.

- Fill the dialog box as shown in the image below.
**Total Shipping Cost**is computed by adding together the terms changing cell*constant in**Cell G5**. So, “**Set Objective**” is**G5**.- Select
**Min**and enter**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** - 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. - Shipments must be positive and that is why we selected the
**Make Unconstrained Variables Non-Negative**check box. - Click on
**SOLVE.**

- Click
**OK**in the**Solver Results**box.

- You will get the requirements and total shipping cost.

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**. - 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.

**Read More: **How to Use Excel Solver for Linear Programming

**Case 2 – Maximize After-Tax Profit with Limited Production Capacity**

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 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. 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.

**STEPS:**

- 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 adding them together. - You can see the rearranged dataset with the
**Profit**We will count the profit for each plant.

- 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**. - One constraint states that the capacity of the plants is less than
**6**million units. We insert that constraint in the**range J4:L19**.

- The second constraint denotes the amount of the
**Received**and**Demand**It is in the**range C21:H23**as shown in the image below.

- To compute the after-tax
**Profit**, enter 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**. - In
**Cell K11**, enter the following formula:

`=SUM(K5:K10)`

- Enter 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, enter the formula below in**Cell C21**:

`=SUM(C14:C19)`

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

- Navigate to the
**Data**tab and select**Solver**from the**Analyze**It will open the**Solver Parameters**box.

- Fill the dialog box as shown in the below figure.
- We computed the
**Total Profit**by adding the profit of each plant. So, “**Set Objective**” is**K11**. - Select
**Max**and enter**range C14:H19**in the “**By Changing Variable Cells**” field. - Insert the constraints using the
**Add** - 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 enter 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, we will use Excel Solver for Linear Programming and we need to select the
**Simplex LP** - Shipments must be positive and that is why we selected the
**Make Unconstrained Variables Non-Negative**check box. - Click on
**SOLVE.**

- Click
**OK**to proceed.

- You will see the maximum profit amount in
**Cell K11**.

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

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

**Read More: **Example with Excel Solver to Minimize Cost

**Download Practice Workbook**

## Related Articles

- How to Use Excel Solver to Rate Sports Team
- How to Use Excel Solver to Determine Which Projects Should Be Undertaken?
- Solving Sequencing Problems Using Excel Solver Solution
- How to Assign Work Using Evolutionary Solver in Excel
- Resource Allocation in Excel
- How to Create Financial Planning Calculator in Excel
- Solving Equations in Excel
- How to Do Portfolio Optimization Using Excel Solver

**<< Go Back to Excel Solver Examples | Solver in Excel | Learn Excel**

How to solve data envelopment analysis problems using Excel Solver?

Raja, any working file on that?