Operations Research - Streamlining Logistics: Supply Chain Optimization

Introduction

Efficient supply chain management is essential for businesses to thrive in today's competitive landscape. Linear Programming (LP) emerges as a powerful tool, especially when it comes to optimizing distribution networks. In this article, we will delve into a practical example of supply chain optimization using Microsoft Excel and Solver. Our objective is to minimize transportation costs while ensuring timely delivery and meeting customer demand.

Example Problem Statement

Consider a distribution network for a company with multiple warehouses and transportation routes. The goal is to determine the optimal quantity of goods to be transported between each pair of locations, minimizing transportation costs. Each route has associated costs, and the overall transportation plan must meet the demand at each location while respecting capacity constraints.

Let's assume the following data:

Warehouses (W1, W2, W3):

Warehouse Capacities: W1 = 100 units, W2 = 150 units, W3 = 200 units.

Customer Demand (C1, C2, C3):

Customer Demand: C1 = 120 units, C2 = 80 units, C3 = 150 units.

Transportation Costs:

W1 to C1: $5 per unit

W1 to C2: $7 per unit

W1 to C3: $6 per unit

W2 to C1: $8 per unit

W2 to C2: $4 per unit

W2 to C3: $3 per unit

W3 to C1: $6 per unit

W3 to C2: $9 per unit

W3 to C3: $5 per unit

Solving the problem

Step 1: Define Decision Variables: Let xij represent the quantity of goods to be transported from Warehouse i to Customer j, where i and j range from 1 to 3 representing the three warehouses and three customers, respectively.

Step 2: Formulate the Objective Function: The objective is to minimize transportation costs. The total transportation cost Z can be expressed as the sum of the transportation costs on each route, multiplied by the quantity transported on that route:

Z=5x11+7x12+6x13+8x21+4x22+3x23+6x31+9x32+5x33

Step 3: Set Up Constraints: a) Demand Constraints:

• For Customer 1: x11+x21+x31=120
• For Customer 2: x12+x22+x32=80
• For Customer 3: x13+x23+x33=150

b) Capacity Constraints:

• For Warehouse 1: x11+x12+x13≤100
• For Warehouse 2: x21+x22+x23≤150
• For Warehouse 3: x31+x32+x33≤200

c) Non-negativity Constraints:

• xij≥0 for all i,j

Setting Up the Excel Worksheet

Define Decision Variables: Open a new Excel worksheet. In cells B2:D4, label the quantities to be transported from each warehouse to each customer. These cells will be our decision variables.

Objective Function: In a cell, let's say E2, label it "Total Cost." This cell will represent the objective function to minimize transportation costs. Enter the formula =B2*$5 + C2*$7 + D2*$6 + B3*$8 + C3*$4 + D3*$3 + B4*$6 + C4*$9 + D4*$5 to calculate the total transportation cost.

Constraints: Introduce constraints to ensure the transportation plan meets demand and respects capacity constraints. Set up constraints for each warehouse and customer, ensuring the total transported from each warehouse equals its capacity and the total transported to each customer meets demand.

Non-negativity Constraints: Set non-negativity constraints for each transportation quantity: =B2 >= 0, =C2 >= 0, ..., =D4 >= 0.

Solver Parameters Dialog Box

Click on "Solver" in the "Data" tab. This opens the Solver Parameters dialog box.

Set Objective Function and Decision Variables: In the Solver Parameters dialog box, set the objective function cell to E2 and the decision variable cells (By Changing Variable Cells) to B2:D4.

Add Constraints: Click on "Add" to enter each constraint. Use the constraints set earlier for warehouse capacities, customer demand, and non-negativity.

Choose Solving Method: Choose the Simplex LP solving method for linear programming problems.

Solver Options: Optionally, set additional options based on your requirements.

Solve: Click "Solve" in the Solver Parameters dialog box. Solver will analyze the model and provide optimal values for B2:D4, minimizing transportation costs while satisfying all constraints.

Interpreting Results

Once Solver completes its analysis, it will display the optimal quantities to be transported between each warehouse and customer in the worksheet. These values represent the most cost-effective transportation plan while meeting demand and respecting capacity constraints.

Conclusion

This example illustrates how Microsoft Excel, coupled with Solver, can be a valuable asset in supply chain optimization. By systematically setting up the problem, incorporating constraints, and utilizing the Solver function, businesses can streamline logistics, minimize costs, and ensure efficient distribution networks. Excel's Solver provides an accessible yet powerful platform for achieving optimal solutions in real-world supply chain scenarios.