Sage 100

I have three clients on MAS90 4.3 & 4.4 with current SPs, that routinely have problems with the average cost values in CI_Item and IM_ItemWarehouse going berzerk. All their items are standard cost but they are noticing the Average Cost values are huge positive or negative numbers, which calculates even ""huger"" values for TotalInventoryValue and TotalWarehouseValue in each file respectively. Has anyone else run into this and even more importantly how to correct without editing each record in DFDM and maybe most importantly how to prevent it?

We have a client who has a similar problem. They use standard cost on all manufactured items. However, they occasionally experience problems with Average cost getting way out of whack. This is particularly a problem on manufactured goods that use a subcomponent that has a whacked out average cost and they do a partial completion. Sage's pat answer was to do an inventory adjustment and take the item to zero quantity and post it. Then process another entry to put the inventory quantity back on-hand with a second inventory adjustment transactions. Unfortunately, we use lot tracking and this doesn't always work well since the lot layers remain ""confused"". There are only two ways around this. 1) add the items back in with new lot numbers or use DFDM to correct the files.

if users let items go into negative on hand quantities it can cause the average cost to go crazy - I've seen Huge values as you describe and even negative average costs. I would check on that and not allow negative on hand as a way to prevent it.

I concur with @DavidOverholt. Every instance of a whacked out average cost that we researched could be traced back to an oversold situation. However, in this instance, the client required us to allow oversold processing because items were checked into production before receiving and QC could catch up on their paperwork.

In thinking about that I am sure that is the reason. Am I correct in that it cannot be done with VI (the fields are not available to import to as far as I can see) Does anyone have a script to correct the whacked out values?

Similar to this post. https://90minds-com.socialcast.com/messages/17270552

Or this one specific to average cost - see @AlnoorCassim comment: https://90minds-com.socialcast.com/messages/5403815

I think it might be easiest to just use the Average Cost Utility from Sage - must be logged into Atlas: (applies to version 4.40.9 and up). Don't know if the PRA is still required. https://partners.sagenorthamerica.com/irj/go/km/docs/sageKM/Sage%20MAS%2090%20and%20200/Gated%20Customers/Warr_Svc_Support%20Plan/541147.html

@ThereseLogeais & @PhilMcIntosh - thanks for the links to @AlnoorCassim instructions - that will be invaluable. @MoiraGoggin - that utility scares me. I did try that on one of the three clients I am referring to and it made bizarre nonsense changes to their inventory values. Since all items were standard cost the valuation should not have changed, but it made a TON of ?Z transactions and the valuation report was incorrect. I will edit the dictionary and import the corrections - much safer. Thanks everyone.

I had similar experience with the average cost utility - I'd call it the random cost utility - it made things worse, not better.

Scary Cost Randomizing Utility (SCRU). LOL

Another thing I have found a few times over the years in average cost is a zero quantity on hand but a warehouse value other than zero. I have never found a way to correct those other than via DFDM.

Looking for update to this issue Have client with approx. 1800 items with on hand qty of zero but with a value in the CI_item.TotalInventoryValue. The warehouse value is zero. Cannot update the field with an import and the balance utilities do not fix the value in the field. any suggestions other than editing with DFDM?

I've never had much faith in the total quantity on hand and total warehouse value in CI_Item file, and I'm not sure what it's purpose is to be honest. I don't know if this would work but try manually entering a receipt (in inventory module) to bring a quantity of one into the item using last cost - update that and then issue it back out. I don't know if that would adjust the on hand cost in CI_Item properly or not. if it does, you could write an export/import routines to do a receipt and issue transaction. You would need to make a GL entry or manipulate the posting accounts to GL.

@alanniergarth - A phone call to @RandyMarion ???

The 'fix"" for items with qty of zero but with a value <>0 in the CI_item (but with a zero value in IM_ItemWarehouse) was to import a qty of one and then adjust out the qty of one using the same cost to bring it back to zero. This had the desired effect for the quantity and value fields in CI_item. It did not fix the whacked out average cost value in CI_item, unfortunately. Apparently, the balance utility coupled with import and export (again) needs to be used to fix the average qty value. Still working on that. The good news is when they run valuation they now have about 2K fewer items reported.

I wish there was a utility one could run that would reset the distributed value of inventory and set the average cost of an item to either $0 or the last cost for that warehouse when the quantity on hand is 0. As Average cost is used when negative inventory tiers are created under the FIFO, LIFO, and Average costing methodology, it would be nice to get these anomalies out of the system.

Talk to @LeeGraham , I purchased a utility from him that runs as part of the rebuild sort files routine. It worked for my clients, but maybe only because they were standard cost and for all intents and purposes ignored average cost. FWIW