STOCK STATUS NOT BALANCE W GL 2

[leishad]

MSSQL 7.6.2 PROGRESSION; PKGS GL,OE, IM,AR, AP, PO, SM

I am trying to determine what accounts for Stock Status Report never balancing with the General Ledger. I suspect there is something we do not understand regarding the info it presents or the way specific data is handled. We have been making adjusting entries but bookkeeping does not understand the source of the difference. With my limited knowledge of bookkeeping the only additional info that I can offer that may come into play (after reading thread639-800050) is that we use average costs.

I have tried to find more data on the Macola site but have not found any description of adequate detail regarding the stock status report.

 

[kscheeler]

Your issue could be related to items with a negative quantity on hand. Since you are using average cost, it is a weighted cost being revaluated after each receipt. If the quantity on hand is negative and your next receipt doesn't bring it positive, you will have an issue with the new valuation because of the calculations that it's trying to do.


Kevin Scheeler

 

[leishad]

We were aware of negatives being a problem but just in terms of seeing it at the time of reporting, not whether it had remained negative during a receipt during the period. I will have to look at this more closely.

Thank-you for your input

 

[maccrystal]

In addition to Kevins suggestion, here are a couple of additional things to investigate when the Stock status and GL are out of balance:

1) Lock-Protected fields is checked off (IM Setup). This allows the user to directly maintain the on-hand balance without a transaction. If the user changes the balance using Inventory file maintanence the quantity will be reduced without making a corresponding JE. If you have the audit trail enabled, this may help you determine the user that requires counseling. Make sure you turn this option "On". This is typically the key cause of GL and stock status discrepancys.

2) Kits. The kit parent and the kit components must all use a material type / location code that relieves the same inventory asset account. If one of the kit components material cost type relieves a different asset account it can cause the GL and stock status to be out of balance due to the way the posting process relieves the general ledger accounts. The perpetual balance is correct, however the componet item, with different asset account will not be properly credited. This results in the stock status and GL being out of balance.

3) New items without cost and "Cost to use" (OE setup) set to Line item. (This means that OE will use the Line item cost to relieve inventory) If the new item is added to invetory without a cost and the item is ordered (OE), the line item cost on the order will be zero. The item is received (PO) it will increase the Qty-onhand and credit the inventory account for the new cost. Now the item has a new average cost. Since the OE system is using the cost (zero) from the line item (based on the setup), it debits zero dollars against the inventory account and releives the correct quanity. This leaves the stock status and GL out of balance. Consider changing the "Cost to Use" to actual. This will cause the system to pull the actual cost from inventory. The only way to get in trouble is if you invoice post before the actual receipt transaction is completed.

http://www.appstratinc.com

 

[MacolaHelp]

To expand upon Kevin's & maccrystal's previous comments: If you have screen designer, consider disabling the avg cost field in iminvloc, as no GL entry is created if someone changes in the file (also monitor these changes with the audit trail). Force them to use cost adjustements in IM\trx instead, which does create GL. Use qty adjs or issues/receipts to increase/decrease QOH. Haven't checked 7.6.200, but previous versions had problems on transfer transactions: transfer from/to kept their respective costs before & after the transaction instead of dynamically updating the recipient location. Check your suspense accts and ship confirm, ship pick accounts if in use to find out if some transactions are falling through the cracks of going to unexpected accounts. To get a real handle on this, post your inventory GL distributions daily from IM to GL. This can be done as a deferred process if needed. Print a stock status and GL trial balance report (also deferred if desired) after each post. Then you are dealing with a much smaller piece of the pie to analyze. You may quickly find what is causing the problem if you force the balance once, lock down the access to transactions that force a GL entry, then review daily and educate your users on proper procedures.

 

[crystalreporting]

GL transactions are posted based on Material Cost Type and Location whereas the stock status report cannot be run by Material Cost Type (only product category - why is this Macola?). You may want to consider creating a Crystal report to show stock status by Material Cost Type (simple report). At least you may be able to narrow down which Cost Types are giving you the problems. Another thing I highly recommend is using a test company to ensure IM transactions are hitting the right accounts. This would allow you to initialize the IMDISFIL, do a normal IM transaction, and see the resulting GL distributions easily. Do this with each possible IM transaction (receipts, adjustments etc.) as well as normal daily Macola functions that affect IM quantities to make sure the resulting distributions hit the right accounts, with the right amounts. Remember too, that if you have the accounts setup incorrectly in the first place, no amount of proper procedures will make the problem go away. Make sure for example that the quantity adjusment account is NOT the same as the IM asset account - in this scenario, the GL would wash, but IM would be affected.

Peter Shirley

http://www.linkcorp.com

 

[MacolaHelp]

Selling products also create IM distributions based on product category for the relief of finished goods or distribution items. Material cost type transactions create distributions on receipts & movement within your organization, including issues done through IM trx processing. OE issues look at PC/location accts. It is definitely easier to diagnose an oob condition by having unique accts for all buckets in these 2 files, as Peter has suggested. A stock status by mat cost type would also be helpful as a "canned" report. Since we can modify ICRs & most people buy crystal, macola seems to have gone away from adding any more canned reports.