Help to calculate "Bill of Material" for products in nested layers...

Hi !

Got a really tricky logic to crack.

Keeping it short: I need to calculate how much rawmaterial that is required to produce a product. In this exmaple the E product.
However, The E Product is not composed of only rawmaterials (would be easy to sum up) but is composed of other refined products that is composed of other refined products that all have a mix of the rawmaterials.

Please see attached excel.
This kind of nested "Bill of Material" of products makes it difficult to calculate the core rawmaterial required.
I have tried to make it easy in the excel for the example.

There is a:
- Conversion table ----- SEE S6
- An input Table of what I want to produce ----- SEE B10
- and the Output (result) table ----- SEE I10 where all of this should calculate how many "rawmaterials" required.

How can this be solved with formulas or perhaps a data model ?

see attached example excel:


Much appreciate this support and tough nut to crack.

Production tables.xlsx

In concept it is (or appears to be) very similar to this ..

https://www.excelforum.com/excel-for...-bom-cost.html

how many variants are there of the Realtion table i.e how many finished products / how many material products (given the latter can include the former) ?

Give this a try: my results differ from yours: see sheet1 for my interpretation of the logic.

Please Login or Register  to view this content.

I have used named ranges so if this works then you need to provide a sample of how your real data is formatted.