QuickBooks Negative Inventory is BAD

You can use the inventory feature in QuickBooks to keep track of the “quantity on hand” (QOH) of inventory parts and assemblies, but this can add a dimension of complexity that can create problems for a small business. One of these problems has to do with allowing negative quantities to occur. Let’s talk about why QuickBooks negative inventory happens, why it is a bad thing, and perhaps what you can do to prevent it.

Why Negative Quantities Happen

It’s pretty simple. You enter a sales transaction for items that you haven’t received yet. There are a lot of reasons why this might happen.

• The simplest reason is that inventory control takes time. If you want to track inventory quantities you have to enter every transaction that affects inventory. If you have a small business, this can create a lot of pressure. You HAVE to get the invoices out because that is how you get paid. Inventory receipts? Well, not as urgent, you’ll get those updated when you have the time. So, sales happen before receipts, QOH goes negative.
• Another case is that people don’t always understand how QuickBooks works. Perhaps they don’t WANT to keep track of the quantity on hand, but someone turned the inventory feature on and created all their items as inventory parts. They sell the items, they don’t worry about receiving items, QOH goes negative.
• You could be drop shipping items to a customer, so that I actually want to record the sale before the item is ordered. QOH goes negative.
• Perhaps you are careful about entering your receipts and tracking inventory. However, in most versions of QuickBooks, an item receipt is the same transaction as a bill- so if you receive the item before you sell it, but the bill for that receipt is dated after the sale date, when you change the receipt into a bill, the date change moves the receiving date to be after the sale. QOH goes negative.

There are other scenarios, but you get the idea. The core issue here is that QuickBooks allows negative quantities to occur. It may warn you that this is happening, but it still lets you, and there isn’t a way to make the software prevent it from happening.

Why QuickBooks Negative Inventory is BAD

OK, so, your inventory QOH is negative. What’s the big deal? How about incorrect financial statements, confusing reports, and damaged data files?

Yes, sounds bad, and it could be. I’m not going to say that ALL of these issues will happen ANY time that you have a negative quantity on hand, but they could.

According to Intuit you can see any or all of these problems if you have negative inventory quantities:

• Profit and Loss Cost of Goods Sold (COGS) amount incorrect
• Cash basis Balance Sheet out of balance
• Balance Sheet inventory amount incorrect
• Vendor reports contain errors
• Bills for inventory purchases showing up on income and expense reports
• Recurring data damage, according to Intuit (SLN73714) – you may see that running a rebuild will bring your b/s back into balance

That is a scary list of issues! I have understood, for years, about how negative inventory can affect COGS in your financial statements. Some of the other issues are not things that I run into very often (cash basis statements, bills showing in unexpected places). But the possibility of recurring data damage is a real concern.

I can’t show you examples of all of these issues, as often they don’t show up unless you have a large file with many items and transactions. Let me go through a very simple exercise to show SOME of the confusing issues you may see.

Here are a few transactions that I have for a new item in my item list, “Test Part”, that starts off with no quantity on hand:

• I’ll enter a bill for 2 of the item at $1.00 each on 7/1/2013. After this, the average cost of the item is $1.00.
• Next I enter a bill for an additional 2 items at $1.10 each on 7/2/2013. After this, the average cost is $1.05.
• Now I sell 6 of the items on 7/3/2013. Heck, I know that there are some on the way, UPS just hasn’t delivered it them me yet.

Let’s look at the transaction journal for my invoice. You can see that it is posting $6.30 to COGS, which would be the cost of 6 items at the average cost of $1.05.

That’s OK – what other cost could we use? We don’t know the actual cost of the two items that I haven’t received yet. My Profit & Loss statement will show this COGS value. My Balance Sheet will show an inventory asset value of –$2.10, but that is OK for now.

Now, one day later (7/4/2013), I receive those two items at a cost of $1.30 each.

If I had received them BEFORE the sale, my average cost would have been $1.133 and COGS for the sale would have been $6.80. However, if I look at the inventory item now it will show that the average cost is still $1.05? Not a problem in this case, I have a zero balance on hand. But, what about my financial statements?

Let’s look at the Profit & Loss Detail report.

The FINAL balance for COGS is accurate – it shows the correct value of $6.80 in this case. But if you are coming back to audit your reports you see some odd things:

• The original invoice transaction is still showing a COGS value of $6.30. If you look at the transaction detail for that invoice you will see this amount, not the more accurate $6.80. This means that you cannot look at a particular invoice to see the correct COGS value.
• You see a Bill showing up in the COGS section. That isn’t normal – a Bill should not be seen here normally.
• The amount of the COGS from the Bill is $0.50.

Easy to explain all of this when we look at a simple transaction. Sure, there is a Bill for $0.50 – that is the difference between the original COGS amount and the corrected COGS amount. No problem!

BUT, what if you have hundreds of transactions, hundreds of items, and you see these odd numbers? You can’t easily look at the COGS value for an invoice, you see odd numbers that don’t seem to have a direct connection to any transaction (try doing a “search” for a Bill amount of $0.50). These adjustments may bring things back to balance in THIS case, but if you are reviewing your records you are going to have a very hard time working this out.

And THIS is a case where the financial statements are accurate – think how bad it will be if there is actual data damage, as Intuit says can happen?

Diagnostic Help

I do want to point out one of the very nice features of QuickBooks Accountant (and Enterprise Accountant) – the Troubleshoot Inventory feature in the Client Data Review. This is a VERY helpful report that will point out negative inventory problems for you quickly. It is one of the first things I’ll look at with a new inventory-based client – to see if there are negative inventory quantities.

If you use this, make sure that the date range reflects the period you are reviewing. For general troubleshooting I want to make sure that the ending date goes at least through the current date (rather than a prior fiscal year, as you might use if you are working with financial statements for tax purposes).

Also make sure that you have the option in the lower right set to “any time in date range” if you are trying to eliminate all of the problems.

How Can I Fix This?

Some people think that it would be nice if QuickBooks had a switch that would prevent you from having a negative quantity on hand. That hasn’t happened yet – and I’m not really convinced that this would be a universally acceptable answer.

How can you avoid this from being a problem? Just don’t sell things before you receive them. Easier said than done, in some situations. To start, make sure that this inventory preference is turned on, so you at least get a warning if you sell something you don’t have.

If you absolutely have a situation where you must sell something before it is received/billed, consider using one of the non-posting transactions to generate your documents for the customer. Create an estimate, sales order, or pending invoice for example. These won’t post to your financial statements, and you can turn them into a posting invoice at a later date. This works in some situations, but not others.

Another option that I’ll mention is the Enhanced Inventory Receiving function found in QuickBooks Enterprise (V12 and later). Normally, a receipt/bill is a single transaction with a single date. This can be the cause of many date-related inventory issues where you receive an item but then get billed for it at a later date. Enhanced Inventory Receiving (EIR) changes this by splitting these into two transactions – a receipt and a bill. Sounds good! However, I’m not really a fan of this feature. There are problems with it, and a big issue I have is that once you turn this on you cannot turn it off. There are several articles in this blog that talk about some of the issues.

Other than that, all that is left is to change transaction dates so that you avoid having the negative quantity. You can move the invoice date up to the date of the receipt – but that can mess up your receivables aging. You can adjust the receipt/bill date back to the date of the invoice, but that creates problems for your payables due dates. If you are a manufacturer and you are building assembly items, moving the date of the receipt around can cause problems if you are using that part in an assembly build – since BUILDS cannot be done with negative inventory you may find that your assembly builds are turned into “pending” builds if you adjust receipt dates (and that is NOT good).

As you could see with my simple example above, QuickBooks will try to make an adjustment to correctly post the COGS values when you bring the negative balance back to a positive value. For simple situations, this works. You still have issues if someone is trying to audit your books, or explain why you have some odd COGS values showing in detail reports, but for many small businesses this is not an issue. The big concerns that I have, and the reason why I always try to “stamp out” negative inventory QOH, are:

• Businesses that never bring the balances back to zero or positive – so that COGS calculations are never accurate.
• Businesses that have turned on inventory control, but never do receipts (they should be moved to non-inventory parts).
• Intuit’s statement about corrupted data files and inaccurate reports – I don’t know how bad the situation has to be before we see those kinds of problems, but that is very scary!