Excel Supply Chain Analysis: Solving Transportation Problems

Learn how to use Microsoft Excel to conceptualize, set up, and solve transportation problems in order to minimize costs in a supply chain.

Introduction

• Solve supply chain transportation problems with Excel
• What you should know

1. Solving a Transportation Problem

• Describe the transportation problem
• Outline a transportation model in Excel
• Define decision variables, constraints, and total cost
• Create and run the Solver model

2. Solving a Transshipment Problem

• Describe the transshipment problem
• Outline a transshipment problem in Excel
• Define decision variables, constraints, and total cost
• Create and run the Solver model
• Evaluate a Solver solution

3. Analyzing Fixed Costs and Facility Opening Decisions

• Describe fixed cost and facility opening problems
• Outline a linking constraint problem in Excel
• Implement linking constraints in Excel
• Create and run the Solver model

4. Enforcing Level of Service Constraints

• Describe level of service constraints
• Outline level of service constraint analysis in Excel
• Calculate level of service metrics
• Create and run the Solver model

5. Practicing Transportation Problem Analysis

• Challenge: Analyze a transportation problem
• Solution: Analyze a transportation problem
• Challenge: Analyze a transshipment problem
• Solution: Analyze a transshipment problem
• Challenge: Which distribution centers to open
• Solution: Which distribution centers to open
• Challenge: Level of service constraints problem
• Solution: Level of service constraints problem

6. Extending Your Transportation Analysis

• Change parameters by hand
• Adjust distances using factors
• Explore extreme scenarios
• Find solutions based on a target cost
• Perform sensitivity analysis

Conclusion

• Next steps