Inventory Value Positive/Negative - Inventory Quantity Zero

rico1931

Hi Everyone,

We have two items that are sitting in our Inventory Valuation with both a Positive Inventory Value and one with a Negative Inventory Value but both items are NOT in stock (Qty of Zero)

Wonder how is this possible? I thought that once Inventory is Zero, Inventory Value should be zero as well since we have nothing in stock...

Also wondering if there is a way to fix this issue? We have tried the Revaluation Journal but nothing, since there is zero stock. And we have tried the Item Journal to positive increase the item to 1 and then negatively adjust it out with the increase + inventory value, but that didn't work.

Anyone have any ideas? We are using FIFO costing method. This one has me stumped -_- ](*,) ](*,) ](*,)

bbrown

Has everything been completely invoiced? Have you run Adjust Cost?

rico1931

Hi bbrown!

Thanks for the reply.

Yup, everything has been invoiced. There are no Item Ledger Entries open. Ran Adjust cost for this part like 100 times and still sitting there in the inventory valuation...

golfer

Check the Cost is Adjusted on the item card. If yes, then it´s adjusted, and the difference is probably because you run the valuation report in a date range outside the adjusted entry. Look at the associated value entries to see the dates. If Cost is Adjusted=No, then the ACIE has not adjusted the entry. Probably because the allowed posting range was outside the transaction date. Look at the value entries and set allowed posting range in General Ledger Setup including the date and run the ACIE again.

If needed, you can revaulate any positive transactions, even if the remaining qty is zero. Simply enter the item no. manually and look up/enter the Entry No. in Applies-to entry.

Kowa

rico1931 wrote:

I thought that once Inventory is Zero, Inventory Value should be zero as well since we have nothing in stock...

Well, it is supposed to be, but there are numerous error sources which can prevent this.
There is a white paper available on that subject, as well as an analysis report which can help to identify the causes.
http://blogs.msdn.com/b/nav/archive/2012/01/12/costing-error-detection-and-data-correction-white-paper-for-microsoft-dynamics-nav.aspx

Miklos_Hollender

I hope you aren't using Average costing, that has a long history of issues. If you use FIFO that _should_ be working. Here is my own

General Methodology For Investingaging Inventory Value Issues

Copy all Item Ledger Entries of the item to Excel. Make a column that calculates the unit cost of each entry by dividing cost amount actual + expected with quantity. Make another column that makes a running total of quantity i.e. inventory at that point in time, so if the quantity column is B and this one is C then the first row is B1, the second is B2 + C1, the third one is B3 + C2 and so on. Do the same for cost amount actual. Do you spot anything weird in any row? If not repeat the same exercise but put different locations (filter on it) on different Excel sheets because FIFO works on a per location level. Anything weird?

Does your weird row have a remaining quantity, and does another row have a negative remaining quantity? (Or the other way around?) If yes your problem is the lack of application - you must apply them. This happens when you allow your inventory to go negative. I.e. if you have 0 inventory, post a sales order, go negative, then receive the purchase order, this happens. Or you have 0 inventory, you have an aftersales case, you ship out the replacement item to the customer and only then receive back the faulty item. Then this happens. It is good to make a customization that never allows stock to go negative.

I make it 50% likely your running total shows a negative inventory at some point and with that some negative remaining quantity. You apply that run adjust costs and it is OK.

If it is not the case, but you spot a wrong row, see where it is applied from. If you cannot see it on the user interface, take a look at the Item Application Entry table. That one is not very user friendly. You can do this. Add Lookup Flowfields to it to make it more readable and filterable (but filtering makes it slow, whatever). Inbound Entry Posting Date,Outbound Entry Posting Date,Outbound Item No.,Inbound Document No.,Outbound Document No.

Kowa

Miklos Hollender wrote:

If yes your problem is the lack of application - you must apply them. This happens when you allow your inventory to go negative. I.e. if you have 0 inventory, post a sales order, go negative, then receive the purchase order, this happens.

The automatic item application works under those conditions too. If it doesn't, there is something else amiss (different variants, locations, lot or serial nos., job or job task nos.).

rico1931

Wow.. Thanks everyone for the wonderful feed back!

We tried it all and we found out where our problem is, something to do with a entry type called rounding. For some reason it has two entries that make up the difference between 4/29/13 and 5/1/13 ... Yes, 4/29 is around the time we close our posting date so we went back in the setup and allowed the posting range to before 4/29/13 and tried running ACIE for our one part and nothing :-( The Inventory periods are still open for that time since we are trying to reconcile 2013 at this point.

I've attached a picture of the Value Entries and we see the difference that is sitting in the Inventory Valuation (which we always run as of "today") in the amount of $141.68. We've tried the revaluation Journal by running the "Calculate Inventory Value" Calculate Per: both Item and Item Ledger Entry for this one part and nothing shows up on the line so we can't revalue the item :-(

Now if I do it the manual way (tried this in our test system) and revalued them by using the applies-to Entry and zeroed out everything it gives me a negative inventory value lol.. guess I'm going to have reload our test system with current data and play with it enough to get the inventory value at zero!

Any suggestions or ideas would be greatly appreciated!

Thanks!

poppins

Miklos Hollender wrote:

I make it 50% likely your running total shows a negative inventory at some point and with that some negative remaining quantity. You apply that run adjust costs and it is OK.

How to do that? I mean, how to apply ?

jubeljoy

As per standard format manual Cost Adjustment will not work for Zero Inventory Items. So you have to do some changes on the code and then execute the report to pass the adjustment entries.

Kowa

For NAV 2013/NAV 2013 R2 there is a hotfix (KB 354142) in Cumulative Update 17/Cumulative Update 10 if an amount of ± 0.01 remains after the item cost adjustment.

tinoruijs

Kowa wrote:

For NAV 2013/NAV 2013 R2 there is a hotfix (KB 354142) in Cumulative Update 17/Cumulative Update 10 if an amount of ± 0.01 remains after the item cost adjustment.

Oh great. Another update to do. But good there's a solution.

jglathe

Hi there,

small correction: this KB is part of RU10 (Build 36005) in NAV2013. Has anybody thought of a backport to NAV2009?

with best regards

Jens

Kowa

jglathe wrote:

small correction: this KB is part of RU10 (Build 36005) in NAV2013.

Sorry, the KB ID I meant is 359755, that is included in Update 17
https://mbs2.microsoft.com/Knowledgebas ... US;2984829

Kowa

Doubled post

absolutelyfreeweb

the first time I heard adjust cost routine to be called ACIE... (Adjust cost - Item entries)

dn

Good tips