TRUE JOE WAYS: QuickBooks Average Cost - Part 3

by

In our two previous articles we have seen how QuickBooks computes average cost, and how various transactions and user habits can influence average cost.  In today's article we will look at the effects of 'selling negative' on Average Cost, and subsequent replenishment of inventory.

The Impact of Negative Inventory on Average Cost

QuickBooks allows you to record the sale of inventory on sales forms even if there is not a sufficient quantity on hand. For example, assume you have 10 Widgets on hand in QuickBooks and you sell 15 Widgets – on an Invoice dated February 10. Obviously the QuickBooks quantity was understated because the additional 5 units were on the shelf. The sale is at $10.00 per Widget for a total sale of $150.00 (15 x $10.00)

QuickBooks will allow you to record the sale of all 15 units even though QuickBooks shows only 10 on hand. The resulting quantity on hand will be -5. (10-15). Since QuickBooks doesn’t know the actual cost for the 5 additional inventory items, the program “guesses” at the average cost and bases the guess on the average cost for the most recent quantities on hand. In this example the 10 Widgets have an average cost of $2.00, so QuickBooks will record the sale of the 5 additional units with a $2.00 average cost as well. The cost attributed to the sale will be $30.00 (15 Widgets x $2.00 Cost per Widget)

QuickBooks will post the following to the General Ledger (assuming no sales tax or other charges):

Here comes the interesting part. To compensate for the guess made on the invoice, QuickBooks patiently waits for a replenishment of stock – for example on a Bill or Inventory Adjustment.

You now enter a bill dated February 15 and receive 20 Widgets at $3.00 per Widget. The negative inventory quantities on hand for the Widgets create two problems at this point.

  • Even though the quantity on hand is no longer negative, the quantity is still understated. Since the starting quantity was -5, the increase of 20 brings the total on hand to 15 (20-5) instead of the 20 you just received.
  • Since QuickBooks now has a per-unit cost (e.g. average cost) for the additional 5 units, the program compensates for its guess on the invoice by adjusting Cost of Goods Sold and Inventory Asset on the Bill. The Bill should Debit Inventory Asset for $60.00 (20 Widgets x $3.00 per Widget) and credit Accounts Payable for $60.00 to offset. This post still happens, but an additional debit to Cost of Goods Sold and offsetting credit to Inventory asset also post to the General Ledger – to compensate for the incorrect guess on the invoice. In this example, the additional inventory post will debit Cost of Goods Sold by $5.00 and credit Inventory Asset by $5.00. ($3.00 per Widget Actual Less $2.00 per Widget recorded on the Invoice = $1.00 per Widget. $1.00 x 5 Widgets = $5.00)

QuickBooks will post the following to the General Ledger (assuming no or other costs on the bill):

Note: Assuming these adjustments to Cost of Goods Sold are material, the debit to Cost of Goods Sold on in this scenario causes several reporting issues. First, the income and associated cost may not be in the same reporting period. If the company manages profitability on a daily or weekly frequency this dating problem with Cost of Goods Sold becomes even more significant. Second, the cost is not assigned to the customer/job from the sales transaction. This will cause the Cost of Goods Sold to drop from the Profit & Loss by Job report and there is no way to assign a Customer/Job name to this post. You will have to transfer the costs to the applicable Customers/Jobs using a journal entry. However, determining the applicable Customers/Jobs is very difficult and time consuming. Third, QuickBooks does not associate the costs with the class used on the sales transaction. The subsequent impact on the Profit & Loss by Class report is similar to the impact on the Profit & Loss by Job report. The Cost of Goods Sold posts will show in the Unclassified column on the Profit & Loss.

A Note about Merging Inventory Items

When you merge two inventory items, you consolidate their average cost timelines into a single item record. This change to the inventory item’s timeline will likely have the following impact on historical transactions:

  • QuickBooks will post different amounts to Cost of Goods Sold and Inventory Asset for each sales of the now merged item record.
  • If either or both of the items have or had negative quantities on hand, the consolidated item may cause the negative quantity histories for either or both items to evaporate – causing QuickBooks to shift posts to Cost of Goods Sold from purchase forms/inventory adjustments to sales forms. The inverse could also be true. The inverse is also true – where the consolidated part record could have negative quantities on sales transactions where this did not previously occur – causing posts to Cost of Goods Sold to shift from sales transactions to purchases and inventory adjustments.
  • If the inventory item is used as a component of an Assembly item, the change will impact the average cost of the Assembly Items as well and could cause some existing build transactions to be parked as Pending. You can only build Assemblies in QuickBooks if you have sufficient quantities on hand for each component (each item in the Bill of Materials). If the merger causes the combined part to have negative quantities on hand at different places in the inventory part’s timeline, pending builds may result. If the builds are now pending, the quantities on hand for Assembly Items could also go negative, causing future build transactions to show compensating entries to Cost of Goods Sold and Inventory Asset.

Editor's Comment: QuickBooks Enterprise 15 now has a new 'optional feature' that permits the QuickBooks Administrator to turn on a preference which prevents transactions from taking inventory negative.  You can learn more about this new feature in the related link below.