Navigation :

Bill of Materials

Inventory analysis can be complicated by the presence of a bill of materials (BOM). When a BOM is involved, the items being sold or serviced are typically not the same as the ones being purchased since a bundling or manufacturing operation takes place in the middle. Each bundle or assembly can be produced from a list of parts, with each part corresponding to a specific quantity in the bundle. In order to optimize the stock level of parts, it’s usually required to convert the original demand expressed in bundles or assemblies in terms of a demand expressed in parts. The function billOfMaterials() in Envision is specifically tailored for this use case.

Table of contents

Bill Of Materials table overview

A bill of materials (BOM) or product structure is a list of raw materials, sub-assemblies, intermediate assemblies, sub-components, parts and the quantities of each that are needed to manufacture an end product, that is, the bundle. In its simplest form, a BOM table comes with three columns:

In practice, BOM tables are frequently recursive: a bundle can be composed of other bundles. Hence, in order to identify the final parts that are required for a given bundle, the BOM table needs to be processed recursively. If a circular dependency is found within the BOM table, the table is inconsistent. A circular dependency basically states: a bundle is composed of the bundle itself plus some other parts, which does not make sense.

By convention, any item that is not found in the BOM table is assumed to be an end product; hence, this item does not require any particular processing, and should be left as is.

Syntax of extend.billOfMaterials()

The purpose of the billOfMaterials() function is to convert the demand observed at the bundle level into a demand expressed at the part level. This conversion is controlled through the bill of materials table, which specifies the composition of each bundle. The syntax of extend.billOfMaterials() is as follows:

table Items[Id] = with
  [| as Id |]
  [| "id0" |]
  [| "id1" |]

table BOM = with
  [| as RawId, as PartId, as Quantity |]
  [| "id1",   "id0",      2           |]

expect BOM.Id = BOM.RawId

table Orders = with
  [| as Uid, as Quantity, as DemandDate     |]
  [| "id1",  1,           date(2024, 1, 20) |]

expect Orders.Id = Orders.Uid

// Beware: labeled arguments
table T = extend.billOfMaterials(
  Item: Items.Id
  Part: BOM.PartId
  Quantity: BOM.Quantity
  DemandId: Orders.Uid // 'Uid' for OrderId
  DemandValue: Orders.Quantity)

// illustrating how to get the date
// and how to export 'T'
T.DemandDate = same(Orders.DemandDate) by Orders.Uid at T.DemandId

show table "Translated" with
  Items.Id
  T.DemandDate
  T.Quantity

Three tables - Items, BOM and Orders are expected to be present. Table Items is usually the list of products or references. The table BOM contains the bill of materials, and it’s expected downstream of Items. The table Orders is expected to be the demand history, typically referring to the orders table. Table Orders is also expected to be downstream of Items.

The arguments of the function are:

The resulting table is an extension of table Items, with proper affinities in place. Table T contains two fields that are already populated:

Frequently, it is useful to inject a date into table T. This can be done with a by-at statement as illustrated by the line below the billOfMaterials() block here above.

The extend.billOfMaterials() function supports recursive bundles, meaning that an item can appear both as a component and as a bundle in Table BOM. Naturally, the function fails and results in an error if cyclic dependencies are found in the bill of materials table.

Illustration with a sample dataset

The following script can be executed directly using a sample dataset. The script translates the original Orders table into a new table T that represents the same demand, only re-expressed at the part level instead.

read "/sample/Lokad_Items.tsv" as Items[id] with
  Id : text
  BuyPrice : number
read "/sample/Lokad_Orders.tsv" as Orders expect [Id, Date] with
  Id : text
  Date : date
  Quantity : number
  "NetAmount" as NetA : number
read "/sample/Lokad_BOM.tsv" as BOM expect [Id] with
  "Bundle" as Id : text
  Part : text
  Quantity : number

Orders.Uid = text(rank() scan Orders.1)

table T = extend.billOfMaterials(
  Item: Id
  Part: BOM.Part
  Quantity: BOM.Quantity
  DemandId: Orders.Uid
  DemandValue: Orders.Quantity)

T.DemandDate = same(Orders.Date) by Orders.Uid at T.DemandId

// assigning a sell price to parts
T.NetA = same(Orders.NetA) by Orders.Uid 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" with
  Id
  T.DemandDate
  T.Quantity
  T.NetA

The sample dataset does not contain a line identifier in the Orders table, so we create this identifier on line 11 for the sole purpose of joining tables later on. The purpose of line 11 is merely to assign a unique number to each line of the Orders table, and then to convert this number into text through the concat() function.

Frequently, when bundles are sold, individual parts don’t have a “real” sell price. While it’s possible to assign an arbitrary sell price to each part, the market price is fundamentally defined at the bundle level. Yet, from an inventory optimization perspective, it’s important to assign a sell price to parts, otherwise it is not possible to compute the parts’ gross-margins, and gross-margin are the core reason why each part is stocked in the first place.

Thus, in order to assign a sell price to parts, we can decide that each part will have a sell price proportional to the bundle sell price multiplied by the “weight” of the part within the bundle; the weight being defined as the cost of the part within the bundle compared to the total cost of the bundle. Lines 23 to 26 illustrate how such a calculation can be done: T.NetA is the fraction of the bundle net amount assigned to the part. The part price is then likely to vary from one transaction to the next, depending on the bundle being sold. Usually, the final part sell price is obtained by averaging the price of the part over, say, the last 3 months of transactions.

In order to assign a buy price to bundles, the calculation is much simpler and does not require any further assumption. The only requirement is to sum the buy price of the parts while applying the correct multiple for their respective quantities. Line 29 illustrates how such a calculation can be done.

Flattening a bill of materials

Recursive bills of materials are more complicated to process than non-recursive ones. Yet, from a data management viewpoint, a recursive bill of materials is typically easier to maintain. Thus, it can be useful to flatten a bill of materials, or in other words, to remove all the recursive dependencies from the table. This flattening can be performed with the following script.

read "/sample/Lokad_Items.tsv" as Items[id] with
  Id : text
read "/sample/Lokad_BOM.tsv" as BOM expect [Id] with
  "Bundle" as Id : text
  Part : text
  Quantity : number

table T = extend.billOfMaterials(
  Item: Id
  Part: BOM.Part
  Quantity: BOM.Quantity
  DemandId: Id
  DemandValue: 1)

// eliminating trivial entries
where T.DemandId != Id
  show table "Flattened BOM" with
    T.DemandId as "Bundle"
    Id as "Part"
    T.Quantity

We are applying the billOfMaterials() function directly to the Items table, and then filtering the trivial entries where the bundles contain only a single part - the bundle itself. The filtered table is the flattened bill of materials.

User Contributed Notes
0 notes + add a note

© Lokad 2014 - 2024