Site icon INDZARA

Raw Material Inventory Management in Excel

Manufacturing Inventory Tracker - Free Inventory Excel Template

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

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

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

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

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

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

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)

Exit mobile version