VBA Code for Demand calculation using multi level BOM

I have read various posts on related topics, but nothing quite fits my needs...

I have a Multi Level BOM structure which is defined using a single level BOM table with three columns (Parent, Child, Qty per). Parts at any BOM level can have "Direct Demand" (e.g. from sales orders). In addition, parts (including sub-assemblies) have demand derived from the BOM relationships.

So the INPUTS are (a) the Part Master with associated direct (sales) demand for each part and (b) the Single Level BOM table. The desired OUTPUT is aggregate demand for each part in the Past Master which is the sum of the Direct demand and the demand resulting from the BOM structure.

Example is attached showing the Part Master/Demand, Single Level BOM structure and Desired Results.

I am looking for a VB Macro that will process the INPUTS to create the OUTPUT.

Many thanks for help on this....Einar

You may be right, but i'm a much more experienced Excel user....thanks!

I'm new to this forum so maybe i'm missing something but I cannot seem to see your attached file. Additional info: the data I provided was simply a sample to establish the concept i need to deal with. The actual data will be queried from an ERP system and is several thousand records with BOMs that are up to 5 levels deep. If formulas can do the job, that's great. Some of the research I did indicted that a recursive macro (subroutine with a loop) was needed.

Thanks for your help and i look forward to reviewing your suggested solution.