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
Greetings, I think I’ll like this product if I can get it to work…nothing seems to happen when I try to enter a Purchase or Sale. I am assuming that a purchase will be reflected across the sheets? Thanks Thomas
Please ensure that the data is entered inside the tables correctly. Article on Excel tables for data entry: https://indzara.com/faq-items/introduction-excel-tables-data-entry/
And please refresh using ‘Refresh All’ button in the DATA ribbon.
If there are further questions, please email file to support@indzara.com
Best wishes.
DEAR SIR,CHECK ORDERS AVAILABILTY NOT WORKING..
Please provide more details to explain. Email the file to support@indzara.com and clearly highlight the questions. More the details provided, easier it will be for me to answer.
Thanks & Best wishes.
Dear Sir,
Please tell me how add one more order type in this format?
You can add one more order type in the drop down by just adding the value in the Data Validation list. Then we have to implement what that new order type should do to inventory calculations.
Best wishes.
i need to classified shelf life of porduct in warehouse by expired date.. can you help me?
I am sorry. I don’t have such a solution. Best wishes.
I am a small manufacturing whereby I buy raw material and/or components and assemble them to form a finish product. I would like to add a certain amount of labor as well as overhead cost into the total cost when building the BOM and eventually become the cost of sale. Can that be done?
Hi
just need to know if you have done any work in progress for machineshop because I am interested in having one
Sorry, I don’t have any template specifically designed for machineshop. Best wishes.
Hi,
impressive work.
I would like to buy your spreadsheet but first I have few questions:
Can it be customised and edited? For example more sheets being added?
I am currently looking for a way to import sales out of a POS in csv format for stock depletion and sale report. Do you have anything in line with my needs?
Thanks for your interest.
Yes, the templates can be edited. New sheets can be added as needed. However, if you need the new sheets to integrate with the existing sheets, you would have to add the necessary formulas.
I don’t have any other templates that are set up to import POS data. Please let me know if there are any questions.
Best wishes.
Is it possible to add a column that supplies at what date the inventory level will be depleted based on the customer orders.
Hi, do u have a example of inventory related to safety match inventory where it shows also the detailed raw materials computation either by grams or kilograms..example I need to produce 10 boxes.
I am sorry. I couldn’t follow your question. Please provide more explanation.
For manufacturing inventory, I have only two – this template and the one at https://indzara.com/product/manufacturing-inventory-sales-manager-excel-template/
Best wishes.
Hi,
We are a manufacturing firm and purchase raw materials from many suppliers and then use that raw material to manufacture a final product and then sell that final product to different dealers.
Is this template useful in keeping a track of
– the raw material purchased vendor wise
– inventory
– ready stock
– dealers to whom final product is sold
Please see Manufacturing Inventory & Sales Manager template https://indzara.com/product/manufacturing-inventory-sales-manager-excel-template/ for more reporting and tracking by Suppliers and customers. Please let me know if there are any questions. Thanks. Best wishes.