Inventory workarounds and troubleshooting in QuickBooks

By Joe Woodard, AccountingWEB QuickBooks columnist

Troubleshooting Average Cost

How QuickBooks Calculates Average Cost
For an inventory item, average cost is the total value of the items currently in stock divided by the quantity on hand. If you have a total Widget value of $20.00 and 10 units on hand, the average cost is $2.00 ($20.00/10).
QuickBooks uses average cost—and not another method such as LIFO or FIFO—to determine the value of your inventory.
Note: This section covers the calculation of average cost for normal and intended QuickBooks use devoid of user entry errors or data file setup errors. The rest of this topic covers the aberrations involved due to user error or changing list information and/or historical transactions.
 
QuickBooks recalculates average cost during normal QuickBooks use in the following ways:
·         When you record the purchase of an Inventory Part Item or the purchase of an Inventory Assembly Item. QuickBooks adds the cost of the new items to the cost of the existing stock and then divides by the total number of new and old items. If the per-unit cost of the Inventory Part or Assembly Item you purchase is the same as the current average cost, the average cost recalculation will not cause any change. For example, assuming you have no quantities of widgets on hand, if you purchase 10 widgets for $2.00 per widget the average cost is $2.00. If you then purchase 8 more widgets at $2.00 per widget, QuickBooks will re-calculate average cost but the calculation will not cause any change to average cost for the widgets on hand. The average cost for widgets will remain $2.00.
·         When you change the value or quantity of an Inventory Part Item or Inventory Assembly Item using an Inventory Adjustment. As with a purchase, an inventory adjustment will or will not cause a change to average cost for an item, depending on how you adjust the quantities and total value for each part. On an inventory adjustment you can preserve the average cost if you change the quantity on hand and value proportionally. For example, if you have 10 widgets with an average cost of $2.00, the total value is $20.00 ($2.00 x 10). If on an Inventory Adjustment you change the quantity of widgets to 20 units and the total value to $40.00, QuickBooks recalculates average cost, but the average cost does not change. The average cost is still $2.00 ($2.00 x 20 = $40.00). By way of contrast, if you change the quantity on hand for widgets to 20 but leave the total value at $20.00, the average cost will change to $1.00 ($1.00 x 20 = $20.00)
·          Record a build of an Inventory Assembly Item. This calculation is the most complex around Average Cost because there are multiple average costs involved. As with the first two examples the build will cause average cost to recalculate, but may or may not change average cost. QuickBooks considers the following around average cost when you record a build transaction:
o   The average cost of each Inventory Part Item in the Bill of Materials. To calculate the value of the Inventory Assembly Item you are building, QuickBooks uses the average cost for each Inventory Part Item in the Bill of Materials as of the date and time you record the build. For example, you can build 1 Assembled Gadget from 1 unit of “Widget A”, 1 unit of “Widget B” and 1 unit of “Widget C”. If each of the Widget items (components) has an average cost of $1.00 QuickBooks calculates a $3.00 cost for the one Assembly Gadget you build ($1.00 +$1.00 + $1.00 = $3.00).  
o   The average cost of any Gadgets already in stock. Then, QuickBooks absorbs this $3.00 Gadget into the average cost of existing Gadgets on hand. (This is similar to how QuickBooks recalculates average cost when you purchase a new inventory part, only you are building rather than purchasing.) For example, if you have 2 Assembled Gadgets on the shelf with an average cost of $2.00 per gadget, the total value of the assembled gadgets on hand is $4.00 ($2.00 x 2). If you assemble 1 new gadget (as described above) and the per-unit value of the 1 newly assembled gadget is $3.00 you add $3.00 of value to the assembled gadgets on the shelf and one unit to the on hand quantity. You now have 3 assembled gadgets in stock (2 + 1) with a total value of $7.00 ($4.00 + $3.00). The average cost for all gadgets in stock will be the new value ($7.00) divided by the new quantity (3), or $2.34.
Note: The calculation of the value assigned to Inventory Assembly Items during a build can be much more complex than the example above. For example, the Bill of Material for the Inventory Assembly Item can contain Non-Inventory Part Items, Service Items and Other Charge Items. Since these items are not related to inventory and do not factor into average cost, QuickBooks uses the values you enter into the Bill of Materials as part of the value of the assembled item - arbitrarily. For example, if in the Bill of Materials for an Assembly Item you include $1.00 of labor (Service Item) and $1.00 of overhead (Other Charge Item), QuickBooks will add $2.00 to the value of each Assembled part you build. If you build 1 part, QuickBooks adds $2.00 to the value. If you build 2 parts, QuickBooks adds $4.00 of value. The General Ledger posts are a debit to Inventory Asset and a credit to the accounts in the Service Item and Other Charge Item.


 
Note: As the most complex build scenario, you can include an Assembly Item in the Bill of Materials for another Assembly Item. However, this calculation is not as complex as it sounds. It is actually very similar to use of an Inventory Part Item in the Bill of Materials. Each Assembly Item in stock has an average cost. When you use the Assembly Item in the Bill of Materials for another Assembly Item, QuickBooks uses the average cost of the Assembly Item (component) and the Inventory Part Item (component) in the same way – based on the average cost of the component as of the date and time you record the build.
Average Cost and the Sale of Inventory
Note: This section covers the role that average cost plays in the normal and intended use of QuickBooks devoid of user entry errors or data file setup errors. The remaining sessions of this topic cover the aberrations involved due to user error or changing list information and historical transactions.
The sale of Inventory Part Items and Inventory Assembly Items does not change average cost. However, average cost does play a key role. QuickBooks posts a credit to Inventory Asset and a debit to Cost of Goods Sold for each inventory item you enter on a sales form. QuickBooks calculates this post to Inventory Asset/Cost of Goods Sold using the average cost of the item as of the date and time you record the sales transaction.
For example, if you record an invoice for the sale of 3 Widgets at $10.00 per Widget ($30.00 total) and the Widgets have a $2.00 average cost, QuickBooks will post the following to the General Ledger (assuming no sales tax or other charges):

Account
Debit
Credit
Accounts Receivable
30.00
 
Cost of Goods Sold
6.00
 
Inventory Asset
 
6.00
Income
 
30.00
The average cost for the Widgets does not change because the quantities decreased proportionally to the value. For example, 10 Widgets with a total value of $20.00 have an average cost of $2.00. 7 widgets with a total value of $14.00 have the same average cost of $2.00.
The Perpetually Posting Nature of Average Cost
QuickBooks is a perpetually posting system – meaning that every transaction remains active in the database. QuickBooks then refers to these individual transactions when calculating average cost as well as the values Transaction Summary and Detail reports.
For example, QuickBooks computes the balance in a bank account on the Balance Sheet by totaling the individual banking transactions with all of their increases (debits) and decreases (credits). If there are 10,000 transactions for the account, QuickBooks refers to each of the 10,000 transactions individually each time you create a Balance Sheet. QuickBooks does the same when computing the Beginning Balance on a bank reconciliation, only for this calculation QuickBooks excludes historical transactions that are uncleared.
The same principle applies to average cost. QuickBooks considers every increase and decrease in value and quantity on hand for the entire history of the item when computing average cost. By concept, the average cost resets each time the quantities on hand are zero. When computing average cost manually (e.g. to proof the QuickBooks calculation) you only need to factor in the changes to quantity and value since the last time the product had 0 quantities on hand. However, QuickBooks considers the entire item history in its calculation.
Tip: If you want to reset the history for an item that has zero quantities on hand, first edit the name of the existing item (e.g. by adding a “–old” suffix to the name). Then, make the old inventory item inactive and create a new inventory item to replace the old inventory item. Each time the user selects the item on a purchase or sales form QuickBooks will use the new item you create. Historical average cost will calculate as of the date of the first purchase transaction or inventory adjustment for the item that increases the quantity on hand. Consider that you will need to edit memorized transactions, item-specific memorized reports and the Bills of Materials in Assembly Items so they refer to the new item rather than the old. As a result, this re-set option may not be practical for clients who need to make changes both items and the associated memorized reports/transactions and lists.
Impacting the Average Cost Timeline
The computation of average cost is perpetual and can change day by day, hour by hour or even minute by minute as users change the quantities and/or value of inventory on hand in all of the ways described above.
Because the calculation of average cost is dynamic – not static – the date and time you record inventory transactions (especially sales and build transactions) will impact the debits and credits to Inventory Asset and Cost of Goods Sold.
Consider the following timeline
February 1 – Enter a Bill to receive the initial stock of 10 Widgets at $2.00 per Widget creates a total value on hand of $20.00 and an average cost of $2.00.
February 2 – Enter an Invoice to sell 3 Widgets at $10.00 per Widget. The debit to Cost of Goods Sold and the credit to Inventory Asset are in the amount of $6.00 (3 Widgets at $2.00 each = $6.00). 7 units remain on hand with an average cost of $2.00 per unit or $14.00 in Widget value on hand.
February 3 – Enter a Bill to receive additional stock of 10 Widgets at $3.00 per Widget. The Bill increases the value on hand by $30.00 for a total value on hand of $44.00 ($30.00 + $14.00). The quantity on hand increases to 17 (7 + 10). $2.59 is the new average cost ($44.00/17).
February 4 – Enter an Invoice to sell 3 Widgets at $10.00 per Widget. The debit to Cost of Goods Sold and the credit to Inventory Asset are in the amount of $7.77 (3 x $2.59).
This timeline is very straight forward. However, consider that the debits to average cost and inventory asset on the invoices is dynamic, not static. So, any change to the average cost timeline for the Widget causes a cascading change to subsequently dated transactions that include the Widget.
See the modified timeline below where all changes to the timeline are bolded
 February 1 – Enter a Bill to receive the initial stock of 10 Widgets at $2.00 per Widget creates a total value on hand of $20.00 and an average cost of $2.00.
February 2 – Enter an Invoice to sell 3 Widgets at $10.00 per Widget. The debit to Cost of Goods Sold and the credit to Inventory Asset are in the amount of $6.00 (3 Widgets at $2.00 each = $6.00). 7 units remain on hand with an average cost of $2.00 per unit or $14.00 in Widget value on hand.
February 3 – Enter a Bill to receive additional stock of 10 Widgets at $3.00 per Widget. The Bill increases the value on hand by $30.00 for a total value on hand of $44.00 ($30.00 + $14.00). The quantity on hand increases to 17 (7 + 10). $2.59 is the new average cost ($44.00/17).
February 4 – Enter an Invoice to sell 3 Widgets at $10.00 per Widget. The debit to Cost of Goods Sold and the credit to Inventory Asset are in the amount of $6.00 (3 x $2.00).

February 5 – A user edits the Bill recorded on February 3 so that it is now dated February 5. The increase in Average Cost from $2.00 to $2.59 is now dated after the date of the February 4 invoice.

Common Use Errors Related to the Average Cost Timeline
The change to the date of the bill in the scenario above is a very simple historical change to average cost. As users make changes farther and farther into the past, the greater the cascading impact you will see on various reports including the Profit & Loss, Balance Sheet and Inventory Valuation Summary reports. This segment cover the most common changes users make to historical average cost and the impact of these changes on the General Ledger and on inventory valuation.
Note: Not all changes to the average cost timeline create inventory problems. For example, most accounting professionals are not concerned with the small change in the example above to average cost that impacts sales from one day in the past and for an immaterial amount. However, edits to historical inventory adjustments and purchase transactions (even those dated very recently) can cause inventory to show negative quantities on hand and the resulting impact on the file can cause other reporting issues, including distorting Profit & Loss by Class and Profit & Loss by Job reports. See the section on negative inventory below for more information.
Entering an Inventory Adjustment Dated in the Past
When you enter an inventory adjustment dated in the past, QuickBooks re-calculates average cost not only as of the date of the inventory adjustment but also for all transactions dated after the date of the inventory adjustment. Consider the change that a back dated Inventory Adjustment entered on February 5 and dated February 1 will have on average cost. All changes to the timeline are bolded below.
February 1 – Enter a Bill to receive the initial stock of 10 Widgets at $2.00 per Widget creates a total value on hand of $20.00 and an average cost of $2.00. Back-dated Inventory Adjustment changes the average cost to $1.33 ($20.00/15).
February 2 – Enter an Invoice to sell 3 Widgets at $10.00 per Widget. The debit to Cost of Goods Sold and the credit to Inventory Asset are in the amount of $3.99 (3 Widgets at $1.33 each = $3.99). 12 units remain on hand with an average cost of $1.33 per unit or $15.96 in Widget value on hand.
February 3 – Enter a Bill to receive additional stock of 10 Widgets at $3.00 per Widget. The Bill increases the value on hand by $30.00 for a total value on hand of $45.96 ($30.00 + $15.96). The quantity on hand increases to 22 (12 + 10). $2.09 is the new average cost ($45.96/22).
February 4 – Enter an Invoice to sell 3 Widgets at $10.00 per Widget. The debit to Cost of Goods Sold and the credit to Inventory Asset are in the amount of $6.27 (3 x $2.09).
February 5 – Enter an Inventory Adjustment dated February 1 that increases the quantity on hand for Widgets from 10 to 15 but does not change the total value.
Editing Historical Transactions to Change Quantities or Value on Hand
The one of the example above - an edit to the date of a Bill - changed the timeline for the calculation of average cost and had a cascading impact on a single invoice. The following edits to historical transactions will also change the average cost timeline, but may have a significant impact on historical reports – depending on the date of the original transaction and the nature of the change.
·         Changing the Inventory Item on a purchase transaction (e.g. Bill) or inventory adjustment. If you change the increase in inventory quantity/value from “Widget A” to “Widget B”, QuickBooks will recalculate average cost and quantities on hand for all transactions using “Widget A” and/or “Widget B” dated after the date of the transaction you edit. The change may also cause the Widget A item to have a negative quantity on hand at some point in the inventory timeline. See the section on the dangers of negative inventory below for more information.
·         Editing the quantities and/or value of inventory on a purchase transaction (e.g. Bill) or inventory adjustment. These changes impact the calculation of average cost and will affect all transactions in the inventory item’s timeline dated after the date of the transaction you edit.
·         Deleting or voiding a purchase transaction containing inventory parts and/or Assembly Items. Or, deleting an inventory adjustment.
·         Changing the date of a purchase transaction containing inventory parts and/or Assembly Items. Or, changing the date of an inventory adjustment.
·         Changing the date of a build transaction or deleting a build transaction. Build transactions affect Assembly Items in much the same way that Bills affect Inventory Part Items. The build increases the quantity on hand and usually increases the value of the Item. When you change the date of the build or delete the build you change the average cost timeline for the Inventory Assembly part – creating a cascading impact throughout the sales transactions that include the Inventory Assembly Item.
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):
Account
Debit
Credit
Accounts Receivable
150.00
 
Cost of Goods Sold
30.00
 
Inventory Asset
 
30.00
Income
 
150.00
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):
Account
Debit
Credit
Inventory Asset
60.00
 
Cost of Goods Sold
5.00
 
Inventory Asset
 
5.00
Accounts Payable
 
60.00
 
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.
Tip: You can use the Unclassified column on the Profit & Loss by Class report to quickly locate the transaction on which QuickBooks made corrective adjustments to Cost of Goods Sold for negative inventory. You can then run an Item detail report on the Inventory Item to determine when the item had a negative quantity on hand. If you enter an inventory adjustment to zero the quantity and you date the inventory adjustment at least one day prior to the date the inventory went negative, QuickBooks will remove the Cost of the Goods Sold adjustment from the bill and will make the adjustment on the sales transaction instead. This is the most surgical way to fix this problem with average cost and Cost of Goods Sold, but it is also the most time consuming.
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.
Simple Conclusion: Don’t merge Inventory Items under almost any circumstances, especially when the Inventory Part Items are in historical transactions dated 30 days or more in the past or if either of the items you are merging is included in the Bill of Materials for one or more Assembly Items.

 

About the author
Joe Woodard is an Advanced Certified QuickBooks ProAdvisor and Intuit Solution Provider who has taught over 20,000 QuickBooks consultants across the country. Joe works with Intuit, state CPA societies, and Atlanta-area CPA firms to present advanced QuickBooks instruction to accounting professionals and software consultants. Joe has earned a unique relationship with Intuit as a trainer, consultant and author. Joe has built two successful accounting software consulting practices: the first in New Orleans, Louisiana and the second in Atlanta, Georgia - Creative Financial Software (CFS). In addition to consulting with small businesses, CFS provides advisory services to CPA firms and other QuickBooks ProAdvisors/Intuit Solution Providers across the country -- helping them to better service their clients who use Intuit products. Joe recently hosted the first annual "Scaling New Heights" QuickBooks conference in Atlanta.

See all articles by Joe Woodard.

You may like these other stories...

The following list highlights 10 apps that that may be of interest to you, your clients, or your clients' clients. They were featured during a session of AWEBLive!, the 12-hour CPE marathon, and presented by Gregory L....
I am a recent MS Accounting degree graduate and I am looking into a programming/IT related career. Anyone here have experience or know any accountants that diverted their careers into IT/Programming/System design, etc?...
Event Date: May 29, 2014 In this presentation Excel expert David Ringstrom, CPA brings you up to speed on the Excel feature you should be using, but probably aren't. The Table feature offers the ability to both...

Upcoming CPE Webinars

Apr 24
In this session Excel expert David Ringstrom, CPA introduces you to a powerful but underutilized macro feature in Excel.
Apr 25
This material focuses on the principles of accounting for non-profit organizations' revenues. It will include discussions of revenue recognition for cash and non-cash contributions as well as other revenues commonly received by non-profit organizations.
Apr 30
During the second session of a four-part series on Individual Leadership, the focus will be on time management- a critical success factor for effective leadership. Each person has 24 hours of time to spend each day; the key is making wise investments and knowing what investments yield the greatest return.
May 1
This material focuses on the principles of accounting for non-profit organizations’ expenses. It will include discussions of functional expense categories, accounting for functional expenses and allocations of joint costs.