sample data set scripts

Basic analysis for the sample dataset

/// reads from /newdataset
read "/input/newdataset/Lokad_Items.tsv" as Items with
  Supplier : text
  Brand : text
read "/input/newdataset/Lokad_Orders.tsv" as Orders[Id, Date, *]
read "/input/newdataset/Lokad_PurchaseOrders.tsv" as PO[Id, Date, *]

oend := max(Orders.Date)

// Default currency, and currency conversions
currency := mode(Orders.Currency) if (date >= oend - 365)
Orders.NetAmount = forex(Orders.NetAmount, Orders.Currency, currency, Orders.Date)
PO.NetAmount = forex(PO.NetAmount, PO.Currency, currency, PO.Date)

// FIFO inventory valuation
PO.Unsold = fifo(StockOnHand, PO.Date, PO.Received)
OnHandStockValue = sum(PO.Unsold * PO.NetAmount /. PO.Received)

PO.OnOrder = fifo(StockOnOrder, PO.Date, PO.Quantity)
OnOrderStockValue = sum(PO.OnOrder * PO.NetAmount /. PO.Quantity)

// Top linechart
when date >= oend - 2 * 52 * 7 & date < monday(oend)
  Week.sold := sum(Orders.NetAmount)
  Week.purch := sum(PO.NetAmount)
  show linechart "Sold and purchased " a2f4 tomato unit:" \{currency}" with
    Week.sold as "Sold"
    Week.purch as "Purchased"

// Indicators first row
when date >= oend - 365
  show table "Sold 1 year" a5b5 tomato unit:" \{currency}" with sum(Orders.NetAmount)
  show table "Purchased 1 year" c5d5 tomato unit:" \{currency}" with sum(PO.NetAmount)
show table "Stock on hand" e5 tomato unit:" \{currency}" with sum(OnHandStockValue)
show table "Stock on order" f5 tomato unit:" \{currency}" with sum(OnOrderStockValue)

show summary "Summary" a1f1 tomato with
  min(Orders.Date) as "First order"
  max(Orders.Date) as "Last order"
  min(PO.Date) as "First PO"
  max(PO.Date) as "Last PO"
  sum(1) as "Items"
  distinct(Supplier) as "Suppliers"

// Barchart third row
when date >= oend - 365
  show barchart "Sold per brand (1 year)" a6c8 tomato unit:" \{currency}" with
    sum(Orders.NetAmount) as "Sold"
    group by Brand
    order by sum(Orders.NetAmount) desc

show barchart "Stocked per brand (on hand + on order)" d6f8 tomato unit:" \{currency}" with
  sum(OnHandStockValue+OnOrderStockValue) as "Stocked"
  group by Brand
  order by sum(OnHandStockValue+OnOrderStockValue) desc

Sales analysis for the sample dataset

/// reads from /input/newdataset
read "/input/newdataset/Lokad_Items.tsv" as Items with
  Brand : text
  Name : text
read "/input/newdataset/Lokad_Orders.tsv" as Orders[Id, Date, *] with
  OrderId : text
  Loc : text

oend := max(Orders.Date)

// Default currency, and currency conversions
when date >= oend - 365
  currency := mode(Orders.Currency)
Orders.NetAmount = forex(Orders.NetAmount, Orders.Currency, currency, Orders.Date)

// KPIs at the top
show label "Sales analysis" a1f1 tomato

when date >= oend - 365
  show summary "Current year" d2f2 tomato with
    sum(Orders.NetAmount) as "Turnover" unit:" \{currency}"
    distinct(Orders.OrderId) as "Orders"
    sum(Orders.Quantity) as "Units"

when date < oend - 365 & date >= oend - 2 * 365
  show summary "Previous year" a2c2 tomato with
    sum(Orders.NetAmount) as "Turnover" unit:" \{currency}"
    distinct(Orders.OrderId) as "Orders"
    sum(Orders.Quantity) as "Units"

// Linecharts
Week.sold := sum(Orders.NetAmount)
when date >= monday(oend) - 52 * 7 & date < monday(oend)
  show linechart "Weekly sales volume" a3f5 tomato unit:" \{currency}" with
    Week.sold as "Current Year"
    Week.sold[-52] as "Previous year"

// Split by brand and by channel
show barchart "Sales per brand" a6c8 tomato unit:" \{currency}" with
  sum(Orders.NetAmount)
  group by Brand
  order by sum(Orders.NetAmount) desc

show barchart "Sales per location" d6f8 tomato unit:" \{currency}" with
  sum(Orders.NetAmount)
  group by Orders.Loc
  order by sum(Orders.NetAmount) desc

// Top sellers, top rising
when date >= oend - 365
  show table "Top sellers (1 year)" a9c11 tomato with
    Id
    Name
    sum(Orders.NetAmount) as "Sold" unit:" \{currency}"
    sum(Orders.Quantity) as "Quantity"
    mode(Orders.Loc) as "Main channel"
    order by sum(Orders.NetAmount) desc

when date >= oend - 30
  show table "Top sellers (1 month)" d9f11 tomato with
    Id
    Name
    sum(Orders.NetAmount) as "Sold" unit:" \{currency}"
    sum(Orders.Quantity) as "Quantity"
    mode(Orders.Loc) as "Main channel"
    order by sum(Orders.NetAmount) desc

Stock value analysis for the sample dataset

/// reads from /input/newdataset
read "/input/newdataset/Lokad_Items.tsv" as Items with
  Category : text
  Supplier : text
  Name : text
read "/input/newdataset/Lokad_Orders.tsv" as Orders[Id, Date, *]
read "/input/newdataset/Lokad_PurchaseOrders.tsv" as PO[Id, Date, *]

oend := max(Orders.Date)

// Default currency, and currency conversions
when date >= oend - 365
  currency := mode(Orders.Currency)
Orders.NetAmount = forex(Orders.NetAmount, Orders.Currency, currency, Orders.Date)
PO.NetAmount = forex(PO.NetAmount, PO.Currency, currency, PO.Date)

// FIFO inventory valuation
PO.Fifo = fifo(StockOnHand + StockOnOrder, PO.Date, PO.Quantity)
Fifo = sum(PO.Fifo * PO.NetAmount / PO.Quantity)

PO.Fifo2 = fifo(StockOnOrder, PO.Date, PO.Quantity)
Fifo2 = sum(PO.Fifo2 * PO.NetAmount / PO.Quantity)

// FIFO inventory age
StockAge = sum(PO.Fifo * (oend - PO.Date)) /. sum(PO.Fifo)

// Top row of KPIs
show label "Stock value analysis" a1f1 tomato

when date > oend - 31
where sum(Orders.Quantity) by Id > 0
  unstockedItems := sum(1)

show summary "Stocks information" a2f2 tomato with
  sum(Fifo - Fifo2) as "Stock on hand (FIFO)" unit:" \{currency}"
  sum(Fifo2) as "Stock on order (FIFO)" unit:" \{currency}"
  count(StockOnHand + StockOnOrder > 0) as "Items in stock"
  unstockedItems as "Items unstocked"

// Stock by category and supplier
show barchart "Stock by category" a3c5 tomato with
  sum(Fifo)
  group by Category
  order by sum(Fifo) desc

show barchart "Stock by supplier" d3f5 tomato unit:" \{currency}" with
  sum(Fifo)
  group by Supplier
  order by sum(Fifo) desc

// Most heavy stocked items, most aged stock items
show table "Most heavy stock items" a6c8 tomato with
  Id
  Name
  StockOnHand as "On Hand"
  StockOnOrder as "On Order"
  Fifo as "Value" unit:" \{currency}"
  order by Fifo desc

show table "Most aged stock items" d6f8 tomato with
  Id
  Name
  StockOnHand as "On Hand"
  Fifo as "Value" unit:" \{currency}"
  StockAge as "Age (days)"
  order by Fifo * StockAge desc

// Most pressured stock items
D = sum(Orders.Quantity) when date >= oend - 13 * 7
where StockOnHand + StockOnOrder <= 0 & D > 0
  show table "Most demanded unstocked items" a9c11 tomato with
    Id
    Name
    D as "Units sold last quarter"
    order by D desc

V = sum(Orders.NetAmount) when date >= oend - 13 * 7
where sum(V) by Supplier > 0
  show barchart "Stock age by supplier" d9f11 tomato unit:" days" with
    sum(V * StockAge) / max(1, sum(V))
    group by Supplier
    order by sum(Fifo) desc

Gross margin analysis for the sample dataset

/// reads from /newdataset
read "/input/newdataset/Lokad_Items.tsv" as Items with
  Category : text
  Supplier : text
  Ref : text
  Name : text
read "/input/newdataset/Lokad_Orders.tsv" as Orders[Id, Date, *] with
  OrderId : text
read "/input/newdataset/Lokad_PurchaseOrders.tsv" as PO[Id, Date, *]

oend := max(Orders.Date)

// Default currency, and currency conversions
currency := mode(Orders.Currency) if (date >= oend - 365)
Orders.NetAmount = forex(Orders.NetAmount, Orders.Currency, currency, Orders.Date)

Orders.COGS = BuyPrice * Orders.Quantity
Orders.Margin = Orders.NetAmount - Orders.COGS
Orders.GrossMargin = Orders.Margin /. Orders.NetAmount

// Top row of KPIs
show label "Gross margin analysis (LIFO inventory cost)" a1f1 tomato

when date > oend - 365
  show summary "Current year" d2f3 tomato with
    sum(Orders.Margin) as "Margin" unit:" \{currency}"
    avg(Orders.GrossMargin) as "Gross margin" unit:"%"
    sum(Orders.COGS) as "Cogs" unit:" \{currency}"
    distinct(Orders.OrderId) as "Orders"
    sum(Orders.Margin) / distinct(Orders.OrderId) as "Margin per order" unit:" \{currency}"
    sum(Orders.Margin) / sum(Orders.Quantity) as "Margin per unit" unit:" \{currency}"

when date > oend - 2 * 365 & date <= oend - 365
  show summary "Previous year" a2c3 tomato with
    sum(Orders.Margin) as "Margin" unit:" \{currency}"
    avg(Orders.GrossMargin) as "Gross margin" unit:"%"
    sum(Orders.COGS) as "Cogs" unit:" \{currency}"
    distinct(Orders.OrderId) as "Orders"
    sum(Orders.Margin) / distinct(Orders.OrderId) as "Margin per order" unit:" \{currency}"
    sum(Orders.Margin) / sum(Orders.Quantity) as "Margin per unit" unit:" \{currency}"

// Linechart
Week.margin := sum(Orders.Margin)
when date >= monday(oend) - 52 * 7 & date < monday(oend)
  show linechart "Weekly margin" a4f6 tomato unit:" \{currency}" with
    Week.margin as "Current year"
    Week.margin[-52] as "Previous year"

// Split by category and supplier
when date > oend - 365
  show barchart "Margin per category (1 year)" a7c9 tomato unit:" \{currency}" with
    sum(Orders.Margin)
    group by Category
  show barchart "Margin per supplier (1 year)" d7f9 tomato unit:" \{currency}" with
    sum(Orders.Margin)
    group by Supplier

// Tops and flops
when date > oend - 365
where sum(Orders.NetAmount) > 0
  show table "Most profitable items (1 year)" a10c12 tomato with
    Ref
    same(Name) as "Name"
    0 + sum(Orders.NetAmount) as "NetAmount" unit:" \{currency}"
    0 + sum(Orders.COGS) as "COGS" unit:" \{currency}"
    0 + sum(Orders.Margin) as "Margin" unit:" \{currency}"
    0 + avg(Orders.GrossMargin) as "Gross Margin" unit:"%"
    group by Ref
    order by avg(Orders.Margin) desc

  RankMargin = rank(sum(Orders.Margin) by Ref)
  where RankMargin <= 0.2*max(RankMargin) by 1
    show table "Least profitable items (1 year, among top 20%)" d10f12 tomato with
      same(RankMargin) as "RankMargin"
      Ref
      same(Name) as "Name"
      0 + sum(Orders.NetAmount) as "NetAmount" unit:" \{currency}"
      0 + sum(Orders.COGS) as "COGS" unit:" \{currency}"
      0 + sum(Orders.Margin) as "Margin" unit:" \{currency}"
      0 + avg(Orders.GrossMargin) as "Gross Margin" unit:"%"
      group by Ref
      order by -avg(Orders.Margin) desc

Suppliers analysis for the sample dataset

/// reads from /input/newdataset
read "/input/newdataset/Lokad_Items.tsv" as Items with
  Category : text
read "/input/newdataset/Lokad_Orders.tsv" as Orders[Id, Date, *]
read "/input/newdataset/Lokad_PurchaseOrders.tsv" as PO[Id, Date, *] with
  Supplier : text
  PONumber : text

oend := max(Orders.Date)

// Default currency, and currency conversions
currency := mode(Orders.Currency) when date >= oend - 365
PO.NetAmount = forex(PO.NetAmount, PO.Currency, currency, PO.Date)

// Top KPIs
show label "Supply analysis" a1f1 tomato

when date >= oend - 365
  show summary "Current year" d2f3 tomato with
    sum(PO.NetAmount) as "Purchased " unit:" \{currency}"
    distinct(PO.Supplier) as "Suppliers"
    distinct(PO.PONumber) as "POs"
    sum(PO.Quantity) as "Units purchased"
    avg(sum(PO.Quantity) by PO.PONumber) as "Units per order"
    avg(sum(PO.NetAmount) by PO.PONumber) as "Amount per order" unit:" \{currency}"

when date >= oend - 2 * 365 & date < oend - 365
  show summary "Previous year" a2c3 tomato with
    sum(PO.NetAmount) as "Purchased " unit:" \{currency}"
    distinct(PO.Supplier) as "Suppliers"
    distinct(PO.PONumber) as "POs"
    sum(PO.Quantity) as "Units purchased"
    avg(sum(PO.Quantity) by PO.PONumber) as "Units per order"
    avg(sum(PO.NetAmount) by PO.PONumber) as "Amount per order" unit:" \{currency}"

// Linecharts
Week.purchased := sum(PO.NetAmount)
when date < monday(oend) & date >= monday(oend) - 7 * 52
  show linechart "Weekly purchase volumes" a4f6 tomato unit:" \{currency}" with
    Week.purchased as "Current year"
    Week.purchased[-52] as "Previous year"

// Split by supplier
show barchart "Purchase by supplier (1 year)" a7c9 tomato unit:" \{currency}" with
  sum(PO.NetAmount)
  group by PO.Supplier

show barchart "Purchase by category (1 year)" d7f9 tomato unit:" \{currency}" with
  sum(PO.NetAmount)
  group by Category

// Supplier analysis with lead times
when date >= oend - 365
where PO.DeliveryDate >= PO.Date // delivered PO only
  show table "Supplier analysis (1 year)" a10f12 tomato with
    PO.Supplier as "Supplier"
    distinct(PO.PONumber) as "POs"
    avg(mode(PO.DeliveryDate - PO.Date) by PO.PONumber) as "Supplier Lead Time" unit:" days"
    365 / distinct(PO.PONumber) as "Ordering Lead Time" unit:" days"
    avg(sum(PO.NetAmount) by PO.PONumber) as "Avg POs amount" unit:" \{currency}"
    avg(sum(PO.Quantity) by PO.PONumber) as "Avg POs units"
    group by PO.Supplier
    order by sum(PO.NetAmount) desc

Purchase simulator intended for quantile grids with the sample dataset

/// reads from /input/newdataset
read "/input/newdataset/Lokad_Items.tsv" as Items with
  Id: text
  Ref: text
  Loc: text
  Name: text
  Category: text
  SubCategory: text
  Brand: text
  Supplier: text
  StockOnHand: number
  StockOnOrder: number
  BackOrder: number
  BuyPrice: number
  SellPrice: number
  MOQ: number
  LotMultiplier: number
  Volume: number

read "/input/newdataset/Lokad_Orders.tsv" as Orders with
  Id: text
  Ref: text
  Loc: text
  Date: date
  Quantity: number
  NetAmount: number
  OrderId: text
  Currency: text
  Client: text

read "/input/newdataset/Lokad_PurchaseOrders.tsv" as PO with
  Id: text
  Loc: text
  Ref: text
  DeliveryDate: date
  Quantity: number
  Received: number
  NetAmount: number
  Supplier: text
  PONumber: text
  Date: date
  Currency: text


read "/input/newdataset/Lokad_BOM.tsv" as BOM[*] with
  Bundle : text
  Part : text
  Quantity : number

//Bill of materials logic
IsBundle by Ref = exists(BOM.Bundle) by BOM.Bundle
// table ExtendedOrders = extend.billOfMaterials(
//   Item : Ref
//   Part : BOM.Part
//   Quantity : BOM.Quantity
//   DemandId : concat(rank(Orders.1))
//   DemandValue : Orders.Quantity)

// ExtendedOrders.DemandDate by ExtendedOrders.DemandId = same(Orders.Date) by concat(rank(Orders.1))
// ExtendedOrders.Currency by ExtendedOrders.DemandId = same(Orders.Currency) by concat(rank(Orders.1))
// ExtendedOrders.NetAmount by ExtendedOrders.DemandId = same(Orders.NetAmount) by concat(rank(Orders.1))

keep where not IsBundle

//Forecasting lead times distribution with purchase orders history
where PO.DeliveryDate >= PO.Date // delivered PO only
  LeadTime = call forecast.leadtime(
    category: Brand, Category, SubCategory
    supplier: Supplier
    offset: 0
    present: (max(Orders.Date) by 1) + 1
    leadtimeDate: PO.Date
    leadtimeValue: PO.DeliveryDate - PO.Date + 1
    leadtimeSupplier: PO.Supplier)

//Forecasting demand using the lead time distribution
Demand = call forecast.demand(
  category: Brand, Category, SubCategory
  horizon: Leadtime
  offset: 0
  present: (max(Orders.Date) by 1) + 1
  demandDate: Orders.Date
  demandValue: Orders.Quantity)

//Extending the demand distribution into a grid
table Grid = extend.distrib(Demand, StockOnHand + StockOnOrder, LotMultiplier)

oosPenalty := Form.oosPenalty > 0 ? (Form.oosPenalty / 100) : 0.25 // % relative to selling price
carryingCost := Form.carryingCost > 0 ? (Form.carryingCost / 100) : 0.3  // % annual carrying cost relative to purchase price
discount := Form.discount > 0 ? (Form.discount / 100) : 0.20 // % annual economic discount
budget := Form.budget > 0 ? Form.budget : 1000
currency := Form.currency != "" ? Form.currency : "USD"

oend := max(Orders.Date)

// Purchase simulation settings
show form "Purchase simulator" a1b6 tomato with
  Form.budget as "Max spend budget"
  Form.currency as "Target currency"
  Form.carryingCost as "Annual carrying costs (default: 30%)"
  Form.oosPenalty as "Stock-out penalty on sell price (default: 25%)"
  Form.discount as "Annual discount (default: 20%)"

// Converting everything to target currency
Orders.NetAmount = forex(Orders.NetAmount, Orders.Currency, currency, Orders.Date)
PO.NetAmount = forex(PO.NetAmount, PO.Currency, currency, PO.Date)
SellCurrency = same(Orders.Currency) or "USD"
BuyCurrency = same(PO.Currency) or "USD"
SellPrice = forex(SellPrice, SellCurrency, currency, oend)
BuyPrice = forex(BuyPrice, BuyCurrency, currency, oend)

// FIFO inventory valuation
PO.Fifo = fifo(StockOnHand + StockOnOrder, PO.Date, PO.Quantity)
Fifo = sum(PO.Fifo * PO.NetAmount / PO.Quantity)

PO.Fifo2 = fifo(StockOnOrder, PO.Date, PO.Quantity)
Fifo2 = sum(PO.Fifo2 * PO.NetAmount / PO.Quantity)

// KPIs at the top
lastYearSold := sum(Orders.NetAmount) when date >= oend - 365
lastYearPurchased := sum(PO.NetAmount) when date >= oend - 365

show summary "KPI" c1g1 tomato with
  sum(Fifo - Fifo2) as "FIFO On Hand" unit:" \{currency}"
  sum(Fifo2) as "FIFO On Order" unit:" \{currency}"
  lastYearSold as "Sold 1 year" unit:" \{currency}"
  lastYearPurchased as "Bought 1 year" unit:" \{currency}"
  sum(1) as "Items in scope"


// Prioritization logic
Grid.Q = Grid.Max - Grid.Min + 1
Grid.ReorderPoint = Grid.Max

M = SellPrice - BuyPrice
S = - oosPenalty * SellPrice
C = - carryingCost * BuyPrice * mean(LeadTime) / 365
A = 1 - discount * mean(LeadTime) / 365
D = max(1, sum(Orders.Quantity)) when date > oend - 365 // 'max' to avoid DBZ

RwdM = stockrwd(Demand, M, 0, 0, 0.3)
RwdS = stockrwd(Demand, 0, S, 0, A)
RwdC = stockrwd(Demand, 0, 0, C, A)
Reward = RwdM + RwdS + RwdC
Grid.Reward = int(Reward, Grid.Min, Grid.Max)
Grid.Score = Grid.Reward / max(1, BuyPrice * Grid.Q)

// Fill rate
Fillrate = fillrate(Demand)
CurrentFR = int(Fillrate, 0, StockOnHand + StockOnOrder)

where D >= 1 & BuyPrice > 0 // excluding erroneous entries
  where Grid.ReorderPoint > StockOnHand + StockOnOrder
    Grid.Rank = rank(Grid.Score, Id, -Grid.ReorderPoint)
    Grid.Invest = cumsum(BuyPrice * Grid.Q, Grid.Rank)

    where Grid.Invest < budget + 0
    where sum(Grid.Q) > 0
      NewFR = int(Fillrate, 0, StockOnHand + StockOnOrder + sum(Grid.Q))
      show table "DOWNLOAD. Purchase priority list with \{budget} \{currency}" c2g6 tomato with
        Id as "Id"
        Supplier as "Supplier"
        StockOnHand as "OnHand"
        StockOnOrder as "OnOrder"
        sum(Grid.Q) as "Qty"
        LotMultiplier as "LotMultiplier"
        LeadTime as "LT"
        CurrentFR as "FR" unit:"%"
        sum(Grid.Reward) as "Rwd" unit:" \{currency}"
        NewFR as "ExFR" unit:"%"
        sum(BuyPrice * Grid.Q) as "Cost" unit:" \{currency}"
        group by Id
        order by [avg(Grid.Score), Ref] desc