- Inventory forecasting
- Prioritized ordering report
- Old forecasting input file format
- Old forecasting output file format
- Choosing the service levels
- Managing your inventory settings
- The old Excel forecast report
- Using tags to improve accuracy
- Oddities in classic forecasts
- Oddities in quantile forecasts
- Stock-out's bias on quantile forecasts
- Daily, weekly and monthly aggregations

Home » Resources » Here

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. MOQ (minimal order quantities), however, introduces 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.

`solve.moq`

call functionG.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 // Optional, but must have the same number for each, max 8 GroupId: A, B GroupQuantity: G.A, G.B GroupMinQuantity: AMoq, BMoq)

The parameters are as follow:

`G`

: the*grid*, a table typically obtained through*extend.distrib()*.`Item`

: the identifiers of the SKUs or products relevant for the MOQ optimization.`Quantity`

: the grid quantity, used for ordering the lines of the grid.`Reward`

: the economical reward associated with purchasing the line of the grid.`Cost`

: the economical cost of purchasing the line of the grid.`MaxCost`

: threshold for one of the three optimization modes for the solver. The`MaxCost`

indicates that grid lines will be taken until the budget is exhausted, and no more line can be added without exceeding the budget.`MaxTarget`

: idem. When used, the target is reached from below; no more grid line can be added without exceeding the target.`MinTarget`

: idem. When used, the target is reached from above; no more grid line can be added without getting below the target.`Target`

: the target contribution associated to the grid line. Apply only when either`MaxTarget`

or`MinTarget`

are specified.`GroupId`

: identifies the grouping for the MOQ constraints.`GroupQuantity`

: the contribution of the grid line to the MOQ constraint.`GroupMinQuantity`

: the lower bound of the MOQ constraint.

The three parameters

`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 8 arguments can be passed to the MOQ solvers, representing as many distinct MOQ constraints.`MOQ`

column. If there is no MOQ constraint applicable, then this field is expected to be equal to 1.read "/sample/Lokad_Items.tsv" read "/sample/Lokad_Orders.tsv" as Orders read "/sample/Lokad_PurchaseOrders.tsv" as PO where PO.DeliveryDate > PO.Date //Filtering on closed POs Horizon = forecast.leadtime( hierarchy: Category, SubCategory present: (max(Orders.Date) by 1) + 1 leadtimeDate: PO.Date leadtimeValue: PO.DeliveryDate - PO.Date + 1) Demand = forecast.demand( horizon: Horizon hierarchy: Category, SubCategory present: (max(Orders.Date) by 1) + 1 demandDate: Orders.Date demandValue: Orders.Quantity) budget := 1000 MinOrderQuantity = 5 oosPenalty := 0.25 // % relative to selling price carryingCost := 0.3 // % annual carrying cost relative to purchase price discount := 0.20 // % annual economic discount M = SellPrice - BuyPrice S = - 0.25 * SellPrice // stock-out penalty C = - 0.3 * BuyPrice * mean(Leadtime) / 365 // % '0.3' as annual carrying cost MB = 0.5 * SellPrice // back-order case SB = 0.5 * SellPrice // back-order case AM = 0.3 // opportunity to buy later AC = 1 - 0.2 * mean(Leadtime) / 365 // % '0.2' as annual economic discount RM = MB * uniform(1, Backorder) + (stockrwd.m(Demand, AM) * M) >> Backorder RS = SB * uniform(1, Backorder) + zoz(stockrwd.s(Demand) * S) >> Backorder RC = (stockrwd.c(Demand, AC) * C) >> BackOrder R = RM + RS + RC // plain recomposition table G = extend.distrib(Demand >> BackOrder, StockOnHand + StockOnOrder) G.Q = G.Max - G.Min + 1 G.Reward = int(R, G.Min, G.Max) G.Cost = BuyPrice * G.Q where G.Max >= StockOnHand + StockOnOrder G.Eligible = solve.moq( Item: Id Quantity: G.Min Reward: G.Reward Cost: G.Cost MaxCost: budget GroupId: Id GroupQuantity: G.Q GroupMinQuantity: MinOrderQuantity) where G.Eligible & sum(G.Eligible ? 1 : 0) > 0 show table "Purchase priority list (budget: $\{budget})" a1f4 tomato with Id as "Id" MinOrderQuantity as "MOQ" sum(G.Q) as "Quantity" sum(G.Reward) as "Reward{$}" sum(BuyPrice * G.Q) as "Purchase Cost{$}" 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 have only 1 type of MOQ constraints, but the function `moqsolv`

also work 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.read "/sample/Lokad_Items.tsv" read "/sample/Lokad_Orders.tsv" as Orders read "/sample/Lokad_PurchaseOrders.tsv" as PO // snipped .. G.Eligible = solve.moq( Item: Id Quantity: G.Min Reward: G.Reward Cost: G.Cost MaxCost: budget GroupId: SubCategory GroupQuantity: G.Q GroupMinQuantity: MinOrderQuantity) // MOQ per subcategory // 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.read "/sample/Lokad_Items.tsv" read "/sample/Lokad_Orders.tsv" as Orders read "/sample/Lokad_PurchaseOrders.tsv" as PO LotMultiplier = 5 where PO.DeliveryDate > PO.Date //Filtering on closed POs Horizon = forecast.leadtime( hierarchy: Category, SubCategory present: (max(Orders.Date) by 1) + 1 leadtimeDate: PO.Date leadtimeValue: PO.DeliveryDate - PO.Date + 1) Demand = forecast.demand( horizon: Horizon hierarchy: Category, SubCategory present: (max(Orders.Date) by 1) + 1 demandDate: Orders.Date demandValue: Orders.Quantity) show form "Purchase with lot multipliers" a1b2 tomato with Form.budget as "Max budget" budget := Form.budget oosPenalty := 0.25 // % relative to selling price carryingCost := 0.3 // % annual carrying cost relative to purchase price discount := 0.20 // % annual economic discount M = SellPrice - BuyPrice S = - 0.25 * SellPrice // stock-out penalty C = - 0.3 * BuyPrice * mean(Leadtime) / 365 // % '0.3' as annual carrying cost MB = 0.5 * SellPrice // back-order case SB = 0.5 * SellPrice // back-order case AM = 0.3 // opportunity to buy later AC = 1 - 0.2 * mean(Leadtime) / 365 // % '0.2' as annual economic discount RM = MB * uniform(1, Backorder) + (stockrwd.m(Demand, AM) * M) >> Backorder RS = SB * uniform(1, Backorder) + zoz(stockrwd.s(Demand) * S) >> Backorder RC = (stockrwd.c(Demand AC) * C) >> BackOrder R = RM + RS + RC // plain recomposition // the third argument is 'LotMultiplier' table G = extend.distrib(Demand >> BackOrder, StockOnHand + StockOnOrder, LotMultiplier) G.Q = G.Max - G.Min + 1 G.Reward = int(R, G.Min, G.Max) G.Cost = BuyPrice * G.Q where G.Max > StockOnHand + StockOnOrder // 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 & sum(G.Eligible ? 1 : 0) > 0 show table "Purchase priority list (budget: $\{budget})" a1f4 tomato with Id as "Id" sum(G.Q) as "Quantity" sum(G.Reward) as "Reward{$}" sum(BuyPrice * G.Q) as "Purchase Cost{$}" group by Id order by sum(G.Reward) / sum(G.Cost) desc

The script leverages a special behavior of the

`extend.distrib()`

function which is precisely intended to capture lot multiplier constraints. The third argument of this function is precisely the lot multiplier quantity.In order to make things a tiny bit more complex, let's assume that there is

`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" read "/sample/Lokad_Orders.tsv" as Orders read "/sample/Lokad_PurchaseOrders.tsv" as PO where PO.DeliveryDate > PO.Date //Filtering on closed POs Horizon = forecast.leadtime( hierarchy: Category, SubCategory present: (max(Orders.Date) by 1) + 1 leadtimeDate: PO.Date leadtimeValue: PO.DeliveryDate - PO.Date + 1) Demand = forecast.demand( horizon: Horizon hierarchy: Category, SubCategory present: (max(Orders.Date) by 1) + 1 demandDate: Orders.Date demandValue: Orders.Quantity) cVolume := 15 // expected volume of the container (m3) oosPenalty := 0.25 // % relative to selling price carryingCost := 0.3 // % annual carrying cost relative to purchase price discount := 0.20 // % annual economic discount M = SellPrice - BuyPrice S = - 0.25 * SellPrice // stock-out penalty C = - 0.3 * BuyPrice * mean(Leadtime) / 365 // % '0.3' as annual carrying cost MB = 0.5 * SellPrice // back-order case SB = 0.5 * SellPrice // back-order case AM = 0.3 // opportunity to buy later AC = 1 - 0.2 * mean(Leadtime) / 365 // % '0.2' as annual economic discount RM = MB * uniform(1, Backorder) + (stockrwd.m(Demand, AM) * M) >> Backorder RS = SB * uniform(1, Backorder) + zoz(stockrwd.s(Demand) * S) >> Backorder RC = (stockrwd.c(Demand AC) * C) >> BackOrder R = RM + RS + RC // plain recomposition table G = extend.distrib(Demand >> BackOrder, StockOnHand + StockOnOrder) G.Q = G.Max - G.Min + 1 G.Reward = int(R, G.Min, G.Max) G.Score = G.Reward / max(1, BuyPrice * G.Q) G.Volume = Volume * G.Q where G.Max > StockOnHand + StockOnOrder G.Rank = rank(G.Score, Id, -G.Max) G.CId = priopack(G.Rank, G.Volume, Supplier, cVolume, 2 * cVolume, Id) // filling the container for the most pressing supplier where sum(G.Q) > 0 show table "Containers (container size: \{cVolume})" a1f4 tomato with same(Supplier) as "Supplier" G.CId as "Container" Id as "Id" sum(G.Q) as "Quantity" sum(G.Reward) as "Reward{$}" sum(BuyPrice * G.Q) as "Investment{$}" sum(G.Volume) as "Volume{ m3}" group by (G.CId, Id) order by avg(sum(G.Reward) by (Supplier, G.CId)) desc

This dashboard produces a single table that contains 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 in Envision for the purchase of dealing with the per-container constraints.