I would appreciate advice on Best Practice for generating a Bill Of Materials for Products that include sub-assemblies.

I have a Products Table, with records for products such as:

- Cake

- Buns

- Trifle

I have a Product Items Table with the ingredients of each product.  For example, the Product 'Cake' could have:

- Butter

- Sugar

- Eggs

...etc.

 

The solution accepts a Sales Forecast for the range of Products and generates the appropriate MRP (Materials Requirement Plan) down to Ingredient level.  That all works perfectly.

 

The next stage that I am asking for advice on is that an 'Ingredient' could be 'Caramelised Chestnuts'.  That Ingredient is a sub-assembly - it requires the Purchasing Department to buy the ingredients 'Chestnuts' and 'Sugar'.

 

This is a really common requirement (the automotive industry must be full of such requirements, for example), so I am reluctant to try to implement a method without first checking what the current industry modus operandi is for such a calculation.  Can anyone offer advice on the principle I should be using to calculate down to Ingredient level?  I would like the solution to be extensible, obviously - hopefully working even if 'Caramelised Chestnuts' had an Ingredient that was itself another sub-assembly.

I have developed a 'Proof Of Concept' file that loops through Sub-assembly levels, but it is very complex, and I fear I have missed some fundamental principle that has long-before been established.

 

All suggestions gratefully appreciated.

Its not much, but you might want to take a look at my Bill-of_Materials example file on my website:

 

http://www.spf-15.com/fmExamples/