When someone is setting up a new company file in QuickBooks a common complaint I see is that they are getting an incorrect quantity on hand for inventory items, or an incorrect average cost for the items. Today I’ll explain why this usually happens, and give you a little tip on how to correct the problem.
When you add a new inventory part or assembly you are allowed to enter an “on hand” quantity, and an associated “total value”, as shown here.
Note that you cannot change these values if you are editing an existing item. The only time that you can directly enter this information in this window is if you are adding an item.
Behind the scenes QuickBooks is creating an inventory adjustment transaction when you enter values here, something that isn’t obvious. This adjustment is taking the on hand quantity and total value that you entered and calculating the average cost for the item. I’ve seen several problems with this:
- When setting up a new company the user may also enter outstanding bills and payments, and with those bills they may enter a received quantity of the items. This may be a duplication of some of the items entered in the new item window.
- Many times the user will not know the accurate value of the quantity entered. QuickBooks is using this value to create the average cost of the item, which affects both the inventory asset account as well as cost of goods sold (COGS) when the items are sold later. Often I see the user entering a zero value here.
How to Correct the Problem
If the quantity is wrong most people will notice this fairly quickly, seeing the incorrect balance in the item list or an inventory report. If the value is wrong it might not be noticed right away. The problem becomes more apparent when you see that COGS values are not being updated correctly. The problem is, if you start looking at the transactions you have entered, such as invoices and receipts, you can’t see where the problem comes from.
You can’t go back to the item list and correct this in the Edit Item window, because once the item is added to the list QuickBooks won’t let you change the average cost or the quantity on hand in this window.
The quickest way to pin this down is to run a QuickReport for the item. In the item list, select the item and right click on it to open the context menu. At the bottom of the list you will see the QuickReport. Select this, and change the date range to “All” to make sure that it shows you all of the transactions for this item.
You will see the inventory adjustment that was created by adding the quantity on hand in the Edit Item window. Note that it has a memo saying Opening Balance.
To correct this, double click on the inventory adjust transaction that is listed in the report. This will open an Inventory Adjustment window.
Scroll down until you find the item, and you will see the quantity and value that were entered. Note that this transaction posted to your Opening Balance Equity account.
You can correct this by either deleting the transaction (press ctrl-d in Windows versions), or editing the quantity and/or value as is appropriate.
BE CAREFUL when doing this – changing this will affect your balance sheet and possibly your profit/loss statement. If the adjustment occurred in a prior period you may be changing values for periods where you have already generated financial statements and tax returns.
I built an iventory assembly, but I still have those quantities availiable on these items in my inventory list, eventhoug I used the full amounts on that assembly. What did I do wrong? How do I correct this problem?
Please help.
Thanks,
Lesly
That is a bit off this topic, Lesly, but I’ll give you some ideas.
First, look at the “build” transaction. Is it marked “pending”? If so, the build didn’t take place.
If it isn’t pending, then where are you looking at to see the quantity of the components that were consumed? If you are looking at a report, make sure that the report dates match the date of the build transaction. If the report is dated before the date of the build, the report won’t show the component consumption.
If you still aren’t sure, then find one of the items that should have been consumed, in the item list. Right click on it, display the “QuickReport”. Set the dates of the report to cover the period of time when you issued the build. You should see a transaction for that consumption, with a type of “build assembly”. If that is found, you actually did the build and it is showing up.
I am using different kinds of materials for every style of dress that I produce. Do I have to immediately codify the item upon purchase? Do I have to give a name for the product/style that I will produce upon purchase of the material? When I buy material do will I use the command received items and bill?
Emelinda: You have several options, it is hard to give you a direct answer via this blog/comment. It depends on how you want to cost things, and control things. If you want to control the amount of each type of material that you have, so you know what exactly you have in stock for planning purposes, you have to create an item for each one (inventory part) and you have to receive that item in the “receive inventory” feature – with bill if you want to track costs. If you want to generalize a bit, you can create an item for each general kind of item. Or, if you aren’t tracking specific costs of specific items, and aren’t tracking quantity on hand, you can just create a generic item for each general type of material as “non-inventory” items, which expenses them when you purchase and doesn’t track quantity.
Hi Charlie,
I would like your advise on the best way to keep track of inventory that I give to my sales reps. Basically, I give them inventory, which they go out and sell to stores. How do I keep track of the inventory that I gave them and the inventory that they later sell? i appreciate any help you can give me!
I can’t give you a full answer in a simple comment in a blog like this. In addition, I’d need to know a lot more about your situation and business practices before I could give you a good answer. Briefly: Assuming that you still own that inventory that you are giving to the sales reps, and assuming you have the Premier or Enterprise version of QuickBooks, I would consider creating a dummy “customer” record for each sales rep, and then create a sales order for each one. When you give them an item, add it to the sales order. If they return it or sell it, remove it from the sales order. Then all you have to do is to look at the sales order for that rep and you know what they have. Also, if you have your preferences et up properly, the items that are assigned to sales reps are not in your “available” inventory that you can sell (or give to another sales rep), but are still in your “on hand” inventory so that you can still show that you own them.
When an inventory assembly is returned, how do I get it back into stock
Valerie, for the assembly itself, you can either enter a credit memo (if you are reversing out an invoice) or an inventory adjustment.
For the components, that is tougher. You can do individual adjustments if you wish, for each component. Or you can delete the original “build” transaction – but note that this is a bit dangerous for several reasons, and I usually don’t recommend that.
Charlie,
Thank you for your help. When I do an inventory adj., which adj. account should I use? My book tells me to use 8220-Inventory Adj., however I do not have that option
Valerie, that all depends on what kind of transaction you used to consume the part in the first place, and why it was returned. Let’s say that you sold this item in an invoice, and it was returned because the person didn’t want it. You could just delete the invoice (if this was the only item on it) and that would return the item to stock and remove the receivable. You could create a credit memo and add the item to that memo – that would create a credit and return the item to stock. If you had used an inventory adjustment to consume it in the first place, you could either delete that adjustment or use the same account. There are hundreds of ways of dealing with it, you have to first look at the transaction that consumed the item and then at the reason for the return.
Hello Charlie,
Thank you for your great articles! They are very helpful.
I have a question about converting my existing non-inventory items to inventory items. With six year of history of selling these items, can I change over to using them “inventory items” without destrubing the history or am I getting myself into a big mess?
Regards,
Jim Bovard
Jim, I wouldn’t recommend doing that change. It could have a huge impact on your financial statements because non-inventory items are posted differently than inventory items, and QuickBooks usually tries to change the transactions to match the types. It could be a disaster.
If you want to see what happens, make a backup of your company file first. THen if you don’t like the results, you can restore that backup.
Charlie,
I am running a wood components supply business. We have thousands of inventory parts in QBooks, each of which starts life as one of a hundred or so raw material parts (we pull a raw part from inventory, finish it to a certain color). We do not inventory the parts that we sell as finished goods. Rather, we make everything To Order.
I’d like to find a way to keep track of the raw material components. I would like to define each item we sell as an Assembly Part, with the BOM containing the Raw Material Part. Then, when I enter a Sales Order for the Assembly Part, it would relieve the Raw Material Part from inventory. However, it seems that I can’t simply sell the Assembly Part, but must build it first. This would be very cumbersome. Any ideas?
Thanks!
Mark, how many different component items go into an assembly?
Your only two alternatives are inventory assembly items, which you have to build, or group items. Groups are limited to the number of components. The advantage to a group is that you don’t have to “build” it, it is set up to be a make to order item. The disadvantage is that you don’t get sales info on the sold “group” item. See my article comparing these at https://qbblog.ccrsoftware.info/2008/06/groups-vs-assemblies/
Thanks, Charlie! We can indeed use Groups. However, we now need to configure all of our parts (thousands) to work this way. Is there any way to change Inventory Parts to Item Groups? Is there any way to batch-configure? We often import item lists using excel. Is this something your software can do?
Thanks,
Mark Summitt
There isn’t a simple way to handle this, really. You might contact the people who make the Transaction Pro Importer to see if it will let you import group items and their components. https://qbblog.ccrsoftware.info/2010/01/importing-quickbooks-transactions-with-transaction-pro-importer/
Hi Charlie
I had used import function in quickbooks to import all item data including quantity at hand.
After import in hand quantities are fine but the average cost shown is zero in all products.
It will create problem for me to assess Cost of inventory & COGS etc.
Please guide me to rectify this problem.
Regards
The original import needed to have a total value set with it, as well. But, that is too late, unless you have a backup that you can go to that is from before the import so that you can start over.
Other than that, you can manually enter a “value” adjustment for each item to enter the total value for the item, which will set the average cost. You have to do this through the user interface – or possibly get an import tool like Transaction Pro Importer from Baystate Consulting.