Conducting a physical inventory count is a tedious, but essential business process. This guide gives you the tools you need to efficiently handle a physical inventory count, and get you started on a process that works for you. You can also download our free inventory templates and follow our quick tips on how to use them.
Free Inventory Templates
Since product tracking needs differ, inventory templates are rarely one-size-fits-all. While some products just need a basic quantity count, others will require variations in size, color or both for accuracy. Our list of inventory templates will work for most retail and wholesale product inventory counts. You can print them or even work on the spreadsheet from your mobile device.
Inventory Template (click to open in Google Drive or send to yourself for later) | Includes |
|---|---|
| |
| |
|
You will also find a purchase order template in the inventory workbook you can use once you’re done with your inventory count. Our guide below will show you how to use the templates.
Alternatively, you could set up a point-of-sale system (POS) to automate inventory management. An end-to-end POS system like Square for Retail helps small retailers handle sales and inventory counts plus get real-time insights on product performance and profits. You can set up a free Square for Retail account in minutes. Visit Square for Retail to get started.
1. Inventory Management Workbook
For retailers and ecommerce business owners who are looking for added automation and reporting functionality in their physical inventory counting process, the above mentioned inventory management workbook is your best choice. It contains everything from physical inventory count templates to inventory performance calculators. A step-by-step instruction is also included in the workbook.
To use, print, or modify the workbook, first make a copy. From there, you can save it to your own Google Drive, export to Excel, or print. Below we walk through how to use each element of the workbook. Each letter represents a different tab on the bottom of the spreadsheet.
A. Inventory List Template
This inventory list template is the main hub for this workbook. This is where users add product records and specify product code, markup and reorder point for each product, while the built-in functions are designed to compute for stock level and retail price. Use this template with Google Spreadsheet and you can collaborate with others to conduct your physical inventory count.
Customize the options headers and add your stock counts for each or leave it blank if not needed.
Once you’ve specified your reorder point, the second half of the inventory template will pick up the information from your purchase order record and sales record to determine your total stock on hand. If the total stock-on-hand falls below your reorder threshold, the workbook will tag this record and include it in your low stock report.
Use this template with Google Spreadsheets to access and input data directly from your tablet.
B. Supplier Contact List
Your supplier contact list will help you prepare your purchase orders accurately. You only need to have this encoded once and updated regularly for new information. Your purchase order template will generate supplier contact information based on this list.
Supplier Contact List Template
C. Purchasing Records
This tab creates a record of all your purchases and provides two functions. First, it adjusts your stock-on-hand total in the inventory list tab by recording inventory that has yet to arrive. Second, it allows you to generate a list of purchase order reports using the floating record filter pallet.
This purchase record template displays your existing products in a drop-down list.
D. Sales Records
This sales record template offers similar functions with that of the purchase record template. It updates your on-hand inventory as shown in your inventory list to reflect sales of items that have left and not yet left your store. Your available products also appear in a drop-down list.
Use the floating funnel pallet to generate reports in your preferred date range.
E. Low Stock Reporting
The low stock reporting section creates a list of products that are below your specified reorder point. All you will need to do is to refresh the filter to reflect any update on the stock inventory levels. Do this by clicking on the funnel icon under column F, scroll down then click on the option “Reorder” twice.
You can also sort the list by supplier name to help you prepare your purchase orders.
F. Purchase Order Template
As with the rest of our templates, this version of our purchase order template is linked with the information you have provided elsewhere in the workbook. A drop-down list is available for products available in your inventory template, and auto populates the cost price for you. It will also automatically compute for total cost per item, and per order after deducting specified discounts (if any).
Choose your supplier name from the drop-down list and the template will generate the contact details for you.
Alternatively, we also have a free simple purchase order template that you can download anytime. This works best if you prefer to use the basic inventory templates instead of the workbook. Learn more with our purchase order template instructions.
G. Inventory Performance Calculator
This inventory performance calculator is a free tool that will help you compute your inventory shrinkage rate and inventory turnover ratio. Your inventory shrinkage rate tells you just how much inventory you’ve lost for various reasons. In contrast, your inventory turnover ratio tells you how efficiently you have converted inventory into sales for a specific period.
Knowing how your inventory flows will help you arrive at better purchasing decisions.
2. Basic Inventory List Templates
This workbook contains five different templates in different tabs for counting your on-hand stock. To use, print, or modify the templates in the workbook, make a copy of the file. From there, you can edit in Google Sheets, export to Excel, or print.
The first tab has the most basic template for businesses that just need a quick counting list for just a few items. The second tab had columns dedicated to shelf and stockroom quantities, which is ideal for mom and pop retailers that have a small storage room.
The next template is for counting by the case – ideal for warehouses, liquor stores, or other businesses that have large quantities of the same products. The templates on the last two tabs have options for tracking variants such as multiple sizes or colors of the same product.
Below are instructions for using each tab. Each letter corresponds to a different tab in the workbook.
A. Simple Quantity-On Hand
This is the easiest inventory count template and works for stores stocking a variety of items that sell by the piece or case unit. The form lists product names and numbers, and leaves a space for the counter to enter the quantity-on-hand (QOH) for each item or case unit. Simple!
Simple Quantity-On-Hand Inventory Template
B. Total Quantity-On-Hand
This is the same concept as the Simple Quantity-on-Hand template, but it adds columns for a stockroom count and a grand total. This is best for stores that carry overstock in a storeroom and also stock items on store shelves. With this inventory template, you count products in the stockroom and on store shelves separately, then total them up.
Total Quantity-On-Hand Inventory Template
C. Case Count Inventory List
This inventory template is best for stores that stock items by the case, but sell items by the piece. This is very common in ecommerce, where items are often stocked in their case packs, then picked singly to fill orders. It’s also useful for stores that carry lots of overstock. With this template, you count cases, note the case quantities (12, 24, etc.), then count any individual units on the shelf. Then you add up the total quantity-on-hand after counting is done. This way, counters can just keep counting and not worry about doing the math.
Case Count Quantity-On-Hand Inventory Template
D. Single Variant Option
This inventory template is best for counting goods that have one option, like size. Products such as cleaning items, snacks and pet food, while having available variants, are classified according to size. You can update the size options on the headers for the template or expand by adding additional columns if you need more.
Single Option Inventory Template
E. Multiple Variant Options
This inventory template easily handles counts for products that have two options, like color and size. Retailers that deal with apparel will have a variety of sizes and colors for T-shirts, pants, and shoes. So would stores that sell anything from household items to school supplies. Customize the template headers with your own naming conventions for size or expand by adding additional columns if you need more.
Multi-option Inventory Template
3. Liquor-Bar Inventory Template
For store or restaurant owners selling beer, wine, spirits, and mixers, keeping a tab on supply requires a more detailed inventory count process. The ease or complexity of conducting bar inventory will depend on your business size. If you only have one venue or store, using a spreadsheet will do the trick for maintaining inventory count.
Liquor-Bar Inventory Template
However, if you have plans on expanding your business as a whole, we recommend using a cloud-based point-of-sale (POS) like Revel POS to help maintain liquor and bar inventory. Revel lets you input and track inventory from the moment it is ordered then adjust your inventory count in real time as products are rung into the POS.
How To Customize or Prefill these Inventory Templates
Here are three ways you can customize or prefill these inventory templates to make your counts faster:
1. Enter data directly into the template
For any of the above templates, you can prefill fields like product name, SKU number, colors, etc., if you wish. Simply type this data into the appropriate fields in the inventory template.
2. Copy and paste data from an existing inventory list spreadsheet
Do you already have an inventory list in a spreadsheet? You can simply copy and paste needed column data from your master spreadsheet into the inventory template. This way, you don’t alter your original inventory list. You also can add new columns or relabel the column headers in the template any way you wish.
3. Download data from an inventory management system
Do you use an inventory management system but want to create a customized inventory count form like these? Most systems let you download a .csv file of your inventory, which you can open in a spreadsheet, then customize by creating the columns needed for your count. Or you can copy and paste the needed data from your master spreadsheet into the inventory template.
Now that you’re familiar with how inventory lists work for physical counts, let’s see how to tackle the inventory counting process.
Inventory Count Processes & Best Practices
Most stores conduct two types of physical inventory counts: annual counts and periodic cycle counts. Here’s the tickler; if you perform regular cycle counts, your annual count is a no-brainer. If you don’t do cycle counts, annual counts are a big deal indeed. And often a big pain, to boot. Counting physical inventory is a chore, even for the smallest and most organized stores. But it must be done, so let’s explore ways to make your efforts worthwhile, starting with how often you should count your inventory.
Annual Counts & Cycle Counts
Annual Counts – This is your full inventory count of everything in stock, performed once each year, usually right at the close of the fiscal year for tax purposes. If you only do annual counts, there’s a good chance you’ll have to deal with a stack of issues like miscounts, misplaced inventory, shortages, overages, etc. In fact, these inventory irritations are likely to pop up throughout the year, costing you time and sales. This is where cycle counts can help tremendously.
Cycle Count – Your cycle count is simply a partial inventory count performed on a rotating basis. Usually, you count just a few product lines or a single category each week, so the chore is tiny and easy to fit into weekly tasks. The main benefit of cycle counting is that you discover small issues, like stock shortages or misplaced overstock, before they become giant, sales-affecting headaches. Another benefit is that your entire inventory is tidied up year-round, so when annual counts roll around there are few, if any, surprises.
Preparing for Inventory Counts
Whether you’re performing an annual count or a cycle count, the inventory prep work is the same. Although, if you do cycle counts, you’ll discover the prep work is faster because your inventory is managed regularly. Here are the general steps:
1. Print off inventory count forms
Before you count, you’ll need to print off an inventory count form using the templates above, or use one from your computerized inventory system. Some advanced systems, like Lightspeed, even have bar code scanners to make the process even faster. For annual or large cycle counts, you can organize the count by product lines, store location, or product categories; whatever works best for your shop, staff, and count helpers.
2. Tidy the inventory to be counted
For annual counts, this means combing through your store, stockroom, or warehouse; wherever your inventory lives. Make sure that all items are organized and located in the correct spots. Also make sure that overstock goods are accessible and countable. Some stores and ecommerce companies even pre-count large blocks of overstock items at this stage. Again, do whatever works for your operation.
3. Set a cutoff point
This is most important for annual counts when your operation really needs to shut down for a day or so to do the job right. Once you reach your cutoff point, all receiving, sales, and other activities affecting inventory stop. These don’t start again until the inventory count is complete and any spreadsheets or computerized systems are updated with correct count numbers.
Inventory Counting & Adjusting Procedures
Once it’s time to count, the process is pretty simple. If you’re a small shop, you, your staff, or a few friends should be able to knock it out in a day. Larger stores or warehouses might have a late night or even a two-day stretch. Here’s how to tackle it:
Assign counters specific areas or product lines, and start counting
I prefer to count inventory in teams of two. One person counts while the other writes and double-checks as needed. Counters certainly can operate alone, but teams are the preferred method used by most businesses, large and small.
Spot check for accuracy
It’s always a good idea to have those that didn’t count a certain line or category spot check for others. We usually spot-checked two to 5% of the counts just to be sure everyone was on top of things while counting.
Make adjustments and resolve any count issues
If you use a computerized inventory list, you’ll need to adjust your spreadsheet or system to match the new physical counts. If you track inventory manually, you’ll need to adjust the counts in your inventory records so you can prepare an inventory valuation for tax purposes. You’ll also need to resolve and account for any major discrepancies, like short stock or overages.
How To Computerize Your Inventory To Fit Your Needs and Budget
There are many ways to manage inventory using your computer. Heck, there are even mobile apps that let you manage stock counts and inventory these days. Several top options are low-cost and quite simple to use, so there’s really nothing to stop you from considering a computerized inventory system. Some are geared specifically for retail stores, others for ecommerce companies, and still others combine the two. One is sure to meet your business’s needs and budget.
Here’s a look at several top inventory management systems and the types of businesses they best fit. You might try one of these on for size if you don’t already use computerized inventory, especially since most give you a free trial.
Solution | Best for | Price |
|---|---|---|
Google Document or Spreadsheet | Making custom count forms or tracking small inventories manually | Free |
Small retailers needing a POS and business management system | Free | |
Retailers with larger inventories | ||
Any small business. Less expensive than QuickBooks with comparable features. | ||
Any small business. Less expensive than QuickBooks with comparable features. |
Bottom Line
Counting inventory with a POS system or simple-to-use forms are the first steps in creating an accurate inventory list for managing your stock. The inventory templates and processes listed above will get you started on the right track. Once you have an inventory list with accurate counts, use it as a clean slate for inventory control. And if you’re not already tracking inventory through a modern, computerized system, now is the time to start.
Sakkie v/d Merwe
I’m looking for an Excel Stock count sheet, which includes Item Nr, Description, Stock on hand and stock received. It needs to calculate itself and work out the variance. I don’t need costing and unit prices on the spreadsheet.
Laura Handrick
Hi Sakkie,
You might want to hire a freelancer to build exactly the kind of Excel sheet you want that includes the calculations you need.
Here’s how to find one.
Best!
https://fitsmallbusiness.com/hire-freelancers/