Raw Material Stock management using excel help

Icecream Man

New Member
Joined
Jan 20, 2019
Messages
2
I am currently trying to construct a table of raw materials that will automatically adjust based on information entered about productions.
The system currently consists of 3 excel sheets:
1. A general ledge of all finished product stock in and out. This consists of columns for date, product name, number of batches made and a description that defines what happened i.e. Production is used when product is made, sale with details when it is sold, etc.
This table works well as I have set it up to automatically record total stock levels for our 3 product lines.

2. Our product formulations. Simply a list of ingredients and quantities used to make one batch of the product. Consists of all raw materials in one column and the amount of that material used in a batch of product.

3. The raw material stock table. Consists of columns titled (raw material name, unit of measure, stock on hand and additional columns for other details.

My goal is to link these 3 tables so that the "stock on hand" column for raw materials will automatically update based on the information I enter in Table 1 when we produce product.

I.e. We make a batch of product and consume raw materials. In Table 1 I enter the date, product made, how many batches we made and the description "Production".
Instead of manually updating the "Stock on Hand" column in Table 3 for each individual raw material, I want to develop a formula that will identify the product made (Product name column in Table 1), identify the description that identifies a production (Description column in Table 1 reads "Production"). The formula then takes the formulation under the identified product name (Table 2), multiplies it by the number of batches and then subtracts it from the "Stock on Hand" column in Table 3.

Can anyone please advise how to achieve this?

I have tried a few different attempts using IF formulas with nested AND formulas but to no avail.

Appreciate the help.

Cheers
Icecream Man
 

Excel Facts

Why are there 1,048,576 rows in Excel?
The Excel team increased the size of the grid in 2007. There are 2^20 rows and 2^14 columns for a total of 17 billion cells.
Hi Icecream Man,
so for your raw materials you have a starting inventory from which you want to deduct if it is used by filling in finished product? How many different ingredients are there?

So going by your name:

1) products:
date - product - batches
22-1-2019 - vanilla ice cream - 2
21-1-2019 - strawberry ice cream - 3

2) formulations (table)
Name - ingredient - quantity
vanilla - milk - 6
vanilla - vanilla - 1
vanilla - sugar - 3
strawberry - milk - 6
strawberry - sugar - 2
strawberry - strawberry - 1

3) stock on hand
ingredient - start stock - usage in period - leftover stock
milk - 12 - ? - ?
vanilla - 10 - ? - ?
sugar - 10 - ? - ?

There are multiple routes you can take. A simple one is e.g. adding columns to sheet 1: one for each ingredient. Next, add a column to table 2 with a helper value, merging the product and ingredient, e.g. "strawberry_sugar" and "vanilla_sugar". In sheet 1, do a VLOOKUP on that table with the column header (the ingredient) and the row title (the product) to pick up the quantity needed per batch. See this for help: https://exceljet.net/formula/vlookup-with-multiple-critiera
Next, for sheet 3 you can use SUMIFS to pick up that data from sheet 1. Define for yourself what the period is, you can e.g. pick up all milk used in january, see https://www.mrexcel.com/forum/excel-questions/601474-sumifs-date-range-criteria.html on how to use that formula with date ranges.

Hope that gets you started,

Koen
The next step is to
 
Upvote 0
Thanks Rijnsent!
I've applied your suggested approach with some modifications to fit my current data layout and it has worked quite elegantly. Appreciate the help!

Cheers
 
Upvote 0

Forum statistics

Threads
1,213,546
Messages
6,114,256
Members
448,558
Latest member
aivin

We've detected that you are using an adblocker.

We have a great community of people providing Excel help here, but the hosting costs are enormous. You can help keep this site running by allowing ads on MrExcel.com.
Allow Ads at MrExcel

Which adblocker are you using?

Disable AdBlock

Follow these easy steps to disable AdBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the icon in the browser’s toolbar.
2)Click on the "Pause on this site" option.
Go back

Disable AdBlock Plus

Follow these easy steps to disable AdBlock Plus

1)Click on the icon in the browser’s toolbar.
2)Click on the toggle to disable it for "mrexcel.com".
Go back

Disable uBlock Origin

Follow these easy steps to disable uBlock Origin

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back

Disable uBlock

Follow these easy steps to disable uBlock

1)Click on the icon in the browser’s toolbar.
2)Click on the "Power" button.
3)Click on the "Refresh" button.
Go back
Back
Top