Sage 100: How to Correct Out of Balance Inventory Items for mixed Valuation Methods (Average Cost, Lot/Serial, FIFO/LIFO)

Description

How to Correct Out-of Balance Inventory Items for mixed Valuation Methods (Average Cost, Lot/Serial, FIFO/LIFO)

Disclaimer

Use caution when working with the below product functionality. Always create a backup of your data before proceeding with advanced solutions.

Cause

The Average Cost is incorrect for mixed Valuation Methods (Average Cost, Lot/Serial, FIFO/LIFO) on versions 4.40 and higher or:

• There are differences between the Inventory Valuation, Trial Balance and Stock Status reports
• Item Code has a negative or incorrect Average Cost and is a tiered valued item
• Inventory Reports have an Item Code with zero QOH, but has an Extended Total Inventory Value
• Stranded values in the IM_DataEntryCostCalcCommit.M4T table or the CostCalcQtyCommitted or CostCalcCostCommitted fields in the IM_ItemCost.M4T or IM_ItemWarehouse.M4T tables

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

Support

If necessary, seek the assistance of Acute Data Systems to help provide assistance with this process.