Recently, a customer asked me to present a detailed explanation of how Average Cost is calculated for an item. This may seem like a no-brainer, but there are certainly a few quirky details you should know about.
Every time an item’s quantity is incremented (so through Item Receipts, positive Inventory Adjustments, Inventory Transfer, etc), there is necessarily a cost associated to that. This cost is used in a weighted-average calculation to determine the Average Cost. Example:
I buy 10 Widgets A at 10$ a piece Monday
I buy 20 Widgets A at 15$ a piece Tuesday.
My average cost is (10*10$ + 20*15$) / (10+20) = 13.33$
Decrementing transactions (Item Fulfillments, consumption through Builds, decrementing Adjustments and Transfers) have no effect on Average Cost.
HOWEVER, once an item reaches 0 on hand or lower, Average Cost ceases to be calculated. So if an item is underwater (negative on hand quantity), Average Cost is NOT updated at all, until you resurface. You can buy as much as you like for whatever value you want, until you return to positive on hand quantities, this has absolutely no impact on Average Cost.
Also, as soon as an item reaches 0 on hand, Average Cost history is lost. That is to say, the next transaction that increments the item’s on hand value will be considered the sole transaction in the weighted average calculation. Example:
I have 10 widgets on hand with a current Average Cost of 15$ each.
I sell 15 widgets. I now have -5 on hand. If I look at my item, it still indicated 15$ Average Cost.
I buy 5 widgets for 100$ each. My on hand value is now 0. My Average Cost has not been impacted, and still indicates 15$ (note that it does NOT indicate 0$ - it remembers its last “valid” average cost).
I buy 1 widget for 30$. My on hand value is now 1. My Average Cost’s history is reset, and so my Avg Cost is now 30$
The rules are basically the same. The only nuance to note is that Assembly Items are usually only incremented by performing Builds. In that case, the unit cost of the build, for the purpose of the weighted-average calculation, is the sum of the Average Cost of the member components.
A Note on COGS
Note that while Average Cost is not updated when an item is underwater, Netsuite will correctly calculate the value of the item for the purpose of Cost of Goods Sold impact based on the value of incrementing transactions. Example:
I have -32 on hand of my Widget, with a stated Average Cost of 10$
However, I have been purchasing this item for much more recently (though not enough to make it above water), making its true worth at round 22$.
When I sell this item, Netsuite will record a COGS of 10$. However, when I increment my inventory through purchases, Netuiste will post a “Cost of Sales Adjustment” based on the value of the incrementing transaction. So in this case, I can expect to see an additional 12$ posted as a “Cost of Sales Adjustment”.
So even though the stated Average Cost of my item will be inaccurate, the ledgers should correctly reflect what is really going on.