Multi level bill of materials - requirements

[Etienne_F]

Hi, 

i have a need to calculate some component requirements based on bill of materials, and this for many materials and plants at same time. Finished good materials can have multi level bill of materials, which means components themselves have their own BOM.

I have read forum messages on this topic, especially the challenge 81 and its solutions, tried on my own to adapt what had been done with iterative macro but couldn't come to and end as it was slightly differnt starting point. Notably here i don't know at first which bill of material is level 1, 2, 3 4 etc.

i have attached a sample to explain the problematic. "as is" sheet is how it is extracted from the source system. "Production needs" is the hypothesis of production volume required for each finished goods. "expectation" is the output i'd like to get as a list, so that we can see how much quantity of components is required in total and also which finished good material has contributed to this(in my example no component comes twice but this could be the case multiple times, some are uniques, some are recurrent). 

Wonder if you could help me buid the logic needed to achieve this(

thanks

[Etienne_F]

Hi, no one has any suggestion?

thanks

[Christina_H]

I'm not following exactly what you're trying to do so this isn't a complete solution, but I've put together a macro that aims to output a list of all materials needed with quantities.  My calculations aren't correct since the results don't match yours, but you may be in a better position than me to figure out why!

[DawnDuong · Accepted solution]

hi @Etienne_F 

I solved it with one iterative and one batch macro. Checked that the Finished Good A is identical to your expected results, but for for finished goods B, the quantities related to components 37 and 38 are different. I think the Excel's computations are likely not accurate, but you are better positioned to verify that.

Hope this helps.

Dawn.

[Etienne_F]

Hi, thanks for the input, it is very helpful but i struggle to amend it to make it adapated to the exact requirement. I actually want to modify the workflow to a lowest level. in case multiple plants would produce same material code, i want to include also plant code as part of key for joins. And i also want to introduce some periods regarding the input(production needs). I started to amend the pieces one by one, but don't understand how to deal with the source input of iterative macro where it is written ".\data forum-FirstRow.xlsx" I don't understand what that means and how to change that part.

would you be able tog uide me there?

thanks