Matching Material Costs to Production in Chronological Order

Hi all,

This isn't a specific formula question, but an open question on how I might attack a work project I've been given.

I work in a factory and wish to create a sheet which, as automatically as possible, assigns price per kg to each kg of Finished Product made. The complexity then comes from having many products which use several materials at once, the order of consumption (the date when production used the material) and the varying price points of stock.

The one core assumption I would need to follow is that the OLDEST stock delivered into site is the FIRST stock to be selected for use in production at any given time.

I've created a simple example to demonstrate what I would need and expected outcomes - do you guys think of a methodology (I'm not afraid of formulas!) to attribute cost of materials for each Finished Goods kg in a automated or semi-automated fashion?

Any advice would be great, Jonny

Thank you for the response! I'm happy to learn the VBA nessacary for this process if it provides the most efficent method - its not my strong point but whatever works best.

How would you suggest using VBA here?

Thanks again

https://stackoverflow.com/questions/...ction-in-excel

Thanks for the link Tim - Im finding it quite tricky to Google the right terms when searching for this!

Ill review the link and see if I can understand!

Thanks again

Hi all,

I've seen the suggested approach on Tim's link, but my very limited knowledge of VBA means I can't read / adapt the VBA code to match my needs.

Would anyone with a understanding of the VBA being used in Tim's link be able to explain to me specifically how I would tailor the VBA to my workbook, and ideally explain the principles behind the VBA?

Thanks in advance!

one more udf

This may be useful ....

Thanks for the feedback everyone

Tim - on your workbook, I can see the macro is working perfectly - I'm still trying to understand the macro, even with your notes I'm struggling.

I had a question on the workbook though - if I wanted to take this to the next level, which would be having multiple ingredients both on the production table and the stocks table, is this relatively straightforward within the macro as it exists or does it require alot more work?

Appreciate the help everyone

Not a lot, but a bit