Solving Transportation or Distribution Problems Using Excel Solver (2 Suitable Cases)

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.

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.