Reconciling QuickBooks Inventory Balances
An important part of controlling your inventory is to make sure that your company financial statements match your inventory status. The inventory asset balance in your Balance Sheet should match the value in the Inventory Valuation Summary report. Unfortunately, these values don’t always match, and in this article I’ll talk about some reasons why this can happen, and ways to reconcile your inventory balances.
If you have an inventory related business it is imperative that you maintain good control over your inventory. All too often businesses don’t pay enough attention to their inventory, but proper inventory management can make the difference between making a profit or failing. Managing inventory can be a time consuming chore, but if done properly the benefits outweigh the pain. When I work with a client who is managing inventory one of the monthly chores I set up for them is to compare the inventory asset account balance in the Balance Sheet with the total asset value in the Inventory Valuation Summary. They should match! There are several reasons why they might not.
Let’s look at a very simple test company that I’ve set up. Compare the values in these two reports:
As you can see, the balances differ.
Looking for Journal Entries
One of the most common reasons is that somebody has made a journal entry directly to the Inventory asset account. This shouldn’t be done – the Balance Sheet will see the adjustment, but the Inventory Valuation Summary won’t. To find these adjustments double-click on the Inventory Asset account in the Balance Sheet to open the Transactions by Account report. In my sample you can see that there is one General Journal entry affecting the assets (note that you can sort this report by type to arrange all of these so they are in one place).
How to correct this? Generally you should notdelete these transactions. They are there for a reason (although you should find out what the reason is, and see if you can avoid doing them). To get the reports to balance you can make another journal entry to reverse or correct the existing entry. You have to be careful to not make changes that would affect accounting periods that have already been closed or have been included in tax returns. However, in my sample, I’m going to delete the transaction by double clicking on the line to open the transaction, then pressing ctrl-D to delete it. Here are the reports after the deletion – notice that the Balance Sheet has changed and we are getting closer, but it still does not match the Inventory Valuation Summary.
Inactive Items
The next thing to do is to look for inactive inventory items that have a balance on hand. You shouldn’t hide an inactive item if it has a balance on hand – it isn’t truly “inactive” if you have some. The Inventory Valuation Summary won’t include inactive items, but the Balance Sheet will still include the value of any you have on hand. If we click the include inactive box on the item list, you can look for any inactive items with a balance on hand. As you can see, we have one item that fits this description.
If we make this item active, you can see that the reports now match.
Report Dates
QuickBooks is very date sensitive. It reports values and quantities based on the dates of the reports and the dates of the transactions. It also is very forgiving when entering dates – you can easily give a transaction a date in the future. This can sometimes cause problems if you are not careful with how you handle dates in reports.
Look at these two reports. Note that the asset values don’t match:
Note that the date ranges on the reports don’t match. The reports are generated on 4/1/2009, but we have a date range of all for the Inventory Valuation Summary. As I’ll show below, the reason is that there is a transaction dated in the future which is showing in one report but not the other. The simple answer to this is to make sure that both reports use the same date selections.
This brings up another common question that I often see – why does the Inventory Valuation Summary show a different quantity on hand than the Item List?
This also is a report date issue. Note that the Item List is not date sensitive, it shows all transactions. So we have a similar situation – the Item List balance has a future dated transaction, the report’s date probably doesn’t include that.
If I right click on the item in the Item List and select the QuickReport for the item, and set the date range to all, you see that we have an item receipt dated a week in the future. This shows on the Item List but not on any report that doesn’t look to the future.
This covers the most common situations you will see. If anyone has other situations that can cause imbalances, let us know!
Jeannie | Apr 2, 2009 | Reply
What if the two totals for inventory are different on the Balance Sheet depending on the parameters you set for the report having the same date?
For instance in the Columns box you put ‘Totals Only’ and get one number and set the same report to ‘Today” and get another number?
Martha Rayon | Apr 10, 2009 | Reply
Very helpful article. Thank you. I will start doing regular comparisons between asset value and inventory valuation reports.
Allen | May 1, 2009 | Reply
What if you have checked both of these solutions and it still does not balance? Is there anything else that could be causing it that I can look for?
Charlie | May 1, 2009 | Reply
Allen, hard to say. I would pick a date in the past and run reports up to that date, to see if I can find a date when they balance. Once you find that date, then try a date that is later by a short bit, to see if there is a difference. Once you find that, then examine the detailed reports to see what you find.
Ditto Pietrzak | May 4, 2009 | Reply
Charlie –
I have a couple questions regarding inventory – 1st on the BOM – how can we delete an empty line that falls between two items listed on BOM? I have found that that has messed up the inventory qty and value if you have this empty line on the BOM. I am new to QB and the company and just trying to get their inventory straighten out.
Also we have inventory parts with no average cost and when we do an inventory adjustment it comes out or goes into inventory with zero value – how can I get a cost into the average cost to equal the BOM on an assembly part or an inventory part?
Thanks so much for your articles – it has helped me a lot.
Charlie | May 4, 2009 | Reply
Ditto: See this article about editing the BOM: http://qbblog.ccrsoftware.info/2008/03/editing-your-bill-of-materials/
Regarding cost – Why does the item not have a cost? You either can edit the original transaction that should have had a cost (receipt, or build) or you want to do a “value adjustment” to give the item a cost. Note that this changes your balance sheet values, so it should be done with caution. Make a backup copy of the file first before trying this.
Ditto Pietrzak | May 5, 2009 | Reply
Thanks Charlie –
I can see why we don’t have any average costs for some of our items. Instead of doing a build to inventory someone just did a inventory adjustment to get the inventory in.
I am very new to QB and the current employer (2 weeks) and my project is to straighten out their inventory and costs.
I am picking up on QB and you have been a great help.
Ditto
caca | Oct 2, 2009 | Reply
How to post the year end in quick book?
Charlie | Oct 2, 2009 | Reply
caca: I’m not sure what exactly you are looking for, that can be interpreted in several ways. However, two comments. First, in QuickBooks, in many cases you don’t HAVE to do a formal “closing” or posting to close a year. Second, there are various procedures you can do at year end – there is a very good list in the QuickBooks help file that will lead you through that process.
Meghan | Oct 8, 2009 | Reply
I’m starting work for a small business in the next couple of weeks and they have not posted their inventory in several years, so the inventory is completely messed up. They would like to reset the inventory to zero and start over, but only on the inventory section. How do you do that?
Charlie | Oct 8, 2009 | Reply
Meghan, there are several ways of doing it, depending on what you want to do and how many inventory items you have.
The inventory items represent a value in your balance sheet. You have to find a way to reconcile that. You should talk to your bookkeeper or accountant about that.
One thing you can do is to create a “clearing” account in the COA, and then doing one big massive inventory adjustment to set all items to a zero on hand balance, posting to that clearing account. Then you have to take a physical count of your inventory and add back the items. You could use that same clearing account. When done, if you have a balance left in the clearing account, that is the difference between what you think you have and what the program said you have in inventory assets. You have to reconcile that somehow, and that is a question for your CPA.
Meghan | Oct 8, 2009 | Reply
Okay,how do you get to the clearing account in the COA and what steps do I do to do that?
Charlie | Oct 8, 2009 | Reply
Meghan, you would add an account to your chart of accounts. The type of account is up to you and your CPA. It could be an “other asset” account perhaps.
Once you have added it to your chart of accounts you will be able to post to that in the inventory adjustments screen.
Nevin | Nov 2, 2009 | Reply
Charlie,
Thank you for such an informative website.
I have built “assemblies” from “parts” and would like to be able group parts under inventory subcategories on the balance sheet report. I attempted to map the “parts” under the Inventory Information section on the edit item screen to a sub-account of the Inventory Assets account, but couldn’t get correct figures.
Any suggestions?
Charlie | Nov 19, 2009 | Reply
Nevin, I can’t say much without seen the details of what you have set up. Sorry…
Janet Cornelius | Dec 30, 2009 | Reply
My balance sheet asset value is approx $42k over my asset value on the Inventory valuation. I have check all that youl list but it is still off… are there any other reasons other than what you mentioned?
Charlie | Dec 31, 2009 | Reply
Janet, what I list here are the most common issues. Note that you sometimes have to really dig in to find all of them. The next step is to try to go back in time to find a moment when the two numbers agree, then move forward a month at a time to see if they unbalance, then examine the transactions to see what is going on.
Kate | Jan 27, 2010 | Reply
How can I fix the problem of our Inventory Asset amount not matching our actual inventory value? We did a physical count and verified values for year end but our Balance Sheet is leaving off items purchased before January 1, 2009. I have expanded the dates to include the months that should add to the collective total of our Inventory Asset, yet it still only recognizes last year. I have verified the cost of each item in our inventory so that is not an issue. However, when I add the total of the excluded inventory items to what our Balance Sheet Inventory Asset amount shows, the amount is still not correct to the actual value from our inventory audit at year end. I understand this is a complicated problem, can you help?
Charlie | Jan 28, 2010 | Reply
Kate, beyone what I have in this article, I would have to see the file itself. Any ProAdvisor should be able to help if they have the Accountant’s Edition (and it would be helpful if you have the 2010 version of QB). This would be something that I or any other ProAdvisor would charge a fee to do.