3. Populate the Excel Template

The GAINS simulation excel template contains multiple tables—one sheet for each table type. Each table corresponds to a specific aspect of your supply chain model. The worksheets that have a Green colored tab are required tables. And any columns with headers that are colored Green are required fields.

Lists all customers that will be in your model.

Required Fields

BaseScenario – Name of the BaseScenario. Consider beginning with "Baseline"

Customer – Unique customer ID or name

CustomerProfileName – This is a foreign key and the CustomerProfile must exist in the CustomerProfiles table.

Optional Fields

CustomerDescription – Name or description of this customer

Address, City, State, Country, PostalCode - not used by the solver

Latitude, Longitude (optional but required for mapping features to work)

Prefix - text often used in reporting for sorting and filtering

CustomerRollup - used to group customers into categories for the purpose of reporting

Lists all distribution centers, warehouses, or facilities that store and ship inventory.

Required Fields

BaseScenario – Name of the BaseScenario. Consider beginning with "Baseline"

Site – Unique site ID or name

Optional Fields

SiteDescription – Name or description of this site

Address, City, State, Country, PostalCode - not used by the solver

Latitude, Longitude (optional but required for mapping features to work)

Prefix - text often used in reporting for sorting and filtering

StorageCapacity - this is used if you'd like to constrain the storage capacity of this site. At any moment in time during the simulation run, this site cannot exceed this capacity and will stop receiving product if this occurs.

YardDetentionCost - this is a cost associated with any product that waits in the Yard. Product will be forced to wait in the Yard if the storage capacity and/or the unloading capacity has reached it's limit.

SiteRollup - used to group customers into categories for the purpose of reporting

SiteProfileName – This is a foreign key and the SiteProfile must exist in the SiteProfiles table. This must be used if this Site is replenished from another site or a supplier that does not have unlimited inventory and therefore policies around inbound orders must be specified in the event that inventory isn't available for replenishment.

Product master data. Every SKU ordered by customers must appear here.

Required Fields

BaseScenario – Name of the BaseScenario. Consider beginning with "Baseline"

SKU – Unique product ID

WeightPerUnit – Weight in the unit specified in SimOptions (typically lbs or kg)

CubePerUnit – Volume per unit (typically cubic feet)

UnitsPerCase – How many units in one case

Optional Fields

ProductDescription – Name or description of this SKU

Department - Name or description of a department. Can be used for reporting

Class - An optional way to categorize products. Not used by the solver, but can be used for reporting

Revenue Per Unit - Revenue for each unit delivered to a customer.

Other Per Unit - For reporting, another conversion for this SKU besides Volume & Weight

What to do if weight and cube are missing:

◦ Check product master systems or packaging specs

◦ Ask warehouse or packaging teams

◦ If completely unavailable, use industry averages, but note that transportation cost calculations will be less accurate

Every supplier that replenishes your sites must be listed.

Required Fields

BaseScenario – Name of the BaseScenario. Consider beginning with "Baseline"

Supplier – Unique supplier ID

Optional Fields

Address, City, State, Country, PostalCode – Supplier location

Latitude, Longitude – Supplier coordinates

Prefix - text often used in reporting for sorting and filtering

This is your historical order data for the baseline period. Every order that occurred during your model timeframe goes here.

Required Fields

OrderID – Unique order number

Customer – Must match a customer in the Customers table

OrderDate – When the order was placed

DueDate (optional but highly recommended) – When the customer needed delivery

Getting order data: Extract from order management or ERP systems for your selected time period. You may need to filter large datasets to match your model scope (only customers and SKUs in your model).

Detail lines for each order showing what SKUs and quantities were ordered.

Required Fields

OrderID – Must match orders in CustomerOrders table

OrderLineID – Unique ID for each line (e.g., ORDER001-01, ORDER001-02)

SKU – Product ordered

Quantity – How much was ordered

QuantityUOM – Unit of measurement (cases, units, etc.)

Defines how customers accept orders. Profiles control whether orders can be split, partially filled, or backordered.

Required Fields

CustomerProfileName – Name matching references in Customers table

BackordersAllowed – True/False. Can unfulfilled orders wait for future stock?

OrderSplit – True/False. Can a single order ship from multiple sites?

LineSplit - True/False. Can a single line on an order ship from multiple sites?

PartialOrder – True/False. Will customers accept partial orders (fewer total items)?

PartialLine – True/False. Will customers accept partial line items (fewer of a specific SKU)?

For a baseline model, ask your sales or customer service teams what policies you currently allow. Most companies allow partial orders and line splits but vary on backordering. If you only have one profile type, create a single "Standard" profile and assign all customers to it.

Defines where each site sources inventory. Maps site/SKU combinations to suppliers and specifies lead times.

Required Fields

Site – Distribution center

Source – Supplier (or Site) providing inventory

PlannedLeadTime – Days it takes for the supplier to fill the order with inventory before it ships. This time will be added to any Transit Time found in the IntersiteLanes table.

SourcingParameter – Ranking/priority. Lower number = higher priority

Practical approach: For a baseline, map each site to the supplier that actually serves it. If lead times vary by SKU, ask procurement. If you don't have exact times, analyze historical shipments or use supplier contract terms.

Optional Fields

SKU – If you want SKU-specific sourcing. Leave blank to apply rule to all SKUs at the site

MinimumOrderQuantity - A minimum amount to be ordered each time an order is placed

BuyingMultipleQuantity - The increment/multiple/batch size that products must be ordered in

Defines inventory policies (Min/Max parameters) for each site/SKU combination. Also specifies initial inventory and costs.

Required Fields

• Site– Distribution center

• SKU – Product

• InventoryPolicy – "s,S" (Min/Max) is the current option

• InventoryPolicyParameter1 – Reorder Point (s). When inventory hits this level, replenish

• InventoryPolicyParameter2– Reorder Quantity (S). Order this amount

• SourcingLogic – The logic used to select between sources that are used to replenish inventory to this Site. The solver currently supports, "ByRanking". The ranking of each source is found in the SiteSourcing table.

Optional Fields

• StartingInventory – Initial stock at the simulation start date

• UnitCost – Cost of one unit of this SKUL

• CarryingCost_Site – Cost per Unit per day when inventory is held at a site

• CarryingCost_InTransit– Cost per Unit per day when inventory is in transit on its way to a site.

• OrderCost – Cost per order when this SKUL is ordered for replenishment

• OutboundTargetFillRate – Can optionally be used with custom buying logic

• AverageMonthlyDemand – Can optionally be used with custom buying logic

Defines where orders come from. Specifies the ranking of sites for fulfilling each customer's orders.

Required Fields

• Customer – Customer being served

• SKU – If you want SKU-specific sourcing. Leave blank for default

• Source – Site that fulfills this customer

• SourcingLogic – The logic used to select between sources that are used to replenish inventory to this Customer. The solver currently supports, "ByRanking". The ranking of each source is found in the SourcingParameter field.

• SourcingParameter – Ranking. 1 = preferred, 2 = fallback, etc.

Optional Fields

• RevenuePerUnit – Revenue earned per unit delivered of this SKU to this Customer. This value will be used instead of any RevenuePerUnit that might be defined in the SKUs table for this SKU.

Global settings that apply to the entire simulation. These settings can be changed in the Architect interface for each scenario. See the Solver Settings help page for additional information.

Required Fields

• StartDate – First day of simulation

• StopDate – Last day of simulation

• InventorySnapshotInterval – The time between when a snapshot of the current inventory position, of each SKUL, is reported out. (default is 1 day)

• DefaultWeightUOM – Unit of weight (LB or KG)

• DefaultVolumeUOM – Unit of volume (typically FT3)

• DefaultDistanceUOM – Distance unit (MI or KM)

• DefaultTimeUOM – Time unit (typically DAY)

Optional Fields

• WarmUpLength – the amount of time at the beginning of the simulation that is not counted in the results, used to eliminate start-up bias and reflect normal operating conditions. NOTE: When using a warm-up period, the simulation solver will begin processing events at the Start Date (specified above) minus the Warmup Length.