Merging QuickBooks Inventory Items — Beware

Here is how they set it up, and the disaster that ensued.

This particular person had decided to set up multiple inventory items for each actual item, setting a different price on each item for each price level. Then on sales forms, the user would just pick whatever item was priced at the level for that customer type.

This might seem to work, but it sure creates a mess if you’re trying to keep tight controls on your inventory. The problem is that now that you have two items, every time you purchase this product, you’ll have to record a purchase for multiple items. This turns out to be quite a pain because you have to allocate the cost of the total purchase among multiple items, all of which are really the same actual item.

Also, every time you sell the item, you have to pick the item with the correct sale price. This may seem easy at first, but it sure gets painful when you have thousands of items. Also, consider that the item list in QuickBooks Premier and below is limited to 14,500 entries, so if you create two or three items for each actual inventory item, you’ll run out of room pretty quickly.

I’ve seen this type of problem several times before, so I imagine that many (if not most) of you will eventually run into a similar problem at some point in your consulting career. And of course, you’ll arrive on the scene after thousands of transactions have already handcuffed you to fix not only the list problem, but also leave you needing to deal with the underlying data that may go back for years. It’s the data and the financial reports that make this problem REALLY hard to fix.

The first idea you’ll have is this: What if I just merge all those items that are really the same item into a single item? It sounds perfect because QuickBooks does all the work for you, including changing all the historical transactions. And well, it may sound perfect … but wait just a minute. Let’s look a little deeper.

Let’s review the accounting behind the scenes when you use inventory in QuickBooks.

The accounting behind the scenes of inventory transactions:

• When you purchase an inventory item, QuickBooks debits the Inventory Asset for the amount of the purchase price.
• When you sell inventory, QuickBooks credits the Inventory Asset and debits Cost of Goods Sold for the average cost of that item at the time it is sold.
• As purchases occur, the average cost of each item fluctuates to reflect the average cost of all units on hand at that moment. The purchase debit always equals total units times the cost per unit (for that purchase), but the credit to inventory when you sell the item fluctuates with the average cost of all units on hand as of the date of the sale.

So now let’s look at the problem that can occur when you merge inventory items.

When you merge inventory items, QuickBooks merges ALL
historical transactions, including the purchases, sales, average costs, etc. While that may seem fine, the results sometimes cause prior period financial statements to change, which is almost always not what you intended.

In the table below, notice what happens to the balance in the inventory asset when two inventory items are set up with different costs, followed by purchasing, selling and merging. The table shows two examples. The first example shows how it works fine if all the quantities are the same, but the second example shows how things go haywire when the purchase and sale quantities are all over the map. Of course, the second example is much more likely to be the case when you arrive on the scene. But still, let’s compare the two scenarios.

In example 1, notice that when the merge
happens, the average between the two costs is used. Everything works out great because both items have the same quantity on hand, making the total cost
(and average cost) the same before and after the merge.

However, in example 2, notice you have a difference in the total inventory value ($697 vs. $670). The problem has to do with the “weighting” of the average costs. That is, the quantity on hand just before the merge (Test1 – 12@$10 and Test2 – 22@$25), becomes a total quantity of 34@$20.50=$697. The weighted average of the costs is $20.50, and the total quantity is multiplied by that average to arrive at the total value of the on-hand inventory.

Therefore, because the number of units purchased and sold before the merge use the “pre-merge” average cost, the act of merging the items together causes a recalculation of the averages. This results in a different inventory reduction on all sales transactions, and
that means all historical financial statements will be different after your merge. Of course, this is an
unacceptable situation.

Merging Inventory Items in QuickBooks: Good Idea or Bad Idea?

As you can see, merging inventory items in QuickBooks is not generally a good idea. I’m still glad the feature exists, because in a few rare cases, I may determine that it’s faster to merge. But in general, it’s really something to be avoided.

Note that this problem is exactly the same for ALL versions of QuickBooks, regardless of the year or edition. It is not a bug in the software, but rather a feature that best practices would dictate us not to use.