Example 3.9 Bill of Material Explosion

Example 3.8 illustrated using the Indented BOM data set to roll up costs assigned at the leaf nodes of the BOM family tree to obtain the cost of the final product. Similarly, the same data set can also be used to "explode" (or "propagate") requirements along the tree, based on requirements specified for the root nodes. The "explosion" can be performed with or without taking into account the quantities on hand or the scrap factors. This example demonstrates a few of these explosions.

Note that the BOM procedure automatically performs the requirement explosion in the calculation of the gross and net requirements of each item when producing the summarized parts list. In the Summarized Parts data set produced by PROC BOM, the gross and net requirements are calculated taking into account both the quantities on hand and the scrap factors. See the section Summarized Parts Data Set for details about calculating the gross and net requirements for each item.

The following SAS code uses the data set SlBOM2 (displayed in Output 3.2.1) with additional specifications of scrap factors (20 percent for the relationship between the parent item '1100' and its component '2100', and 10 percent for the relationship of the component '2200' and its parent '1700') to produce the indented bill of material for 'LA01' and the summarized parts list for the requirement of 50 units of item 'LA01'. The indented BOM, IndBom9, is shown in Figure 3.9.1, and the summarized parts list, SumBOM9, is shown in Figure 3.9.2.

/* Product structure and part master data */
data SlBOM9;
set SlBOM2(drop=LeadTime);

/* Specify scrap factors for 2100 and 2200 */
if (Component="2100" and Parent="1100") then Scrap=0.2;
else if (Component="2200" and Parent="1700") then Scrap=0.1;
run;

  /* Create the indented BOM and the summarized parts list */ 
proc bom data=SlBOM9 out=IndBOM9 summaryout=SumBOM9;
   structure / part=Component
               parent=Parent 
               component=Component
               quantity=QtyPer
               id=(Desc Unit)
               requirement=Gros_Req
               qtyonhand=On_Hand 
               factor=Scrap;
run;

The summarized parts list displayed in Output 3.9.2 lists all items and their quantities required to be made or ordered in order to fill the requirement of 50 units for 'LA01'. The requirements in this list are calculated taking into account each item’s quantity on hand and each relationship’s scrap factor. However, if you want to analyze how future orders for end items will impact inventory levels, a gross requirements report that lists all items and their total requirements to make the pre-specified amount of end items, without any regard for quantities on hand, will be helpful. The gross requirements report can be determined by a top-down calculation through indented bills of material, taking into account only the scrap factors. In other words, once you have an Indented BOM data set available, you can use it to calculate the gross requirements report for any specified quantity of any end item, as shown below. It is not necessary to invoke the BOM procedure for each value.

The following code performs the top-down calculation, as discussed above, through the Indented BOM data set as displayed in Output 3.9.1 to produce a gross requirements report for an additional order of 50 units of 'LA01'. The gross requirements report, SumReq9, is displayed in Output 3.9.3; this data set has been sorted by the _Part_ variable.

/* Explode the gross requirement to low-level components */
data IndBOM9a;
set IndBOM9;
array reqs[4] req1 req2 req3 req4;
retain req1 req2 req3 req4 0;
drop req1 req2 req3 req4;

/* Calculate the gross requirement */
if _Level_=0 then Req=50;
else Req=reqs[_Level_]*QtyPer*(1.0+Scrap);

/* keep the requirement of the current level */
reqs[_Level_+1]=Req;

output;
run;

  /* Calculate the total requirement for each item */
proc sql;
   create table SumReq9 as
      select _Part_, Desc, Unit, 
             sum(Req) as Gros_Req
      from IndBOM9a 
      group by  _Part_, Desc, Unit;
quit;

In another situation, you may like to know the quantity of each component used in making a certain amount of the end item, without any regard for items on hand and scrap factors. A similar calculation as described above can be used to determine the total usage for each item. In fact, the quantities of lower-level components that are used to make 1 unit of the end item are already contained in the variable Qty_Prod of the Indented BOM data set. You only need to multiply those quantities by the specified amount of the end item and then add the values for the same item together for each one. The following codes performs this task to create a report of the total usage for each item in making 50 units of 'LA01'. This usage report, SumUse9, has been sorted by the _Part_ variable and is shown in Output 3.9.4.

  /* Calculate the total usage for each item */
proc sql;
   create table SumUse9 as
      select _Part_, Desc, Unit,  
             sum(Qty_Prod * 50) as Qty_Use
      from IndBOM9 
      group by  _Part_, Desc, Unit;
quit;

As discussed in Example 3.7, you can also use the %BOMRSUB SAS macro described in Chapter 4, Bill-of-Material Postprocessing Macros, to create the same report as that shown in Output 3.9.4.