How can I easily calculate average inventory value?

I am still having no luck with this. Could anyone give me more detailed information or where to look for it?

couberpu wrote:

In Nav 3.6, or 3.7b, report ID 10146 Item Turnover. You can change TimeBetweenDataPoints to 1D, 1W, 1M, 1Y..

We are using Nav 5.0 so didn't have the report. I was able to find the code for it though right here at Mibuso after searching with the report ID. I had two problems with it:

1. That report calculates inventory amounts when I need inventory values. How can I accomplish getting inventory values?

2. "Quantity on Hand" is an unknown variable in Nav 5.0 so the line CALCFIELDS("Quantity on Hand","Sales (Qty.)","Negative Adjmt. (Qty.)"); gives an error message when trying to save the report.

There's another topic discussing a similar situation.

Just to clarify, I am aiming at having a report that calculates inventory turnover (definition). Originally I just modified report 1001 to calculate it:
(CostOfInvDecreases / ((ValueOfInvoicedQty + "Value Entry"."Cost Amount (Actual)") /2 )). But calculating an average with only using beginning and ending inventory values can be very misleading.

EDIT: I guess the quickest solution for me would be if someone was kind enough to take a look at the code of Report 1001 - Inventory Valuation and help me out on what code I need to add and where to in order to calculate average inventory value (from daily values, not just beginning and ending) between the defined dates. Anyone out there who could help me with this? [-o<

Thank you very much for you help so far Eugene, I already got quite close to having it work... I'll try to explain what I did and the results I got:

You mentioned that "Value Entry"."Cost Amount (Actual)" is the change of value during the specified period, but instead it gives me the value at EndDate. So I thought I'd use (ValueOfRcdIncreases - CostOfShipDecreases) instead to calculate the change of value, making the formula ValueOfInvoicedQty + (ValueOfRcdIncreases - CostOfShipDecreases) / (EndDate-StartDate+1+1)

Then I started testing it.

EndDate = StartDate calculates the average correctly
EndDate = StartDate +1D also calculates the average correctly
EndDate = StartDate +2D does not calculate the average correctly anymore

I'm guessing that there is some fairly obvious mistake that I've made, but unfortunately I don't have time right now to figure it out or to test it further. I will give an update once I'm back to work on Monday...

Eugene wrote:

you replied:

"Value Entry"."Cost Amount (Actual)" gives me the value at EndDate

indeed i though it totals the values in the range startingdate..endingdate but upon closer inspection i see that it totals values in the interval 0D..endingdate

so i suggest you declaring a new decimal variable (let's say A) then add it to CREATETOTALS in OnPreDataItem and then in OnAfterGetRecord assign values as follows:

IF ("Posting Date" >= StartingDate) AND ("Posting Date" <= EndingDate) THEN A := "Cost Amount (Actual)"


Then in the footer you'd have accumulated change of value and the formula for average would be:

ValueOfInvoicedQty + A / (EndDate-StartDate+1+1)

Ok, I declared the new variable A and added

CurrReport.CREATETOTALS(A);

to the end of Value Entry - OnPreDataItem(). I also added

IF ("Posting Date" >= StartDate) AND ("Posting Date" <= EndDate) THEN A := "Cost Amount (Actual)";

to the end of Value Entry - OnAfterGetReport(). Then modified the formula at the footer to

ValueOfInvoicedQty + A / (EndDate-StartDate+1+1)

The result is the same though as before. The logic of the formula is only valid for EndDate = StartDate & EndDate = StartDate +1D.

And now that I think of it, the problem is indeed in the logic of the formula. You cannot calculate a multiple-period average by taking the initial value and then adding the sum of changes divided by the number of periods (average value of change). So the "incorrect" figures I get by using these formulas are exactly what Navision is told to calculate, I just need to tell it to calculate something else if I want to get the average inventory value...

1. example:

Day 1 - Inventory value 100
Day 2 - Inventory increase +50 -> Inventory value 150
Day 3 - Inventory decrease -100 -> Inventory value 50

The correct average should of course be (100+150+50) / 3 = 100. The formula described earlier would give 100 + (50 - 100) / (3-1+1+1) = 87,5.

2. example

Day 1 - Inventory value 100
Day 2 - Inventory increase +50 & Inventory decrease -100 -> Inventory value 50
Day 3 - Inventory value 50

The correct average this time would be (100+50+50) / 3 = 66,67. The earlier formula would again give 100 + (50 - 100) / (3-1+1+1) = 87,5.

Thank you again Eugene for your help, and any further thoughts would be very helpful!

Eugene wrote:

my mistake indeed, the formula should have been

[(N+1)*X+N*A[1]+(N-1)*A[2]+...+2*A[N-1]+1*A[N]]/(N+1)

so in on Value Entry - OnPreDataItem():

IF ("Posting Date" >= StartDate) AND ("Posting Date" <= EndDate) THEN 
A := "Cost Amount (Actual)" * (EndingDate-"Posting Date"+1);

for overflow issues you may wish to transform it as follows:

[X+N/(N+1)*A[1]+(N-1)/(N+1)*A[2]+...+2/(N+1)*A[N-1]+1/(N+1)*A[N]]

Ok thank you, I will take a look at this right now

Eugene wrote:

should be right now

Yes, it appears to be working fine now! =D> Thank you so much for your help with all of this, Eugene! You've made my week even though it's still only Monday...

One more question though, what kind of overflow issues do you mean?