No Negative Inventory, Please

Negative Inventory? Never! (Never is a Long Time)

There is a reason Mario Nowogrodzki, renowned inventory management specialist, gets violently ill when informed that a client has negative inventory in their QuickBooks data file. Consider the case below of a client who allowed the quantity on hand of a popular inventory item to be negative more often than it was positive.

In April 2019 I received an email from a client desperately seeking help with poor performance of their QuickBooks Enterprise Solutions (QBES) v19.0 data file.

The client’s poorly performing QuickBooks data file size was under 420KB and contained approximately 1,000 inventory part and assembly items. Advanced Inventory was turned on in the file because they have multiple warehouses. The client began using QBES in January 2011. In 2019 Mendelson Consulting moved their data file from an on-premise server to an Azure cloud server managed by Mendelson’s cloud division Noobeh. The only application software running on the Azure server was QBES 19.0, Microsoft Office and a couple of web browsers (Internet Explorer and Chrome).

The issue with performance in their QBES data file was that it was taking an exorbitant amount of time to save some transactions – especially invoices created from sales orders. The client recognized that the common denominator in these transactions was a certain inventory part that was present on all those transactions. The client discovered the problem item when they tried to enter an inventory adjustment for that item only. The client complained that it appeared QuickBooks would “freeze” (stop responding) while saving a transaction including that inventory item. Saving a transaction that included the problem item was taking more than 40 minutes and, compounding on the problem, the client would often lose patience waiting for the transaction to save and abort the program.

Troubleshooting this, I recommended all the things we typically recommend when we suspect corruption in a data file.

The first steps are to verify the installation, like repairing and updating the installation of QuickBooks. Then file work including creating and restoring a portable company, resorting lists, and rebuilding the data (more than one time, consecutively). Doing all this resulted in no change in performance. I recommended that the client create a new item to replace the original problem item and merge the original item into the new item. Still no change. I then had the client create a new item to replace the original (now merged into a new item), however this time the original item was made inactive. New transactions involving the new item saved normally. I suggested replacing the original inactive item on the open sales orders with the new item, whenever possible. There were approximately 100 open sales orders containing the original item.

Fast forward to the first week of June 2019.

I received an email from the client that the problem appeared to be getting worse.

Open sales orders containing the original inactive item were taking more than 40 minutes to save when they tried to replace the original inactive item with the new item. Invoices created from the sales order containing the original inactive item were still taking longer than 40 minutes to save. To compound the problem, despite being invoiced in full, sales orders containing the original inactive item remained open with the only unfulfilled item being the original inactive item.

I decided to redo all of the above by creating a portable company from the client server and restoring the portable company on one of the Mendelson more powerful Azure servers. The server I used is more powerful than the client’s server because the environments on Azure are built specifically based on resource requirements, and we as QuickBooks consultants often require a lot more system resources to perform our data file work and tests.

Transactions that took 40 minutes or more to save on the client server took about 10 minutes to save on the Mendelson server – still an exorbitant and unacceptable amount of time. I then merged the original inactive inventory item into the new item, and while it took some time to complete, it successfully merged. I changed the name of the original item, adjusted the quantity and value to zero, made the item inactive, created a new item to replace it, and adjusted the quantity and value of the new item to the same as the original. I then went through all of the steps to ensure the data file was as healthy as possible and returned the file back to the client by restoring the portable company file on their server.

Shortly after, the client complained again at how long it was taking to save or edit a transaction with the problem item (remember, I merged the original item into the new item created back in April). These transactions were open sales orders containing the original problem item or invoices created from the sales orders. I recommended that the client create a new sales order for each of the open sales orders containing the problem item, then delete the original open sales order. The first sales order the client tried to delete took more than 45 minutes to delete. I tested deleting the same sales order in the file I had on the Mendelson server. I was able to delete it in 10 minutes (still not good). I then offered to delete all open sales orders containing the problem item, on the Mendelson server, thinking I could do so faster than the client could on their server, over the upcoming weekend. On Friday night I remoted into the client’s server, created a portable company file and restored it on the Mendelson server, and went to work deleting about 100 open sales orders.

The first sales order took about 10 minutes to delete. Same with the second. My hope was that perhaps one of these transactions was corrupt, and once deleted, all the others would delete quickly, normally.

As I watched the circle spin and spin while trying to complete deleting a sales order, it occurred to me that perhaps it was taking so long because QBES was doing a complicated calculation in the background.

I wondered if negative inventory was the problem. I decided to look at the history of the original item by generating an Inventory Valuation Summary report, double-clicking on the quantity on hand for the problem item, and changing the date range to all dates. What I found was astonishing.

The first transaction for the problem inventory item in the data file was an invoice dated 01/14/2011. Between 01/14/2011 and 03/26/2012, 175 invoices were created including the problem item. There were no purchases of that item during that time frame. There were two inventory adjustments dated 03/26/2012 involving only that item, adding 18,285 items.

The quantity on hand of that inventory item before the inventory adjustments on 03/26/2012 was -15,265 (yes, negative 15,265!)

There was a total of approximately 3,650 transactions involving the problem item, with negative quantities continuing several times in 2014, 2015, 2016, and 2017, and from 01/01/2018 thru 05/10/2019. To test a resolution, I adjusted the quantity (and value) on hand of the problem item to 50,000, dated 01/02/2011 (prior to any being sold). It took approximately 8 minutes to record that transaction. However, deleting a sales order including the problem item now only took seconds to save.

I immediately logged back into the client server and the client’s live file, and entered the same 50,000 quantity and value adjustment dated 01/02/2011. That adjustment took about 15 minutes to record. However, deleting the open sales orders took only seconds per deletion afterwards – as it is supposed to! I completed deleting all of the open sales orders in less than 2 hours, then deleted my 01/02/2011 adjustment. I then did all of the steps to ensure the data file was healthy, and emailed the client about what I had found and that the project was finished.

When an inventory item is sold before it is purchased, QuickBooks doesn’t know what the cost of that inventory item is and thus doesn’t know the dollar value to transfer from the Inventory asset account to COGS.

So what happens?

When quantity goes negative, QBES temporarily calculates COGS on the invoice date using the most recent average cost of the item sold, and continually updates and changes COGS until enough quantity is finally purchased to cover the quantity sold. If there are several purchases needed to cover quantity sold, QBES must recalculate the average cost per item with each purchase and go back and correct the COGS on every sale prior to the purchase. As I watched the circle spin and spin when trying to save a transaction, that’s exactly what QBES was doing – recalculating and updating historical COGS and the reports showing quantity available for sale. In this case that took more than 45 minutes per transaction.

Negative inventory is perhaps the worst cause of data file corruption and poor performance in a QuickBooks data file, period.

Negative inventory is a logical impossibility.

One cannot deliver a product to a customer that one does not have available for sale. One cannot point to or count negative inventory on a shelf in one’s warehouse.

Whenever Mario presents a session about QuickBooks inventory at a conference, he talks about negative inventory. He states that he becomes violently ill when he sees it, and this client’s experience confirms why. I joke that perhaps Mario won’t recover from this file incident (!!) While this client’s data had absolutely the worst performance we at Mendelson have ever seen, the explanation for the poor performance is the number of transactions involving that item and the frequency of negative quantity on hand for that item.

In 2015, Mario finally convinced Intuit to add a preference setting in QuickBooks to address this problem.

The preference setting won’t allow negative inventory, causing QuickBooks to issue a warning that selling this item will cause inventory to be negative. QuickBooks won’t allow the user to save the sales transaction until an adjustment is made or sufficient items are purchased on a transaction dated before or on the same date as the sales transaction. You can find the setting in QuickBooks Enterprise: Edit menu >> Preferences >> Items & Inventory >> Company tab.

Unfortunately, the preference is turned off by default.

We strongly recommend that users turn on this preference or, at a minimum, heed the warning if there are not enough quantity of items on hand to sell the item.

Contact Mendelson Consulting today to find out how we can solve the various challenges facing your business, like why your inventory isn’t right and your file performance is terrible. We have the solutions and the platforms that scale to your business needs today and in the future, and we have the experience and expertise to address even the most challenging problems you have with QuickBooks.