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:
Bundlecolumn that identifies the bundle or end product.
Partcolumn that identifies one of the parts within a bundle.
Quantitycolumn 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 -
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
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
The arguments of the function are:
Itemargument is used to identify the bundles or parts as present in the original demand table.
Partargument is used to identify the part column in the BOM table.
Quantityargument is used to identify the quantity column in the BOM table. It represents the number of units of
B.PartIdwhich are involved when selling or serving an assembly identified by
DemandIdargument is used to preserve a relationship between the original table
Ordersand its extension
DemandValueargument 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
Tthe originating lines present in table
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.
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[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) // 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
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.