CORRECTION OF ERRORS IN INVENTORY VALUATION

I am using MS Dynamics NAV 2009. We have over 10,000 inventory items in our system, managed in 8 locations. We regularly transfer inventory from the central warehouse to these locations and make the appropriate transfer entries in the system. Issues (i.e. consumption to Cost Of Sales) happens from the locations other than central stores.
The system is configured as follows:
Automatic Posting = “Yes”
Expected Cost Posting = “No”
Automatic Cost Adjustment = “Always”
Average Cost Calculation Type = “Item”
Average Cost Period = “Day”
I discovered that a colleague had posted the wrong opening inventory quantities as at 1 January 2013, when the system first came into use. I had already calculated what the correct positions were supposed to be as at 31 December 2012. I generated the appropriate journal and posted to 31 December 2012. No Problem. All inventory quantities and values are OK as at that date.
I then proceeded to post an equal and opposite Item Journal to the one that my colleague had posted on 1 January 2013, using that same posting date. Unfortunately, I forgot to enforce Item Costs (i.e. unit costs were left blank).
When I ran the Inventory Valuation report as at 1 January, I discovered that all inventory values had multiplied in value by a huge factor, in cases up to 30 times the expected value. The effect of all this is that my inventory is now in total chaos. I am testing various ways of rectifying this situation – to no avail!
I tried “reversing my reversal” i.e. I was trying to nullify my opposite entry, by putting the items back. This did not work. I then tried clearing out all the inventory as at 1 January 2013, so that I remain with zero quantity and zero value. I cannot even get the system to do this. Every time an inventory item goes to a zero quantity, it nonetheless remains with a positive value in most cases, or a negative value. Then I post a journal with quantity = 1 and no value. No problem, now I have a single quantity against the value. I then try to remove this by posting a negative adjustment against it with the total value so as to clear it, but lo and behold, the quantity goes, but some values remain.
So at this stage I am going round and round in circles and no solution in sight. The thing is, as I have in excess of 10,000 items, the change in value is colossal. I need a solution that will cut across all inventory items.
What is the best approach to solving a problem like this? Should I temporarily switch Automatic Cost Adjustment to “Never”? Will this solve the problem, or introduce a different type of error later on? This feature needs to be on, as we use Weighted Average Costing.
Thanks in advance for your help.

Welcome to forum!

Average costing method requires very accurate workflow - if you ever sell before purchase is posted it’s likely to get errors in costing. Then, even if Automatic Cost Posting = YES, you still need to run both ACIE & Post Inv. Cost to GL batchjobs at least once at the end of period (month?). This should correct inaccuracies caused by selling before purchase, and is the only way to reflect indirect costs (type=Charge (Item)).

First, try to run ACIE and check if that corrected the mess, however, it may not adjust everything nicely. If you search the forum, you’ll find here many threads about cases when Qty=0 but some + or - value is still hanging and vice versa, Qty # 0, but value = 0. This happens with Average costing, as the algorithms are very complicated, therefore using Average is not a good choice until it’s absolutely necessary. Problem is, that costing method can not be changed if you have entries for the given Item already, even if current stock is zero.

The most important issue - NEVER EVER post anything against GL Inventory accounts directly !! These GL accounts must have Direct posting = NO, and is true for whatever costing method you choose.

Thank you, Modris, for your very considered response. I will try and run the ACIE and see whether or not that rectifies the issue. I will also report back if that is indeed the case.

However, at the time of writing, and before I received your response, I had already done the following:

(1). Set Automatic Cost Adjustment to “Never.”

(2). Posted a journal to clear out all inventory items to zero quantity and value (I read elsewhere on this forum that that is one way of attempting to rectify erroneous inventory valuations).

Next, I was going to post a journal to re-instate the inventory quantities at zero value, and then post a revaluation journal.

I am not at all sure that any of this will work. The only reassuring thing is that I have created a “test” instance of our database with a mirror of the live system at a time as of two days ago. So my “experiments” will not make the live environment any worse. My approach is that if a solution is effective in the test system, I will then go live with that.

Fingers a-crossed!

I have managed to resolve my issues regarding the errors that were introduced in the inventory values. The answer lay in what might appear to be a surprising area, and not intuitively one that you would associate with a correction of inventory values in the system.

Mathematics. To be more precise, simple arithmetic.

Basically, by discovering how MS Dynamics NAV 2009 calculates the Weighted Average Unit Cost of inventory items, I was able to exploit that knowledge mathematically in order to force the system to recalculate the inventory unit cost and consequentially the valuation. The same method also changed the Unit Cost on the inventory cards to the values that I was enforcing. Moreover, the degree of accuracy of this method was greater than 99% with an error swing of +(-)0.01 to +(-)0.02%.

In total, about 6,000 inventory items were affected, and have now been corrected. I was able to do this via a mass posting through a batch upload (in fact I used the Data Migration feature on the Rapid Implementation Module).

In a later posting, when I find time, I will explain exactly what I did.

Thanks for all your help. I wouldn’t have discovered what I dd about the way Inventory works without your valued input.

Dear Clarence Chongo

I would love to hear from you about what you exactly did to solve your issue. Please share us some of your mathematic tricks and how did you enforce the system to calculate the unit cost, you wanted to?

I also have some serious issues of stock values to be corrected.

Thank you!

Can share you trick with us

I have serious issue of stock value to be corrected.

Will love to hear what you did pls on this issue . Please share some of your mathematic tricks and how you enforced the system to calculate the unit cost you wanted to?