Identifying Quantity Discrepancies with Cycle Count Analysis
Start by running the Inventory Variance Report comparing on-hand quantities against the last cycle count or physical inventory. item)) > 0. This identifies items where the item master quantity does not match the sum of location quantities, a common discrepancy caused by incomplete location transfers.
Set up ABC cycle counting in the Cycle Count Setup form with A items counted monthly, B items quarterly, and C items annually to catch discrepancies early.
- Run query comparing item.qty_on_hand against SUM(itemloc.qty_on_hand) to find item-location quantity mismatches
- Configure ABC cycle counting: A items monthly (top 20% by value), B items quarterly (next 30%), C items annually
- Review negative on-hand report: SELECT item, qty_on_hand FROM dbo.item WHERE qty_on_hand < 0 for immediate investigation
- Check allocated quantities against open orders: allocated qty without matching open CO or job indicates stale allocations
Auditing Material Transactions for Root Cause Analysis
Trace discrepancies to their source by auditing the material transaction history. trans_date DESC. Look for suspicious patterns: large adjustments without reference numbers, backflush transactions with quantities that do not match job completion quantities, and transfer transactions where the from-location deduction does not have a matching to-location receipt.
job = 'JOB_NUMBER'.
- Query matltran table for the discrepant item over 3 months to identify suspicious adjustment and transfer patterns
- Flag adjustments without reference numbers: SELECT * FROM matltran WHERE trans_type = 'A' AND ref_num IS NULL
- Compare backflush quantities against job completion: mismatches indicate BOM quantity errors or scrap underreporting
- Verify transfer transactions have matching from/to entries, orphaned transfers indicate interrupted location moves
Reconciling Inventory Sub-Ledger with General Ledger
Financial inventory discrepancies require reconciliation between the inventory sub-ledger value and the GL inventory account balance. Run the Inventory Valuation Report as of the period end date and compare against the GL trial balance for the inventory control account. product_code WHERE stocked = 1).
gltrans WHERE acct = 'INVENTORY_CONTROL_ACCT' AND period <= 'CURRENT_PERIOD'. Investigate the difference by checking for unposted journal entries, timing differences between transaction dates and posting dates, and standard cost changes that were applied to the sub-ledger but not posted to GL.
- Compare Inventory Valuation Report total against GL trial balance for the inventory control account as of period end
- Check for unposted inventory journal entries: SELECT SUM(amount) FROM journal_entry WHERE source = 'INV' AND posted = 0
- Investigate standard cost update variances that may have posted to the sub-ledger but not yet flowed to GL
- Reconcile monthly and investigate variances exceeding $500 or 1% of total inventory value immediately
Try it here, free
SyteLine Cycle Count Variance Worksheet
An editable cycle count sheet that computes variance quantity, variance value and record accuracy per line and overall.
Frequently asked questions
What inventory accuracy rate should I target in SyteLine?
Target 95% accuracy for B and C items and 99% accuracy for A items measured by cycle count variance rate. Track accuracy as: (Total counts - Counts with variance) / Total counts. Most well-managed SyteLine implementations achieve 97-98% overall accuracy. If your accuracy is below 90%, prioritize fixing systemic issues like backflush errors and unauthorized adjustments before expanding cycle count frequency.
Should I allow negative inventory in SyteLine?
No. Disable negative inventory in the Inventory Parameters form by unchecking Allow Negative Quantities. Negative inventory creates compounding errors in cost calculations, MRP planning, and financial reporting. If transactions require negative inventory, the root cause is a process failure, typically a receipt not entered before an issue. Enforce receiving discipline and use SyteLine alerts to notify when pending issues exist without matching receipts.
How do I handle inventory discrepancies found during physical inventory?
Enter physical count results through the Physical Inventory Count form and generate the Variance Report before posting adjustments. Require supervisor approval for adjustments exceeding $250 or 10% of item value. Post adjustments to a dedicated Inventory Adjustment variance account, not directly to COGS. Investigate the top 20 variances by dollar value for root cause and document findings. Complete the process within 5 business days of the count to minimize additional transaction complexity.
Need help resolving inventory accuracy issues in SyteLine? Netray's inventory management consultants can implement cycle counting programs and reconciliation procedures. Contact us for an inventory accuracy assessment.