Total Item Cost Correct but Incorrect Item Cost by Location

headley27

We have a seemingly bad issue.

We are on version 5.0 using average costing and have just noticed that some items have the wrong cost (often a negative cost) at a specific location.
Quite often we can narrow it down to specific Item Ledger Entries and when drilling down further it seems like one or more Value Entries have been applied to the wrong location.

For example - Item XYZ:

Location A: 50 On Hand @ -$446.07 Cost
Location B: 20 On Hand @ -$339.91 Cost
Location C: 96 On Hand @ +$1290.35 Cost

Total Cost should be $504.37 which is correct but should obviously be distributed differently across locations.

Cost (each) should be approx. $3.00 +/- a couple of cents at each location:
Location A: 50 On Hand @~ +$152 Cost
Location B: 20 On Hand @~ +$61 Cost
Location C: 96 On Hand @~ +$292 Cost

It looks like the Adjust Cost - Item Entries Report (795) has caused this.

Things were OK with Version 3.7. It looks as though the problems started shortly after the upgrade to 5.0.

Has anyone seen anything like this before?

Any suggestions on how to fix this?

Thank you very much.

Scott_Frappier

headley27:

I'm sorry to hear about your issue.

I have never heard of value entries being created for an incorrect location in the average cost system. Usually, the problem stems from core code in the Inventory Adjustment codeunit that caused incorrect averages to be shown.

We've seen zero inventory quantities with value, and incorrect values on items on versions from 2.6 all the way to 4.0 SP3. I'd be very surprised if the problem didn't occur in 3.7 as there were many issues with the average cost calculation system at that version.

For our clients that run Average Cost, we have upgraded them to 5.0+ (or recommend to upgrade) as the application now has now created an "auditing and traceability" feature that allows you to drill down, by date, to see the averages of an item. We have used this at previous sites that had incorrect averages, did some changes so that the calculation would re-occur, and 5.0+ fixed every scenario that we had encountered. At the moment, the only problem that we saw with average cost calculations at 5.0+ involved Item Charges, which was an easy fix implemented in one of our sites.

One thing that you can try to see if it corrects the values is as follows:
NOTE - TRY THIS IN A TESTING DATABASE AND NOT IN THE PRODUCTION DATABASE. YMMV.

1.) Go to table 5804 - Avg. Cost Adjmt. Entry Point and filter down to the 'XYZ' number.
2.) Uncheck the "Cost is Adjusted" for every combination of Item/Variant/Location for that item from the beginning of time.
3.) Check the Item Card - Cost is Adjusted on the Invoicing tab should be set to FALSE.
4.) Run Adjust-Cost Item Entries and examine the results.

During the upgrade process, this should have occurred for each item automatically as its part of the U.S. upgrade toolkit process. I'm not sure if it's different for Canada (which is where I'm assuming you're from), but we had to disable it as part of the upgrade process as we did not want large swings for asset values on the balance sheet. Instead, any items that did have incorrect costs, we used the aforementioned solution and it would correct them without incident.

Hope this helps...

- Scott

headley27

Thank you Scott, I appreciate the advice but no luck.

I do recall that during the upgrade, the process you describe did happen for each item.
Running the process now does not make any change to the value entries.

The problem we are having definately started after the upgrade to 5.0.
I have made database comparisions to confirm this.

What I have noticed as well is that there are Item Ledger Entries that were closed in 3.7 (back as far as 2004) that have new value entries attached since the upgrade. :shock:

All of the incorrect value entries are adjustments and were created by the Adjust Cost - Item Entries Report (795).

Thanks again for your help.

If anyone else can shed some light I would greatly appreciate the input. ](*,)

Scott_Frappier

headley27:

Yep - you'll have a bunch of new value entries that should post to the current period if the average is incorrect. To reverse all of these, you need a developer to create a call to post value entries and then set the Cost is Adjusted on all the entries to FALSE for each respective period...not a fun nor simple task.

I sent you a PM asking for more detail - if anyone else has any further input, I'd love to know as well. I'm just trying to understand what happened that caused this issue as we've never had any major average costing issues in our client base after 5.0.

Thanks!

- Scott