Free Excel Inventory Template for Manufacturing Businesses

Posted on
Manufacturing Inventory Tracker - Free Inventory Excel Template

This free inventory tracker template is designed for small businesses which manufacture or assemble products from raw materials. You can use this 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.

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

 

FREE DOWNLOAD

Manufacturing Inventory Tracker – Excel Template

 

VIDEO TOUR

 

HOW TO USE THE INVENTORY TRACKER?

There are five sheets in the 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.

 

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 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.

 

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

The template cannot handle complex scenarios where sub-assemblies of raw materials are raw materials to the final product.

 

Related Inventory Management Templates

78 thoughts on “Free Excel Inventory Template for Manufacturing Businesses

  1. Hello sir, how to alter the recipe/formula as when in need. And it has to be effective throughout the process.

    1. Hello

      Please unlock the sheet using passowrd “indzara”.

      Thanks

  2. Hi Thank you for the template, I just want to know how I can show my raw materials, for a bakery, in kilograms, grams and ml?
    Thank you

    1. Hello

      Thanks for your feedback. Since you are using the metric system, you can standardize weight as kg and volume as liters, then enter data in decimals. Like 100 grams would be 0.10 kg and 500 ml would be 0.5 liters.

      Best wishes

    2. I would like to appreciate your template,it’s really helping us. I have a question on Raw material worksheet,may you kindly assist me with a formula to indicate Yes/No when raw material reach Re-order point.
      Kind regards

      1. Thanks for your feedback.

        In the raw materials tab, the current value and the reorder level can be mentioned. Once the inventory level goes down the reorder level, the last column would indicate if an order needs to be placed or not.

        Best wishes

  3. hi
    i should be thankful to to get free education from you as i trying so much watching a video

    1. You are welcome.

  4. I really like your writing style, excellent information, thanks for posting :
    D.

    1. Thanks

  5. Thanks for sharing such a wonderful sheet with everybody. It works really well and has almost all functions needed. It’s of great help and very user-friendly. Kudos!

    1. Thank you for your positive feedback.

      Best wishes

  6. Hi,
    Thanks so much for the template, it helps a lot. I only have one question:

    In the ORDERS tab, when I enter a new purchase order, it should give a list of RAW MATERIAL NAME (from the raw materials tab I suppose). However I only get the PRODUCT list, both when I enter Sales order or Purchase order. How can I adjust this?

    Also, can I see a total list of raw materials that have been substracted from the inventory in accordance with the sales orders?

    1. Thanks for using our template.

      Please send the file along with your comments to contact@indzara.com

      Best wishes

Leave a Reply

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