Raw materials from finished products inventory management

driver_1980

New Member
Joined
Nov 14, 2020
Messages
4
Office Version
  1. 365
Platform
  1. Windows
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.
 

Some videos you may like

Excel Facts

Round to nearest half hour?
Use =MROUND(A2,"0:30") to round to nearest half hour. Use =CEILING(A2,"0:30") to round to next half hour.

Watch MrExcel Video

Forum statistics

Threads
1,118,801
Messages
5,574,406
Members
412,590
Latest member
Velly
Top