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.
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
- 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.
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 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.
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.
The template cannot handle complex scenarios where sub-assemblies of raw materials are raw materials to the final product.
Related Inventory Management Templates
You may also be interested in...
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.
HOW TO USE THE RENTAL INVENTORY TRACKER
Overview of Steps
- Enter Assets and # of items in Assets sheet
- To enter a rental contract order, enter the order details in Orders sheet
- Check availability in Orders sheet before committing to the order (max 90 days window)
- To see current availability of assets, view Assets sheet
- To view at any time availability over a month, use Calendar sheet
Enter the list of assets or equipment in the Assets sheet.
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
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.
If the available quantity is negative, that indicates that there is not enough inventory.
This availability calculator can handle rental windows of up to 90 days long.
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.
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.
This will help you to be aware of how many items will be rented out on any specific day.
To view one order’s details, use filter on Order Number in Assets table
To view orders/assets with return date of today, use filter on Return Date in Assets table
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.
If you find the above template useful, please see Rental Inventory and Sales Manager, that provides accounting, invoicing, and reporting features for rental businesses.
You may also be interested in...
For small businesses, keeping track of the sales invoices and payments made by customers is a critical task. Use this Invoice Tracker template to keep track of all your sales invoices in one place. You can enter payments made by customers and let the template calculate balance outstanding amounts. Never miss an unpaid invoice. Download this invoice tracker spreadsheet free using the link below.
- Track all invoices in one place
- Identify which invoices are past due and how late they are
- Calculate how much amount is outstanding
- Calculate expected payments in immediate future
- Easy to identify due amounts by Customer
- Simple and easy to use
Microsoft Excel 2013 (or later) for Windows
HOW TO USE THE TEMPLATE
- Enter each invoice in the Invoice table beginning from row 15. (If you are new to Excel Tables, please read this article on data entry)
- Enter Invoice Number, Customer, Invoice Date, Due Date and Invoice Amount
- When Customer makes payment, enter it in Paid Amount column.
- Green colored columns have formulas. Do not edit.
Outstanding Amount: This is calculated as Invoice Amount – Paid Amount
Status: There are five possible values for Status.
- ‘ERROR”, when at least one of the fields Invoice Amount, Invoice Date, Due Date is left blank. Also, when Due Date < Invoice Date.
- PAID IN FULL: Outstanding Amount is 0
- CURRENT: If Outstanding Amount >0 and Due Date > Today (not due yet)
- DUE TODAY: If Outstanding Amount > 0 and Due Date = Today
- PAST DUE: If Outstanding Amount is 0 and Due Date = Today
- OVERPAID: If Outstanding Amount < 0 (i.e., Paid Amount is > Invoice Amount)
Note: Error rows are not used in calculation of metrics.
Rows with errors will be highlighted with a red border for your assistance.
Past Due Age: If the Status is ‘Past Due’, then this represents the aging bucket (1 – 30 Days, 31 – 60 Days, 61 – 90 Days, 91+ Days). Otherwise, it will be blank.
Selected: This will display 1 if the record (or row) is not filtered by the slicers.
The top of the Invoices sheet will present the summary in a dashboard as shown below. This will update instantly as you add more invoices to the table.
Let’s break it down in 3 sections.
Number of current (Due Date is in the future) invoices will be shown along with outstanding amount due.
Past Due Invoices:
Number of Past due (Due Date is in the Past ) invoices will be shown along with outstanding amount due. Accounts Receivable (A/R) aging breakdown is also provided.
Payments that are expected in the next 7 days (including today) and next 30 days (including today) will be displayed.
These metrics can be easily filtered using the 3 Slicers provided.
The invoice table will be filtered as well. One can use this feature to easily target selective invoices. For example, click on Status = Past Due, to see only invoices that are past due.
How to set up a different currency?
Press Ctrl+G and choose CURRENCY_CELLS.
Press Ctrl+1 and select your preferred currency symbol.
How to add a new column?
Adding columns is very easy. Just type any field name in cell K14. Then, type values for each invoice in column K from K15 down. You can add any number of columns.
How to sorting invoices by Due Dates?
It’s easy to use the default sorting capabilities within Excel. For example, right click on the due dates column and choose Sort option.
You can use sorting on any of the fields.
How to track partial payments from customers?
Sometimes your customer may make an invoice payment in installments. For example, a customer pays $300 invoice in 2 payments $100 and $200. Or a customer makes a payment for two invoices together. Please watch this video tutorial below that explains how to address these scenarios.
How to calculate Due Dates automatically?
By default the template allows manual entry of due dates. If, in your small business, there is a standard term used such as Net 30 or Net 45, then we can automate this calculation. To create Due Date always as (Invoice Date + 30), just type =[@[INVOICE DATE]]+30 in cell D15.
You may also be interested in...
If you run a small business selling products, download this Free Sales Invoice Template and create invoices easily in Excel. You can create unlimited invoices and manage them all in one file. You can save time by storing your products and customers once and re-using them for invoices whenever needed.
- Create professional sales invoices for your small business instantly
- Customize it to your need
- Customize customer data that appears on the invoice
- Change columns (2 columns are flexible) in invoice
- Discounts can be set as amounts or %
- Logo and other formats like colors or fonts can be changed as preferred
- Currency can be set easily to suit your business need
- Automatically calculate totals and subtotals
- Handles tax rates at order level
- Accommodates products that are not taxable
- Handles discounts at product and order levels
- Save time by storing customer and product data
The template is designed to accommodate 20 line items per invoice.
- Microsoft Excel for Windows (2010 and above)
- Microsoft Excel for Mac (2011 and above)
Download Sales Invoice Excel Template 2020
HOW TO CREATE INVOICES
Enter Business Information in Settings sheet
Enter Products in Products sheet
If you are new to Excel Tables, please read the article on how to use Excel Tables for data entry.
You can add columns or rename existing columns as you need. You can add more products anytime.
Enter Customers in Customers sheet
You can add columns or rename existing columns as you need. You can add more customers anytime.
Change Logo in Invoice sheet
Right Click on the logo image and choose ‘Change Picture. Select an image from your computer to replace.
If you don’t need a logo, just select the logo image and press the Delete key.
Customize Customer Information on Invoice
It’s easy to customize the appearance by selecting which field should appear.
7 fields can be customized. The list of information available in the drop down is based on columns in the Customers sheet.
Enter Tax Rate % in Invoice sheet
Enter Tax Rate in cell I50. You can change this later for each invoice if needed.
Apply Currency format in Invoice sheet
The template comes with no currency formatting by default as I would like the template to be useful in all countries. It’s easy to apply your currency.
In the Invoice sheet, press Ctrl+G. This opens a new dialog box.
Choose CURR and click OK. This will select all the cells that need to be formatted as currency. Press Ctrl+1.
After selecting and pressing OK, the invoice will have currency format applied.
CREATE AN INVOICE WITH INVOICE BUILDER TEMPLATE
With our initial setup complete, it’s time to create our first invoice. First, let’s make a copy of the Invoice sheet.
Rename the new sheet with the Invoice #. For example, for Invoice #1234, Let’s name the sheet 1234.
Now, we will create the invoice #1234.
Overview of Steps
- Enter Invoice # in cell J15
- Choose customer name in cell D17
- Enter Invoice Date in cell J18
- Enter Payment Due Date in cell J20
- Enter Product ID/Name, Quantity & Discount
- Enter Tax Rate% in cell I50
- Enter Order Discount Amount/% in cell I52
- Enter Other charges, if any, in cell J53
- Enter any comments or notes in cell C51
- Press Ctrl+P to preview & Print/export to PDF.
Steps in Detail with Screenshots
1. Enter Invoice # in cell J15.
2. Choose customer name in cell D17
Other customer information will automatically populate.
3. Enter Invoice Date in cell J18
4. Payment Due Date in cell J20
5. Enter Product ID/Name, Quantity & Discount
Choose Product ID from the list.
Description and Unit columns will automatically populate. If you would prefer some other attribute (or information) of products instead, you can just change it. How cool is that?
Enter Quantity and unit discount. The Price, Taxable and Amount will automatically populate.
You can choose to display either Amount Before Tax or Amount After Tax.
Continue entering products as many there are in the invoice. The maximum allowed is 20 in this template.
6. Enter Tax Rate% in cell I50
As we scroll down, we see the subtotal and total section. Here, we have two options. Enter Tax Rate% in cell I50.
7. Enter Discount
Next is Discount, where we can choose to use Discount Amount or Discount %.
If you choose Discount %, enter the value in decimals. For example, 0.01 for 10%. If you do not want to display any discount, just clear the cell.
8. Enter Other Charges
Enter Other Charges, if any, in cell J53. You can rename Other Charges to anything that applies. For example: ‘Shipping Charges’.
9. Enter Comments
Enter any comments or notes in cell C51.
10. Printing or Exporting to PDF
After reviewing and making sure everything looks good, it’s time to print or export.
Press Ctrl+P to preview print.
The page is set up to be print-friendly. So, you should be able to just print as it is.
To export to PDF, select the Export menu option.
In the following window, choose a location in your computer to store the PDF file and give a name to it. Tip: use your invoice number in the file name. Save all your invoices with the Invoice Number in the PDF file name in the folder. Thus, you will be able to easily identify a specific PDF if needed in the future.
Track invoices and due amounts in a simple free Invoice Tracker template
To manage inventory along with creating invoices, please visit Retail Business Manager
What if you sell hourly 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 Quantity column in the invoice. In the Products table, you will enter the Service name and provide the hourly rate as the Price.
What if it is consulting projects and not hourly?
You can also use this template if you work on consulting projects. You can enter your Project ID in the ID field, Project Name in Name field and enter Project description in the Description field. You can enter the project’s cost in the Price field. You can enter Quantity as 1.
What if you want to use Product Name instead of Product ID as unique identifier?
You can choose to user Product ID or Product Name or your own field as key identifier of products. Just select the field in cell C28 in the Invoice sheet. Now, you can enter.
If you choose Name, the drop down list automatically will use Product Name instead of Product ID. Everything works smoothly.
How can I change the colors, fonts or other formatting?
All the formatting options are available to you. Just select the cells you need to modify format and use Excel’s default formatting options (in the Home ribbon) to change fonts, colors, appearance, number format or alignment.
If you change the font size or font, you can adjust the column widths and/or row heights to ensure that the print view is good.
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 IDs/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.
For more small business templates, please visit Small Business Excel Templates
You may also be interested in...
Have you wondered if it would be handy to have a product catalog for your small business? Did you know that we can create the entire online product catalog or online store experience within Excel? Without payment processing, of course 🙂 You could use this to share your product info with your prospects and customers in an interactive tool.
This is how the product catalog/store appears in Excel.
For more features try our premium template Small Business Digital Catalog
If this sounds interesting, please read further on the functionality and for how to use this (hint: it’s so easy. Just enter your data and you are done). You can download this Excel template for free using the link below.
PRODUCT CATALOG – FEATURES
- Filter products by Categories
- Search products by keywords (on Product Name)
- Sort products by Price (Ascending or Descending)
- Scroll to see products in sets of 8
- Add thumbnail images to appear on Catalog
- Add links to website as needed on product detail sheets
- Catalog links to individual product detail sheets
- Format the catalog as needed with usual Excel formatting options
- Customize product detail pages as needed
HOW TO CUSTOMIZE FOR YOUR BUSINESS
Overview of Steps
- Unhide hidden Input_Data sheet. Enter product categories and product data for your business.
- In Product_Catalog sheet, change logo and tagline text to suit your business.
- In each product’s individual product detail sheet, rename the sheet to match the product name
- Update product image and highlights for each product
- Change formatting – colors, fonts, etc. as needed
Entering product data
Unhide Input_Data sheet.
Select Input_Data Sheet.
Now, the sheet should be open and visible.
Enter Product Categories you need. It is set up for up to 6. If you need only 5, just select the 6th category (cell A8) and hit the Delete key to clear value.
Enter Product Data in the Products table.
Click on a product thumbnail image and choose Change Picture. Select picture from your computer.
Enter product name, price and category.
This ends our product data entry process for one product. Repeat for all products. After you enter all your products, you can hide this sheet. Now, we are ready to customize the catalog/store appearance.
Customize Catalog Appearance
First, in the Product_Catalog sheet, change logo by changing picture as explained above. Change the tagline text to suit your business.
If you want to change the colors, fonts or any formatting, please select cells and change formatting as usual in Excel.
Customizing Product Detail Sheets
You will notice that in the catalog, there are links to product detail sheets. Each product has its own sheet where we have its image and description.
- Rename the sheet to match your product name. If your product name is Cool Product, please rename the sheet to Cool Product.
- Replace image and description with your products’ info.
- I have added two links, one to the product page on the website and the other to go back to Catalog. Replace the web link with your product web page link. You can remove the web link if you don’t need or have.
- (Optional) You can change formatting (colors, fonts) as needed. There are no formulas in the product detail sheet.
Repeat this step for each of your products.
Tip: to insert a bullet as I did in the product highlights, use Alt+0149.
Once you have completed all your product detail sheets, you are done. You can go back to the Product_Catalog sheet and your catalog is ready. Start. Sort, Search, Filter, Scroll. 🙂
You can export to PDF or print as all sheets are set up as print friendly.
- Designed by default for 30 products & 6 product categories. Can be extended if needed.
- Names of Products should match the Product Detail Sheet Names. This allows hyperlinks to function properly.
For more small business templates, please visit Small Business Management Excel Templates
As always, I look forward to your feedback to understand how this can be extended to suit more small business needs. If you have any feedback or suggestions, please leave them in the comments. Thanks.
You may also be interested in...
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)
Download Retail Inventory 2020 Template (Windows)
Download Retail Inventory 2020 Template with Sample Data (Windows)
Download Retail Inventory 2020 Template (Mac)
Windows and Excel 2010 (or above version)
Mac and Excel 2011 (or above version)
- 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
Click here to watch the video on YouTube.
HOW TO USE THE TEMPLATE
Enter list of products and re-order points in the Products worksheet
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 the line items for all the orders (both purchase and sale) in the Orders_and_Inventory worksheet
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
- 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.
- 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
If there are line items that cannot be fulfilled or if there are products to re-order, take actions appropriately
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.
The report sheet has slicers (filters) at the top.
Amount and Cumulative Amount by Month
Quantity and Cumulative Quantity by Month
Amount distributed across Product Categories by Month
Quantity distributed across Product Categories by Month
Amount Distributed across Partners
Product Ranking based on Sales Amount or Quantity
If you find the template useful, please share with others. If you have any feedback, please share it in the comments below.
You May also like the following Excel Templates:
RECOMMENDED RETAIL INVENTORY TEMPLATES
- Retail businesses with one warehouse location Retail Business Manager Excel Template
- Retail businesses with up to 10 warehouse locations, Retail Inventory and Sales Manager Template