Sage 100

Looking for a substitute for the Inventory Trial Balance Report - I preach to my clients that the TB Report is the ONLY report that reliably ties out to the month-end Inventory number in the General Ledger. However, there are several problems with the Inventory Trial Balance Report. 1) It includes all Inventory Items that have a zero balance on hand at month end. This can print page after page of useless information if a client assigns new Items Numbers due to annual style changes or models (i.e. 2012 model) each year. 2) The report does not show the unit cost - for a banker or an auditor looking to confirm the value of inventory on hand at a given month end they would rather see Quantity (on hand) x Unit Cost = VALUE at month end. Has anyone tackled this issue before?

Suppressing the 0 lines should be easy enough in Crystal, but I can see why it doesn't show unit cost - what about FIFO/LIFO/LOT/Serial costing? Then you've turned it into a valuation report with all of the lines of detail instead of a compact (once you've suppressed those zeros) report. If all you want is an average unit cost, you could modify it just be taking the value and dividing by the Qty - again, not really accurate for the afore-mentioned costing methods, but if it makes the banker/auditor happy, go for it. :-)

Personally, the less you give the banker or auditor, the fewer questions they will ask. Having said that, The best report this type of reporting purposes (IMHO) is the valuation report. Properly done, it should tie to the GL at a particular point in time. But it is sensitive for that exact moment in time.Generate a copy in paperless office for archiving. Suppressing the zero as Beth suggests is a good step in that direction. In v4.5, you have the discontinued and inactive flags that should suppress a lot of those lines (even if they have value). I guess we should be thankful that they aren't asking for an accurate purchases clearing report.........

An accurate what?! What's an accurate purchases clearing report @JeffSchwenk?

@BethBowers and @JeffSchwenk - Suppressing the 0 lines will help with reducing a 860+ page report. And I understand the issue of LIFO/FIFO etc. If I need to come up with a unit cost I will export the zero suppressed report to Excel and calculate my ""average unit cost"" in Excel. Thanks for your advice.

@MichaelMcDonald I would like to know as well. Even though I have preached to customers in the past as well about using the Inventory Trial Balance to tie to the G/L, ever since moving to 4.4 I have three customers who can no longer tie to the Inventory Trial Balance report with G/L. I have a hard time trusting that report on customers who have converted from a prior version to 4.4+. I really think it has to do with how it calculates the inventory balance using Inventory Transaction Detail history. Plus with it not showing unit cost and not being able to suppress zero lines it is a PITA.

Brian - did you run the pre-migration reports on inventory before the upgrade? Were there any issues? It's not well documented but in the \mas90\utilily folder there are IM utilties that can be run for fixing some of the issues, like IM_BalanceInvQtyCost_Utl - you need to copy the ""utiltiy"" programs into the IM directory and then run them from File | Run.

Moria. Yes we did run those pre-migration utilities as part of the upgrade. The Inventory TB balanced to the GL right when we upgraded to 4.4 but over the last year and a half have gradually been getting out of balance. And I did the usual analysis, no GL posting directly to Inventory account, product lines setup correctly, etc. It just doesn't seem to stay in balance. I am going to try running those test utilities on a backup of their company to see if it does any good. Thanks.