Microsoft Dynamics AX (Archived)

What is the formula of "Average unit cost" in "inventory value" report ?

(0) Share

Report

Posted on by Community Member

We run the inventory value report

[inventory and warehouse management] > [reports] > [status] > [inventory value] > [inventory value]

What is the formular to calculate "Average unit cost" column in that report

(Average unit cost column) = (Inventory: Financial amount column)/(Inventory: Financial quantity column) ???

We found that some item

The (Average unit cost) column is not equal to (Inventory: Financial amount) column divided by (Inventory: Financial quantity) column ???

*This post is locked for comments

I have the same question (0)

All responses (8)

Answers (0)

Guy Terry 28,970 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi dsin,

The appearance of that report is quite configurable. Can you share a screenshot of the setup of the 'ID' you are using?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

Here is the capture of my settings.

Was this reply helpful? Yes No

Suggested answer

Guy Terry 28,970 Moderator on at

Like (0)

Report
Copy link

Link copied!

Since you are not summarising the physical and financial values, I would say you need to include the physical columns in your calculation

Average cost = (Financial Amount + Physical Amount) ÷ (Financial Quantity + Physical Quantity)

I believe this would be in line with Cost price on the 'On hand' tab of the 'On hand' inquiry.

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

I tried calculating using your formula.

However, the calculated unit price != unit price column in the report.

Was this reply helpful? Yes No
Guy Terry 28,970 Moderator on at

Like (0)

Report
Copy link

Link copied!

If you go to the On hand Inquiry and use Dimension display to enable only Site and Warehouse does the cost price on the 'On hand' tab match the report?

On the Item model group(s) of your items, is 'Include physical value' ticked?

Was this reply helpful? Yes No
Community Member on at

Like (0)

Report
Copy link

Link copied!

In item model group, the include physical value is not ticked.

and in on-hand page, with site and warehouse dimension display, some of the cost is matched with the report, but some cost is not matched.

Was this reply helpful? Yes No
Guy Terry 28,970 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi dsin,

I tried an example using an item where 'Include physical value' is not ticked.

First thing I note is that the value of the physically updated transactions is included in the 'Average unit cost' on the report. This is different from what AX does elsewhere when 'Include physical value' is not ticked, and explains why you get different values compared to the 'On hand' inquiry.

I was not able to create an example where the formula I described above (on the 18th Mar) did not match the 'Average unit cost' on the report. Sorry!

Was this reply helpful? Yes No
Guy Terry 28,970 Moderator on at

Like (0)

Report
Copy link

Link copied!

Hi dsin,

If you're using R3, this hotfix might be relevant:

KB3047008 - Inventory value report displays incorrect "Average unit cost"

Was this reply helpful? Yes No