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

# 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.

 FatherCode FatherName ChildCode ChildName Qty Unit Cost F00014829 Product A HZA000991 Product B 29.44 F00014829 Product A PZA00095 Product 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.

 FatherCode FatherName ChildCode ChildName Qty Unit Cost HZA000991 Product B R00025457 Component B1 0.00 691.12 HZA000991 Product B R00027720 Component B2 0.19 32.59 HZA000991 Product B R00032427 Component B3 0.42 9.23 HZA000991 Product B R00033164 Component B4 0.08 33.84 HZA000991 Product B R00034256 Component B5 0.15 4.99 HZA000991 Product B R00036131 Component B6 0.05 42.08 HZA000991 Product B R00039869 Component B7 0.00 233.78 HZA000991 Product B R00056138 Component B8 0.10 39.00 HZA000991 Product B R00084788 Component B9 0.00 76.69 HZA000991 Product B R00098034 Component B10 0.00 67.46 HZA000991 Product B R01038421 Component 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:

FatherName,

ChildCode,

ChildName,

Qty,

[Unit Cost]

FROM

[BOM Cost.xls]

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

left join

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

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).