Purchasing Decisions

This page collects the purchasing-oriented tutorials into a single flow, from valuation to prioritization.

Table of contents

Compute Inventory Valuation with FIFO

Value on-hand inventory by walking purchases in FIFO order.

1. Paste the script and run it.
2. You should now see remaining quantities by purchase date plus a total value KPI.
3. Change Stock.OnHand to see which batches remain.

table Stock[id] = with
  [| as id, as OnHand |]
  [| "A-100", 6 |]
  [| "B-200", 4 |]

table Purchases = with
  [| as Sku, as PurchDate, as PurchQty, as UnitCost |]
  [| "A-100", date(2024, 1, 10), 5, 8.0 |]
  [| "A-100", date(2023, 12, 20), 4, 7.5 |]
  [| "B-200", date(2024, 1, 12), 2, 12.0 |]
  [| "B-200", date(2023, 12, 5), 5, 10.0 |]

keep where Purchases.Sku in id
Purchases.Remaining = fifo(Stock.OnHand[Purchases.Sku], Purchases.PurchDate, Purchases.PurchQty)
Purchases.Value = Purchases.Remaining * Purchases.UnitCost

show table "FIFO inventory valuation" with
  Purchases.Sku
  Purchases.PurchDate
  Purchases.PurchQty
  Purchases.Remaining
  Purchases.UnitCost { unit:"$" }
  Purchases.Value { unit:"$" }
  order by [Purchases.Sku, -Purchases.PurchDate]

show summary "Inventory value" with
  sum(Purchases.Value) as "Total value" { unit:"$" }

You should now see which batches contribute to current inventory value.

Compute Stock Age and Identify Dead Stock with FIFO

Flag old inventory by age after FIFO allocation.

1. Paste the script and run it.
2. You should now see remaining batches colored by age.
3. Adjust asOf or the age threshold to change what is flagged.

asOf = date(2024, 6, 1)

table Stock[id] = with
  [| as id, as OnHand |]
  [| "A-100", 6 |]
  [| "B-200", 4 |]

table Purchases = with
  [| as Sku, as PurchDate, as PurchQty, as UnitCost |]
  [| "A-100", date(2024, 1, 10), 5, 8.0 |]
  [| "A-100", date(2023, 7, 20), 4, 7.5 |]
  [| "B-200", date(2024, 1, 12), 2, 12.0 |]
  [| "B-200", date(2023, 6, 5), 5, 10.0 |]

keep where Purchases.Sku in id
Purchases.Remaining = fifo(Stock.OnHand[Purchases.Sku], Purchases.PurchDate, Purchases.PurchQty)
Purchases.AgeDays = asOf - Purchases.PurchDate
Purchases.IsDead = Purchases.Remaining > 0 and Purchases.AgeDays > 180
Purchases.Color = if Purchases.IsDead then "#c62828" else "#2e7d32"

show table "Aging inventory" with
  Purchases.Sku
  Purchases.PurchDate
  Purchases.AgeDays
  Purchases.Remaining
  Purchases.UnitCost { unit:"$" ; textColor: #[Purchases.Color] }
  Purchases.Remaining { textColor: #[Purchases.Color] }
  order by [Purchases.Sku, -Purchases.PurchDate]

show summary "Dead stock" with
  sum(if Purchases.IsDead then Purchases.Remaining else 0) as "Units flagged"

You should now see which inventory batches are aging out.

Convert Bundle Demand into Part Demand with Bill of Materials

Use a simple BOM to translate bundle demand into part demand.

1. Paste the script and run it.
2. You should now see part demand totals plus a bundle breakdown table.
3. Change bundle demand to see part demand recompute.

table Bundles[bundle] = with
  [| as bundle, as Demand |]
  [| "KIT-1", 40 |]
  [| "KIT-2", 25 |]

table BOMs = with
  [| as Bundle, as Part, as Qty |]
  [| "KIT-1", "A-100", 2 |]
  [| "KIT-1", "B-200", 1 |]
  [| "KIT-2", "B-200", 2 |]
  [| "KIT-2", "C-300", 3 |]

expect BOMs.bundle = BOMs.Bundle
BOMs.BundleDemand = Bundles.Demand
BOMs.PartDemand = BOMs.Qty * Bundles.Demand

table Parts[part] = by BOMs.Part
Parts.Demand = sum(BOMs.PartDemand)

show table "Part demand from bundles" with
  Parts.part
  Parts.Demand
  order by Parts.Demand desc

show table "Bundle breakdown" with
  BOMs.bundle
  BOMs.Part
  BOMs.BundleDemand
  BOMs.PartDemand
  order by [BOMs.bundle, BOMs.Part]

You should now have part-level demand derived from bundle orders.

Compute Prioritized Inventory Purchase List

Build a quick purchase priority list using an aperiodic rate-of-return estimate.

1. Paste the script and run it.
2. You should now see a ranked table of SKUs by aperiodic RoR.
3. Adjust the sell probability or lead time to see priorities change.

present = date(2024, 1, 1)
horizon = 30
keep span date = [present .. present + horizon]

Day.t = date - present
Day.t1 = max(1, Day.t)

table Sku[sku] = with
  [| as sku, as Buy, as Sell, as Salvage, as LeadTime, as DailySellProb |]
  [| "A-100", 12, 18, 6, 3, 0.25 |]
  [| "B-200", 8, 14, 4, 7, 0.15 |]
  [| "C-300", 20, 30, 10, 10, 0.10 |]

table SkuDay = cross(Sku, Day)
SkuDay.ActiveDays = max(0, Day.t - Sku.LeadTime + 1)
SkuDay.SoldProb = 1 - (1 - Sku.DailySellProb) ^ SkuDay.ActiveDays
SkuDay.Rev = Sku.Sell * SkuDay.SoldProb
SkuDay.Salvage = Sku.Salvage * (1 - SkuDay.SoldProb)
SkuDay.Ror = (SkuDay.Rev + SkuDay.Salvage - Sku.Buy) / (Sku.Buy * Day.t1)

Sku.RorDay = max(SkuDay.Ror)
Sku.BestHorizon = argmax(SkuDay.Ror, Day.t)

show table "Purchase priority (aperiodic RoR)" with
  Sku.sku
  Sku.RorDay
  Sku.BestHorizon
  Sku.Buy { unit:"$" }
  Sku.Sell { unit:"$" }
  order by Sku.RorDay desc

You should now have a prioritized purchase list based on return speed.