Sage 100

Age old issue... Average cost item goes negative screwing up the average cost. Currently 116 on hand at 3.39 and should be 2.99. I enter a transaction adjustment for -116 @ 3.39 and update. Then enter a transaction adjustment for 116 @ 2.99 and update. The average cost is still 3.39. I was expecting the cost to be 2.99. Any ideas?

I just had the same thing happen at a client - they are on version 2014. I used DFDM to look at the amounts in CI_Item table. there were odd amounts in the fields TotalInventoryValue and AverageUnitCost we corrected those manually to fix the problem. The TotalQuantityOnHand was correct. I'm not sure why that was not corrected with the adjustment though.

After adjusting out the 116 units, I always use DFDM to check the Average Cost and the Total Value. I also correct manually the IM_ItemWarehouse Record. Then when they all reflect 0.00; i adjust in the quantity with the proper dollar amount.

Thank you.

After all these years with this common issue I was hoping Sage would invent something a tad more elegant than having the user call me to DFDM.

Amen

Resolution If the Inventory Codes are a mix of Average Cost and Tiered (Lot/Serial, FIFO/LIFO) Valuation Methods, the Average Cost Fix Utility needs to be run first, followed by the IM Balance Quantity and Cost utility. 1.Make a copy of the live Company Code into a test company (i.e. Company Code TST) 2.In the TEST company, run the IM_CommitCheckClear_utl and IM_ClearCommitDataEntry_utl utilities to clear stranded values in the IM_ItemWarehouse, IM_ItemCost, and IM_DataEntryCostCalcCommit files. 3.Run the IM_AverageCost_utl.m4p utility in the test company . **This utility was combined with the utility in step 6 in recent versions so you may not see or need to run it. 4.Run the Inventory Maintenance, Period End, Inventory Negative Tier Adjustment. After updating, there may be items that don't have off-setting tiers. Adjustments may need to be made through Inventory Adjustments may need to be processed to create off-setting tiers. NOTE: Clear ALL negative tiers before running the utility. This should be standard practice for customers using tiered valuation methods. 5.Open Inventory Management, Utilities, Remove Zero Quantity Costing Tiers. 6.Go to File_Run. Type *utl then Run the IM Balance Quantity and Cost utility. 7.Open Inventory Management, Utilities, IM Recalculate Item History utility and Proceed. 8.Compare costs between the Inventory Valuation, Trial Balance and Stock Status reports and the total Quantity On Hand on the Stock Status report vs. the Trial Balance report. Verify and review specific items in Item Maintenance as well as the reports by grand total and by Item Code. 9.Once the results have been reviewed and verified, create a copy or backup the current live Company Code and repeat these steps in the live Company Code.

Are these utilities included now? The last KB posted indicated that you had to sign a prerelease agreement.

From release notes: The Following files are updated by Sage 100 ERP 2014 Product Update 4 (5.10.4.0) - IM_AverageCost_Utl .pvc So I think it is available depending on version.