There are lots of different ways to conduct an annual inventory. To be honest, inventory is one of my least favorite annual projects. So, no better reason to get better at it.
There are 3 primary methods I see dealers using to manage their counts
- Perpetual – Done daily, weekly, monthly
- Annual – Once a year, everything gets counted
- Procrastinate – Too much work, let’s put it off another year
I see pros and cons of each method, but over time I’ve landed on the annual count as the one that works for us. We throw everything at it for a couple of days, and then spend the other 363 days (364 in leap years) selling stuff.
As time progresses, we always try reduce the time our count takes, while improving the accuracy. Getting away from a pencil and paper is the only way to make this happen. Computers and spreadsheets are our friends.
The link below is to a Google document that outlines my current process. It’s a live document, so what you see tomorrow could be different next week. Our process is built around the DMS we utilize in our dealership, Woods Cycle Country. 3 years ago we upgraded from Lightspeed NXT to Lightspeed EVO. The new system offers additional methods for updating the count, and we continue to work on using more of the capabilities of the DMS.
Here is a link to the full Google Document (easier to read than the embedded version)
https://docs.google.com/document/d/1eYyoZyTVxkGoyx6AeaLhsVM1YGlAMdct9kjKImy22Y8/edit?usp=sharing
If you have any comments or questions, post them at the end of this article and I’ll respond when I have time.
Original Version
This is the original version of this document. I will not be updating this version included in the blog (use the link above to see the most current version)
Lightspeed EVO – Annual Parts Inventory Process
Over the years we have continued to fine tune our annual inventory process. The transition to EVO provided a major expansion of opportunities for the utilization of additional technology and third party counting services. Below is some of the thought process behind how we conduct our annual inventory utilizing EVO.
** Please note that this document is not a full set of instructions on taking your inventory from start to finish. I have attempted to provide an overview of the process we use, one which varies in a lot of ways from how dealers have traditionally conducted a physical inventory. **
In the past, inventory within NXT was extremely inefficient and error prone. Manual counts (for items where barcoding was not an option) had to be uploaded into the system by hand, and then variances were checked. Many dealers couldn’t complete all of this work over the course of a weekend.
Lightspeed EVO offers an additional method to load counts into the system. While barcode guns can still be used, the screen where these counts are uploaded also offers an option to upload a CSV file (part number/count). The count file is a simple format, part number and count quantity. The addition of an approved method to upload a file opens up a few new ways to post counts:
- Third party counting services can be utilized
- Counts can be completed in a shared document (such as Google sheets). Final counts are then downloaded into a CSV file and uploaded into Lightspeed
- More powerful equipment than the Lightspeed barcode system can be used (antiquated equipment with a limited capacity for numbers and no capability to merge counts)
At Woods, we have chosen to use a blended process to complete our counts:
- RGIS (a national inventory management company) uses their employees to count approximately 80% of our inventory. They count the majority of our accessories and parts that can be barcoded. Their employees complete the count of approximately $700,000 of inventory in 4 hours. When done, they provide us a file in a format we have designated.
- RGIS attempts to complete each count in 4 hours. Based on an initial consultation with your dealership, they will bring in enough people to complete the inventory in this time. They have found that working people at this process for more than 4 hours results in a degradation of the count quality. In our market, they have a minimum charge of $1500. We only went over that during our first count, but have not exceeded it since.
- We utilize Google Sheets to count the balance of our items, using teams of two people.
- The Google sheet contains all of the active parts from our database, along with the description and bin locations.
- The sheets contain a column for each team. One person calls out the part number, the second person finds it in the sheet (using the find feature <CTRL/F>, and then keys in the count provided by the first employee.
- When the manual counts are done, we compile the counts from all the teams into one page, and then download a file in the CSV format that can be uploaded into EVO.
- When all of the data has been uploaded back into Lightspeed, we then generate variance sheets showing all of the line items that were modified during the count. Each one of these items is double checked and final adjustments are made.
- Once this is done, we post a journal entry that ties the GL to the physical inventory.
Prep Work
- Bar code all inventory (that can be coded)
- Consolidate items into single locations
- Ensure all special order and layaways have been picked up/refunded
- Work with all vendors to maximize return allocations
- Slow down on stocking orders a few weeks prior to the count
- Ensure all packing/receiving slips have been processed and turned in (receiving should not be sitting on any paperwork)
- ALL RMA’s and shortages should be processed
- Identify third party counting company (if you will use one)
- Perform a test count
- Identify format that they will provide a count back to you in
- Test the upload process into Lightspeed. Get comfortable with it. (you can test the system all the way up to saving the count). Ask nicely, and the Lightspeed team might even let you use a dummy database (although this really isn’t needed).
Philosophy on Counting
There are a few schools of thought when it comes to conducting an annual inventory:
- Count everything, post the counts, and then audit all of the items that were not counted, and audit all of the adjustments. For dealers who don’t knock out an annual inventory, this process can be painful and impossible to finish in a weekend.
- Begin the inventory by zeroing out the entire inventory (there are a couple of ways to do this in EVO – Charati Doss at FPS said my trick for the updated version of EVO saved her 8 hours). Upload the counts, and then count all of the items where the positive and negative adjustments don’t balance out. This is my preferred method.
- Skip annual counts and just inventory a section each year. Pros and cons, I’ve never been able to make it work, and I think we spend less time on the project with one annual count.
Lessons Learned
Each year we learn new things and the process continues to improve. Here are some of the things we have learned over the years.
- Leading zeros in part numbers. Having a part # that starts with a 0 is a unique situation in our industry, large companies would never do this. When working with part numbers outside of Lightspeed, it’s important to format part number fields as Text fields, not as numbers. Formatting them as text retains the leading zeros, and minimizes problems when posting the count.
- Part numbers with special characters. For some reason, we use parts that have letters, numbers, special characters, spaces, and squirrel noises in them. Big companies don’t do this, and anything outside of numbers in a part # should be avoided. Outside services have keypads and they can enter numerical part numbers into their equipment faster than you can say them. However, if they have to add letters and special characters, this slows them down exponentially. If you don’t use barcodes, start now.
- Minimize duplicate numbers in your system. Having the same part number in the system for two different supplies (i.e. BR8ES spark plugs) creates issues with the upload and unnecessary variance checking. Plus, duplication of part numbers makes stocking harder than it needs to be.
- Make sure any third party counters know to only scan company bar codes and not factory SKUs. We provide RGIS (the company we use) with a file that contains all of the part numbers we have used in Lightspeed. If they scan a part number that’s not in that list, they get an error message on their scanners and have to ask for an override from our staff before they can proceed.
- Make sure that counters don’t “face count”. This is where the front item in a row is scanned, and then the quantity is manually entered. In our industry, we often purchase the same item from different vendors, and many items that look alike might not use the same part number, or even be the same.
- Google sheets
- The sheets start to slow down once they have more than about 30,000 lines on them. Prior to inventory, take the time to clean up and deactivate old part numbers in Lightspeed.
- If you have a large inventory, consider breaking the counts up into two separate sheets, one for OEM and one for Aftermarket. However, don’t get too detailed, as all this data needs to be merged back together again.
- The find feature in Google sheets is great, but make sure the employees know how to use it. If a count is entered in a cell, and then a keyboard entry is not made (they just use the mouse to click on another cell) then those counts might not commit.
- We’ve gone back and forth sorting inventory by supplier and then part #, and just by part #. There are benefits to both. Figure out what works for you based on the number of vendors you work with.
- If you are a Yamaha dealer, don’t forget that not all of their parts are sorted by the first digit. Working around their middle 5 can put items in the count sheet sequentially, making it easier to get the count into the sheet. I wrote a formula for this, but it’s more complicated than it really needs to be.
- Protect all the cells but the count columns. This way, nothing you need is deleted.
- Lightspeed reports
- I’ve found it best to work with the data from Lightspeed in CSV format. Downloading data in the Excel format will strip leading zeros from part numbers, and you end up with a large number of excess columns/field separators. If anyone from Lightspeed reads this, it would also be great if you could give us back the option to export to a text file.
Lightspeed Barcode Guns
When we converted from NXT to EVO, we had 5 guns that we had used for inventory with NXT. We sent these back in to have them reprogrammed for EVO, and even purchased a few additional ones. In our first year with EVO, we used these scanners to complete our count. I never determined why, but the count from almost every one of them was corrupted and we had to recount large sections of the dealership. These problems happened with both the new scanners we had purchased, and some of the old ones that were reprogrammed.
We will never go back to using them for that process (using RGIS is so much faster, cheaper, and more accurate). If you do have some of these scanners, I’d be sure to test them out prior to your count (and not just with a few parts). It’s easy to see what’s on each one. Open up Notepad on a Windows machine, make it the active window, and then use the “download count” feature in the scanner to send the count to the computer. Notepad will show all of the data contained in each scanner.
Pre – Count
Prior to starting the count, take a snapshot of the inventory. Download a full report of all the parts and their on hand quantities/available quantities. In the end, if everything goes wrong, you always have this file and you can use the data in it to revert the on hand counts back to where they were prior to the failed count.
This is also a good time to download a copy of the Inventory Valuation Summary. This report will allow you to account for the parts that are sold to repair orders (these will not show up in your physical count). There is also a detailed report under Service custom reports that should tie back to this value.
Inventory Style
If you made the decision to just post the counts and then work the variances you are ready to get started. If you decided to zero out the on hand inventory and just add everything back in, there are a few ways to do that in EVO. It’s important to note that in March of 2020, Lightspeed updated the inventory update screen to adjust the “available quantity”, not the “on hand quantity”. This is an important distinction.
The best way I have found to zero out the available quantity is as follows
- Construct a custom report for your parts inventory
- Field 1 – Part #
- Field 2 – 0 (you will hard code the report to print a 0 for each line)
- Set the filter to only print parts where the available quantity is <> 0
- Save the download to a CSV
- Upload this file into the Inventory Update screen and then update inventory
- This will set all available quantities to 0
When you do this, the system is going to generate an adjustment report. Because of the large number of line items, rounding becomes an issue and these adjustment reports are often off by a penny. You will see a “Management Report” error message down in the bottom right hand corner of EVO. You can’t fix these errors yourself, but Lightspeed has a script to fix them and it only takes about 60 seconds once you get hold of the support team.
Now you are ready to get rolling.
Special Orders and Layaways
Lightspeed recently made a change in their system so that when a count is uploaded, the field that is uploaded is the “Available Quantity”, not the “On Hand Quantity”. This is important because it means that the items that are on special order and layaway do not have to be added back into the count before it’s uploaded. However it’s a best practice to audit all special orders and layaways prior to the count, making sure that Lightspeed matches the content on the shelf. Any issues should be corrected beforehand.
Getting the Count Into the DMS
Once the counts have been completed it’s time to get the counts back into the system.
If you used RGIS for a portion (or all) of the count then they will provide you with a count file. You can manipulate this into the format you need. The format that EVO uses is simple, Part Number and Quantity. You will upload this file directly into the system. If it’s a large file, Lightspeed will take a little while to process all the records (have patience, the largest update I processed took 13 minutes).
If you used Google sheets for your count, then you’ll need to generate the file that you will use to upload into EVO. Get all of your part numbers on to one page in Sheets, with the part number in column 1 and the final count in column 2. Then use the Sheets export function to create a CSV file with your counts. Make sure you click the option to not automatically detect the type of your data. You want to make sure the part numbers are formatted as text. If Google attempts to verify the type of data, it will turn these into numbers, and you will lose all your leading zeros.
Once these files have been uploaded into EVO, you will see some parts at the top of the screen that are color coded. Double click on each one to sort out why they are highlighted. In some cases, there are duplicates, in others, the parts don’t exist in your active database. Sort out each one, and when they are all taken care of and you are happy with the count, click on the “update inventory” button in the upper right hand corner. This will take a while, but all of the new counts will be written to the database.
At the top of this screen is also a “report” button. If you feel the need, run some reports on what’s being updated prior to completing the update. This way you will have additional backup detailing the changes you made.
Variance Checking
Other than the count, variance checking is the most important part of the process. This is where every part that was adjusted and/or not counted is double checked. While Lightspeed has a field in the parts inventory record that shows positive and negative adjustments made during the month, I’ve found that this field does not display every adjustment (even though on the back end Lightspeed still tracks each adjustment). There are lots of ways to increment inventory during a count:
- Zeroing out all the on hand counts through parts updating
- Zeroing out on hand quantities in the inventory upload module
- Uploading files with new counts
- Manual adjustments made in the parts inventory record
While some of these will show up in the MTD adjustment field, not all of them will. I’ve moved to building my variance checking reports in Google Sheets. Before we started I had taken a snapshot of the full inventory. A second snapshot after all of the counts have been posted is then taken. A few simple VLookup formulas in Sheets, and we can find all of the items where net-adjustments were made.
Some of you might be able to make this work in Lightspeed, but I’m past that now. The other benefit to doing this work in sheets is that you have full control of the formatting of the variance checking sheets. I add some extra space so that employees can write down notes and other information. I also leave enough space between lines so that the team doesn’t go cross eyed.
Post Count
When it’s all said and done, the last thing to do is to tie the GL back to the physical count.
They physical is comprised of 3 primary items
- The components that were counted
- The value of parts on Repair Orders
- Special orders and layaways
- There are potentially a few other items that need to be considered
- Items on consignment
- If invoices and RMA’s have not been reconciled, these can impact the count
The GL also has a few key components
- Account for inventory (parts/accessories/lubricants/motorclothing/others)
- Parts purchases account (in EVO, when an invoice has been received, or an RMA returned, but the invoice from the OEM has not yet been matched up, these items sit in a transitory account). If the office is fully caught up then the value of this account will often be $0 and you don’t need to worry about it. However, if you still have items under the AP / Parts Packing Slips section, then there is probably some money in this account that needs to be included in the reconciliation.
A journal entry is made to move the GL in line with the physical. Hopefully the writeoff (or in some cases gain) was not too substantial. I’ve talked to large organizations, and they are usually OK with a loss of 1-2%. Much more than that, and new management is on the horizon.
Hire a Nerd!
There’s a lot of technical stuff in this document. If you don’t have a nerd on your staff, then consult with one, or better yet hire one. And, for those of you who think that calling someone a nerd is an insult, just understand that only non-nerds see it that way. Us nerds see that nickname as a badge of honor, and in the end, at some point all of the non-nerds out there will either end up paying us a bunch of money, or end up working for one of us.
Useless Piece of Data
Each year when we wrap things up, I always take the total quantity of SKUs that were adjusted either up or down, and then divide that by the number of working days in the prior year. This gives me a number that represents the “average daily mistakes”. These mistakes can be from a list of items that is too numerous for this document
- Receiving mistakes (said we got something that we never received)
- Failure to file discrepancies with the vendors for shortages and other errors
- Labeling errors – put the label for one item on a different part
- Selling errors – typed in a part number for one item but gave the customer something else (usually size related)
- Package mis-match – Sold the customer 2, gave him/her the 10 pack of that item
- Parts control – Techs allowed into parts area. Items make it onto customer vehicles without ever being sold to the RO
- Rushed e-commerce – Extra parts end up in the package because there isn’t a process and the employees are rushed
- Theft – If you have more than 25 employees, someone is stealing from you. It’s easy to put an extra item in the customer’s bag or just not ring someone up
- DMS security – The number of employees who should be able to adjust inventory in the DMS should be limited to 2 or 3. The more people that have that level of access, the more items that will go missing
- DMS errors. People who don’t understand how computers work will always blame the computer for errors, when almost all mistakes are user errors. My belief is that less than 1% of inventory errors are caused by the DMS. The other 99% are mistakes, human errors, and a lack of process
- Employees who just don’t care
Back to my statistics. Years ago, when I started looking at this metric, we usually made about 12 mistakes a day. Over the years, we’ve whittled that number down to about 9 mistakes a day. I always thought we had major issues, until I asked some other dealers to make the same calculation. I now know that, as an industry, we make a lot of mistakes!
Many dealers see the receiving position as an entry-level job. I tend to look at this position as one that employees should be promoted into. A new guy who doesn’t know a tire from a battery is going to cost you a massive amount of money each year. Pay up front, or pay on the back side, one way or the other you’ll pay for the work done by the shipping and receiving employees.
Screen Shots and Samples
A sample of the count sheets we used in 2021. Employees can only enter data under their team name. All the other cells are locked.
Lightspeed EVO – Parts inventory updating screen. The second button in the upper left corner is the one you can use to upload a file containing count data.
Accounting Update – Depending on how you operate, the GL and the Physical can be locked together and every change you make will generate journal entries, or you can disable that feature. That change is made under System Preferences.
Variance checking – This screen shows what our variance check sheets look like. These are formatted, numbered, and handed out. The staff then work through each adjustment and make the final corrections. This sheet shows the count quantity we started out with, the quantity we posted, and the variance.
Field formatting – Make sure key fields like the part number are formatted as plain text, not as numbers. They look the same to you, they don’t look the same to computers.
Importing data – When importing a CSV file (or any other format) make sure to select the option to not convert text to numbers, dates, and formulas. This one step will help to retain your leading zeros (and it fixes a few other import issues). Once the data is in the sheet, you can format it to your heart’s content. .
