QuickBooks 2012 Inventory Lot Tracking
Lot tracking is basically tracking the quantity you have of items purchased (or manufactured) in “Lots”, or groups of some sort. This is used in many different industries (and is required by regulation in some) and it has been a missing feature in QuickBooks inventory. Now, with the 2012 version of Intuit QuickBooks Enterprise Solutions, this feature is available. Let’s take a look.
Lot and Serial Number tracking (which is discussed in another article) is available if you purchase the Advanced Inventory feature for Enterprise, for an additional fee. Note that you can use only one OR the other, you can’t combine them in a single company file.
This article was updated on 9/28/2011 and again on 10/17/2011
How This Works
If I receive an inventory part (or assembly), I can enter a Lot Number in the last column.
Lot numbers can be up to 40 characters long, and must not have any spaces. If you enter something like “Lot 55” you will get a warning that you can only enter one lot number per line – some logic from the Serial tracking feature has crept in here (it is taking the space as a break between two ID’s).
When I sell an item, I can pick a lot number to work with. Note that if I pick a lot number that lot must have enough items to fulfill the quantity I requested. If, for example, I pick lot 55 (see below, it has 100 items) but ask for 200 items, I’ll not be allowed to use that lot. I would have to split this into two lines, selecting a different lot on each line.
I can still buy or sell the item without a lot number, which may cause a problem for some people. I would like to be able to have a setting that forces lot numbers, on an item-by-item basis.
You get the idea – you enter a lot number for any transaction that changes the quantity on hand (or available, such as a sales order). So far as I’ve found, they have covered all the transaction types.
Adjust Quantity/Value on Hand has a new adjustment type, Lot Number. You can use this to adjust the quantity in a particular lot. This is a bit of an oddball transaction, as it doesn’t change the quantity on hand, just the quantity in the lot. So there are no adjustments to your chart of account.
Reports
I can see information on the lots that I have in two reports, Transaction List by Lot Number and Lot Numbers in Stock.
The Lot Numbers in Stock report is a simple report, it just lists the lot numbers for each item. No quantities. I’m not sure how useful this is. It is also one of those frustrating inventory reports where Intuit doesn’t give us any options to change the columns in the report. Update: You can get the quantity from the Quantity on Hand by Lot Number “view” – see this followup article.
For the Transaction List by Lot Number report you have to pick one item and one lot number.
Again, no customization is possible here. It is a bit frustrating.
Lot Tracking Setup
Select Edit in your QuickBooks menu, and then Preferences. Find the Items & Inventory preference and select the Company Preferences tab. Click the Advanced Inventory button.
Check the Enable Lot or Serial Numbers box and select Lot Numbers. Note that you can only use one of these features in a company file.
There are several options that you can set. Most businesses that I’m familiar with will use lot numbers for all of the transaction types that are listed. I would definitely recommend setting the two warnings to “warn me”, at least to start.
I don’t believe that you have any sort of conversion process that happens if you enable this, although I can’t say for sure. No lots will be created for your existing inventory items (unlike what you get if you use FIFO). However, at least in the first revision of the program, there is an annoying bug. When you enter an inventory item and quantity for an item that existed before turning the feature on, when you finish with that line in the order you may get a warning that the lot number is empty. However, there is no lot number that you can pick from – so it shouldn’t warn you. If you continue, you get by this, but it is annoying. You can turn off the warning in the Preferences, but that could be a problem when you NEED to get lot numbers. It shouldn’t warn you if there are no lot numbers to pick from.
Why Use This?
This seems to be a somewhat light treatment for lot tracking. I’m not too worried about using it – there aren’t big changes to how costs are calculated, it doesn’t make major changes to the inventory file or existing transactions. I do have a number of concerns, though.
Why use this? The major reason is going to be tracing back to a lot “after the fact”. If one of my lots has been identified as having a problem, I want to be able to find all transactions where that lot was used. I might have a recall, I might have a batch of faulty parts, I might have some sort of contamination. You can use the Transaction List by Lot Number to find all transactions that have used that lot. You can use the Search feature as well, but I didn’t have as much luck with that working for me. This is a key feature for lot tracking. Update – Intuit says that the “Recall Information” section of the Inventory Center is better suied to searching for information about a particular lot, rather than using “Search”.
Why NOT use this? Not a lot of big problems, but I’m not sure if this is fully ready to fly yet.
- It is the first revision, you might wait until it gets a bit more “seasoned”.
If you have a mixture of items that are lot controlled and others that are not, the nagging of “enter a lot number” will get to you. Sure, you can turn it off, but then you might MISS a lot number on an important transaction. This can be a major problem in some cases. I wish that lot number control could be set item by item, or at least that if there is no lot number for an item this warning wouldn’t show.Update: I was wrong! This feature is there, but unfortunately in the R1 release it doesn’t work. To be fixed. See this article for details.- I really don’t like not being able to get a Lot Numbers in Stock report that shows how many items I have in each lot. I need that for control purposes. Update, see the Quantity on Hand by Lot Number “view”, which isn’t a report but it helps. See this article for details.
- There is absolutely no connection between the total quantity from all lots and the quantity on hand. You can have lots with a quantity that is greater than your quantity on hand, for example. I understand that in some cases we might want that to be disconnected, but I would really like to have the option of tightening this down. This is a more complicated feature, and it creates a lot of potential issues, but if you want true, full fledged lot control, this has to be an option.
- If you use a third party add-on that works with inventory you have to be careful, this feature might interfere with add-ons. I’m not sure about this, just check with your add-on developers to see if they have any information. Intuit is updating the programming interface, but that is still in beta test and it may take awhile before add-on developers can release products using that.
If you have multiple level assemblies you won’t be able to track a lot number from the lowest level assembly up to the highest level.Update: Actually, I was wrong, sort of. If you use a Lot Number for EVERY ASSEMBLY in the “tree” of your multiple level assemblies, you can see all of the items that are affected by a lower level Lot. See this article for details.- You can enter a lot number in an invoice (for example) that doesn’t exist. You are warned, but allowed to proceed. That lot number is recorded in the transaction, but that won’t show in the Lot Numbers in Stock report. It DOES show in the Transaction List by Lot Number report. However, I don’t like the lack of control here, an order entry clerk shouldn’t be able to make a lot number, at least in some cases.
- The R1 release won’t work correctly if you have a quantity in a lot greater than 999. The lot isn’t saved (or recalled) correctly, it will look like it never existed.
So I’m not waiving any red flags here. The basic concept works well, I would like to see them continue to refine and improve this. It is a good first step that would be useful for many businesses. However, as always, please investigate how this works with a test company file before you jump in with both feet.
THANK YOU, Intuit, for starting down this path!
Related posts:
- QuickBooks 2012 Enhanced Inventory Receiving
- QuickBooks 2012 FIFO Inventory
- Tracking QuickBooks BOM Revisions
- QuickBooks 2012 Overview
- Tracking Other People’s Money in QuickBooks
Category: Manufacturing and Inventory, QuickBooks Tips/Tricks, Working with QuickBooks
About the Author (Author Profile)
Charlie Russell is the founder of CCRSoftware. He’s been involved with the small business software industry since the mid 70′s, and remembers releasing his first commercial accounting software product when you had a one-floppy disk drive system, loading the program from one floppy and then replacing that with the other floppy to hold the data. He has a special interest in inventory and manufacturing software for small businesses. Charlie is a Certified Advanced QuickBooks ProAdvisor and participates extensively in the QuickBooks Community user forums under the ID of CCRussell. Visit his CCRSoftware web site for information about his QuickBooks add-on products. Charlie can be reached at charlie.russell@sleeter.com
He is also the author of the California Wildflower Hikes blog and a regular blog contributor to the Intuit Inner Circle.
Connect with Charlie at Google
Related posts:
Subscribe
If you enjoyed this article, subscribe to receive more just like it.
Charlie you have completely confused me.
In your example, gravel, you have two lot numbers, 55, 59 and the total shown for both is (100+300) 400 yet, later you say you can adjust the qty in a lot, but that does not change the qty on hand – huh?
So in your gravel item is there 400 on hand total or not?
I wish you had included a screen shot of the item list, can you see both lots in the item list?
What does the item quick report show?
Jim, as I said in the fourth bullet in my concluding section, “There is absolutely no connection between the total quantity from all lots and the quantity on hand”. This isn’t FIFO “cost lots”, that is something very different. This ONLY is used to track the consumption of items in the lot, it has nothing to do with costing or quantities available/on hand. That works for some cases – there are businesses that have lots they have to track for an item, and for the SAME item they may have some around that aren’t in a particular “lot”. And it prevents you from being stuck without the ability to sell something if it isn’t in a lot.
HOWEVER, for businesses that need tight control over lots, this won’t work.
That is why I pointed that issue out, and why I’m telling people they need to understand what this can and can’t do before they jump in.
It’s a first step, let’s hope they continue to improve on it.
Jim ,
Two simple questions. 1) Please clarify that lot tracking will be available in V12 of Enterprise without having to purchase the advanced Inventory Feature. Do I understand this to be the case? 2) What are the pros and cons of the V12 implementation of lot number tracking versus simply adding a custom field to the Item Table to store this data?
Dick, I thought I stated it pretty clearly in the second paragraph – Lot tracking is a feature of the Advanced Inventory system. You have to get Enterprise V12, and you have to pay for the extra Advanced Inventory option.
This feature tells you how many items remain in each particular lot for an item. If you use a custom field, you don’t get any sort of tracking unless you create a separate item in the item list for the lot, which is really inconvenient. If I buy 10 of item “A” in lot “1″, and then buy 10 of item “A” in lot “2″, I can see that I have 20 items in two lots, with 10 each. If I use a custom field for item “A”, there isn’t any tracking of how many you have in a lot. If I sell 5 items, and put a lot number in the transaction, I can’t later look at the item and see which lot has five and which lot has 10.
Dick, Lot number tracking has a work around in premier that I use, and would work in enterprise without the add on, see this
http://my246shop.com/Rustler/tag/invtory-lot-numbers
Same concept can be used for locations
Jim, I’m not a fan of that approach, but then it is a lot cheaper than upgrading to Enterprise and then adding Advanced Inventory.
It does depend on the size of your inventory, and how many lots you use, and other factors.
It blows up the number of items in your item list, so if you have a lot of items and/or a lot of lots, that can be a problem.
It messes up assemblies – if you have a part as a component of an assembly, QB won’t automatically go to the “lot parts”, so you have to do transfers.
So, that approach doesn’t work for everyone, and it is nice that there is an alternative here from Intuit…
Note to everyone – I made a number of changes to this article to correct some things that I missed in my initial article.
There is a major problem/bug receiving inventory Lot Numbers against a Purchase Order.
When receiving inventory in the Enter Bills, against a PO, there is no way to receive multiple lot numbers.
Example – We order qty 100 on a PO.
Vendor ships all 100, and there are 3 lot numbers.
Qty 40 Lot number 1234
Qty 10 Lot number 3334
Qty 50 Log number 5555
There is no way to enter all three receipts in one session, with out breaking the PO linkage.
I can only receive line one, which retains the PO linkage.
If I try to receive line 2, the PO number linkage is broken.
There is no way to receive line 2, or more, without breaking the PO link.
The only work around is to create 3 separate Enter Bill receipt transactions, each with one line.
This is a terrible work around, as there are now 3 vendor invoices, when there should only be one invoice. Creating 3 separate invoices/receipts is also very inefficient, and facilitates data entry errors.
IMO, this is a major oversight/Bug by Intuit
Cliff, a better workaround (and it is still a workaround). Use a “Lot Number Adjustment” transaction. Receive the item with all 100 in that lot number – OR create a temporary/dummy lot number. Then go to the Adjustment window, select a “lot number” adjustment type. You can move the items from one lot to another, you can create a new lot. In my test, I received 100 to lot 1234. Then I made an adjustment to change the count of 1234 to be 40, and added a line for lot 3334 at 1 and 5555 at 50. It works fine, the only issue is that i have to do the math myself (it won’t show a running total). And, of course, the issue of my having to do it in the first place.
Lot tracking is a new feature, I am not surprised that these kinds of issues come up. It isn’t a “bug” in that it is not creating an incorrect calculation, it is a “design flaw’ where they could have done something better. Let’s hope that they come up with a better solution in a future update.
Charlie,
I talked to Intuit and their workaround is to add lines to the PO before receiving. In your example, you would add 3 rows to the PO, with the correct quantity for each lot number. Then start the receiving transaction.
This same design flaw/bug also exists with Serial Numbers.
Users receiving serial numbers are going to have to work a lot harder, with every single item requiring a workaround transaction.
Calling this a bug can be debated. I feel it is a bug because Intuit could not have intentionally designed it this way. There is no logical way anyone could consciously or intentionally design it this way. It had to be a sloppy oversight. Or does Intuit get to a point where they think/say “It is good enough for government work”, and stop developing the program?
For users not paying attention, I can see them adding additional rows/lines during the receipt transaction. All these additional rows/lines will cause incorrect calculations because the PO linkage will be broken, and all the PO’s will remain open with backorder quantities.
Cliff, after some thinking (and discussion with you in another forum) I’ll agree that the Intuit workaround is better in many cases.
Serial numbers don’t have that error – you can enter multiple serial numbers in one line, just separate them by commas. works well.
I think that the lot tracking feature is nice, but as I’ve said before this is “lot tracking lite”, it is a first step. Might not work for everyone. We’ll hope that they continue to develop and expand it. I know that they are enthused about the concepts, so I think that the product will expand. I just can’t guess as to “when” that will happen.
Mr Russell,
I have stumbled upon your Blog recently and have found several very informative articles. I am seeking a little advice about Advanced Inventory.
A little back story: We are a manufacturer that produces products overseas (via multiple different factories) and we warehouse those products here for domestic sales. We also sell to other international customers direct from the factories overseas. My main concern is tracking our domestic inventory. We are currently using a separate program to track our bin locations inside our warehouse and what is in each bin. My warehouse personnel use this program (i.e. Microsoft Excel, Access) to locate each sold product to pull for shipping. I would like to combine this information into one place and QB Advanced Inventory seems to be the way to go.
Do you think Advanced Inventory could do this for me? Do you have any suggestions for setting this up in the Advanced Inventory Program?
You can experiment with Advanced Inventory via this link.
http://enterprisesuite.intuit.com/contact/test-drive/enterprise-solutions/thankyou/
We use advanced inventory.
It does a good job tracking inventory in multiple Site locations.
Some shortcomings to be aware of -
1) Only one average cost per item can be used across all inventory site locations. You can not have different unit costs for the same item in different locations.
2) The Sales Order Fulfillment Worksheet does not allow you to filter on different site locations.
3) There is no Site information in the Item List.
4) Negative inventory quantities between sites is much easier to inadvertently create. Total quantity might be correct, but errors (negative qty) between sites is easy to create. Intuit should have provided an option to prevent negative inventory in Advanced Inventory.
Thank you, Cliff!
I have hopes for the negative inventory issue, going forward, although that is speculation on my part. If they do something like that, we’ll have to see exactly how they implement it…
Aaron, it is hard to make a firm recommendation based on a paragraph of information in a blog comment, but based on what you have said you should find that Advanced Inventory will be helpful. Note that you must have Enterprise V11 or V12 for this, and that you have to pay an additional fee for the feature EVERY YEAR that you use it.
You can see my overview of the multiple location feature in this article: http://qbblog.ccrsoftware.info/2010/09/quickbooks-2011-advanced-inventory/
OMG, do I have a problem that Intuit has never even come across with 2012 Advance inventory. I inherited a major cluster of problems, as this company did their data conversion in November from Great Plains to QB Mfg Enterprise 12.0 with a December year end. So opening balances and inventory adjustments from the gate were incorrect, now I am trying to correct inventory. So when I go to inventory center, select my site location, then item number see it on the screen, on hand shows 72, click on quantity by lot number, shows me 3 separate lot numbers, 1 has 69lbs, the other 32lbs and the third 40lbs, so obviously I close out and click on transaction detail by lot number showing me some what of a quick report by item and lot number specified, there is no data at all, even when I select the all for dates nothing appears. I can also see the lot and quantity of the ghost lot when I create a sales order, it will allow me to pull from that ghost lot even though its not there and still shows no transaction history ………..HELP PLEASE
Brynn, that is hard to diagnose without having hands on the file. It is not clear what exactly you are seeing.
Keep in mind that there isn’t a direct relationship between “lots” in the Lot Tracking feature and your quantity on hand. You can have a different quantity on hand than what is show in the totals of all the lots…