Sage 100

What am I missing? Inventory year-end question. Client on SAGE 100 Std, V5.10 PU#4. Fiscal YE 9/30/15 and all inventory items using FIFO valuation (costing). At the end of September ran Negative Tier Report and posted Negative Tier Adjustment. Froze all inventory items and created a Physical Count Worksheet. Then performed 100% Physical Count using Excel Spreadsheets for each location. Imported counts into the IM_Physical.M4t table. Printed Physical Count Variance Register and matched up Quantity Counted column to actual counts - no differences. Updated Physical Count Variance Register as of September 30. Now printed Inventory Trial Balance Report as of September 30 expecting the Quantity on Hand - Ending Balance column to show Physical Counts. They are NOT - quantities shown are all over the place. Many showing a negative Ending Balance where positive quantities were counted. With Negative Ending Quantities the extended $$ amounts are negative. Therefore the total Inventory Trial Balance Report is not accurate. What happened??

Perhaps they physically moved (shipped) the inventory before they counted it. Once they freeze, they need to count inventory before they start shipping or receiving (that is before they start moving the inventory). Shipping prior to counting could create negative QOH now (they count less on the floor and then the system also reduces QOH by the shipping). Or, perhaps they posted something to September after the freeze and the system is doing what it's supposed to do, just that you/management didn't know about that posting.

Pick a couple of items where the counts that show on the trial balance are off and look at the transaction history. You should see any activity that happened right before or right after the physical count (IP transaction) that are causing the discrepancy. If that looks good perhaps the item history needs to be re-calculated, the trial balance uses the summary history file to calculate the balance.

Did the client post the transaction with the correct accounting date? I had a client update the inventory count to the wrong period and it raised havoc with the balances. As @RobertaChase mentioned above, this generates a lot of questions from clients. It is not necessary to halt all shipping while inventory has been frozen. But they do need to count the items in shipment as being on hand for the physical count. Maybe the did not count the items being shipped as actually on hand at the time of the freeze. Once counted they can update the invoice. Sage's instruction are as clear as mud... ?Quantities entered in Physical Count Entry must be entered as of the time of freeze. So if an invoice ships a quantity of an item out after the freeze, but before count has been entered, the count must be as if that quantity had not been shipped.

Thank you for your feedback. I followed @BernieLehman suggestion and looked at Transaction History Report for a select few items. I can see all the activity in the transaction history and it shows transactions in September up to and including the last transaction the IP (PHY Count Adjustment) on 9/30. It also shows subsequent transactions in October. The net transactions (+/-) match September Activity (QTY) amount in the Trial Balance Report. Therefore, the September QTY Beginning Balance is way off from the On Hand QTY shown on the Physical Count Variance Report. So my conclusion is the Trial Balance Report has been carrying incorrect QTY balances forward from who knows how far back. How do I begin to fix this mess? @BernieLehman Will re-calculating Item History help? Is there a way to recalculate the Summary History to fix the September QTY Beginning Balance?

I would make a test company and give it a try. If that doesn't fix it you may have to run the utility that compares the transaction history to the current balances, update it and then run the recalc again.