Distribution Network Design: Centre-of-Gravity Locations, Customer Assignment, Transport vs Warehouse Costs, Service Coverage & the Number of Depots
Decide where warehouses should be and how many you need: find a weighted centre of gravity and improve it step by step, assign customers to the nearest depot, trade transport costs off against warehouse costs, measure how much demand is within a delivery time and estimate how safety stock changes with the number of depots, with every cell checked as you go.
One-time payment · lifetime access · lessons, labs, exam and certificate included · no subscription.
About this course
This course designs the depot network of a fictional distributor in a spreadsheet. You place ten towns on a kilometre grid and find the volume-weighted centre of gravity with SUMPRODUCT, measure straight-line distances with SQRT and improve the location with a Weiszfeld step, read a towns × sites distance matrix and assign every town to its nearest depot with MIN and INDEX/MATCH, price inbound trunking and outbound deliveries with a road factor, add fixed and handling warehouse costs with a capacity check, measure the share of volume within a drive-time promise with SUMIFS, apply the square-root rule to safety stock and compare one to four depots with MINIFS and a stacked column chart.
Lessons
- The Demand Map: Coordinates, Volumes and the Weighted Centre of Gravity Pro
- Better Than the Centre of Gravity: Distances, Tonne-km and a Weiszfeld Step Pro
- Candidate Sites: Distance Matrix, Nearest Depot and the Volume Each Site Serves Pro
- Transport Cost: Outbound Deliveries, Inbound Trunking and Road Kilometres Pro
- Warehouse Cost: Fixed Cost per Depot, Handling per Tonne and Capacity Pro
- Service Coverage: Drive Times, the Delivery Promise and the Price of Better Service Pro
- The Inventory Effect: More Depots, More Safety Stock (the Square-Root Rule) Pro
- How Many Depots? Total Cost Curve, Service Target and the Recommendation Pro