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 Output 3.9.1, and the summarized parts list, SumBOM9, is shown in Output 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;


Output 3.9.1 Indented Bill of Material for 'LA01'
ABC Lamp Company
 
Indented Bill of Material, Part LA01

_Level_ _Parent_ _Part_ Desc QtyPer Qty_Prod Unit Scrap
0   LA01 Lamp LA . 1 Each .
1 LA01 B100 Base assembly 1 1 Each 0.0
2 B100 1100 Finished shaft 1 1 Each 0.0
3 1100 2100 3/8 Steel tubing 26 26 Inches 0.2
2 B100 1200 6-Diameter steel plate 1 1 Each 0.0
2 B100 1300 Hub 1 1 Each 0.0
2 B100 1400 1/4-20 Screw 4 4 Each 0.0
1 LA01 S100 Black shade 1 1 Each 0.0
1 LA01 A100 Socket assembly 1 1 Each 0.0
2 A100 1500 Steel holder 1 1 Each 0.0
3 1500 1400 1/4-20 Screw 2 2 Each 0.0
2 A100 1600 One-way socket 1 1 Each 0.0
2 A100 1700 Wiring assembly 1 1 Each 0.0
3 1700 2200 16-Gauge lamp cord 12 12 Feet 0.1
3 1700 2300 Standard plug terminal 1 1 Each 0.0

Output 3.9.2 Item Requirements for a Planned Order of 50 Units of 'LA01'
ABC Lamp Company
 
Summarized Parts List, Part LA01: Requirement=50

_Part_ Low_Code Gros_Req On_Hand Net_Req Desc Unit
1100 2 0 0 0 Finished shaft Each
1200 2 0 0 0 6-Diameter steel plate Each
1300 2 0 0 0 Hub Each
1400 3 60 0 60 1/4-20 Screw Each
1500 2 30 0 30 Steel holder Each
1600 2 30 0 30 One-way socket Each
1700 2 30 0 30 Wiring assembly Each
2100 3 0 0 0 3/8 Steel tubing Inches
2200 3 396 0 396 16-Gauge lamp cord Feet
2300 3 30 0 30 Standard plug terminal Each
A100 1 30 0 30 Socket assembly Each
B100 1 30 50 0 Base assembly Each
LA01 0 50 20 30 Lamp LA Each
S100 1 30 0 30 Black shade Each

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 bill 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;

Output 3.9.3 Total Requirements for Ordering 50 Units of 'LA01'
ABC Lamp Company
 
Gross Requirements Report, Part LA01: Requirement=50

_Part_ Desc Unit Gros_Req
1100 Finished shaft Each 50
1200 6-Diameter steel plate Each 50
1300 Hub Each 50
1400 1/4-20 Screw Each 300
1500 Steel holder Each 50
1600 One-way socket Each 50
1700 Wiring assembly Each 50
2100 3/8 Steel tubing Inches 1560
2200 16-Gauge lamp cord Feet 660
2300 Standard plug terminal Each 50
A100 Socket assembly Each 50
B100 Base assembly Each 50
LA01 Lamp LA Each 50
S100 Black shade Each 50

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;

Output 3.9.4 Total Usages for Making 50 Units of 'LA01'
ABC Lamp Company
 
Summarized Bill of Material, Part LA01: Requirment=50

_Part_ Desc Unit Qty_Use
1100 Finished shaft Each 50
1200 6-Diameter steel plate Each 50
1300 Hub Each 50
1400 1/4-20 Screw Each 300
1500 Steel holder Each 50
1600 One-way socket Each 50
1700 Wiring assembly Each 50
2100 3/8 Steel tubing Inches 1300
2200 16-Gauge lamp cord Feet 600
2300 Standard plug terminal Each 50
A100 Socket assembly Each 50
B100 Base assembly Each 50
LA01 Lamp LA Each 50
S100 Black shade Each 50

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.