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

Notifications

Announcements

No record found.

Community site session details

Community site session details

Session Id :
Microsoft Dynamics AX (Archived)

Inventory valuation report and cost price not producing expected result

(0) ShareShare
ReportReport
Posted on by 290

Apologies in advance if this has been asked before, but I'm a novice user and the frustration level I've reached with AX 2012 R3 is unbelievable. Can anyone point me to a good guide on how the inventory valuation report, calculated cost price and item transactions work together or offer an explanation?

I have an item I buy for $2.00 per pound. It's always purchased for $2.00 per pound. When I ran the inventory valuation report at the end of February, it showed an average cost of $2.82. When I went into on-hand inventory for the item and looked at the on-hand tab, it showed a (weighted average) cost price of $2.82. When I looked at the transactions for the item, I saw a number of production line SOLD transactions for negative quantity with no associated cost which occurred in the final few days of the month. If I add the total quantity of those transactions to the on hand quantity showing on the report and the on-hand tab, and divide the inventory value by those total pounds, I get pretty close to $2.00 per pound. Clearly something is physically reducing my inventory without reducing the associated cost and it's throwing my average cost calculations askew.

Can anyone explain what AX is doing and how I can keep my average cost from being incorrect?

Again, apologies, but I think like an accountant. I am having great difficulty understanding how the system is intended function with physical transactions with no associated financial transaction, or perhaps we have simply missed something in the main account setups for the item groups and there should be financial transactions posting for all transactions?

*This post is locked for comments

I have the same question (0)
  • Suggested answer
    Ludwig Reinhard Profile Picture
    Microsoft Employee on at

    Hi PaladinSteve,

    Inventory valuation and inventory reconciliation are often one of the more complex things in AX.

    I suggest that you have a look at the following site that explains things nicely what AX is actually doing fedotenko.info

    What might also help is the following Whitepaper www.microsoft.com/.../details.aspx (it is for AX2009 but still valid for AX2012).

    All the best,

    Ludwig

  • Brandon Wiese Profile Picture
    17,788 on at

    Did you have any receipts to stock from sources other than purchase orders?  For example, positive counting adjustments, or perhaps sales order returns?

  • PaladinSteve Profile Picture
    290 on at

    Thanks for the articles, Ludwig. I will try to read through them this evening. The glance I gave them didn't seem to show any examples of transactions with quantity but no cost transactions, which are what I'm struggling with, but I will read them in detail tonight.

  • PaladinSteve Profile Picture
    290 on at

    Brandon, no. This is a relatively new system (<6 months) and this particular product has only been purchased and consumed in production thus far. With the consistent purchase price and so little variation in transaction activity, it seemed like it should have been the most predictable to cost, which is was so obvious when the inventory value report showed something different than expected.

  • Brandon Wiese Profile Picture
    17,788 on at

    I can imagine a few scenarios that might explain it.

    Are you including physical cost value, per the model group?

  • Brandon Wiese Profile Picture
    17,788 on at

    Also, what is the price on the Manage costs tab on the Released product?

  • PaladinSteve Profile Picture
    290 on at

    Brandon. Yes, include physical cost value is checked on the item model group. It appears that the cost price on the manage costs tab was $0.00 until 29 February, when a new cost of $2.00 was activated.

  • Verified answer
    Brandon Wiese Profile Picture
    17,788 on at

    Since we don't have access to the actual raw data, all I can do here is make a best guess from experience.  But first let me suggests that an inventory recalculation will probably adjust the cost value of the Sold transactions and you will end up with on-hand at $2.00 as expected afterward.

    The way AX assigns cost value to issues (negative quantities) from stock works like this.  If there's a positive on-hand quantity and on-hand value, then the value is divided by the quantity to arrive at what is commonly called the "running" average cost.  It will do this even when you think you are assigning it a cost value, i.e. using an inventory movement journal to write-off stock and specifying the unit cost in that journal which will be ignored.

    If the on-hand quantity is zero or negative, or the on-hand value is negative (or maybe zero?), then AX uses the price on the Manage costs fast tab of the Released product to calculate the cost value.  But, a number of things impact how it determines quantity and value.  The most obvious is your inventory dimension setup or what constitutes a financial cost boundary.  Somewhat less obvious are settings on the model group, such as whether to include physical cost, which is AX-speak for whether receipts that are not yet financially settled (a.k.a. invoiced) should be included in quantity and value calculations.

    So my best guess given what you've provided is that those Sold issues at $0 drew from the price on the Manage costs on the Released product, which means the on-hand quantity or on-hand value calculated by the system according to all of the setup and configuration available to it must have been zero or negative at the time.  Obviously with backdating and out of sequence transactions it can be somewhat difficult after the fact to reconstruct this fact, but I think it's almost certainly what happened in your case.  And the most confusing case of this is when inventory is financially negative but physically positive, because, well, that just confuses people who are new to AX.

    Then a subsequent receipt brought the on-hand quantity back above zero, and contributed its full value to the on-hand value, but with some issues at $0 the average of the now excessive value over the correct quantity leaves the "running" average cost, which is visible to you on the inventory on-hand forms, too high.  This is perfectly fine and by design.  In fact anyone who deals with AX long enough will eventually see a positive on-hand value while at 0 on-hand quantity, unless standard cost is in place.  It's easy to construct simple examples to illustrate this.

    However, the inventory recalculation process is designed to smooth all of this out, by assigning issues to receipts discreetly, and adjusting the cost value of issues in proportion to the settled receipts.  If you ran that today, I'm certain that the value of the $0 issues would be increased to $2.00 unit cost, and the value of the remaining on-hand inventory would then be reduced to $2.00 unit cost, as you have already calculated it would be had those $0 issues been at $2.00.  And, the adjustment of the cost value of those issues to a production order will automatically increase the value of the production receipt (finished good), which will automatically adjust the cost value of the issue to the sales order that sold it (cost of goods).  And so on.

  • Brandon Wiese Profile Picture
    17,788 on at

    The fact that the activated cost was set at $2.00 is helpful to you, only in that it reduces the swing that occurs while financially negative, forcing the system to fall back on that unit cost.  If your actual cost varies at all, then the inventory recalculation is the only way to ultimately smooth out the issues and receipts over time.

    Note that this is the AX solution to an age old problem, for which (in my opinion) very few other systems have ever mustered even close to a real solution, the problem of vendor invoicing.  You receive a product, and then consume and/or sell is immediately, realizing a cost of goods BEFORE you've paid for the product which should cement your cost for that product.  

    Fundamentally the problem is that you cannot know your cost of goods while the product is not yet invoiced.  Most systems deal with this in odd ways.  For example, if the vendor invoice arrives at a different cost than the receipt, then you may be forced to reverse the receipt, and re-post the receipt at the correct cost, and then invoice the receipt which now matches.  But that process invariably leaves you with orphaned value, as you could actually be at 0 on-hand at the time the purchase invoice arrives, and so the reversal and re-post creates value without having any inventory on-hand against which to associate it.  You are then forced at month end to write-off this value, typically to cost of goods loosely instead of to cost of goods against the original sales order specifically, because it just looks dumb to have non-0 cost value at 0 on-hand and you have to fix it.  AX is one of the few systems I've seen that acknowledges up front that your cost of goods is not firm and that it will be adjusted later, and the entire inventory model is built around this fact and lends itself to this process.

  • PaladinSteve Profile Picture
    290 on at

    Thanks Brandon, I think your last 2 posts finally help me to understand what is going on "under the hood". It sounds like we should be keeping the activated cost set to the last purchase cost in order to minimize the swings when the inventory is going to be consumed prior to the invoice receipt, which it frequently does.

    Coming from other ERP systems, the way AX handles this seems counterintuitive, as most others seem to set inventory value based on the cost when material is received, allowing it to be consumed (either partially or in full). Then, when the invoice is finally received, they send any difference between the cost at receipt and the invoiced cost on material which has already been consumed to a variance account. From my perspective, as an accountant, that is a cleaner way to do it than what AX is doing which seems like it is essentially revaluing posted transactions. As you say, this is AX's way of acknowledging that the true cost often isn't known prior to consumption, but it clearly it makes understanding costs more difficult.

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

Quick Links

Responsible AI policies

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

Neeraj Kumar – Community Spotlight

We are honored to recognize Neeraj Kumar as our Community Spotlight honoree for…

Leaderboard > 🔒一 Microsoft Dynamics AX (Archived)

#1
Martin Dráb Profile Picture

Martin Dráb 4 Most Valuable Professional

#1
Priya_K Profile Picture

Priya_K 4

#3
MyDynamicsNAV Profile Picture

MyDynamicsNAV 2

Last 30 days Overall leaderboard

Featured topics

Product updates

Dynamics 365 release plans