web
You’re offline. This is a read only version of the page.
close
Skip to main content
Community site session details

Community site session details

Session Id :
Dynamics 365 Community / Forums / Microsoft Dynamics GP (Archived) / Average perpetual inve...
Microsoft Dynamics GP (Archived)

Average perpetual inventory cost is incorrect

(0) ShareShare
ReportReport
Posted on by TD2012 943

We have an inventory item using Average Perpetual valuation.  They sold all of it and the item balance was down to zero.  The recently purchased more with a much different cost, the current cost seems to be falling between the old cost and the new.  Shouldn't it be at the new cost without averaging in the old cost since the level was at zero before this purchase? 

Any insight in to what might be going on would be appreciated.

Thanks.

TraceyD

*This post is locked for comments

I have the same question (0)
  • Tracey Steere Profile Picture
    Tracey Steere on at
    Re: Average perpetual inventory cost is incorrect

    Hi Tracey,

    Average costing is calulated in the following way; (Previous On-Hand Quantity x Previous Adjusted Unit Cost) + [(Receipt Quantity - Quantity Returned) x Receipt Unit Cost]/Previous Quantity On-Hand + (Receipt's Quantity On-Hand - Quantity Returned).

    As you can see it will use the cost of previous quatities even though your value is zero. See below a caption from the GP manual.

    Average cost updates in Inventory Control

    When you create a record for an item that uses the average perpetual valuation method, you can enter

    an average cost for the item. The average cost is called the current cost in the Item Maintenance window. After you save the item record, the current cost will be updated when you post transactions for the item.

    Microsoft Dynamics GP uses the moving average cost method. This means that when you post a transaction that increases the inventory quantity, the average cost will be calculated using the following formula.

    (Previous On-Hand Quantity x Previous Adjusted Unit Cost) + [(Receipt Quantity - Quantity Returned) x Receipt Unit Cost]/Previous Quantity On-Hand + (Receipt's Quantity On-Hand - Quantity Returned)

    All calculations are based on quantities and costs that are first converted to the base unit of measure.

    The average cost of an item will be recalculated under the following situations.

    • Posting an invoice receipt in Purchase Order Processing that has an item marked to be revalued in inventory and has a unit cost that is different from the unit cost on the shipment

    • Using the Inventory Adjust Costs window to change the cost of a purchase receipt

    • Closing a purchase order line item when you haven’t invoiced the entire quantity of the item

    • Posting a purchase order return

    • Posting an increase or decrease transaction with a document date that causes the purchase receipt to appear before another purchase receipt in the Purchase Receipts Inquiry window

    • Changing the quantity type of an item to On Hand when the unit cost for the transaction is different from the current cost of the item.

    Hope this helps.

    Regards

    Tracy

  • TD2012 Profile Picture
    TD2012 943 on at
    Re: Average perpetual inventory cost is incorrect

    Thank You!  I was confused about the "previous on-hand quantity", I thought it meant only if there was a balance at the time of the new purchase.  This is very helpful and explains a lot.

  • Community Member Profile Picture
    Community Member on at
    Re: Average perpetual inventory cost is incorrect

    Hello Tracey and Tracy

    I have been following this link with interest as we use Average Perpetual valuation and it has caused headaches over the time as I am sure that you appreciate.

    If you follow the GP calculation through you will see that when the quantity is zero prior to the receipt of a new shipment then any previous value does not have any affect on the new calculation.  This can be demonstrated by the following:

    (0 x $75) + [(10 - 0) x $85]/0 + (10 - 0) = $85  (the unit cost of the new receipt).

    It is far more likely that one of the situations that force a recalculation has happened.  I will point you to a White Paper on GP Inventory at the following link.  A warning! It is fairly heavy wading through it but it will explain the possible affects that other transactions can have on the average (current) cost.  

    www.loganconsulting.com/LinkClick.aspx

    For your information we use Dynamics GP 10.

    I hope this is of some assistance to you as I have spent some months trying to reconcile issues with Average Perpetual within GP.

    Regards

    Judi Carson

  • TD2012 Profile Picture
    TD2012 943 on at
    Re: Average perpetual inventory cost is incorrect

    I will look at this.  Thank you.  I'm not sure why they are even using this as most of their inventory is FIFO.

  • TD2012 Profile Picture
    TD2012 943 on at
    Re: Average perpetual inventory cost is incorrect

    This customer does not use POP, they enter all inventory through adjustment transactions.  The inventory items with issues had a variance entry last year, since then the average cost has been off, even though the inventory was brought down to zero before the latest entries.  I can't find anything to tell me that the variance entry affects cost any differently than an adjustment entry.  Also, there were several months between the entries bringing the inventory to zero and the new inventory.

    Any insight?

    Thank you.

  • Rita Landry Profile Picture
    Rita Landry 5 on at
    Re: Average perpetual inventory cost is incorrect

    We have the same problem with evaluating average cost.  Last year we were using GP8 and the inventory adjustments to change average cost worked perfectly, according to the formula.  This year, we are using GP10, and the system is changing average cost incorrectly and inconsistently.  When we zero out the inventory, sometimes it is using the balance we had before it was zeroed out, which is incorrect.

    For proof that it is incorrect the net of the transactions entered are not the same as the difference between the qty times the old average cost and the qty times the new average cost.

    We just entered a large inventory cost adjustment, and as far as I can tell there is now $7,000 hanging out there, not posted to anything.  Meaning that my qty times average cost will not match the inventory transactions that we just posted.

Under review

Thank you for your reply! To ensure a great experience for everyone, your content is awaiting approval by our Community Managers. Please check back later.

Helpful resources

News and Announcements

Join experts in person to explore low code and AI agents at the Power Platform Community Conference sponsored by Microsoft

Quick Links

Responsible AI policies

As AI tools become more common, we’re introducing a Responsible AI Use…

Abhilash Warrier – Community Spotlight

We are honored to recognize Abhilash Warrier as our Community Spotlight honoree for…

Congratulations to the September Top 10 Community Leaders!

These are the community rock stars!

Leaderboard > 🔒一 Microsoft Dynamics GP (Archived)

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans