Sage 100

 View Only

  • 1.  Inventory Balancing Challenge

    Posted 03-09-2022 19:00

    Client is on v2019 SQL and is unable to balance the Inventory Valuation Report to the Inventory Trial Balance Report.  I have run all the utilities, IM_CommitCheckClear_utl, IM_ClearCommitDataEntry_utl, Balance Inventory Quantity and Cost, Recalculated Item History, etc.  I knew most of those utilities would not make a change, since they are all STANDARD Valuation.  Now I am left with a discrepancy of $ 72K on a $2.2 Mil Inventory.  In reviewing the Inventory Trial Balance, I have found 250 items with Zero Quantity on Hand, but with an Extend Dollar Amount for the item.  For Standard Cost Items, how can that be possible. 

    Looking for a method to fix these 250 items.  What Table do I need to check, correct, fix?  Can I add a record to resolve this? 

    They bought a second company last year, and we have no idea how they got their inventory counts into the existing Sage 100 Company, and now it comes to light that they have not balanced Inventory since March 2021... ​​Management has changed a bit there, and prior management would just do journal entries to balance, and never thought about the "why" or the "impact".  Of course, that always comes back to bite someone.  



    ------------------------------
    Madeline Stefanou
    RKL eSolutions, LLC
    ------------------------------


  • 2.  RE: Inventory Balancing Challenge

    Posted 03-09-2022 19:16
    First thing I'd check is rogue entries in IM_ItemCost.  We've had users change item valuations without clearing up non-zero cost tiers, which can do weird things later.  If no bad data is there, and the IM_ItemWarehouse values are truly zero quantity with extended value for a Standard item... that's a first for me.  Perhaps change to Average, post +1, post -1, then change back to Standard.
    If they are really asking why, do a query of transaction history from the beginning of time (for the item / warehouse) to look for weirdness.
    When I can't truly answer this kind of "why" question, I usually just say there must have been a program crash during a journal posting (some time in the past), which corrupted the inventory data.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------



  • 3.  RE: Inventory Balancing Challenge

    Posted 03-10-2022 12:11

    thank you @Kevin Moyes  I have just one "rouge" record in IM_Cost and it was at zero cost.  I have 18 records in IM_ItemWarehouse with zero quantity and extended cost, so I can clean them up.  I still can't figure where the IM Trial Balance Report is getting the numbers.  I will thy the changing valuation, adding one and updating, then taking it out and updating, then changing back to Standard... 

    Additionally, and not impacting my Valuation Report nor IM Trial Balance Report, I see lots of items where the CI_Item.TotalInventoryValue divided by quantity on hand does not match Standard Unit Cost.   Is there a method to recalculate the item master Total Inventory Value Number?  ​​Some of these items are wrong by $10's of thousands or more.  



    ------------------------------
    Madeline Stefanou
    RKL eSolutions, LLC
    ------------------------------

    Original Message


  • 4.  RE: Inventory Balancing Challenge

    Posted 03-10-2022 12:27
    *Utl rebalancing should fix that kind of thing, but I've never had this issue for a Standard valuation item.
    Years ago we have a nagging issue with 2-digit warehouse codes really messing up the numbers after an IM rebuild.
    Another thing to try is purging IM_PeriodPostingHistory before the IM rebuild.  Valuation prints based on IM_ItemWarehouse and IM_ItemCost, but TrialBalance uses the ByPeriod summary table (repopulated with the IM Rebuild)... but the table is not reset during the rebuild, so there may be extra bad / old data in it.
    Transaction history without Fiscal Year/Period values could also do weird things.

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 5.  RE: Inventory Balancing Challenge

    Posted 03-10-2022 13:07
    I have run the balancing utilities, and that did not resolve.  I also tested the change from Standard to Average, +1 update.  -1 Update.  At each point, after +1 and after -1, my IM Trial Balance Report is accurate.  But dang...  If I now change that item back to Standard Cost, the IM Trial Balance Report shows zero quantity and $$$$ extended costs.  Makes no sense.   I think you are right and the IM Period Posting History is my problem.  I see periods with beginning balance dollars at 0, period change Qty at 0, but then period change dollars is an amount, and it should not be.  I have to step away and think about this for a bit.  Thank you both.

    ------------------------------
    Madeline Stefanou
    RKL eSolutions, LLC
    ------------------------------

    Original Message


  • 6.  RE: Inventory Balancing Challenge

    Posted 03-10-2022 13:13
    Edited by Kevin Moyes 03-10-2022 13:13
    Overridden costs on a + / - pair of transactions could have change quantity total 0, with $, if the unit costs are different, but that should not be possible for a Standard item.  Check the line data in IM_ItemTransactionHistory (and IM_LotSerialTransactionHistory if the item ever had either valuation method in the past).

    ------------------------------
    Kevin Moyes
    Technical Systems Analyst
    Munjal White Consulting Co.
    ------------------------------

    Original Message


  • 7.  RE: Inventory Balancing Challenge

    Posted 03-10-2022 12:55
    CI_Item total quantity field is often wrong, so I see no reason to assume that the total inventory value field would be any more reliable.  The existence of either field at all violates the "one version of the truth" principle anyway.

    ------------------------------
    Phil McIntosh
    President
    Friendly Systems, Inc.
    ------------------------------

    Original Message


Related Content