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!
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?
Jeannie,
I have the same problem…and still don’t know why.
Not sure what you are saying – because Jeannie is mixing different features. “Today” and “Totals Only” aren’t the same parameter. I’m confused as to what is being asked, sorry.
Very helpful article. Thank you. I will start doing regular comparisons between asset value and inventory valuation reports.
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?
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.
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.
Ditto: See this article about editing the BOM: https://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.
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
How to post the year end in quick book?
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.
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?
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.
Okay,how do you get to the clearing account in the COA and what steps do I do to do that?
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.
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?
Nevin, I can’t say much without seen the details of what you have set up. Sorry…
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?
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.
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?
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.
What is the best way to track material purchases that are paid up front with a Debit Card then charged to our customer on an invoice at our cost.
Thanks
Rhonda
A debit card is just like a check from that account. Enter a check transaction against that account, set the check number to something like “DB”, and add the items on the “items” tab in the check window.
Thank you! Charlie,
One more quick question since we give our customer the original cc rec’t (we keep a copy) I want one cc rec’t to be listed as one line item on our customers invoice ex; Line Item 1 Ace Materials $10.00; Line item 2 Lowes $20.00. Do I have to enter an item for each rec’t? I originally set up an item for each Hardware Store where we purchase materials. Ace, Lowes, Home Depot etc, etc. If I select Ace’s Item for an invoice that I am creating today for $10.00 and enter a received payment and post this payment to the bank then I select the Ace item for a new invoice and I want to charge the customer $20.00 for materials purchased it changes my first item to $20.00.
Whew- that was a long question/explanation.
Thanks Again
Rhonda
Rhonda, my apologies – this kind of blog/comment setup isn’t a good way to answer questions that are off the topic of the blog article. I would need a better understanding of what you have set up and how you are doing things, and a detailed answer is hard to give in the space I have here.
A better place for questions that you might have would be the Intuit Community Forum at http://community.intuit.com/forums – where you can ask questions and get answers (the clearer the question the better the answer, usually).
Charlie,
I had my POS and Balance sheet inventory balanced at the beginning of this year. I started keeping a reconciliation from January 1. I noticed one thing that caused me to be out of balance was invoices being received on POS and when it is posted on QB side it uses the invoice date which could be 1 to 2 weeks earlier than we actually receive the item. To solve this, should I use the received date instead of invoice date? I am currently out of balance by over 5900.00. I was going to try to go through the inactive items, but I do not see that include inactive box on my POS side. I am using 2010 QB and POS 8.0. I am a new employee here since August and new to POS inventory .
Thanks
Lorraine
Lorraine, unfortunately I don’t work with POS so I don’t have a good answer for you. You should find a POS expert who is local who might be able to help.
Charlie,
We have just purchased Quickbooks. We have been in businesss for 2 years. We have a inventory listing of $8K. I entered in my beginning balances into the Balance sheet on for the date 01-01-2010. I now wish to enter inventory items. The entering of these items are adding to my Balance sheet. How can I get in beginning inventory items without doubling my Inventory Balance sheet account.
**Note** Quickbooks recommends entering them at 0 cost and then continuing forward with my new purchases. This will throw off the average cost of goods sold. This is not a proper accounting procedure.
Becky, you don’t mention how you are entereing the quantity of the items. I’ll assume that you are using the “on hand” field in the “new item” screen.
This posts the total value (the on hand times the cost) into two accounts – the inventory asset account you have selected, and “opening balance equity”. So you have two accounts that have been adjusted. Normal procedure is to then do a journal entry to clear out opening balance equity.
I’m not an accountant, but it seems to me that if you clear out opening balance equity against whatever account you used to enter your beginning balance in the balance sheet, everything should be back to normal…
**One more note** It has to be an inventory part because we want to track our inventory numbers. It cannot be non-inventory part.
Awesome summary! Thank you.
One question I have….. When I run an Inventory Summary Valuation Report in QB, I cannot mathematically recreate the Asset Value. If I multiply the quantity by the average cost, I do not get the value. Why is this?
For example:
On Hand Quantity is 7680
Avg Cost is 0.6800
Asset Value is 5191.68
If I do the math on Qty x Cost the expected value would be 5222.40. Why is this not the value on the QB report?
Thanks,
Lisa
Lisa, hard to say without looking at the file. Have you looked at an Inventory Valuation Detail report?
Hi,
In Quickbooks, how do I account for inventory taken out of the warehouse for shows, repairing customer machines, etc… The inventory will return, but for the time, my physcal count does not match quickbooks.
Thanks
Mark, inventory used in repairs wouldn’t be coming back, would it?
For things that do return, it depends on what version of QuickBooks you are using. You can just “sell” it at a zero price, then delete the invoice. Not a great solution but it works. You can even set up your shop as a “customer” and have all of these “invoices” there.
If you have Premier, you can turn on the “available inventory” feature, then put these items on sales orders. That removes them from “available” inventory, but doesn’t make a financial transaction. You can see all the items out on sales orders in several reports. You can either return them (delete them from the sales order) or end up selling them to the customer if you wish.
Hi Charlie,
I’ve been reading your articles for some time and have finally subscribed. I have what I consider to be a unique problem. For some reason my balance sheet and inventory valuation report are $2,160.62 apart. I’ve narrowed it down to 4 part numbers (my company has about 1,300 different parts and assemblies), whose values are all zero on the inventory valuation summary but, when I run an analysis on the inventory account’s quick reports they all have balances that equate to the aforementioned difference. There are no journal entries to the inventory accounts so I can not figure out for the life of me why there is a difference? Any ideas on where the problem may be?
Thanks,
Almost forgot, there are no inactive inventory items in this set of books.
Pete, it is hard to tell without having hands on the file.
Have you run a “rebuild” of the database lately? Examined the QBWIN.LOG file for errors to fix? Sometimes imbalances are due to database problems.
Charlie,
I just ran a rebuild and it appears to have fixed the problem. I consider myself to be an advanced user of QuickBooks but, I never attempted a rebuild much less, thought about using it in this situation.
Thank You For Your Help!
Peter
Glad that worked, Peter! The QB Database is, unfortunately, prone to accumulating errors. Sometimes you just have to do a rebuild to fix things. Sometimes sorting the lists will fix things. Also, do you use the “QuickBooks Backup” feature to make a backup periodically? WIth “Verify” on? If you don’t do that once in awhile, problems can build up and the transaction log gets bigger, slowing things down…
I have follow the article in all aspects but still unable to find difference. Please advise if there is anything else to check.
Vikram, when you get to that point, it is best to work with a qualified QuickBooks ProAdvisor, who can look at your specific file and see what is going on.
I found 3 general journal entries that were made directly to the inventory asset account. I wanted to correct these..how do I do that?
I can’t directly answer that because I don’t know why those JE’s were made, if it is in a prior tax period, or any of a number of other questions. You can just delete them and then make the proper kind of adjustment (perhaps an inventory adjustment?) to do the change the correct way, BUT don’t just go off and do that. You have to know why the JE was made so that you can make the proper correction, and any time that you work with a “closed” period you have to be careful as to what the tax implications might be. You may want to talk to an accounting professional before jumping in to do that.
mots of our items are post to non-inventory.We did a trial run to one item with build components by changing non-inventory to inventory assembly.It is the first time that the inventory asset code appears in our balance sheet. The inventory assest pulls out the history of the build parts. Now the bookkeeper freaks out because it will affect her tax report from previous years. Can you please advise what to do.
You don’t give me enough info to make any sort of comment. The best I can say is that an inventory assembly item is similar to an inventory part. It has an asset account, it represents an asset. If you build an assembly, you have a quantity on hand, it has a value, that value is represented in the balance sheet in the asset account. That is the way it works.
As far as affecting previous years, all transactions have a transaction date and that is when that transaction is booked.
If you are talking about the effects of changing an item from one item type to another, well, that can affect prior years. The answer there is to not change an item type like that. Better to create a new item for the assembly, rather than change the type.
I have the issue where the GL’s have caused the amounts to be out. My Inventory Asset Account is currently 399628.00 and my valuation report shows 465604.62. I have a “No Items” on my custom summary report of the difference 65976.53. I can’t delete the GL’s as they were there to fix an issue with Quickbooks not valuing the inventory at the right amounts due to a negative inventory issue with the file set up and they are needed. What entry do I need to make to fix this issue, without making matters any worse? Please let me know QB Tech Support has not been able to figure it out and I need to do month end financials for my bank that are accurate.
Tanya, I can’t really give you specific advice without having hands on the file. If you contact me directly via email (charlie@ccrsoftware.com) I can refer you to someone who can dig in to the file and help you fix the issue. Note that your “financials” don’t normally include an inventory valuation report.