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

Free Inventory Management Excel template from ExcelChamps

Posted on

I am very glad to share a new inventory management template that my friend Puneet from ExcelChamps has published. If you are interested in a very simple-to-use template that uses forms to enter your stock in and stock out, you will find this one very useful. The Dashboard calculates the current inventory level and value automatically. The template also uses an attractive menu based interface which keeps the interaction simple and easy.

 

Excel Inventory Management Template - Dashboard
Excel Inventory Management Template – Dashboard

 

DOWNLOAD

To download the free template, please visit Inventory Management Excel Template (on ExcelChamps.com)

 

Related Inventory Management Templates

Free Excel Inventory Template for Rental Businesses

Posted on
Rental Calendar - Available Quantity

If you are a business owner of a rental business where you rent equipment or assets to your customers, you will find this template handy in tracking inventory. This Excel rental inventory management template can help you know how many items are in stock and how many have been rented out. In addition to knowing the current inventory, you can also determine the expected inventory in future based on future dated rental orders/contracts.

Use this free equipment rental software to get organized and be in control of your rental inventory. You can use this template for any rental business, such as equipment rental, furniture rental to parties or events, renting wedding items to weddings, construction equipment rental, audio video equipment rental, bike rental, sports equipment rental or book rental.

 

 

Rental Calendar - Available Quantity
Rental Calendar – Available Quantity

 

FREE DOWNLOAD

Rental Inventory Tracker Excel Template

 

VIDEO DEMO

HOW TO USE THE RENTAL INVENTORY TRACKER

Overview of Steps

  1. Enter Assets and # of items in Assets sheet
  2. To enter a rental contract order, enter the order details in Orders sheet
  3. Check availability in Orders sheet before committing to the order (max 90 days window)
  4. To see current availability of assets, view Assets sheet
  5. To view at any time availability over a month, use Calendar sheet

 

Detailed Steps

Enter the list of assets or equipment in the Assets sheet.

Enter Rental Equipment or Rental Assets Items
Enter Rental Equipment or Rental Assets Items

 

If you are new to Excel Tables, please read this article first: Introduction to Excel Tables.

Enter Asset Name, Description and # of Items you have of each asset. The last two columns are automatically calculated. When you begin, Rented Out will be 0 and all the items will be In Stock.

 

Enter Rental orders in Orders sheet

Rental Inventory Tracker - Enter rental orders details
Rental Inventory Tracker – Enter rental orders details

 

Enter Order Number,  Order Date, Asset being rented and Quantity being rented. Enter Rent Out Date (date when the asset needs to be given to customer) and Return Date (date when asset will be returned by the customer).

Use the Notes column to keep track of any information related to the order. For example, you can enter contact information of the customer.

 

Check Availability of Rental Asset

At the top of the Orders sheet is a simple availability calculator, which will provide the quantity available to rent for a specific asset given the rent out and return dates.

Check availability of Rental Items
Check availability of Rental Items

 

If the available quantity is negative, that indicates that there is not enough inventory.

Check availability of Rental Items - Not enough Inventory
Check availability of Rental Items – Not enough Inventory

 

This availability calculator can handle rental windows of up to 90 days long.

Check availability - Rental Assets - Window longer than 90 days
Check availability – Rental Assets – Window longer than 90 days

 

View Availability Calendar

The Calendar sheet provides a flexible and useful calendar that shows the availability of rental assets over 31 days. You can choose any start date for this calendar view. Also, you can choose to see Available Quantity or Rented Quantity. Here is a screenshot of Available Quantity.

Rental Calendar - Available Quantity
Rental Calendar – Available Quantity

 

The red colored cells indicate days when assets will not have enough inventory to cover the rental orders. You need to make sure that you re-do the rental order by working with the customer or may have to cancel the order.

 

Here is a screenshot of Rented Quantity.

Rental Calendar - Rented Quantity
Rental Calendar – Rented Quantity

This will help you to be aware of how many items will be rented out on any specific day.

 

ADDITIONAL FUNCTIONALITY

To view one order’s details, use filter on Order Number in Assets table

Filtering by Order Number
Filtering by Order Number

To view orders/assets with return date of today, use filter on Return Date in Assets table

Filtering by Rental Due Date
Filtering by Rental Due Date

 

By default, the Calendar sheet shows 25 assets. It’s easy to extend Calendar for more days and more assets. To unprotect Calendar sheet, use password: indzara. (Article on Unprotecting sheets). Then, just expand the table to more rows to include more assets.


RECOMMENDED TEMPLATE

If you find the above template useful, please see Rental Business Manager, that provides accounting, invoicing, and reporting features for rental businesses.

Rental Business Manager Excel Template
Rental Business Manager Excel Template

Announcing Manufacturing Inventory Manager – Excel Template

Posted on
Manufacturing Inventory Sales Manager Excel Template

Friends, I am glad to announce that I have published a new Excel template. The Retail Inventory and Sales Manager is the most popular premium Excel template at indzara.com. It is applicable to retail businesses, but not for manufacturing type of businesses.

Retail Inventory and Sales Manager - Excel Template
Retail Inventory and Sales Manager – Excel Template

 

The new Manufacturing Inventory and Sales Manager Excel template can be used in scenarios where you purchase raw materials from suppliers, manufacture products and then sell to customers. The template is simple and very easy to use. If interested, please see the product page. Early adopters will receive a discount. For a limited time, you can get a 40% discount.

Manufacturing Inventory and Sales Manager - Excel Template
Manufacturing Inventory and Sales Manager – Excel Template

This is the first version of this product. Please provide your feedback in the product support page so that I can understand what upgrades to make in the next version. Thank you for the support.

 

Manufacturing – Inventory and Sales Manager – Excel Template Support

Posted on

This is the Support page for the Manufacturing – Inventory and Sales Manager – Excel Template. Please post any questions related to this template in the comments below.

Product Page:

Manufacturing – Inventory and Sales Manager – Excel Template

Version History:

  • v2 published July 24, 2015
  • v1 published Apr 25, 2015

Useful Links:

 

Retail Inventory and Sales Manager – Excel Template – Support

Posted on

The Retail Inventory and Sales Manager is suited for managing inventory and sales if you are running a business of buying products from suppliers and selling to customers. This Excel document will assist in knowing the inventory levels of each product and understanding which products to re-order. You can also create invoices instantly and print or save as PDF. In addition, you can quickly view the purchases/sales patterns over time and identify the best performing products. 

 

REQUIREMENTS

You need a copy of Microsoft Excel to open this file. The product has been tested in 

  • Excel for Windows (2007 or 2010 or 2013)
  • Excel for Mac (2011) – Since Excel for Mac doesn’t support Slicers and Pivot Charts, Analysis sheet is not available. Analysis Details sheet provides tables but not the charts. Please e-mail at indzara at gmail if there are questions.


LIMITATIONS

The template is not capable of reading data from bar code scanners.
 
There are several formulas embedded in the document. When the size of your data approaches the limits of this template (2000 products and 50,000 line items), there is a 3 to 5 second lag while inserting new line item. After a new line item is inserted, everything else works instantly. If you notice a lag, you can save a copy of the current file and start entering new orders in a new file.
 

CLICK HERE TO GO TO PRODUCT PAGE WHERE YOU CAN BUY

SUPPORT TOPICS

1. Migrate data from free to premium version in less than 10 minutes

 

 

2. HOW TO CHANGE CURRENCY
In the ‘Invoice’ sheet, from the Home ribbon, choose Find & Select –> Go To.
Retail Inventory and Sales Manager - Excel Template - Change Currency
Retail Inventory and Sales Manager – Excel Template – Change Currency
Retail Inventory and Sales Manager - Excel Template - Change Currency
Retail Inventory and Sales Manager – Excel Template – Change Currency

Choose CURRENCY_CELLS

Press Ctrl+1 or click on the Number format menu.

Retail Inventory and Sales Manager - Excel Template - Change Currency
Retail Inventory and Sales Manager – Excel Template – Change Currency
In the Format cells dialog box, choose the currency you would like to use.
Retail Inventory and Sales Manager - Excel Template - Change Currency
Retail Inventory and Sales Manager – Excel Template – Change Currency
Now, the invoice will display the amounts in the chosen currency.

3. WORKING WITH EXCEL TABLES

The template has several Excel Tables (link to Microsoft help). If you type data in the row after the last row of the table, then the table expands and includes your new row of data automatically. For deleting rows from a table, select cells from rows that you would like to delete. Right Click and select Delete—> Table Rows. This is the recommended way of adding and deleting data from tables.

Retail Inventory and Sales Manager - Excel Template - Delete Rows in Table
Retail Inventory and Sales Manager – Excel Template – Delete Rows in Table
If you have any questions about this Excel template, please leave a comment and I will respond as soon as I can. Thank you.

Invoice Builder (Basic) – Free Excel Template

Posted on
This is a simple template that helps in creating invoices for businesses. If you are running a small business, you will have the need to create invoices. However, you don’t need a sophisticated and expensive software. If your requirements are specific and simple, this template may help. 

Update (July 2016):  A new and improved version is available now. Please visit this page to download the latest free sales invoice template. 


Features

  • Creates a basic invoice instantly
  • Calculates total amounts automatically
  • Enter product information once and avoid repeated data entry
  • Enter customer information once and avoid repeated data entry
  • Allows taxes to be added to the invoice
  • Can use for products and/or services
  • Up to 20 items in an invoice
  • Print or save as PDF and e-mail
  • Can be used to create multiple invoices and store them
  • Use with any currency

 


 

Invoice Builder Excel Template - Invoice
Invoice Builder Excel Template – Invoice

VIDEO

 

 

 


Preparation

    • Complete the Your Information section in the Settings worksheet
Invoice Builder Excel Template
Invoice Builder Excel Template
  • Enter information about Products in the Settings worksheet.
Invoice Builder Excel Template - Products
Invoice Builder Excel Template – Products

Delete the sample data in the template by selecting all the rows, Right Click and Choose Delete Table Rows.

Invoice Builder Excel Template
Invoice Builder Excel Template
    • Enter information about Customers in the Settings worksheet. Delete the sample data in the template.
Invoice Builder Excel Template - Customers
Invoice Builder Excel Template – Customers
  • Change Logo in the Invoice worksheet: Click on the picture and right click on your mouse. Select Change Picture. You can choose any of the images in your computer to use as logo. You can also resize the image however you like.  
  • Invoice Builder Excel Template - Logo
    Invoice Builder Excel Template – Logo
    • You can also just click Delete if you don’t need a logo
 
Steps to create an invoice (Invoice Worksheet)
  1. Delete the sample invoice items (Product, Qty) in the template before you begin. 
  2. Enter Date 
  3. Enter Invoice #
  4. Choose Customer from drop down menu
  5. Choose Product Name
  6. Enter Quantity
  7. Repeat steps 4 and 5 for each product
  8. Enter Other Charges if needed
  9. Enter Comments if needed
  10. Review the invoice
  11. Print or Save as PDF

Invoice Builder Excel Template - Invoice Number
Invoice Builder Excel Template – Invoice Number

Invoice Builder Excel Template - Invoice
Invoice Builder Excel Template – Invoice

Invoice Builder Excel Template - Invoice
Invoice Builder Excel Template – Invoice

Tips:
  • Changing currency
    • In the Settings worksheet, choose the cells in the Unit Price column and change Number format (Currency). 
    • Invoice Builder Excel Template - Invoice
      Invoice Builder Excel Template – Invoice


      In the Invoice worksheet, choose the cells in the Unit Price and the Amount column and change Number format (Currency). Also, change the Number format for the Total, Tax, Other Charges and Grand Total. 

  • Using the Template for services 
    • You can also use this template for services that you may charge hourly. In such cases, you will enter the number of hours in the Qty column in the invoice. In the Products table, you will enter the Service name and provide the hourly rate as the Unit Price. Please see the image below for how I have entered labour cost as a Product.
Invoice Builder Excel Template - Invoice
Invoice Builder Excel Template – Invoice
  • Using the Template for Projects
    • You can also use this template if you don’t sell products but work on consulting projects. You can enter your Project ID in the Product ID field and enter Project description in the Product Name field. You can enter the project’s cost in the Unit Price field. If the Product Name is long, it may appear incomplete in the Invoice sheet. Please adjust the row heights to make sure you can see the entire text.  
  • Storing invoices in the Excel Workbook
    • After you finish the Preparation steps (outlined above), make a copy of the invoice worksheet. You can create any number of copies of the worksheet. When you are ready to create an invoice, rename that specific worksheet with a unique name. I recommend using invoice numbers if you use invoice numbers. By this method, you will be creating invoices instantly from this template, and also storing copies of each invoice all in one Excel workbook. 
    • Invoice Builder Excel Template - Invoice
      Invoice Builder Excel Template – Invoice
    • Invoice Builder Excel Template - Invoice
      Invoice Builder Excel Template – Invoice
  • How to sort data in Product list
    • When you have a lot of products, it might become challenging to choose from the drop down menu in the invoice worksheet, especially if the product names are not sorted. By default, the order in which you entered the products in the Products table will be used as it is in the drop down menu. That means you could sort the products in the Products table and this would automatically give you sorted products in the drop down menu. This should help you in easier selection of products in the invoice. 
    • Choose the cells in the Products table, Right Click –> Sort.

      Invoice Builder Excel Template - Invoice
      Invoice Builder Excel Template – Invoice
    • You can also sort Customers using the same method in the Customers table.
  • How to Delete data in invoice and start over
    • Delete rows in Products Table. Use the method explained above in this post. 
    • Delete rows in Customers Table. Use the method explained above in this post. 
    • Delete Date and Invoice # information in the invoice.
    • Delete Product and Qty information in the invoice. 
    • Delete any Comments entered in the Comments box.

 

I hope you find this basic Invoice Builder template useful. I look forward to your feedback. I would also love to hear how you use this template, if you find this useful.

Inventory and Sales Manager – v2 (for 2000 products)

Posted on
Several of you have left comments or e-mailed me asking for the Inventory and Sales Manager template to support more than 100 products. I have posted a new version (v2) of the template that will allow 2000 products. All the other features remain the same. Please visit the original post to download the new version. The Template file and the Sample data file for v1 have been replaced with the files for v2. 

Please try the new version and let me know your feedback.

Inventory and Sales Manager (Free Excel Template) for Small Business

Posted on
Inventory & Sales Manager Excel Template - Summary Metrics

This Inventory and Sales Manager Excel template is suited for managing inventory and sales if you are running a small business of buying products from suppliers and selling to customers. (Retail/Wholesale).

This retail inventory excel template will assist in knowing the inventory levels of each product and understanding which products to re-order. Also, you can quickly view the purchases/sales patterns over time and the best performing products.


For Retail businesses, a new version is available Retail inventory Tracker (Free)

For Manufacturing businesses, Manufacturing Inventory Tracker Excel Template (Free)

For Rental businesses, Rental Inventory Tracker Excel Template (Free)


Inventory Spreadsheet - Summary Metrics
Inventory Spreadsheet – Summary Metrics


FREE DOWNLOAD


FEATURES

  • Enter and manage up to 2000 different Products
  • Set custom re-order points for each product
  • Simple and Easy data entry
  • Know current inventory levels of each product
  • Identify the products to be re-ordered
  • Know if the sale orders can be fulfilled
  • Easily understand the sales and purchase patterns (monthly and cumulative)
  • Quickly see your top customers and suppliers
  • Identify your best performing products
  • Know how the different product categories contribute to sales
  • Easily retrieve and view your order details

 

VIDEO DEMO

Click here to watch the video on YouTube.


HOW TO USE THE TEMPLATE

Enter Products

Enter list of products and re-order points in the Products worksheet

Excel Inventory Management - Products Table
Excel Inventory Management – Products Table

 

Product Category: This allows you to categorize products. If you have numerous products, categorizing similar products together can help in understanding product performance.

Re-order Point: Amount that you set for each product, where when the current inventory level hits that amount, you will place a new purchase order to replenish inventory. (For more, read Re-Order Point Article in Wikipedia)

 

Enter Orders

Enter the line items for all the orders (both purchase and sale) in the Orders_and_Inventory worksheet

Inventory Sheet - Enter Order Details
Inventory Template – Enter Order Details

 

If you have any existing inventory when you start using the template, enter them first. You can then continue to enter your new orders (purchase and sales) as they happen. The template will then give you accurate count of your inventory.

 

  • Order Number: This Order number is not used in the template to calculate anything. This has been provided for you to track your orders easily. You can filter the Orders table by choosing specific order number to see all the items in that order. If your systems generate any order numbers, you can enter them here. If you don’t have any such systems, you can create your own. The only recommendation is that you should have a unique order number for each order.
  • Order Type: There are two types of Orders: Purchase and Sale. When you place an order to acquire products from suppliers, it is called a Purchase order. When your customer places an order to buy products from you, it is called a Sale.
  • Order Date: For Purchase orders, this is the date when the order is placed by you to your supplier. For Sale orders, this is the date when the order is placed by your customer to you.
  • Expected Date: For Purchase orders, this is the date when the inventory becomes available for you to sell. For Sale orders, this is the date when the inventory will leave you to the customer.
  • Partner: For Purchase orders, your supplier is the Partner. For Sale orders your customer is the Partner.
  • Quantity: Number of units of products. The unit can be any numeric value. Even if your unit is not whole numbers, you can still use the Quantity field.
  • Unit Price: In Purchase orders, this is the cost of buying one unit of the product. In Sale orders, this is the revenue from selling one unit of the product.
  • Amount (Calculated field): (Unit Price X Quantity) = represents the amount of money. In Purchase orders this would be money leaving you and in Sale orders, this would be money that customers pay you.
  • Inventory Availability (Calculated field): This is the quantity (number of items) of the product available in inventory as of the Expected Date.

 

View information about overall inventory availability

Inventory Spreadsheet Excel Template - Summary Metrics
Inventory Spreadsheet Excel Template – Summary Metrics
  • Current Inventory of a product = (Total Purchases of Product – Total Sales of Product) as of today
  • Products Available: Number of Products where the current inventory level is greater than 0.
  • Quantity: Total Number of items of all Products currently available
  • Products to Re-order: Number of Products where the current inventory is less than or equal to the re-order point
  • Order Items that cannot be fulfilled (Current): Among the orders where the fulfillment date is less than or equal to today, number of line items in orders where the available inventory is less than the Sale quantity
  • Order Items that cannot be fulfilled (Future): Among the orders where the fulfillment date is in the future, number of line items in orders where the available inventory is less than the Sale quantity

 

View details of one specific product

Choose a product from the drop down and see details of that specific product.

Choose Product to view current inventory
Choose Product to view current inventory

 

  • Pending Purchase Quantity: Quantity in the Purchase Orders that are expected to be available in the future
  • Pending Sale Quantity: Quantity in the Sale Orders that are expected to be delivered in the future

 

View products to re-order

List of Products to order
List of Products to order

 

If there are line items that cannot be fulfilled or if there are products to re-order, take actions appropriately

 

View Report

View the Report worksheet to understand the purchase/sales trends and also to identify the top performing products and most valuable suppliers/customers.

Since there are pivot tables and charts, please refresh the data by pressing Ctrl+Alt+F5 or going to DATA ribbon and selecting Refresh All. This updates the charts with your new transactions.

Excel Spreadsheet - Data Refresh
Excel Spreadsheet – Data Refresh

 

The report sheet has slicers (filters) at the top.

Sales Report with Filters/Slicers
Sales Report with Filters/Slicers

 

Amount and Cumulative Amount by Month

Inventory and Sales Manager - Report - Amount and Cumulative Amount
Inventory and Sales Manager – Report – Amount and Cumulative Amount

 

Quantity and Cumulative Quantity by Month

Inventory and Sales Manager - Report - Quantity and Cumulative Quantity
Inventory and Sales Manager – Report – Quantity and Cumulative Quantity

 

Amount distributed across Product Categories by Month

Inventory and Sales Manager - Template - Report - Amount by Product Category
Inventory and Sales Manager – Template – Report – Amount by Product Category

 

Quantity distributed across Product Categories by Month

Inventory and Sales Manager - Template - Report - Quantity by Product Category
Inventory and Sales Manager – Template – Report – Quantity by Product Category

 

Amount Distributed across Partners

Inventory and Sales Manager - Report - Amount by Partner
Inventory and Sales Manager – Report – Amount by Partner

 

Product Ranking based on Sales Amount or Quantity

Inventory Sheet - Excel Template - Report - Product Ranking
Inventory Sheet – Excel Template – Report – Product Ranking

 

If you find the template useful, please share with others. If you have any feedback, please share it in the comments below.

 

RECOMMENDED RETAIL INVENTORY TEMPLATES

Retail Business Manager - Excel Template
Retail Business Manager – Excel Template