Supply Chain

Centers‑of‑Gravity Calculator

Customers

• Customer_ID: free format identifier.
• Latitude, Longitude is the result of converting a textual ship-to address (not sold-to adress) into geographic coordinates - a process called geocoding. You can use our:
  • Batch Geocoder - Free
  • Batch Geocoder - Paid
  • Excel interfaces with Google Maps / TomTom / BING geocoding api
  • Or use any other geocoder of your own choice.
  
  
  • Check postal codes for leading zeroes. If - for example - you notice German or French postal codes with only 4 digits only in your data instead of 5, then there is a leading zero issue/missing. This issue is quickly caused in/by Excel if you have not set your postal code column to type "text". It then may cause geocoding problems. And if you aggregate such incorrect data at two digit postal code level, you end up with an incorrect aggregation, with demand ending up at the wrong location!
  • Put aggregated demand of far-away-customers delivered by sea/air (on a country's overseas island, for example) on exit harbour/airport (latitude/longitude), to ensure that Centers-of-Gravity are pulled in the right direction, and to avoid that one of the Centers-of-Gravity ends up in such remote customer area. Or simply remove such customers completely from the model, if their aggregated demand is marginal.

  Suggestions for aggregating customer data

  In general, try to keep your model small. If your original customer data set contains more than 20,000 records, most likely this amount can be greatly reduced by aggregating it, while keeping model outcomes valid. It will make the model run (much) faster, and prevent performance issues.

  
  
  • You may aggregate demand per 2-digit postal code if creating a supply chain model for total Europe. (Note that transport rate tables also often use some aggregated postal code zones.) Each country then means 100 customers at most. For example, customer DE-13 stands for all German customers with postal code 13XXX (ISO Alpha-2 country code for Germany is DE).
    Add 2-digit postal codes to your data table. Create a pivot table to sum the demand per 2-digit postal code. Use the geocodes of the largest customer with the applicable 2-digit postal code to locate the aggregated demand. (No need to geocode all customers!) Or simply use the geocodes from downloadable file below.
    
    
    • 2-Digit postal code geocodes file for Europe
      . Use file at your own risk.
    
    If running a Center-of-Gravity analysis for a single country, then aggregating at 3 digit postal code level may be more appropriate. This will result in a model with 1000 customers, at most.
  
  
  • You may round each customer latitude and longitude to two digits, then aggregate demand per pair of rounded latitude, longitude. By rounding, each customer will "move" 0.78 km (or 0.5 mile) away from its original position, at most. 0.78 km being the as-the-crow-flies distance between point (0.00, 0.00) and (0.005, 0.005). The further away from the Equator, the less it becomes. At latitude 44 it is 0.68 km, at most. And on average it will be 0.37 km only. It will be unnoticeable on the map. Customer "moves" will counterbalance each other to a large extent, so Center-of-Gravity locations will be hardly affected at all (like shifting <<1 km).
    Even rounding geocodes to single digit might be acceptable using geocodes of the largest customer found within rounded-geocodes-category to locate demand, instead of rounded geocodes. (Or you may round per 0.05 or 0.025, instead of per 0.1.)
  
  
  • Or you may split your data into 20% top customers - 80% tail customers (Pareto), and aggregate tail customers only. Or you could use higher levels of aggregation for (the hinterland of) areas that have fixed warehouses. Or, for example, you could remove UK customers from your European dataset, if you know upfront that the UK will have its own warehouse(s), and run your Center-of-Gravity analysis for both UK and mainland Europe separately. Or you could invent some other aggregation method.
    In the rare event that aggregation fails to bring a workable amount of customers, and your case is about many warehouses, then split your data per region and run each region separately. Obviously, this is non-optimal.
• Demand is the total yearly future demand of a customer, ideally expressed in your transport cost driver: volume or (volumetric) weight.
  • Though not obligatory, it is advised to enter demand figures as whole numbers.
  • Collect a full year of demand data if your business is highly seasonal. Otherwise, one month × 12, or one quarter × 4, may do.
  • Demand can be based on sales order quantities × item dimensions. But item masters are often not 100% clean. This may cause large errors. Correct outliers (items that are extremely light/heavy, small/large, with unrealistic kilogram/m³).
    If available, then use - or check against - shipment data with actual volumes or (pay)weights. Check for extremely small shipments or shipments that exceed truck capacity.
  • If a sales budget is the only info available, then demographics may help to create a geographical demand proxy.
  • Design for the future. Include demand growth percentages.
  About including demand growth percentages

  When to include growth percentages

  Include growth percentages if growth differs per product group / sales channel / region / customer. If not different, then you may as well ignore growth, as it will have zero effect on optimal warehouse locations. If different, then you may need to split your demand data per product group / sales channel / region if that is needed to calculate future demand volumes.
  
  Note that growth percentages provided separately "per product group" and "per sales channel" and "per region" (by the Sales department) most likely will cause future demand inconsistencies that may or may not need to be resolved.
  
  

  Detecting internal inconsistencies

  In the theoretical example below, growth percentages have been provided separately "per product group" and "per region". Calculated total future volume based on region growth (2114) differs from the one based on product group growth (2111). This indicates that growth percentages are internally inconsistent. As the difference between the two volume outcomes is small, the issue seems small...
  
  ...but it becomes more clear when viewing aggregated volumes per region, and per product group, and their corresponding output growth percentages. Either the 2 region output growth percentages do not match region input growth percentages, or the 2 product group output growth percentages do not match product group input growth percentages. Only 2 out of 4 output growth percentages match input growth percentages.
  
  

  Checking if internal inconsistencies matter or not

  A simple way to check if the above really matters is to run Center-of-Gravity Calculator both for future volumes based on region growth, and for future volumes based on product group growth, and see if warehouse locations become different (a kind of sensitivity analysis).
  
  

  Resolving internal inconsistencies

  One possible approach is the following. First, introduce growth percentages per each region-product group. Then, let Excel Solver set their values, while minimizing the sum of squared differences between input and output growth percentages (both region and product group) weighted by the customer volume (linked to that region respectively product group). Below the result of that exercise. All 4 output growth percentages now closely match input growth percentages. The mix of customer volumes makes it impossible to create 4 perfect matches.
  
  
  
  
  
• Always validate your data! Check top-X customers list. Are major customers missing? Or do unknown customers appear? Check the demand map. Are gaps seen? Or do large unknown locations appear?
• You may want to aggregate demand when creating a demand map. Otherwise, customers with (almost) the same latitude/longitude are plotted on top of each other and only a relatively small dot will be seen. Default customer opacity is 0.9 (see map settings under the map), to make customers sitting behind each other still visible. For a similar kind of reason, it can be handy to sort your customer data from large to small demand, by clicking on field header Demand, so small customers are plotted on top/in front of large ones (not behind).

Optional fields

• Warehouse_IDs is used to assign a customer to a predefined warehouse, so it should match to a Warehouse_ID as defined in table Predefined warehouses. If left empty, then no fixed assignment applies.
  
  • Create a fixed assignment by entering a single warehouse_ID.
  • Create a semi-fixed assignment by entering a subset of warehouses_IDs that the solver is allowed to select from.
    Use | (pipeline) as separator between the warehouse_IDs. For example, Birmingham|Madrid|WH05.
  Check constraint "Fixed customer-warehouse assignments"
  to activate, via section
  "Parameters & constraints"
  - subsection
  "Constraints"
• Group - free format - is used to force all customers within the same group to be delivered by the same single warehouse only (single-sourcing). What group stands for, is for you to decide. For example, a country. The group is assigned to the warehouse that comes with the lowest sum of weighted distances for this group of customers. Underlying cost calculation is still done at individual customer level. For more info, read section
  "About Center-of-Gravity algorithms"
  - subsection
  "Customer grouping"
  .
  A fixed customer-warehouse assignment takes precedence over a customer group. Warehouse capacity limits may cause a customer group to get broken up.
  Check constraint "Customer groups"
  to activate, via section
  "Parameters & constraints"
  - subsection
  "Constraints"
  .
• Shipment_size translates into an individual customer transport rate. If specified, then it overrules the general customer shipment size. See section
  "Parameters & Constraints"
  - subsection
  "About transport rates & underlying cost curve"
  for more explanation.
• Lead_time_distance is the individual maximum allowed distance from the warehouse. If specified, then it overrules the general lead time distance as set under section
  "Parameters & constraints"
  - subsection
  "Service level"
  . Customers are colored in black if the leadtime distance is exceeded, indicating where service is compromised. Enter the distance in the correct distance unit, miles or kilometers, as you have selected under section
  "Parameters & constraints"
  - subsection
  "Distance"
  .
• Border_group links the customer to a border group. A border group has specific border crossing points to enter or exit the bordered area. Those border crossing points are defined in table Border Crossing Points. A flow from a warehouse to a customer will go via one of the border crossing points, unless the warehouse and customer are both inside the bordered area.
  Check constraint "Border crossing points"
  to activate, via section
  "Parameters & constraints"
  - subsection
  "Constraints"
  .

Optional fields for "Select best warehouses" functionality only

• Qty_1 to 5 are the warehouse cost driver quantities of a customer, such as #orders, #order lines, #unit_picks, #carton_picks, #pallet_picks. You may decide yourself what each qty stands for.

Predefined warehouses (optional)

• Check constraint "Predefined warehouses"
  to activate and include predefined warehouses in the solution, via section
  "Parameters & constraints"
  - subsection
  "Constraints"
  .


• Warehouse_ID - free format, for example "Birmingham" - is used in the Customers and Suppliers table to assign customers or suppliers to this warehouse (instead of having the software assign customers and suppliers).
• Latitude, Longitude is the result of converting a textual address into geographic coordinates - a process called geocoding. For more info, read the help text for fields Latitude, Longitude of Customers.

Optional fields

• Move_limit = 0 (default) means a fixed location, > 0 means a location that may move away from its initial coordinates but no further than its move limit. To be expressed in either miles or kilometers, as you have selected under section
  "Parameters & constraints"
  - subsection
  "Distance"
  .
• Capacity is used to limit the demand that can be assigned to a warehouse. It is total throughput capacity (in relation to demand data period), not storage capacity. Yearly througput capacity = storage capacity × yearly stock turns. If your demand data is about one quarter, then divide yearly throughput capacity by four, or else multiply all demand (and supply) by four.
  Warehouse capacity limits may cause customer groups to 'break'.
  Check constraint "Capacity limits"
  to activate, via section
  "Parameters & constraints"
  - subsection
  "Constraints"
  .
• Transport_rate_index is used to adjust customer transport rates of all customers supplied from this warehouse. An index below 100 means transport from this warehouse to customers is relatively cheaper, an index larger than 100 means more expensive. Customer transport rates are multiplied by the warehouse Transport rate index/100, for all customers assigned to the warehouse. The lower this index, the more customers a warehouse will get assigned (unless other constraints prevent this).
• IsSatellite = 0 (no) or 1 (yes) and is used to distinguish main warehouses from satellite warehouses. This is relevant if suppliers are activated and parameter "Link supplier to closest Main warehouse" is checked. Even if suppliers have not been activated, main→satellite warehouse flows are generated.
• Color overwrites the default warehouse color. Format: #rrggbb (red,green,blue hexidecimal values)
  Pick color →   Color =

Optional fields for "Select best warehouses" functionality only

• Whs_group (opt)
  • Valid entries are 'a', 'b', 'c', 'd', 'e', 'f', 'g', 'h', 'i', or 'j'. If left empty, default 'a' is used.
  • Group 'j' is a special 'must-be-included' group. All warehouses in group 'j' will always be included in the selection (which of course may affect the selection of warehouses from other groups).
  • Group can be interpreted as geographical region. By grouping warehouses per region you can ensure that each region gets a warehouse, if you set the number to be selected from that group higher than 0.
• Fixed_costs are in fact binary-fixed costs, the fixed costs apply if a warehouse is selected.
• Rate_1 to 5 are the warehousing cost per unit throughput for up to 5 cost drivers (and linked to your customer table). Example: if customer X has Qty_1 = 10 and Qty_2 = 5, and warehouse Y has Rate_1 = 2.50, and Rate_2 = 3.75, then the variable costs of this customer X at warehouse Y are 10×2.50 + 5×3.75.

Suppliers (optional)

• Check constraint "Suppliers"
  to activate and include suppliers, via section
  "Parameters & constraints"
  - subsection
  "Constraints"
  .


• Supplier_ID: free format identifier.
• Latitude, Longitude is the result of converting a textual address into geographic coordinates - a process called geocoding. For more info, read the help text for fields Latitude, Longitude of Customers.
• Supply is the total yearly future supply of a supplier. Usually, the supply of all suppliers should - more or less - match the demand of all customers. Unless supply is about the dry ingredients of liquid finished products, for example.

Optional fields

• Warehouse_IDs (= link to table Predefined warehouses) is used to assign a supplier to a warehouse or subset of warehouses (similar to assigning a customer to a warehouse or subset of warehouses). If left empty, the software decides which Center(s)-of-Gravity is/are supplied by the supplier. If specified, then this supplier will not supply all warehouses directly, even if supply option "Each supplier delivers to each warehouse directly" has been selected.
  Check constraint "Fixed customer-warehouse assignments"
  to activate, via section
  "Parameters & constraints"
  - subsection
  "Constraints"
  .
• Shipment_size translates into an individual supplier transport rate.
• Supply_option (1, 3, or 4) can be set to overrule the general supply option (1, 3, or 4) as selected under section
  "Parameters & constraints"
  - subsection
  "Supply options"
  . If you select overall option 2 "LP optimized supply flows", then individual supplier settings are ignored.

Border crossing points (optional)

• Check constraint "Border crossing points"
  to activate, via section
  "Parameters & constraints"
  - subsection
  "Constraints"
  .


• The use of border crossing points may improve distance accuracy and direction in which a Center-of-Gravity is pulled.
• If an area can only be entered/exited at some specific points, you may enter these border crossing points.
• A transport flow has to go via a border crossing point if
  • a warehouse is outside the bordered area and the customer inside ("entry")
  • a warehouse is inside the bordered area and the customer outside ("exit")
• A flow via a border crossing point means a detour. The border crossing point causing the smallest detour is auto-detected. It also means that the warehouse is pulled towards this point instead of the customer, until the border has been crossed.
• Border_group - free format - is the name of the bordered area with all its crossing points (latitude, longitude) entered in this table. Field border_group is also found in the customers table and used to specify which customers belong to the border group (thus defining its geographic shape). To function properly at least some customers of a bordered area should be located within a 50 km range of the border point.
• In the picture on the right the border crossing points in the northern part of Italy prevent extreme shortcuts across the Adriatic Sea. If ferries are a valid transport option, then you may remove all border crossing points or add ferry harbours as points.
• Current implementation handles only one border crossing point per flow, not multiple. A warehouse area exit point takes priority over a customer area entry point: "exit before entry".