How to prioritize purchases economically

This guide shows how to generate a purchase priority list from the 1-echelon 2017 dataset.

Step 1: Open the dataset session

Use the preloaded session so the /sample folder is already available.

Step 2: Create the script

Create a new Envision project, paste the script below, and save it.

read form with
  budget : number

read "/sample/Lokad_Items.tsv" as Items[id] max 1m with
  "Id" as id : text
  Supplier: text
  Category: text
  SubCategory: text
  SellPrice : number
  BuyPrice : number
  StockOnHand : number
  StockOnOrder : number

read "/sample/Lokad_Orders.tsv.gz" as Orders max 1m expect [id, date] with
  "Id" as id : text
  "Date" as date : date
  Quantity : number
read "/sample/Lokad_PurchaseOrders.tsv" as PO max 1m expect [id, date] with
  "Id" as id : text
  "Date" as date : date
  DeliveryDate : date

Items.Backorder = 0
Items.LotMultiplier = 1

orderingLeadtime = 7 // 7 days

// exclude non-delivered POs
where PO.DeliveryDate > PO.date
  Items.Leadtime = 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.Leadtime + dirac(orderingLeadtime),
  hierarchy: (Items.Category, Items.SubCategory),
  present: (max(Orders.date) by 1) + 1,
  demandDate: Orders.date,
  demandValue: Orders.Quantity)

show form "Purchase simulator" with
  budget as "Max budget"

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)
// plain recomposition
Items.R = Items.RM + Items.RS + Items.RC

Items.Stock = Items.StockOnHand + Items.StockOnOrder

Items.DBO = Items.Demand + Items.Backorder
table G max 100k = extend.ranvar(Items.DBO, Items.Stock, Items.LotMultiplier)

where G.Max > Items.Stock
  G.Q = G.Max - G.Min + 1
  // integral of the stock reward function
  G.Reward = int(Items.R, G.Min, G.Max)
  G.Score = G.Reward / max(1, Items.BuyPrice * G.Q)

  // top scores first
  // but preserve ordering of pairs (Id, G.Max)
  G.Rank = rank(G.Score, id, -G.Max)
  G.Invest = cumsum(Items.BuyPrice * G.Q) scan [G.Rank]

  where G.Invest < budget
  where any(G.Q > 0) into Items
    show table "Purchase priority list with $\{budget}" with
      id as "id"
      Items.Supplier as "Supplier"
      Items.StockOnHand as "OnHand"
      Items.StockOnOrder as "OnOrder"
      sum(G.Q) as "Qty"
      mean(Items.Leadtime) as "Leadtime"
      sum(G.Reward) as "Reward" {unit:"$"}
      sum(Items.BuyPrice * G.Q) as "Cost" {unit:"$"}
      group by id
      order by avg(G.Score) desc

Step 3: Run and read the list

Run the script, set the budget in the form, and review the table in order. Purchase the items starting from the top until the budget is exhausted.