How to expand demand through a bill of materials

This guide shows how to translate bundle demand into part demand and how to flatten a recursive bill of materials (BOM) using extend.billOfMaterials.

Expand bundle demand into part demand

Define items, a recursive BOM, and orders. Then expand orders to parts and allocate the bundle amount to parts by cost share.

table Items[id] = with
  [| as id, as BuyPrice |]
  [| "kit", 0 |]
  [| "subkit", 0 |]
  [| "partA", 2 |]
  [| "partB", 3 |]
  [| "partC", 5 |]

table BOM = with
  [| as Bundle, as Part, as Quantity |]
  [| "kit", "subkit", 1 |]
  [| "kit", "partC", 2 |]
  [| "subkit", "partA", 1 |]
  [| "subkit", "partB", 3 |]

expect BOM.id = BOM.Bundle

table Orders = with
  [| as OrderId, as Id, as Quantity, as DemandDate, as NetA |]
  [| "o1", "kit", 2, date(2024, 1, 10), 200 |]
  [| "o2", "subkit", 1, date(2024, 1, 12), 40 |]

expect Orders.id = Orders.Id

table T max 100k = extend.billOfMaterials(
  Item: Items.id,
  Part: BOM.Part,
  Quantity: BOM.Quantity,
  DemandId: Orders.OrderId,
  DemandValue: Orders.Quantity)

T.DemandDate = same(Orders.DemandDate) by Orders.OrderId at T.DemandId

T.NetA = same(Orders.NetA) by Orders.OrderId at T.DemandId
T.PartPrice = Items.BuyPrice * T.Quantity
T.BundlePrice = sum(T.PartPrice) by T.DemandId
T.NetA = T.NetA * (T.PartPrice / T.BundlePrice)

show table "Expanded" { 1..8, 1..12 } with
  Items.id
  T.DemandDate
  T.Quantity
  T.NetA
  T.DemandId as "OrderId"

Flatten a recursive BOM

Expand each bundle into its terminal parts and drop the trivial bundle -> bundle rows.

table Items[id] = with
  [| as id |]
  [| "kit" |]
  [| "subkit" |]
  [| "partA" |]
  [| "partB" |]
  [| "partC" |]

table BOM = with
  [| as Bundle, as Part, as Quantity |]
  [| "kit", "subkit", 1 |]
  [| "kit", "partC", 2 |]
  [| "subkit", "partA", 1 |]
  [| "subkit", "partB", 3 |]

expect BOM.id = BOM.Bundle

table T max 100k = extend.billOfMaterials(
  Item: Items.id,
  Part: BOM.Part,
  Quantity: BOM.Quantity,
  DemandId: Items.id,
  DemandValue: 1)

where T.DemandId != Items.id
  show table "Flattened BOM" with
    T.DemandId as "Bundle"
    Items.id as "Part"
    T.Quantity

Example output: