Average Cost Math - The Little Details You Should Know

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.

Inventory Items
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$

Assembly Items
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.

Credit Memos and The Account Field

The Credit Memo record (and possibly other records) have an Account field, where you can choose which Accounts Receivable account will be affected by the Credit Memo. However, this field has somewhat unusual behaviors.

First of all, the field won't show up on Credit Memos if you only have one Accounts Receivable account in your Chart of Account. This is true even if you're using a custom Credit Memo form and the "Show" checkbox for the "Account" field is checked. As soon as you have two or more accounts of type "Accounts Receivable", the drop-down will show up. This can be quite confusing, especially since NetSuite usually shows drop-downs with only one result and allows you to create new records (such as an account in this case) on-the-fly with the little "+" button beside the drop-down.

Second, the default account used for Credit Memos is not a company setting, unlike most accounts for transactions. In the case of Credit Memos, the account is what we call a "sticky" default, which means the default account that is selected on the Credit Memo is the one used on the last created Credit Memo. This means you must always check the account on the Credit Memos you create to make sure it's the correct one.

NetSuite Credit Limit and Bypassing Credit Hold

From NetSuite's Help Center:

You can use credit limits and credit holds to manage the amount of credit you extend to customers.

A credit limit defines the maximum currency amount the customer is allowed to accrue in outstanding receivables. A credit hold blocks entry of a transaction if the customer's credit limit has been reached.

Credit holds are also applied when customers are delinquent in making payments. You can define a grace period after the due date of invoices. Then, once the grace period has passed, customers are unable to place new orders on terms until they have paid their overdue invoices.

When using the Credit Limit functionality in NetSuite, the system won't let you create or edit a customer's transaction if he is on Credit Hold. This is done to avoid bypassing the Credit Limit by adding new items to existing transactions. Since there are several cases where this restriction needs to be bypassed, NetSuite offers some options to work around the Credit Hold:

• There is a company-wide setting (found at Setup > Accounting > Accounting Preferences > General Tab) that allows you to enforce, warn of or ignore credit holds.
• The setting mentioned above can be overwritten by users by going to Home > Set Preferences > Transactions Tab. This must be allowed by administrators by checking Allow Override for Customer Credit Limit Handling at Setup > Company > General Preferences.
• Regardless of the aforementioned preferences, there is a hidden flag called "credholdoverride" on transactions that can be set through scripting to bypass the credit hold.

Tips on Average Cost Accounting in NetSuite

First and foremost, welcome to our blog! Here at IT-Ration Consulting, we've been in the NetSuite business for over 6 years now, and we thought we could share some of our extensive knowledge to the general NetSuite community.

As a first post, I'd like to talk a bit about the Average Cost costing method and how it's managed in NetSuite. The most important thing to keep in mind if you're planning to use Average Cost as your costing method in NetSuite is that the average price is calculated from the Item Receipt record. That means any discrepancies between your Purchase Order, Item Receipt and Vendor Bill may impact your Inventory Asset and Cost of Goods Sold accounts.

In fact, any difference in item rate between the Item Receipt and Bill is sent to the Inventory Received Not Billed account. This can cause many headaches when it comes time to complete your account reconciliation. It can also skew your company’s profitability quite a bit.

The simplest solution is to always make sure your Purchase Order, Bills and associated Item Receipt have the same item rates. This can be taken care of manually by diligent users, but some scripting and customization can automatically take care of this for you as well.

If you'd like more information on the Average Cost costing method in NetSuite as well as potential solutions, my co-worker has written a great whitepaper entitled "Item Average Cost Impacts in NetSuite".