Raw materials from finished products inventory management

[driver_1980]

Hello

I'm a novice Excel user and could use a little help please. I've created a number of tables to create a stock management system for products but I need help creating a raw materials table that is linked to the products made. Please find as follows the stock management system I have created: -

Table 1, Finished goods table
Product Code, Product name, Price

Table 2, Raw materials table
Material Code, Material name, Price

From the finished goods list (table 1) I've created simple items produced (table 3) and items sold (table 4) tables using VLOOKUP formula referencing the finished goods list (table 1).
These tables 3 and 4 are as follows: -
Date sold/made, product code, product name, quantity, price.

Table 5 is an inventory table taking the sales and units produced from the previous tables and using the SUMIF formula to arrive at totals per product.
Table 5 has the following columns: -
Product code, product name, opening inventory, units produced, units sold, closing inventory.

So far so good. As I said at the start of this message, I need to create a table for raw materials inventory that responds to the finished items produced (table 3). For instance, if I have two products A and B
Product A requires the following raw materials: -
2 no. ABC123
2 no. DEF456
Product B requires
2 no. ABC123
2 no. GHI789

In table 5, the finished product inventory table, I might have produced 4 units of product A and two units of product B in a month.
How would I get a 6th table (raw materials inventory level) to count up the the individual components consumed as a result of the finished goods produced? I'm thinking of IF functions, SUMIF functions, referencing the raw materials table (table 2) but I'm really stuck.

Many thanks in advance for your help.