Processing bills of materials with Envision

Bill Of Materials












Home » Resources » Here

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


Bill Of Materials table overview

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

  • The Bundle column that identifies the bundle or end product.
  • The Part column that identifies one of the parts of a bundle.
  • The Quantity that counts the number of units needed for the part within the bundle.

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 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 translate the demand observed at the bundle level into a demand expressed at the part level. This translation is controlled through the bill of materials table which specifies the composition of each bundle. The syntax of extend.billOfMaterials() is as follow:
table T = extend.billOfMaterials(
  Item: J.Id
  Part: B.PartId
  Quantity: B.Quantity
  DemandId: O.OrderId
  DemandValue: O.Quantity)

// illustrating how to get the date and how to export 'T'
T.DemandDate = same(O.Date) by O.OrderId at T.DemandId 
show table "Translated" with J.Id, T.DemandDate, T.Quantity
Three tables - J, B and O - are expected. Table J is usually the items table, but this is not a strict requirement. Table B is intended to be the bill of materials; and it’s expected to be an extension of table J, i.e. of type [Id, *], when J happens to be the item's table. Table O is expected to be the demand history, typically referring to the orders table. Table O is also expected to be an extension of Table J.

The arguments of the function are:
  • The Item argument is used to identify the bundles or parts as present in the original demand table.
  • The Part argument is used to identify the part column in the BOM table.
  • The Quantity argument is used to identify the quantity column in the BOM table. It represents the number of units of B.PartId which are involved when selling or serving an assembly identified by J.Id.
  • The DemandId argument is used to preserve an affinity between the original table O and its extension T.
  • The DemandValue argument is the quantity of units as present in the original demand table.

The resulting table is an extension of the table J, with proper affinities in place. Table T contains two fields that are already populated:
  • T.DemandId, which is intended to offer a way to identify in the table T the originating lines in the table O.
  • T.Quantity, which is obtained by rolling out the bill of materials.

Frequently, it is useful to inject a date into the 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. That is, an item can appear both as a component and as a bundle within Table B. Naturally, the function fails with an error if cyclic dependencies are found in the bill of materials table.

Illustration with the sample dataset

The following script can be executed directly against the sample dataset. The script translates the original Orders table into a new table T that represents the same demand be re-expressed at the part level instead.
read "/sample/Lokad_Items.tsv" with "Ref" as Id
read "/sample/Lokad_Orders.tsv" as Orders with "Ref" as Id
read "/sample/Lokad_BOM.tsv" as BOM[Id, *] with "Bundle" as Id

Orders.Uid = concat(rank(Orders.1)) // 'concat()' to get a 'text' vector

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.NetAmount = same(Orders.NetAmount) by Orders.Uid at T.DemandId
T.NetAmount = T.NetAmount * (BuyPrice * T.Quantity / sum(BuyPrice * T.Quantity) by T.DemandId)

// assigning a buy price to bundles
Orders.Cost = sum(BuyPrice * T.Quantity) by T.DemandId at Orders.Uid

show table "Expanded" with Id, T.DemandDate, T.Quantity, T.NetAmount

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

Frequently, when bundles are sold, parts don't have a "real" sell price. Indeed, while it is possible to assign an arbitrary sell price to each part, the market price if 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 part's gross-margins; and gross-margin is 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. The lines 17-18 are illustrating how such a calculation can be done: T.NetAmount 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 need to sum the buy price of the parts while applying the correct multiple for their respective quantities. The line 21 illustrates how such a calculation can be done.

Flattening a bill of materials

Recursive bill 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, that is, to remove all the recursive dependencies from the table. This flattening can be performed with the script below.
read "/sample/Lokad_Items.tsv" with "Ref" as Id
read "/sample/Lokad_BOM.tsv" as BOM[Id, *] with "Bundle" as Id

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

where T.DemandId != Id // eliminating trivial entries
  show table "Flattened BOM" with T.DemandId as "Bundle", Id as "Part", T.Quantity
We are applying the billOfMaterials() directly to the item's table, and then filtering the trivial entries where the bundles contains only a single part, the bundle itself. The filtered table is the flattened bill of materials.