Raw Material Inventory Management 2024 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)
174 Comments
hi i am starting a pvc solvents manufacturing unit. a number of products can be made from the same set of raw materials. i just downloaded your free management inventory spreadsheet for my understanding before making the purchase. it was simply superb. I have a few doubts.
1. i dont understand the colour codes in the product sheet. do they mean anything.
2. As i said, same raw materials are used in different products, but availability just shows for one product alone and rest as zero. (eg: pvc solvent 250 l & pvc solvent 500 ml are my products. but checking availability shows for 25oml alone and the other as zero)
3. I have entered sale of pvc 500ml but its not getting deducted in the raw material sheet.
kindly guide me pls
Thank you for showing interest in our template and sharing your valuable feedback.
1. Yes, the columns with green color contains formulas. Hence modifying or deleting it will have impact on sheet’s calculation.
Regarding 2 and 3, I am unable to replicate the highlighted issue in our end, please share your sheet to us at the below link to assist further:
https://support.indzara.com/support/tickets/new
Best wishes.
Best wishes.
Good day
Thank you for this free template.
I am struggling with the Purchases in the Raw Materials tab. When an order is placed for a Raw Material (Purchase) in the Orders tab then the Purchases column in the Raw Materials tab is not being updated. Similarly, when a sale is recorded in the Orders tab then it’s not updated in the Products tab under “Sales quantity as of today”
Please assist
Thank you
Thank you for showing interest in our template.
We regret the inconvenience caused but I am unable to replicate the highlighted issue. Please share your copy of the template to us at the below link to check further.
https://support.indzara.com/support/tickets/new
Best wishes.
Hello…thank you for sharing this template for free, specially useful for small and startup businesses. But 2 things pls:
1. I would like to establish value/amount of beginning and ending inventories of raw materials – can we add columns on the right without disrupting the formula of the template?
2. We tried using this template as a shared file in google sheet, how to correct parse errors in most of the data fields of the google sheet?
Thank you for your help/reply on this.
Thank you for showing interest in our template.
1. Yes, you can add columns to the end of the table without disturbing the sheet’s calculation.
2. We use advance Excel features that are not directly supported on Google Sheet and vice versa. Hence we have built a separate version for Google Sheet template and following is the link to the google sheet version of manufacturing inventory tracker template:
https://indzara.com/2020/04/manufacturing-inventory-tracker-free-google-sheet-template/
Best wishes.
Is there a way to link multiple lot numbers of the same raw material and have it reflect in the BOM and product availability
Thank you for showing interest in our template.
Yes, it is possible. We take customization projects for a fee. Please write to us at the below link for estimation:
https://support.indzara.com/support/tickets/new
Best wishes.
Hi,
Can 1 more feature be added to it wherein if I enter a sale order (for eg 50 banana milkshake) then the number of raw materials required to fulfill that sale order should be displayed (Available banana-25, Required banana – 25)?
Yes, it is possible but this has to be taken as a customization project for a fee. Please write to support@indzara.com for estimation.
Best wishes.
I downloaded the template and input data. However I would like to upload this to google drive so I can access it on the move. When I put the file in drive, none of the formulas work.
Is there a way I can use this on google sheets / drive?
Thank you for showing interest in our template.
Currently, our excel templates are directly not supported in Google Sheet. Hence we have developed the Google Sheet template separately. You can Make a copy of the template to your google drive from the below link:
https://indzara.com/2020/04/manufacturing-inventory-tracker-free-google-sheet-template/
Best wishes.
Just downloaded this workbook and trying to get to know it.
Why isn’t “Product” in the BOM sheet picked up from “Product” in the Products sheet? It seems that would make the most sense, connecting the two. Am I missing something here?
Thank you for showing interest in our template.
I have verified the template and the drop down is available to select product name from Product table in BOM tab. If you copy paste the cell into the product name which does not have the drop down instead of pasting as value, then the drop down will be removed.
If this is not your concern, requesting to share your sheet to support@indzara.com along with some screenshot highlighting your issue to check further.
Best wishes.
Hello, I am facing the same issue. The product in the BOM tab is not directly updating product name from the “PRODUCTS” tab. I hope there won’t be any issue in other values if I copy paste or manually write the product name in the BOM tab. Also, does your sheet work on Microsoft teams? Thanks
Thank you for showing interest in our template.
Currently, we have not made the products to auto-populate in BOM table since 1 product might require multiple raw materials to manufacture, in such scenario, the same product name has to be repeated again and again for each raw material to be mapped with the product along with its quantity.
I also would like to inform you that copy pasting the product name will not impact the sheet’s calculation, however I would recommend you to paste it as values to save the cells format.
Sorry, I have not tested with Microsoft Teams yet but if you are referring to Excel online, yes it works.
Best wishes.
Can this template be adapted for use in a pharmaceutical manufacturing company?
Thank you for showing interest in our template.
Yes, this template can be used for any company with fixed quantity of required raw material. But if you are manufacturing a product from a manufactured sub-product instead of purchased raw material, currently, this feature is not available in the template and we have planned to include the same in the next version of the template.
We also take customization project for a fee. You can share your requirement to support@indzara.com for estimation.
Best wishes.
Hello,
The Free Excel Inventory Template for Manufacturing Businesses is not downloading. Are you still offering a free template? Do you have another link to download?
Thank you
Victor
Thank you for showing interest in our template.
Yes, it is still available as a free template. I tried downloading the template and I am unable to replicate an issue. Requesting to share a screenshot of the download error to support@indzara.com to check further.
Best wishes.
I have been using the Manufacturing Inventory Tracking Spreadsheet and it works great. I ran into a problem when I created a new raw material gave it a starting quantity and then tried to insert it into an existing BOM. The spread sheet assigns a quantity to the raw material used for this item even though there is no history for this raw material. It also deducts the amount in the history used column from the available raw materials number leaving me with a negative available quantity. How can I correct this problem?
Than you for using our template and sharing your feedback.
Requesting to share your sheet with the highlighted issue to support@indzara.com to check further.
Best wishes.