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.

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:

• The Bundle column that identifies the bundle or end product.
• The Part column that identifies one of the parts within a bundle.
• The Quantity column that counts the number of units required for a specific part within a 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 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 T = extend.billOfMaterials(
Item: J.Id
Part: B.PartId
Quantity: B.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.Date) by Orders.Uid at T.DemandId
show table "Translated" with
J.Id
T.DemandDate
T.Quantity

Three tables - J, B and Orders - are expected to be present. 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 Items table. Table Orders is expected to be the demand history, typically referring to the orders table. Table Orders 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 a relationship between the original table Orders 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 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 of identifying in table T the originating lines present in table Orders.
• T.Quantity, which is obtained by rolling out the bill of materials.

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 B. 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 with
Id : text
Id : text
"NetAmount" as NetA : number
"Bundle" as Id : text

// 'concat()' to get a 'text' vector
Orders.Uid = concat(rank() sort 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.BundlePrice = sum(T.PartPrice) by T.DemandId
T.NetA = T.NetA * (T.PartPrice / T.BundlePrice)

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

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 with
Id : text
"Bundle" as Id : text

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.