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.
Average Cost Tip
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
Alternate time line
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 covers the most common changes users make to historical average cost and the impact of these changes on the General Ledger and on inventory valuation.
2nd Note
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.
In the third, and final, part of this mini-series, we will examine the impact of negative inventory on Average Cost, and also the affects of 'merging items'.