Raw Material Inventory Management 2025 in Excel
This free inventory tracker template is designed for small businesses which manufacture or assemble products from raw materials.
You can use this manufacturing inventory management excel template 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.
We can input simple Bill of Materials (BOM) where we define how we can manufacture a products from its raw materials. We let the template take care of all the calculations.
Free Download
Video Tour
How to track raw material inventory in Excel
There are five sheets in the raw material inventory management excel template: 1) Home 2) Products 3) Raw Materials 4) BOM and 5) Orders
Overview of steps
- Enter Products in Products sheet
- Enter Raw Materials in Raw Materials sheet
- Enter Bill of Materials in BOM shet
- Enter orders in Orders sheet after checking availability
- View Current raw material stock in Raw Materials sheet
Steps in Detail
I will use an example of a Juice/Smoothie company which creates juices from fruits & vegetables. However, this template can be used in any industry.
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.
Use this raw material stock register format in excel to handle all your raw materials availability.
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 Jan, but raw materials will reach us only on 4th Jan. So, Order date is 3rd Jan and Expected Date is 4th Jan.
- 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.
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
This free excel template for manufacturing company cannot handle complex scenarios where sub-assemblies of raw materials are raw materials to the final product.
Related free Inventory Management Templates
- Retail Inventory Tracker (Free)
- Rental Inventory Tracker (Free)
Recommended Template for Manufacturing Businesses
Manufacturing Inventory & Sales Manager (Inventory, Orders, Invoice, Sales Reporting, Customer Data Management)
178 Comments
Hi Indzara team,
we have raw materials and different combination of raw materials as assembled raw materials and then combination of different assembled raw materials as finished products, for example
A1, A2, B1 are the raw materials and combination of A1, A2 is C1(assembled raw material)
then combination of C1 and B1 is called D1(finished part).
so now my problem is how can I check the availability of C1 and also D1(combined with C1(A1, A2) ,B1 raw materiasl) on the top of orders page.
I hope you understand my issue, if you dont understand please let me know, I will be waiting for your reply.
Thanks for the details.
The template supports only one level of manufacturing/assembly – Raw materials being converted to products. I am sorry that your scenario of two levels is not supported.
Best wishes.
Hi Indzara team,
Some times we purchase 10 raw materials and only 7 will be usefull and remaining 3 will be defective products so this three will go to trash, so in this case how can I track usefull and non usefull raw materials count through this template.
To reduce the inventory of raw materials (due to wastage), please enter a purchase order for the raw material and use negative quantity value (for example: -5). This will reduce the raw material inventory accordingly.
Thanks & Best wishes.
Hi Indzara team,
Can I add extra column of tables near orange tables(at starting of the tables)? does it effect the template?
Yes, you should be able to add columns to the tables. Please do not edit any formulas in calculated columns. Thanks. Best wishes.
i downloaded sheets but it is blank
The template is ready to enter your own data. The calculations are built-in and once you enter your input data, you will see the results. Thanks. Best wishes.
Dear Sir,
We are trying to find a Template to track in-house office “Supplies Purchased, Issued & When to Reorder”. I have been searching for something so that we can enter our “Purchases” (i.e. safety goggles, gloves, band saw blades, etc..) Our Industry is in Steel Fabrication and we have a lot of in-house office/safety & shop supplies that we are purchasing & issuing to our employees on a daily basis. As of right now we have no system in place to track how much of each item has been purchased vs. what we have on hand and what needs to be reordered as well as Who, What, & When was it issued? Any suggestions on what Template to purchase if any would work for this? Your help & working Template to resolve our issue would be GREATLY Appreciated! Thank You!
Thanks for your comment. I have replied to your email address with some questions. Thanks & Best wishes.
this is good Ind. quick question. can you also develop and inventory system for an automotive industry or booking a service and maintenance template for automotive industry.
Thanks. I am sorry I don’t have such a template yet. Please subscribe to our social media outlets to be notified of new templates. Thanks. Best wishes.
Dear Sir,
Thank you very much for the efforts that you have put in making these trackers available for us. Your templates and trackers are very useful for us in various areas of our operations.
We are a small company trying to use this tracker for tracking goods issued and receipt in our store room. We generally procure raw materials and stock it in store room. In this tracker, it is recorded as ‘Purchase’ when goods are received and while goods are issued to indenting department, we record it as ‘Issue’ in place of ‘sales’. It would be a great support if you could to kindly help us with ‘Purchase Return’ and ‘Goods Return’ options where the inventory adjustments happens automatically.
Will be awaiting for your valuable response at the earliest.
Many thanks in advance!
You are welcome. Please try entering negative values in Quantity field in ORDERS sheet. This should create the reverse inventory logic. A sale order with negative quantity should increase inventory = Goods return. A Purchase order with negative quantity should decrease inventory = Purchase return.
Please let me know if there are questions. Thank you.
Dear sir,
I am interested with the Manufacturing Inventory & sales manager template, could you give me trial version on this template for analyzing whether this template suitable with my need or not.
If suitable, I will buy for your template
Thank you
Thanks for the interest. I don’t have a trial version, but the purchase is risk free. Please email me if the product does not meet your needs and I will issue a full refund.
Thanks & Best wishes.
Thank u so much
I have a problem that when i go to column F and G there is no balance showing. in Column F i put formula of subtraction in it then the results come out
But in column G no results found.
You are very welcome. Please email the file to indzara at gmail and I will be glad to take a look at the data. Thanks.
Thanks so much for the template. I am entering raw materials and when I got to line 41 the red text ended. How can I extend that feature?
You are very welcome. The red font indicating raw materials below re-order points should automatically extend in the table. If it is not, please check the Conditional Formatting rule (Home Ribbon –> Conditional Formatting –> Manage Rules –> This Table. You can view/edit the cell range accordingly. Thanks.