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.

Manufacturing Inventory Tracker - Free Inventory Excel Template
Manufacturing Inventory Tracker – Free Inventory Excel Template

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

  1. Enter Products in Products sheet
  2. Enter Raw Materials in Raw Materials sheet
  3. Enter Bill of Materials in BOM shet
  4. Enter orders in Orders sheet after checking availability
  5. 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

Inventory Tracker - Enter List of Products
Inventory Tracker – Enter List of Products

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.

Inventory Template - Enter Raw Materials used in Business, their starting inventory and Re-Order Point
Inventory Template – Enter Raw Materials used in Business, their starting inventory and Re-Order Point
  • 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

Inventory Template: Enter Bill Of Materials for each product
Inventory Template: Enter Bill Of Materials for each product

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.

Excel Inventory Template – Enter Raw material Purchase and Product Sale Orders
Excel Inventory Template – Enter Raw material Purchase and Product Sale Orders
  • 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.

Inventory Availability - Check capacity to make a Product
Inventory Availability – Check capacity to make a Product

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.

Manufacturing Inventory & Sales Manager (Inventory, Orders, Invoice, Sales Reporting, Customer Data Management)

178 Comments

  • Amazing!!!

    The only thing missing for me is the price of materials

    Reply
  • OK, my bad. I found my mistake and the Raw Materials sheet now shows the calculated reduction of raw materials due to SALE on Orders sheet. However, Products sheet still does not calculate Sales Quantity as of Today.

    Reply
  • I downloaded your Manufacturing Inventory Tracker template on June 29, 2019. I am having the same problem Urvashi noted on June 3, 2019, that is, entry of a SALE on the Orders sheet does not calculate reduction of raw materials inventory on the Raw Materials sheet for the manufacture of the product sold , nor does it calculate the number of the products sold as of today on the Products sheet. Please advise me when you have made corrections to the template so I can download a working version. I am very excited about this template – it will be perfect for my situation if you can get it working.

    Reply
  • please help for change Expected date: It only working with year 2016 not the updated year .
    Please suggest to resolve this query.

    Reply
  • Hi,
    We have a business of batteries, where we manufacture our own batteries which consists of many raw materials.
    So I tried the template and updated all the products, raw materials, BOM and orders.
    When update an order we expect raw material should be deducted automatically from raw material but it is not happening.
    Request you please suggest how we can do that.

    Reply
    • Thanks for using our template.
      Please ensure that the formulas and links are not broken. You may download a fresh copy of the sheet and enter the data. In case it still does not work, please email your issues along with a copy of your file to contact@indzara.com

      Best wishes

      Reply
  • Love this template. Is there a way to convert it to google sheets? In uploading it, some of the formulas are lost. Thanks

    Reply
    • Thanks for using our template.

      We have designed and tested our templates on MS Excel. Some features will not work on the Google platform.

      Best wishes

      Reply
  • Hello,
    In the orders sheet – is it possible to make a sale of the raw materials. I have a manufacturing business in which raw materials are also sold as spare parts.
    Thanks

    Reply
  • How to add even service, with Purchase and Sale in oders?

    Reply
    • Thanks for your message.

      Could you please elaborate more so that we can address the issues.

      Best wishes

      Reply
  • Hello. I just found your template and am wondering if you can help me figure out how to use for our business. We have live training seminars and need a certain amount of materials for each attendee, as well as just some materials that we have out that don’t necessarily count on each guest getting one, but for them to use while they are there. I could use your help in how to set this up. Is that something you might be able to work with me on?

    Reply
    • Thanks for your message.

      We are not accepting any customized projects now.

      Best wishes

      Reply

Leave a Reply

Your email address will not be published. Required fields are marked *