Adjusting Inventory Val.-Closed item ledger entry

On our Inventory Valuation report we have multiple lines for various items showing quantity and dollar value. These lines are positve as well as negative. I would like to adjust some of them out. However, on the Item Ledger Entries for some of these they are not showing open. I cannot adjust them out using the Items Journal and applying the Entry No. because they are not open. Any suggestions on how I can correct this? Thanks Dan

In general, the Inventory Valuation Report doesn’t show closed Entries unless you are back-dating the Report. You don’t say what version of Navision you are running, but in the base 3.70 report, you will only get a total line by item unless you customize the Report. I guess the bottom line is you shouldn’t have to adjust those entries out if they are already closed. I must be missing something here.

Hi What do you mean by:

quote:

I would like to adjust some of them out

If the entry is closed it means the item has been used, the positive entry in would be open and the negative entry out would close it - assuming you are referring to the item ledger entry. If you are looking to alter teh costs of these transactions have you looked at the revaluation journal?

Thanks for your responses. We are running on Navision 3.10 B. The Inventrory Valuation report is not being backdated. When we check the Print Details box on the options tab it gives us the multiple lines that make up the values. Here is an example of what we are getting. Entrty Type| Post Date| Remaining Qty| Unit Cost| Inventory Value Positive| 12/29/03| 2 | 20.19| 40.38 Negative| 1/9/04| -2| -312.84| 625.68 Item total | 0| 666.06 It shows a remaining quantity of 0 but a value of 666.06. The negative entry of negative 2 with a unit cost of negative 312.84 results in a positive value of 625.68. How can I get rid of both of these entries that are making up the 666.06 value? The item ledger entries for both of these entries are closed. Thanks Dan

Hi Dan Don’t think you like this but I believe the only standard way to resolve this is to book 2 in at zero cost through an item journal and then remove them at the cost difference. This difference I believe is caused by the manual alteration of the cost figure on journals (but I could be wrong with your version being 3.10B). Make sure you test this first (I have not) and be aware of what will happen to each of the cost fields on the relevant item card when you process this through and run the adjust cost entries batch job, although I am not sure anything will happen. Good luck [:D].

This might be a stupid question, but have you run the Inventory Periodics?

Hi Chris Never a stupid question [:D] - it will affect the amount remaining if movement has happened.

Thanks. You all are great to be helping us out. Yes, Chris, I am running Inventory Periodics. I am running the Adjust Cost - Item Entries and the Post Inventory Cost to G/L. If there is something else I should be running, please let me know. I do these just about every evening. Steve, I tried putting in 2 at 0 cost through the Items Journal and then deducting them out at the difference in cost but it did not change anything. I’m still showing 0 items at 666.06. This is not an isolated incident. We have several items similiar to this. Thanks again for what you’ve done and for any other suggestions. Dan

Hi Dan After processing the journal you wil lhave run the adjust cost item entries and posted it to a set date. Is this date within the period you are running the inventory valuaiton report for?

Hi Try this on a test system. Create a new item. Process a positive adjustment for 1 at $100 - post it. Process a negative adjustment for 1 at $200 - post it. Inventory valuatiuon report will read quantity of zero and value of -$100 To correct this. Process a positive adjustment for 1 at $100 - post it. Process a negative adjustment for 1 at $0 - post it. Inventory valuatiuon report will read quantity of zero and value of $0 [:D]

Good morning Steve, The problem continues. I did as you suggested. After creating a new item on the training system and doing a positive adjustment of 1 for $100 and then a negative adjustment for 1 at $200, the Inventory Valuation report showed a value of $0.00 and not -$100. I did not go any further with the rest of your response because there did not appear to be anything to correct. The training system would not let me run the periodic activity for Adjust Cost Item Entries but it did let me do the Post Inventory Cost to G/L. I don’t know if that would have made a difference. Thanks Dan

Hi Well something is certainly wrong if you are forcing the cost differences and it is returning 0,and if you cannot run the adjust cost item entries routine! [:D] Not running this will ultimately make a differnece, although in my testing scenario it would not. My items have FIFO costing - what is yours? Which fields are you altering the cost of on the item journal?

Hi Steve, I not sure I totally understand your question but we are on a LIFO costing metod and I am entering the prices in the Unit Amount field in the Item Journal. Thanks Dan

Hi Dan I am not in front of Navision until Wednesday this week, but I will set up an item with your settings and process it using the field you are adjusting to see if this makes a difference here.

Hi Dan

quote:

I am entering the prices in the Unit Amount field in the Item Journal.

This is part of the problem - you need to be entering this in the Unit Cost field as well. Try the correction journal again, or the scenario again adjusting the unit cost field as it works with a LIFO item here.

I’m not sure we’re chasing down the wrong path here. While your looking at Steven’s stuff, can you try something else as well? After looking at the Inventory Valuation Report, it seems to be doing its own cost adjustment, so even running the Inventory Periodics shouldn’t matter here. Can you try the following for the specific Item: 1) Check the Open Flag on the ILEs. That should be off for all entries 2) Export all the Ledger Entries for this item to excel and sum the Quantity, Invoiced Quantity, and Adusted Cost fields. From what you say these should be 0 for all 3. 3) If all the totals in (2) are 0, then I would guess you have a problem with your Item Application Entry for the Lines that show up on the Valuation Report. The simplest issue would be an incorrect Date. To check this, run the Valuation Report for 12/31/9999 and see it that item disappears. That is a mouthful, but hopefully you can get through this.

Hi Steve, Again, no success. I tried your suggestion of creating a new item and doing positive and negative adjustments using the Unit Cost field as well as the Unit Amount field in the Items Journal. The Inventory Valuation report still showed 0 quantity at 0 value - not at -$100. You asked what costing method we use and I responded LIFO which is true. However it is my understanding that Navision posts entries using average cost until the Periodic Activity is run which then adjusts to LIFO. Would this immediate posting to average cost cause the Inventory Valuation report to show $0 value instead of -$100? Thanks, Dan

Hi Dan I asked your costing method just to ensure we were processing the same item, but for no other reason, you are correct, it will use the average cost, but eventually it will get updated by the cost posting batch job. At a loss at what to suggest. Have you had any modifications in this area? I mean booking one item in at one value and forcing a difference does create your scenario - but now you cannot create it and also cannot correct it. Do you have access to a virgin database? Can you run the same scenario through?

Hi Dan Chris Krantz has kindly run through the scenario for me (thanks Chris I owe you one [:D]) and said the following:

quote:

Never a problem, I checked on my 3.01B database (US with no improvements).I got the same results as Dan. More than likely, this is because we are running a different report than you. My Inventory Valuation Report is 10139 (NAVUS3.01).

I am naturally running the GB version and use report 1001 Inventory Valuation (NAVW13.60) Chris went on to say:

quote:

As per your example, I posted a Purchase (Cost 100) and a Sale (Cost 200) for a new item. When I ran the valuation report, nothing showed up (Even though the adjusted costs were 100 and -200 on the Item Ledgers). This would be because the report seems to do its own adjustments internally. To further test, I manually changed the date on the Item Application to a future date and ran the report again (up to the current date). I saw the -100 with 0 quantity.

Okay in the scenario run the entries with different dates - I believe this is what Chris is saying, or he maybe saying the differences you are seeing are due to timings and how you run the report [:D]. Chris did believe that the entries would disappear when the update cost batch job is run, but I am of the opinion they will not be if you force them different, as you have done by over-writing the cost, as it is a situation I encountered in the UK. Over to you again, and thanks again Chris.

Dear Gurus, I wish to have replies on how to replicate the scenario. What are the potential reasons because of which inventory is zero and value is still some positive value? I also faced this kind of problem where the reason was that after commenting the code the able consultant changed the costing method(from standard to FIFO) of the item when there still were open entries in item ledger for that item. To correct that what I did was just do one positive adjustment with any value and then do the negative adjustment on say 1st jan’04 and run the periodic activities. So whenever i take an inventory valuation on or after 1st jan’ 04 it would show the right value i.e. zero quantity and zero value. So I would suggest that just do a positive adjustment with whatever value and then -ve adjustment. However I am afraid if it will really work for LIFO. Regards,