2025 Raw Material Inventory Management in Google Sheets
This Google Sheet is developed as a solution to automatically calculate the current raw material stock as well as determining how many units of each product you can make using the raw materials available.
This template is designed for Google sheets, but if you are looking for an Excel template, please visit the Manufacturing Inventory Tracker in Excel
Free Template Download
How to Create an Manufacturing Inventory Tracker in Google Sheets
- Make a Copy
- Enter the Input Data
- View the current raw material stock
Step 1: Make a copy of the sheet
Click on the link above and then make a copy of the sheet as shown in the screenshot below.
You can save the copy in your own google drive for your use. You can then share the copy with your colleagues to collaborate as needed.
Step 2: Enter the Input Data
Enter list of parts or products in the Products sheet as shown below
The Sales Quantity column is calculated based on sales orders that we will be entering soon.
Enter list of raw materials you use in your business in the Raw Materials sheet:
- Starting Inventory: In this sample data, we have 10 Apples and 10 Bananas in stock when we begin using the template.
- Re-Order Point: We have re-order points (Wikipedia Article on Re-Order Point) set for each raw material.
- Available Now: Displays the current stock level of each raw material.
- To Order: When current stock for a raw material goes below its re-order level, this field displays YES. Also, the template highlights the raw material name in red font.
Enter Bill of Materials (BOM) in BOM sheet:
In the sample above, 1 unit of Banana Berry Shake is created from 1 Banana, 5 Strawberries and 10 Blueberries. 1 unit of Apple Banana Shake is created from 2 Apples and 2 Bananas. Unit of Measure column is just for your reference.
Once you have entered BOM for all your products, the initial set up is done.
Entering purchase and sale orders in Orders sheet.
As a manufacturing business, you may buy raw materials from your suppliers and sell the finished products to your customers. So, there are two key transactions – Purchase and Sale.
We can enter Purchase and Sale Orders in a single table in Orders sheet.
- Order Type: Order P1 is to purchase 50 Apples and 20 Bananas – So, we use Order Type of PURCHASE. Order S1 is an order to sell 5 Banana Berry Shakes. We use SALE order type.
- Order Date: Date when the order is placed.
- Expected Date: Date when the inventory is impacted. For example, order P1 was placed on 3rd July, but raw materials will reach us only on 4th July. So, Order date is 3rd July and Expected Date is 4th July.
- Product or Raw Material Name: The template allows drop down selection for product / raw material name. If the order type is Purchase, it will allow Raw Materials and if order type is Sale, it will allow Products.
- Quantity: Enter quantity or units of items on the order
If an order has 5 line items, then enter as 5 rows.
Step 3: Check Availability
Before entering a new Sale order, if you would like to check the current capacity for a product, you can easily do that.
When you select a product, the template runs the calculations to figure out how many units of the product can be manufactured using the raw materials in stock right now. In the image above, we see that we can make 13 Apple Banana Shakes.
Limit
The template cannot handle complex scenarios where sub-assemblies of raw materials are raw materials to the final product
10 Comments
Hi. I am using this sheet past couple of months and loving it. I’m facing issue once I have crossed 1000 rows in ORDER sheet. Any entry I’m doing in new rows is not affecting the “RAW MATERIALS” sheet. Kindly guide for the same.
Thanks
Thank you for using our template and sharing your valuable feedback.
We have used named ranges, hence on adding 1000 more rows in Google Sheets, you will have to goto Data -> Named ranges to view the list of named ranges. Then you will have to edit all the named ranges referring to the sheet name that is expanded by you recently.
Hope this solves your requirement.
Best wishes.
Problem solved. Thank you so much!
Hi, The above solution provided solution in case of “Purchases”, but not for SALES. I have updated and checked all ranges, they are correct. Kindly guide what could be the issue?
Any Sales I’m making for the product is not affecting the raw material
I’m having an issue – in the Orders tab under “PRODUCT OR RAW MATERIAL NAME”, there is no dropdown menu. Can you send me the formula or data validation instructions to fix this?
Besides that, this is exactly what I needed, thank you!
Thank you for showing interest in our template.
Having a depended drop down dynamically for multiple rows is currently not possible in Google Sheet. Hence the same is not available in our template.
Best wishes.
Hello,
This template is great however I am having a similar issue. I am unable to affect my inventory that I input into my “raw materials” or “products” sheets by entering orders. Entering new purchase or sales orders with today’s date or a date in the past does not seem to update anything in the other sheets. Please advise!
Thank you!
Thanks for your interest.
Please share file or screenshots to support@indzara.com along with specific highlights of the orders that have issues. We will review and get back to you.
Best wishes.
It says limit – the template cannot handle complex scenarios where sub-assemblies of raw materials are raw materials to the final product. Let me know if you have a template without this limitation. Thank you.
Thank you for sharing your feedback.
Currently, we do not have a template to manufacture a product using another manufactured product as raw-material.
Best wishes.