Calculate Total Raw Material qty required by one formula

[ajaydkulkarni]

Hi Experts

I have a classic question and I am unable to solve it without any helper column. I want to check with you if you have any possible answer.

I am attaching a spreadsheet as an example. I have given some sample and easy bills of materials to produce one unit of each product w,x,y & z. And raw materials required are RM-1,2,3 & 4 in different quantities for each product. Now question is by a single formula how can we arrive at raw materials required to produce certain qty of finished goods.

I have also calculated answers but looking for a good formula to make the model scalable for more complex scenarios.

Thanking you in advance

Rgds

Ajay