The Best Ways to Utilize Inventory Tracking

What Does Inventory Tracking Look Like

Inventory tracking may look different for every business. It can be a massive multi-warehouse system, or as simple as a legal pad with tally marks. Many companies start with an inventory spreadsheet in Excel, and maybe a couple handheld USB barcode scanners. Larger, multi-location businesses may have advanced implementations like a completely hands-free RFID infrastructure.

The key driver is a careful balance between the level of detail that needs to be tracked, the amount of effort that tracking takes, and the budget.

When talking about tracking inventory, we‘re mainly asking questions similar to this: “how many widgets do we have at the Main Street warehouse?” Knowing how much, or how little, a company has of a product affects many areas of the business.

• Finance knows how much money is tied up in inventory.
• Sales knows what they can sell.
• Marketing knows if there is an excess and when to run promotions.
• Distribution knows what to order and when.

Having quick answers to these kinds of questions help a business run smoothly and help to instill a sense of confidence into the minds of clients.

What the World Would Be Like If Inventory Tracking Didn‘t Exist

I was recently at a conference where they were selling t-shirts and hats. It was a popular brand, and there were many people lined up to purchase with money in hand.

The problem was that the group didn‘t have their shirts separated by size, and they only had 6 hats. They had a similar problem last year, but because they weren‘t tracking how much of each item was sold, or what was left over, they didn‘t reorder correctly.

At the end of the day all 6 hats were sold, there was the same pile of mixed-sized shirts, and a long line of customers who wanted to purchase, but couldn‘t.

This is a small scale example, but one that often shows up in retail. Without inventory tracking, the wrong product is often ordered. This means that there is too much of the wrong product and may be not enough money leftover in the budget to order the right product. In the end, customers are unhappy because they are ready and willing to buy.

With big box companies like Walmart, Target, and Amazon so easily accessible, it‘s easy to lose customers to retailers who always have product on their shelf.

What Small Business Inventory Software Should Track

In the example above, we started off by asking how much product was at a single location. There are actually a number of variables that may need to be tracked depending on the industry.

• Manufacturers might track Unit of Measure to move inventory between case sizes depending on customer demand.
• Food and Pharmaceutical distributors will need to track lot codes, expiration dates, and even temperature to ensure traceability for their sensitive goods.
• Medical device and electronics companies will need to track serial numbers for warranty guarantees.
• Retailers selling shoes might need to track multiple product dimensions such as size, width, color, and profile.
• Distributors will be looking at how much product is at each warehouse around the world, and the trending demand of each product from each location.

Each data point that needs to be tracked adds time and effort to tracking, so it is important to start with only what is needed. As the business adapts to greater data, it can make educated decisions on additional data points to track that can produce a return on the effort invested.

How to Keep Track of Inventory in Excel

For businesses with fewer locations and item SKUs, Excel is a great way to get started tracking inventory. By using a simple system and building out processes, it will make identifying an inventory management system that fits the business needs simpler when it is time.

Each business will have different variables that need to be tracked. These data points, or dimensions, will become columns in the Excel spreadsheet. So, each row will represent a distinct item in your inventory.

Will one row represent the number of red cross trainers?

SKU	Type	Color	Quantity
123456	Crosstrainer	Red	4

Or will the row need to add in additional fields?

SKU	Type	Color	Size	Width	Quantity
123456	Crosstrainer	Red	12.5	W	2

The first example will mean fewer rows to search through to record inventory transactions. The second example is a more accurate view of the inventory, but might be more difficult to manage as business continues to grow.

The following processes are the basic functions of inventory management software. By implementing these systems, a business can organize itself to make transitioning into more robust software seamless.

Receiving

When receiving the product, first verify the quantity shipped against the purchase order and the invoice. Once satisfied that the correct product was delivered, add the additional quantities into the Excel file.

This is often done by two separate people so the person receiving can focus on bringing the product in. Receiving is a critical point of contact as ensuring that shipments match their invoices and purchase orders is the first step towards an accurate inventory.

Shipping

When picking and shipping, the pickers can mark the bill of lading with the accurate quantities as they pull the product. This is usually passed to someone to update the invoice in the sales system after picking is completed. By taking the time to adjust your inventory in the Excel spreadsheet, you‘re increasing visibility.

Adjusting

Receiving and shipping product are the largest and most visible transactions within inventory, but not the only transactions. Inventory often needs to be removed because it is damaged, lost, stolen, or just used for other purposes.

I usually recommend tracking each of these transactions on a separate tab of the spreadsheet. It can be eye-opening to see how much inventory is removed as a cost of doing business. After being tracked and reviewed, often the need is greatly reduced and less product needs to be adjusted.

Counting

When starting out, it is common to have inaccuracies due to human error. Employees may forget to complete entries, or make mistakes when manually keying information. This is a growth period, and processes are still being developed. Every business has a procedure for counting inventory, but it is wise to implement in phases.

Most businesses start by counting their entire inventory, usually once a month. The warehouse shuts down; no receiving, no shipping, and everyone counts every product. This is usually expensive, and often the least accurate. Although simple, this periodic count is often a costly practice.

Cycle Counting is the practice of counting only a handful of items every day so that the entire warehouse is counted in the same period, but without overtime. By utilizing downtime that occurs naturally through the day, or just a few minutes at the end of every day, inventory can be trued up regularly and without disruption.

Inventory Barcode Scanners

Even with an inventory spreadsheet, businesses can take advantage of fast data entry by using barcode scanners. Almost every product can expect to have a Universal Product Code (UPC) on a tag.

A USB barcode scanner can plug into the keyboard and be used to find products in a larger spreadsheet. Pressing CTRL+F on the keyboard will bring up the Find search box. By scanning the barcode, it will prevent errors and enter those 14 digits in a single keystroke, bringing the user right to the line with that product.

Barcodes bring speed and accuracy to processes. By making them a part of the process early on, it will help identify products that need to be barcoded. If your items are currently not barcoded, vendors are familiar with barcodes and are usually eager to add additional value.

Excel Inventory Template

I‘m excited to share my knowledge of inventory tracking with you as it has been a part of my life for so long. I want to help you and your business feel more comfortable with it, and often the first step is the hardest.

To help get the ball rolling I have created an Excel Inventory Template for you to download. Feel free to customize it based on your businesses needs and requirements. Use this as a tool to get a feel for the processes and see if it benefits your team with the increased visibility.

If you have any questions, please don‘t hesitate to reach out to me and my team. We help businesses like yours every day to start leveraging systems and processes to increase visibility with repeatable systems.