How can I easily calculate average inventory value?

jiipm2

Greetings!

I am working on creating a report listing inventory turnover ratios by item.

I started by taking the Inventory Valuation report (ID 1001) as it provides me with Cost of Goods Sold (by using Decreases (LCY) ) and I could also easily add a calculation for Average Inventory Value by using the beginning inventory value and the ending inventory value.

The thing is that I then realized that naturally the Average Inventory Value calculated in this way is very misleading as it only uses two points of reference, as opposed to let's say 365 points of reference if I want to go through a 1-year period.

Is there any relatively easy way to calculate Average Inventory Value so that Navision automatically uses each day as a reference point?

I am still quite a novice when it comes to Navision so I would naturally prefer to take some working code from another report and just add it to my modified 1001 report. Any help would be greatly appreciated...

nunomaia

Look at table 5804 - Avg. Cost Adjmt. Entry Point.

In NAV 5.0 you can choose average day type.

jiipm2

nunomaia wrote:

Look at table 5804 - Avg. Cost Adjmt. Entry Point.

In NAV 5.0 you can choose average day type.

Hey, and thanks for the reply!

I'm looking at table 5804 right now, but I don't really know what to do with it in terms of the issue I am trying to solve? :-k

jiipm2

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

couberpu

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

jiipm2

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<

Eugene

in your formula

CostOfInvDecreases / ((ValueOfInvoicedQty + "Value Entry"."Cost Amount (Actual)") /2

the ValueOfInvoicedQty is the value at StartingDate ( more precisely before the start of StartingDate)
the "Value Entry"."Cost Amount (Actual)" (read in the footer) is the change of value during period StartingDate..EndingDate - this is because of
CurrReport.CREATETOTALS(..., ValueOfInvoicedQty, "Value Entry"."Cost Amount (Actual)",...)
defined in Value Entry - OnPreDataItem()

so your average inventory value during period from StartingDate to EndingDate should be calculated as:
ValueOfInvoicedQty + ["Value Entry"."Cost Amount (Actual)"/(EndingDate-StartingDate+1+1)]


for example:
when you enter EndingDate = StartingDate you will get

ValueOfInvoicedQty + "Value Entry"."Cost Amount (Actual)"/2 = ([ValueOfInvoicedQty] + [ValueOfInvoicedQty + "Value Entry"."Cost Amount (Actual)"])/2

when you enter EndingDate = StartingDate +1D you will get

ValueOfInvoicedQty + ("Value Entry"."Cost Amount (Actual)"[StartingDate] +"Value Entry"."Cost Amount (Actual)"[EndingDate])/3

which is an average of 3 days, namely:

(  (ValueOfInvoicedQty) + (ValueOfInvoicedQty+"Value Entry"."Cost Amount (Actual)"[StartingDate]) +(ValueOfInvoicedQty+"Value Entry"."Cost Amount (Actual)"[EndingDate])  )/3

jiipm2

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

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)

jiipm2

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

the logic behind the formula was as follows:
at start you have X
at day I you have a change A
so the average for N days is

[X+sum(X+A[I])]/(N+1)= [X+NX+sum(A[I])]/(N+1)=X+sum(A[I])/(N+1)

Eugene

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]]

jiipm2

Eugene wrote:

the logic behind the formula was as follows:
at start you have X
at day I you have a change A
so the average for N days is

[X+sum(X+A[I])]/(N+1)= [X+NX+sum(A[I])]/(N+1)=X+sum(A[I])/(N+1)

So I guess the problem with it is that the change A in that formula is change compared to the original X[0], as opposed to how Navision defines the change by comparing to X[I-1]...

The question then becomes either:

(a) how can I edit the report to calculate the sums of all the changes compared to the original value X[0] instead of the previous value X[I-1]?

or the original

(b) how can I edit the report to just sum the daily inventory values and then divide it by the number of days?

jiipm2

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

should be right now

jiipm2

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?

Eugene

Accumulated value of A is going to be very big so it is better to devide values on the fly in OnAfterGetRecord

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

and then in the footer you dont need to devide by (EndingDate-StartingDate+1+1)