Excel Network SourcingNetwork Sourcing
Sourcing models are quite different to routing models in that sourcing models are strategic, while routing models fall into the tactical/operational modelling tier. This means that sourcing models attempt to answer questions that are posed at a higher level of abstraction than routing models. Modelling individual sequences of activities (like routing models) is not considered in sourcing models. Rather, the focus shifts to modelling aggregate transactions and the options around how they are performed. An example of this may be deciding how to service a customer footprint, how customers should be partitioned between two depots or, if a third depot costs x per annum, should opening such a depot be considered?
Sourcing (or network) models are rate-based in their modelling approach and provide linear sets of inequalities to model the cost of decisions or flows in the network.
Input Worksheets
To start with, create a sample set of template worksheets. You can then run the example or enter your own data. The requirements for each worksheet are listed below and described in detail in subsequent sections.
Production, Intermediate and Demand node information is captured on the following worksheets:
- Production and Production Detail: Nodes from which products are sourced or created and moved through the network to satisfy consumption at demand nodes. Typically these are factories or supply depots.
- Intermediate and Intermediate Detail: Nodes through which products pass, such as warehouses, distribution centers and cross-docking facilities.
- Demand and Demand Detail: Nodes at which product demand must be satisfied, for example retail stores and other destination endpoints.
Costs and Lane Rate information on:
- Lane Rates: Specifies how nodes are connected and the cost and capacity constraints which are associated with those movements.
- Cost Models: A way to specify connections to a number of other nodes from a single source rather than as separate lane rates for each.
And finally products can be grouped together on:
- Product Groups: Collect similar products together to simplify the specification of the model. This helps reduce the number of lane rates required in the model.
Node - Production, Intermediate and Demand
Worksheet names: NS-Production, NS-Intermediate, NS-Demand
Production, Intermediate and Demand nodes all share the following fields:
- Active: A 0/1 indicator which specifies if the node is active or not. Nodes which are not active (0) will not be included in the final model.
- ID: Any unique identifier for the node. For example “Factory A”, “Warehouse 12”, “Location314”.
- Longitude(x): Longitude of the node in decimal degrees.
- Longitude(y): Latitude of the node in decimal degrees.
- Range Min kg: A soft range constraint indicating the minimum kg allowed at the node, default is 0.
- Range Max kg: A soft range constraint on the maximum allowed.
- Range Penalty kg cost: A penalty cost per unit that is allocated below the minimum or above the maximum range constraint on the node.
- Fixed cost kg: A fixed cost incurred as soon as products move over this node irrespective of the number.
- Cost/kg: A unit cost incurred per kg of product at the node.
In addition to the above input fields, Intermediate and Demand nodes also have the following:
- Allowable Sources: A list of valid node identifiers. Specifies which nodes can serve as a source for product movement to this node. If the list is empty then all sources are permitted.
- Max Sources: The maximum number of nodes that act as sources. The default value of -1 indicates that any number of valid sources are allowed.
Node Detail - Production, Intermediate and Demand
Worksheet names: NS-Production Detail, NS-Intermediate Detail, NS-Demand Detail
Production Detail, Intermediate Detail and Demand Detail all share the following fields:
- Active: A 0/1 indicator which specifies if the row is active or not. Rows which are not active (0) will not be included in the final model.
- [Product|Intermediate|Demand] ID: A valid, active parent node for which this detail applies.
- Product ID: A product ID or name to which the constraints and costs will apply.
- Range Min kg: A soft range constraint indicating the minimum kg allowed for this product at the node, default is 0.
- Range Max kg: A soft range constraint on the maximum allowed for the product.
- Range Penalty kg cost: A penalty cost per unit that is allocated below the minimum or above the maximum range constraint for this product at this node.
- Fixed cost kg: A fixed cost incurred as soon this product is moved over this node irrespective of the number.
- Cost/kg: A unit cost incurred per kg of this product at the node.
Lane Rates
Worksheet name: NS-Lane Rates
- Active: A 0/1 indicator which specifies if the lane is active or not. Lanes which are not active (0) will not be included in the final model.
- ID: A unique Lane Rate ID, example “Lane-Factory A -> Warehouse 123”.
- Source: A valid, active source node ID indicating the lane starting node.
- Destination: A valid, active node ID indicating the lane end node.
- Products: A list of valid, active productIDs which can use this lane. When empty it is assumed all products are permissible.
- Product Groups: A list of valid, active product group IDs which can use this lane. When empty it is assumed all product groups are permissible.
- Cost/km: A non-negative distance cost based on the road network distance between the source and destination nodes.
- Cost/kg: A non-negative weight cost.
- Cost/kg/km: A non-negative weight/distance cost.
- Cost/TravelHour: A non-negative cost per unit time to traverse the start end nodes over the road network.
- Travel Factor: An additional factor that can be applied to the travel time between two nodes.
- Range Min kg: The minimum flow to use a lane (default is 0). Typically used to force the use of a lane.
- Range Max kg: The maximum flow that may be moved over the lane, example a total haulier weight limit.
Cost Models
Worksheet name: NS-Cost Model
Cost models are almost identical to lanes apart from the following omissions:
- Destination: No destination is required as a cost model encapsulates connections from the source node to all valid, active demand nodes.
- Product IDs: No individual Product IDs are permitted, only Product Group IDs.
Product Groups
Worksheet name: NS-Product Groups
Each product must belong to a single product group.
- Product ID: A Product ID that is used in the active model.
- Product Group ID: The Product Group to which the product belongs. Note that a product may only belong to a single Product Group.
Output Worksheets
The following information is returned if the model can be solved and no data errors are detected.
Node Assignments
Worksheet name: NS-Assignments
This worksheet holds information regarding the product movements between nodes. Details of each field can be found here.
Node Product Flow
Worksheet name: NS-Node Product Flow
This worksheet holds a summary of the product quantities which are sourced, moved or consumed at specific nodes. Details of each field can be found here.
Node Flow
Worksheet name: NS-Node Flow
This worksheet is similar to the Node Product Flow information except that it aggregates the values of all movements and provides a high level view of the total movement of products across the network. Details of each field can be found here.
Solve Status
Worksheet name: NS-Solve Status
The Solve Status worksheet holds general information about the model:
- Objective: The total cost of the solution returned.
- Lower Bound: A theoretical minimum cost of any solution that can be found by the solver.
- Optimality Gap: The current gap between the lower bound and the current solution. When an optimal proof is provided the gap will be 0.
- Production Nodes: The number of active production nodes in the model.
- Intermediate Nodes: The number of active intermediate nodes in the model.
- Destination Nodes: The number of destination nodes in the model.
- Product Groups: The number of product groups.
- Model Size: The model size for cost purposes.
- Logs: Status messages received from the solver.
Common Data Input Errors
- All node IDs must be unique.
- A node cannot be both a production and demand node.
- Allowable Source IDs must be active nodes in the model.