Transportation Cost Optimization (Excel)

Introduction

In Supply Chain Management, one of the most important areas to optimize is transportation cost. The idea is to optimize the transportation cost while supplying the stores with their needs in time.

So, the question we should answer is:

How to optimize the transportation cost (Minimum Cost Possible) while supplying the stores' needs?

In this project, I'm going to answer this question using Excel. The data is basic and not real, but it represents well reality. This means that I can use the same logic and calculations with real data.

You can download the Excel Document by clicking on the button below:

Sheet 1: The Problem

In this sheet, I have all the details I need in order to answer the question: How to optimize the transportation cost (Minimum Cost Possible) while supplying the stores' needs?

I have information about:

Suppliers & Clients:

Our company has 2 factories and sells for 3 stores.

Supply:

• The first factory F1 produces 6 units.

• The second factory F2 produces 3 units.

Demand:

• The first store S1 needs 2 units.

• The second store S2 needs 3 units.

• The third store S3 needs 4 units.

Cost:

• The cost of transportation of 1 unit between F1 and S1 is 2.9

• The cost of transportation of 1 unit between F1 and S2 is 4.2

• The cost of transportation of 1 unit between F1 and S3 is 0.8

• The cost of transportation of 1 unit between F2 and S1 is 2.3

• The cost of transportation of 1 unit between F2 and S2 is 8

• The cost of transportation of 1 unit between F2 and S3 is 7.1

Problem Illustration

To better understand how the details (Factories, Stores, Cost, Supply, and Demand) interact with each other, I visualized them using app.diagrams.net.

Sheet 2: The Solution

First, I transformed all the data into a tabular format of 2 tables:

Supply & Demand:

• The row "Demand" presents the needs of each store.

• The column "Supply" presents the production of each factory.

• The "Cells filled in light green" present the different combinations possible between the factories and the stores. These cells are going to be calculated by excel to give us the best combination that minimizes the costs.

Cost Matrix:

This table represents the transportation cost per unit between the factories and the shops.

The Minimum Cost Possible:

This calculates the total cost of the different transportation combinations. I used the function SUMPRODUCT() with the light green cells array and the second table array of the costs.

The use of SOLVER:

SOLVER is a what-if analysis tool that finds the optimal value of a target cell by changing values in cells used to calculate the target cell.

Before using the Solver tool, I added a row and a column to my Supply & Demand table as I will need them to add constraints.

• The "Sum/S" row is calculating the sum of the supply of both factories to each store.

• The "Sum/F" column is calculating the sum of the supply to all stores from each factory.

Using the Solver tool I set the objective to point to the Minimum cost possible cell. And as I am trying to minimize the cost I selected "Min".

I set the changing variable cells to the light green cells.

These are the constraints I added to the calculation:

• The "Sum/S" should be equal to the "Demand" for each store.

• The "Sum/F" should be equal to the "Supply" for each Factory.

• The light green cells should be equal or superior to 0.

• The light green cells should be integers.

I, then, selected "Simplex LP" as a solving method as the problem I'm trying to solve is linear.

Considering the data and the constraints I had, the Minimum Cost Possible was 38.1 with this combination:

Conclusion

Now that we have an automated solution to calculate the minimum transportation cost possible, whenever the stores' needs and/or the transportation cost and/or the factories' supply change, we can simply modify them with the new numbers and again we get an automated minimum transportation cost possible.