Navigation :

MOQs and other ordering constraints

Probabilistic forecasts offer the possibility to generate a purchase priority list where each incremental unit to be purchased is ranked against its business drivers, such as the expected gross margin and the expected inventory carrying cost. However MOQ (minimal order quantities) introduce non-linear constraints that complicates the calculation of the purchase order quantities. In order to cope with this requirement frequently encountered in supply chains, Lokad has designed a numerical solver precisely intended for MOQs. The solver also addresses the situation where MOQs are combined with a container constraint.

Table of contents

The solve.moq call function

The MOQ solver is a specialized numerical solver that can be accessed within Envision as a call function. The mathematical reference framework is the general MOQ problem, which represents an integer programming problem. The syntax for this function is illustrated below.

G.Buy = solve.moq(
  Item: Id
  Quantity: G.Min
  Reward: G.Reward
  Cost: G.Cost
  // Provide one of these three:
  MaxCost: maxBudget
  MaxTarget: maxTarget
  MinTarget: minTarget
  // Optional:
  Target: G.Target
  TargetGroup: Supplier
  // Optional, but must have
  // the same number for each, max 8
  GroupId:          Items.A, Items.B
  GroupQuantity:    G.A, G.B
  GroupMinQuantity: Items.AMoq, Items.BMoq)

The parameters are as follows:

The last three parameters, namely GroupId, GroupQuantity and GroupMinQuantity, can have multiple arguments, one for each distinct MOQ constraint. However, the same number of arguments should be provided for each parameter. Up to eight arguments can be passed to the MOQ solver, representing as many distinct MOQ constraints. It is important to keep in mind that several types of minimum order can be considered, e.g., Euros, units, volume; GroupQuantity and GroupMinQuantity can be of any nature, e.g., Euros, units, volume; The only constraint is that within one group, the GroupQuantity and GroupMinQuantity must be of the same nature. In the provided example, group A GroupQuantity and GroupMinQuantity can be expressed in Euros whereas group B GroupQuantity and GroupMinQuantity can be expressed in units.

The MOQ solver can use two various approaches, namely the greedy and non-greedy algorithms can be applied. The computation of the solution in case of non-greedy MOQ solver is more complex and less intuitive than the greedy approach. The main idea is that a first naïve optimal solution that does not consider the MOQ constraints is built. From this solution all the unsatisfied moq are listed and ranked depending on how likely they are to be cost effective if they are satisfied. The less promising ones are abandoned (meaning that their items will not be ordered) to free more budget to satisfy the most promising ones. Once a solution satisfies all the constraints, we still try to abandon some MOQ constraint to satisfy others, until all the possible permutations have been explored or the time allocated to the computation is consumed, and we then select the best solution found so far. Since the search is capped time-wise, the solution may depend on the machine it is computed on.

The greedy approach is used when the constraint set is hierarchical (meaning that if an item is included in two MOQs, one of the MOQ has an item set strictly included in the item set of the other). If a hierarchical representation cannot be built, the non-greedy version of the MOQ solver is called. In the present section, we will detail the more intuitive greedy MOQ solver approach. For further explanation, refer to the greedy algorithm description. In case of the greedy MOQ solver, every constraint has a minimal quantity to be purchased and a set of items (that needs to be bought in large enough quantities, or not bought at all). Every item has a purchase list, which is a list of incremental possible purchase. Every incremental possible purchase is called a purchase line and has a cost, a reward, and contributions to the constraints. We will guide you next through all the steps we follow.

Step 1: First, the solver builds a hierarchical representation of the MOQ constraints (a constraint “tree”). The hypothesis is that if an item is included in two MOQs, one of the MOQ has an item set strictly included in the item set of the other. If it is the case, a hierarchical representation can be built.

Step 2: We consider the “smallest MOQs”, i.e., the bottom MOQs of the hierarchical tree which have no other MOQ included in their item set. We generate a ranked purchase decisions list from the individual item purchase lists, using the following logic:

Step 3: We run down the ranked purchase decisions list until the min quantity is being reached. Next, we fuse the lines that were run together into a single, aggregated one. The cost, the reward and the target contribution of this aggregated line are the sum of the costs, rewards and target contribution. We end up with one fused and ranked purchase list per “bottom” MOQ. Every one of these lists can now be considered as if it was the purchase list of a made-up item, with its cost, target contributions and reward being the aggregation of the items costs, target contributions and rewards.

Step 4: We update the MOQ constraint tree by replacing every set of items of the bottom MOQs by a fake aggregated item that uses the corresponding fused ranked purchase list. Now this new MOQ constraint tree has new bottom MOQs and we can go back to step 2. If there is no MOQ left, we go to step 5.

Step 5: We generate a ranked decisions list based on the purchase lists, using the step 2 logic. We end up with a single ranked purchase list that always satisfies the varying MOQs when being run through. We run it through until we reach the max cost, the max target, or the min target. We exceed this limit and check whether the last selected line is an aggregated one, i.e., the line that is built by fusing several lines. If it is not an aggregated line, we stop. If the limit is a min target, we include the last line in the output, otherwise the last line is not included. If the last reached line is an aggregated one, we store the solution that was found and compute its score. We then remove the last reached line from the ranked decisions list and remove all the subsequent lines that are dependent on it. We run through the list until we reach the limit. We compute the score of this new solution. Again, if the last line is an aggregated line, we remove the last reached line from the ranked decisions list and remove all the subsequent lines that are dependent on it. We re-apply the same logic until a solution that does not end with an aggregated line is found or until the end of the purchase list is found. Finally, we select the solution with the best score.

The score is being computed based on the cost, reward and the target contribution associated with the line. If maxCost is specified, we replace targetContribution with the cost. The rules are as follows:

We consider that the contribution of a line to the target cannot be smaller than 1e-12, since we cannot divide by 0. If the reward is negative, we compute the score differently: we try to reach the target while limiting as much as we can the negative reward associated to fulfilling the target constraint. If the reward is positive, the objective is to rank the decisions by their return on investment (ROI) and stop when the target or cost constraint is reached.

Minimal Order Quantities (MOQ) per SKU

Suppliers frequently impose minimal order quantities (MOQs) on their customers. Such MOQ constraints can be applied at various levels: per SKU, per category, per order, etc. Let’s assume that we are facing MOQ constraints at the SKU level: for every SKU there is a minimal quantity to be ordered, and beyond this threshold, it is up to the person ordering the goods to decide if any additional units of that specific SKU need to be ordered or not. In the script below, we assume that the Items table contains a MOQ column. If there is no MOQ constraint applicable, then this field is expected to be equal to 1.

read "/sample/Lokad_Items.tsv" as Items[Id] with
  Id : text
  Name : text
  Category : text
  SubCategory : text
  Brand : text
  SellPrice : number
  BuyPrice : number
  StockOnHand : number
  StockOnOrder : number
  Backorder : number
read "/sample/Lokad_Orders.tsv" as Orders expect [Id, Date] with
  Id : text
  Date : date
  Quantity : number
read "/sample/Lokad_PurchaseOrders.tsv" as PO expect [Id, Date] with
  Id : text
  Date : date
  DeliveryDate : date

// Filtering on closed POs
where PO.DeliveryDate > PO.Date
  Items.Horizon = forecast.leadtime(
  	hierarchy: Items.Category, Items.SubCategory
  	present: (max(Orders.Date) by 1) + 1
  	leadtimeDate: PO.Date
  	leadtimeValue: PO.DeliveryDate - PO.Date + 1)

Items.Demand = forecast.demand(
  horizon: Items.Horizon
  hierarchy: Items.Category, Items.SubCategory
  present: (max(Orders.Date) by 1) + 1
  demandDate: Orders.Date
  demandValue: Orders.Quantity)

budget = 1000
Items.Leadtime = dirac(15)
Items.MOQ = 5

// % relative to selling price
oosPenalty = 0.25
// % annual carrying cost relative to purchase price
carryingCost = 0.3
// % annual economic discount
discount = 0.20

Items.M = Items.SellPrice - Items.BuyPrice
Items.S = - 0.25 * Items.SellPrice // stock-out penalty
// % '0.3' as annual carrying cost
Items.C = - 0.3 * Items.BuyPrice * mean(Items.Leadtime) / 365
// back-order case
Items.MB = 0.5 * Items.SellPrice
Items.MBU = Items.MB * uniform(1, Items.Backorder)
// back-order case
Items.SB = 0.5 * Items.SellPrice
Items.SBU = Items.SB * uniform(1, Items.Backorder)
// opportunity to buy later
Items.AM = 0.3
// % '0.2' as annual economic discount
Items.AC = 1 - 0.2 * mean(Items.Leadtime) / 365

Items.RM = Items.MBU + shift((stockrwd.m(Items.Demand, Items.AM) * Items.M), Items.Backorder)
Items.RS = Items.SBU + shift(zoz(stockrwd.s(Items.Demand) * Items.S), Items.Backorder)
Items.RC = shift((stockrwd.c(Items.Demand, Items.AC) * Items.C), Items.BackOrder)
Items.R = Items.RM + Items.RS + Items.RC // plain recomposition

Items.Stock = Items.StockOnHand + Items.StockOnOrder

Items.DBO = Items.Demand + Items.BackOrder
table G = extend.ranvar(Items.DBO, Items.Stock)
G.Q = G.Max - G.Min + 1
G.Reward = int(Items.R, G.Min, G.Max)
G.Cost = Items.BuyPrice * G.Q

where G.Max >= Items.Stock
  G.Eligible = solve.moq(
		Item: Id
		Quantity: G.Min
		Reward: G.Reward
		Cost: G.Cost
		MaxCost: budget
		GroupId: Id
		GroupQuantity: G.Q
		GroupMinQuantity: Items.MOQ)

  where G.Eligible and sum(if G.Eligible then 1 else 0) by Id > 0
    show table "Purchase priority list (budget: $\{budget})" a1f4 tomato with
      Id
      Items.MOQ
      sum(G.Q) as "Quantity"
      sum(G.Reward) as "Reward" { unit: "$" }
      sum(Items.BuyPrice * G.Q) as "Purchase Cost" { unit: "$" }
      group by Id
      order by [sum(G.Reward) / sum(G.Cost)] desc

This script produces a dashboard where the MOQ constraints are satisfied for all the lines in the list. In order to satisfy those constraints, we use the moqsolv special function of Envision. In the present case, we only have 1 type of MOQ constraint, but the function moqsolv also works with multiple MOQ constraints. The function moqsolv returns true for the lines of the grid that are elected to be part of the final result. Under the hood, moqsolv is using an advance non-linear optimizer that has been specifically tailored for the MOQ problem.

Minimal Order Quantities (MOQ) per groups of SKUs

We have seen in the previous section how to handle MOQ constraints at the SKU level. Now, let’s review how such a constraint can be handled at a higher level of aggregation. Let’s assume that the MOQ threshold is made available as part of the Items table. Since the MOQ constraint applies at a certain grouping level, we assume, for the sake of consistency, that all items that belong to the same MOQ group have the same MOQ value. The script below illustrates how this multi-SKU constraint can be handled.

read "/sample/Lokad_Items.tsv" as Items[Id] with
  Id : text
  Name : text
  Category : text
  SubCategory : text
  Brand : text
  SellPrice : number
  BuyPrice : number
  StockOnHand : number
  StockOnOrder : number
  Backorder : number
read "/sample/Lokad_Orders.tsv" as Orders expect [Id, Date] with
  Id : text
  Date : date
  Quantity : number
read "/sample/Lokad_PurchaseOrders.tsv" as PO expect [Id, Date] with
  Id : text
  Date : date
  DeliveryDate : date

// snipped ..

   G.Eligible = solve.moq(
		Item: Id
		Quantity: G.Min
		Reward: G.Reward
		Cost: G.Cost
		MaxCost: budget
		GroupId: Items.SubCategory
		GroupQuantity: G.Q
		// MOQ per subcategory
		GroupMinQuantity: Items.MOQ)

  // snipped ...

The script above is actually near identical to the script of the previous section. For the sake of clarity, we are only displaying the call to moqsolv, as it’s the only line that changes, as it takes an alternative MOQ constraint as argument.

Lot multipliers per SKU

Sometimes, SKUs can only be ordered by certain quantities, and unlike the minimal order quantity (MOQ) constraint detailed above, the ordered quantity needs to be a multiple of a “base” quantity. For example, a product can only be ordered by crates of 12 units. It’s not possible to order 13 units, it’s either 12 units or 24 units. We refer to the quantity to be multiplied as the lot multiplier. It is possible to adjust the prioritization logic to fit this constraint as well.

read form with
  budget : number
read "/sample/Lokad_Items.tsv" as Items[id] max 1m with
  Id : text
  Name : text
  Category : text
  SubCategory : text
  Brand : text
  SellPrice : number
  BuyPrice : number
  StockOnHand : number
  StockOnOrder : number
  Backorder : number
read "/sample/Lokad_Orders.tsv" as Orders max 1m expect [id, Date] with
  Id : text
  Date : date
  Quantity : number
read "/sample/Lokad_PurchaseOrders.tsv" as PO max 1m expect [id, Date] with
  Id : text
  Date : date
  DeliveryDate : date

Items.Leadtime = dirac(15)
Items.LotMultiplier = 5

// Filtering on closed POs
where PO.DeliveryDate > PO.Date
  Items.Horizon = forecast.leadtime(
    hierarchy: Items.Category, Items.SubCategory
    present: (max(Orders.Date) by 1) + 1
    leadtimeDate: PO.Date
    leadtimeValue: PO.DeliveryDate - PO.Date + 1)

Items.Demand = forecast.demand(
  horizon: Items.Horizon
  hierarchy: Items.Category, Items.SubCategory
  present: (max(Orders.Date) by 1) + 1
  demandDate: Orders.Date
  demandValue: Orders.Quantity)

show form "Purchase with lot multipliers" a1b2 tomato with
  budget as "Max budget"

// % relative to selling price
oosPenalty = 0.25
// % annual carrying cost relative to purchase price
carryingCost = 0.3
// % annual economic discount
discount = 0.20

Items.M = Items.SellPrice - Items.BuyPrice
// stock-out penalty
Items.S = - oosPenalty * Items.SellPrice
// % '0.3' as annual carrying cost
Items.C = - carryingCost * Items.BuyPrice * mean(Items.Leadtime) / 365
// back-order case
Items.MB = 0.5 * Items.SellPrice
Items.MBU = Items.MB * uniform(1, Items.Backorder)
// back-order case
Items.SB = 0.5 * Items.SellPrice
Items.SBU = Items.SB * uniform(1, Items.Backorder)
// opportunity to buy later
Items.AM = 0.3
// % '0.2' as annual economic discount
Items.AC = 1 - discount * mean(Items.Leadtime) / 365

Items.RM = Items.MBU + shift((stockrwd.m(Items.Demand, Items.AM) * Items.M), Items.Backorder)
Items.RS = Items.SBU + shift(zoz(stockrwd.s(Items.Demand) * Items.S), Items.Backorder)
Items.RC = shift((stockrwd.c(Items.Demand, Items.AC) * Items.C), Items.Backorder)
Items.R = Items.RM + Items.RS + Items.RC // plain recomposition

Items.Stock = Items.StockOnHand + Items.StockOnOrder

Items.DBO = Items.Demand + Items.Backorder

// the third argument is 'LotMultiplier'
table G = extend.ranvar(Items.DBO, Items.Stock, Items.LotMultiplier)
G.Q = G.Max - G.Min + 1
G.Reward = int(Items.R, G.Min, G.Max)
G.Cost = Items.BuyPrice * G.Q

where G.Max > Items.Stock
  // a mock MOQ constraint
  G.Eligible = solve.moq(
    Item: id
    Quantity: G.Min
    Reward: G.Reward
    Cost: G.Cost
    MaxCost: budget
    GroupId: id
    GroupQuantity: G.Q
    GroupMinQuantity: 1)

  where G.Eligible and sum(if G.Eligible then 1 else 0) by id > 0
    show table "Purchase priority list (budget: $\{budget})" a1f4 tomato with
      id as "Id"
      sum(G.Q) as "Quantity"
      sum(G.Reward) as "Reward" { unit: "$" }
      sum(Items.BuyPrice * G.Q) as "Purchase Cost" { unit: "$" }
      group by id
      order by [sum(G.Reward) / sum(G.Cost)] desc

The script leverages a special behavior of the extend.ranvar() function, which is precisely intended to capture lot multiplier constraints. The third argument of this function is the lot multiplier quantity.

Target container capacity per supplier

With overseas imports frequently comes the constraint of purchasing up to a full container, or half of a full container. The volume of the container is known, and in this example we assume that the volumes of all the items being purchased are known too. The goal is to compose a short list of items that represents the contents of the next full container to be purchased.

In order to make things a tiny bit more complex, let’s assume that there is no shipping consolidation between suppliers. Thus, in order to compose a container, all the purchase lines should be associated to the same supplier. This implies that one should first identify the most pressing supplier, and then, fill the container accordingly. Let’s suppose that the Items file contains a S (for Supplier) column indicating the primary vendor, assuming we are in a mono-sourcing scenario (i.e. each item has exactly one supplier).

read "/sample/Lokad_Items.tsv" as Items[Id] with
  Id : text
  Name : text
  Category : text
  SubCategory : text
  Brand : text
  Supplier : text
  SellPrice : number
  BuyPrice : number
  StockOnHand : number
  StockOnOrder : number
  Backorder : number
read "/sample/Lokad_Orders.tsv" as Orders expect [Id, Date] with
  Id : text
  Date : date
  Quantity : number
read "/sample/Lokad_PurchaseOrders.tsv" as PO expect [Id, Date] with
  Id : text
  Date : date
  DeliveryDate : date

oend = max(Orders.Date) + 1
Items.Volume = 10
Items.Leadtime = dirac(15)
PO.Leadtime = PO.DeliveryDate - PO.Date + 1

// Filtering on closed POs
where PO.Leadtime > 0
  Items.Horizon = forecast.leadtime(
    hierarchy: Items.Category, Items.SubCategory
    present: oend
    leadtimeDate: PO.Date
    leadtimeValue: PO.Leadtime)

Items.Demand = forecast.demand(
  horizon: Items.Horizon
  hierarchy: Items.Category, Items.SubCategory
  present: oend
  demandDate: Orders.Date
  demandValue: Orders.Quantity)

// expected volume of the container (m3)
cV = 15
// expected jumping threshold of the container
cJT = 2 * cV

// % relative to selling price
oosPenalty = 0.25
// % annual carrying cost relative to purchase price
carryingCost = 0.3
// % annual economic discount
discount = 0.20

Items.M = Items.SellPrice - Items.BuyPrice
// stock-out penalty
Items.S = - 0.25 * Items.SellPrice
// % '0.3' as annual carrying cost
Items.C = - 0.3 * Items.BuyPrice * mean(Items.Leadtime) / 365
// back-order case
Items.MB = 0.5 * Items.SellPrice
Items.MBU = Items.MB * uniform(1, Items.Backorder)
// back-order case
Items.SB = 0.5 * Items.SellPrice
Items.SBU = Items.SB * uniform(1, Items.Backorder)
// opportunity to buy later
Items.AM = 0.3
// % '0.2' as annual economic discount
Items.AC = 1 - 0.2 * mean(Items.Leadtime) / 365

Items.RM = Items.MBU + shift((stockrwd.m(Items.Demand, Items.AM) * Items.M), Items.Backorder)
Items.RS = Items.SBU + shift(zoz(stockrwd.s(Items.Demand) * Items.S), Items.Backorder)
Items.RC = shift((stockrwd.c(Items.Demand, Items.AC) * Items.C), Items.BackOrder)
Items.R = Items.RM + Items.RS + Items.RC // plain recomposition

Items.Stock = Items.StockOnHand + Items.StockOnOrder

Items.DBO = Items.Demand + Items.BackOrder
table G = extend.ranvar(Items.DBO, Items.Stock)
G.Q = G.Max - G.Min + 1
G.Rwd = int(Items.R, G.Min, G.Max) // reward
G.Score = G.Rwd / max(1, Items.BuyPrice * G.Q)
G.V = Items.Volume * G.Q

where G.Max > Items.Stock
  G.Rk = rank(G.Score, Id, -G.Max)
  // 'S' is for Supplier
  G.CId = priopack(G.V, cV, cJT, Id) by Items.S sort G.Rk

  // filling the container for the most pressing
  // supplier
  where sum(G.Q) by Id > 0
    show table "Containers \{cV}m3" a1f4 tomato with
      same(Items.Supplier) as "Supplier"
      G.CId as "Container"
      Id as "Id"
      sum(G.Q) as "Quantity"
      sum(G.Rwd) as "Reward" { unit:"$" }
      sum(Items.BuyPrice * G.Q) as "Investment" { unit:"$" }
      sum(G.V) as "Volume{ m3}"
      group by [G.CId, Id]
      order by [avg(sum(G.Rwd) by [Items.S, G.CId])] desc

This dashboard produces a single table that details the list of batches sorted by decreasing reward. The stock rewards are computed based on the stockrwd function. The batching logic - aka splitting quantities over several containers - is performed using the priopack function. This function has been specifically introduced to Envision for the purpose of dealing with the per-container constraints.

User Contributed Notes
0 notes + add a note

© Lokad 2014 - 2024