7 Replies Latest reply: May 21, 2012 7:06 AM by Bogdan Iordache RSS

    Bill of material explosion (BOM)

      Hi everyone

       

      It was requested of me to build a report that calculates the current cost of a specific product.

      Let's call this product. Product A. Product A is the Father Product in this case.

       

      Product A consists of two components, Product B & C. The quantity required of each component

      to produce Product A is shown in the Qty field. The cost per unit of each component is shown in Unit Cost field.

       

      FatherCodeFatherNameChildCodeChildName Qty Unit Cost
      F00014829Product AHZA000991Product B 29.44
      F00014829Product APZA00095Product C 1.00 56.27

       

      The issue I am having is that Product B is a Father Product on its own as well. There is a number of components required to produce Product B.

       

      FatherCodeFatherNameChildCodeChildName Qty Unit Cost
      HZA000991Product BR00025457Component B1 0.00 691.12
      HZA000991Product BR00027720Component B2 0.19 32.59
      HZA000991Product BR00032427Component B3 0.42 9.23
      HZA000991Product BR00033164Component B4 0.08 33.84
      HZA000991Product BR00034256Component B5 0.15 4.99
      HZA000991Product BR00036131Component B6 0.05 42.08
      HZA000991Product BR00039869Component B7 0.00 233.78
      HZA000991Product BR00056138Component B8 0.10 39.00
      HZA000991Product BR00084788Component B9 0.00 76.69
      HZA000991Product BR00098034Component B10 0.00 67.46
      HZA000991Product BR01038421Component B11 0.00 170.63

       

      What I want at the end is to select Product A. Qlikview should then calculate how much it will cost to produce Product A. Qlikview should automatically calculate how much it will cost to produce one unit of product B. This cost of product B should then be added to the Product B (as a component of Product A).

       

      The total cost of one unit of product A should be 660.65. This is the correct answer.

       

      Please find attached excel sheet. This excel sheet is an extract of the database we are using. I think the hierarchy function should be use in some way, but I am not sure how.

        • Bill of material explosion (BOM)

          Has anyone got a solution for this?

            • Bill of material explosion (BOM)

              Is there a way to tell which are finished goods?

              Can you have more than 2 levels(Component B1 is also made of ...)?

                • Bill of material explosion (BOM)

                  There you go:

                   

                   

                  Temp:

                  LOAD FatherCode,

                       FatherName,

                       ChildCode,

                       ChildName,

                       Qty,

                       [Unit Cost]

                  FROM

                  [BOM Cost.xls]

                  (biff, embedded labels, table is Sheet1$) ;

                  left join

                  LOAD FatherCode as ChildCode,        

                       sum(Qty*[Unit Cost]) as [Unit Cost Child]

                  FROM

                  [BOM Cost.xls]

                  (biff, embedded labels, table is Sheet1$)  group by FatherCode;

                   

                  Final:

                  load FatherCode,FatherName,ChildCode,ChildName,Qty,if(isnull([Unit Cost]),[Unit Cost Child],[Unit Cost]) as [Unit Cost] resident Temp;

                  drop table Temp;

                    • Bill of material explosion (BOM)

                      Bobacaty

                       

                      Just to answer your questions:

                       

                      Is there a way to tell which are finished goods? The Finished Goods start with the letter "F"

                      Can you have more than 2 levels(Component B1 is also made of ...)? Yes you can have more than two levels.

                       

                      The above script is working, but when I drill down into the Finished Product (Product A), I only get the cost of product B & C, I do not get the breakdown of product B. I am using a pivot table to see the explosion. For me to see the breakdown of Product B, I have to select product B as a Father.

                       

                      Are there a way around this to be albe to start at level one and drill down to lets say level 5?

                       

                      Thanks for your help so far.

                        • Bill of material explosion (BOM)

                          In this case you would have to  build the 5 levels starting from finished goods. First you only load the products starting with 'F' and their ChildCode as ChildCode1. Then you load the ChildCode1 products that were previously loaded (where exists(ChildCode1,FatherCode)) renaming FatherCode to ChildCode1 and ChildCode to ChildCode2...

                          And so on....until you reach the bottom level. Your data model should have 1-N tables linked by theChildCodeN.

                          Be careful to also rename the Qty and Unit Cost to the same Qty1....(I bet your link between the tables is not only the product code but also the LotNo...)

                          Then in the chart you would have to build a Drill Down group with the Products and in the expression get the level you are on and put the coresponding QtyN...

                          It is a complicated calculation and you have to make sure that one of your childcodes is not part of more than one FatherCode. Then you would have to split the total ChildCost depending on the Father's qty...

                          Why do you have nulls in your table?Shouldn't the ERP distribute the cost?

                            • Bill of material explosion (BOM)

                              Hi Bobocaty

                               

                              I will try the above solution you mentioned.

                               

                              However, I do have several Childcodes that form part of more than on Fathercode. The cost should be based on the qty required to produce 1 unit of a father.

                               

                              The cost I am using is the last purchase cost, we do not purchase finished goods, as we produce them ourselves. This is why only the components have a cost. What if one of the father codes do have a cost allocated to it (we maybe had to import the finished product), how would I ignore this, and rather use the cost of the children?

                               

                              Could I maybe email you the full QVD file, for you to understand the complexity?

                                • Bill of material explosion (BOM)

                                  Hi Chris,

                                   

                                  You can email me the qvd(bobocaty@yahoo.com). Are you sure that this is the way  to get the correct cost? In production you have more than the cost of the products that are part of the finished good. You have fixed costs(electricity, employees costs,amortization...) that you cannot include into the real cost of the final product(that's why maybe there is another table in your ERP that has the recipe and the real costs distributed).