Product Tips
Negative Inventory and QuickBooksBy: Marilyn Sudbeck, CPA, Certified QuickBooks POS ProAdvisor
|
|
Situation
Your client is reporting negative inventory. If you find negative inventory numbers, even if the clients have not yet discovered anything amiss, any Profit and Loss (and many other reports) will not be correct.
Background
QuickBooks uses an Average Cost Basis when calculating inventory value. This is why you should never, never, never let inventory go negative.
Example: A couple of years ago I was working with an Enterprise Solutions client. In reviewing their P&L for April 2006, it was obvious that their gross profit was not even close to their standard of 40%. In researching the reason, I found the problem. They had a product with a retail price of $2.50. Their cost price was $1.65. But because they had allowed the item to go negative, the average cost was $256.12 each. Yes, that's right - an average cost of $256.12. And the sale of that product skewed their results.
Response
What can you do? Here are two options for avoiding this problem in the future, along with two options for cleaning up past records.
Future: Two Options
A. Use the Sales Order Function.
If clients need to sell the product before they have it in inventory, use the Sales Order function. The function is available in QuickBooks financial software in the Premier or Enterprise Solutions editions. In QuickBooks Point of Sale software, the function is available in the Pro version.
Use with Care. When using this approach, you just need to make sure your clients understand what will happen with a Sales Order. The key point is that a Sales Order is a non-posting document, designed with the expectation that users will later turn the Sales Order into a Receipt or Invoice at some point (both of which are posting documents and will be reflected elsewhere). On its own, a Sales Order will not affect inventory, and will not show up on any Sales reports. Some clients will expect otherwise in the absence of your advice and guidance.
B. Make the Purchase First.
When creating a new item to be sold, train clients to always make a purchase prior to making a sale. This is the preferred suggestion of the Intuit Tech Support Team. But this may be difficult to do in real life situations, especially when a company does a lot of special orders for their customers.
Cleaning up Past Records: Two Options
How to fix the books if the creation of negative inventory has already happened? I know of at least two methods.
A. Adjust Inventory within Program. Most often I use a clean-up approach that avoids the necessity of changing hundreds of dates (see Option B below). Especially if you are close to the beginning of the year, you might want to consider clean up using menu commands.
First, I have the clients do a physical inventory in QuickBooks financial software:
Vendors > Inventory Activities > Physical Inventory Worksheet
Then I or the clients adjust the inventory so everything is current and correct:
Vendors > Inventory Activities > Adjust Quantity/Value on Hand
Always check the box in the lower left that says Value Adjustment just in case you need to adjust the value of any item. The value may be incorrect due to effect of negative inventory. I typically make the adjustment account an expense account and call it something like "2007 inventory cleanup." That way I can see the impact of the cleanup on the P&L.
I also make sure the value of the inventory asset account equals the total of the inventory value in the Inventory Valuation Summary Report:
Reports > Inventory > Inventory Valuation Summary
In the case of POS, I make sure the inventory account on the balance sheet agrees with the value in the POS.
While this response may not be perfect, it is a way to get the clients on the right track going forward.
B. Change All Items Individually.
The suggestion from Intuit's Tech Support group is to go back to all the products and the times they went negative and change the date(s) of either the invoice or the receipt of goods. But if there are many inventory items, this could create a lot of work. There are also other potential drawbacks to this approach (see Editor's Notes following).
More from Intuit
Editor's Notes:
- From the QuickBooks Support site, here is the current Knowledge Base entry on Negative Inventory. As Marilyn notes, the Intuit-recommended approach is to go in and redo the dates of certain records:
- Drawbacks. Re-dating transactions could have other unsatisfactory consequences. When you create a Bill from the Item Receipt, the date the item is received (and the average cost) is changed from the date of the Item Receipt to the date of the Bill. For example, let's say you set up a new item and receive the items on an Item Receipt dated 2/1/08. You sell the items on 2/2/08 on an Invoice. You then receive the Bill on 2/3/2008.
Negative inventory causes bills to show in COGS detail reports when they should not
Within QuickBooks, the Item Receipt and the Bill are actually the same form. When you date the Bill as 2/3/08, you have essentially changed the date you received the item from 2/1/08 to 2/3/08 - which in this case causes inventory to go negative. This is one reason why Intuit suggests users need to go back and re-date many of these transactions back to the date of the Item Receipt.
However, the user needs to be reminded that re-dating the Bill will also affect aging reports and dates due. This example sketches a simple scenario; actual client files can be much more complex.
Our thanks to Marilyn Sudbeck for allowing us to share this tip. Marilyn is a CPA, Certified QuickBooks ProAdvisor, and Certified QuickBooks Point of Sale ProAdvisor, who has served as instructor for various Intuit training events, including Webinars on QuickBooks Point of Sale. You can visit Marilyn online at sudbeckcpa.com.
Last Updated: 03/03/2008
