Operations & Logistics
Multi-Sheet Inventory Reconciliation: ERP Book Stock, In-Transit Transfers & Shrinkage Audit
Reconcile ERP ledger balances against physical warehouse cycle counts by netting outbound in-transit stock, computing unit and dollar shrinkage variances, and flagging material recount exceptions.
Separating timing differences (in-transit stock) from true inventory shrinkage
Comparing raw ERP book balances directly against warehouse bin counts produces false shrinkage alarms whenever inter-warehouse transfers have been deducted from origin or posted to ERP before physical dock receipt. Subtracting verified inbound in-transit units from ERP book stock isolates Expected Floor Stock before measuring physical variance.
Flagging any SKU whose absolute dollar variance ABS((Physical - Expected_Floor) * Unit_Cost) exceeds your materiality threshold (for example, $200) focuses cycle-count teams on true discrepancies rather than rounding noise.
| SKU | ERP Book | Inbound In-Transit | Expected Floor | Physical Count | Variance ($) |
|---|---|---|---|---|---|
| SKU-10 | 500 | 40 | 460 | 460 | $0 (Timing only) |
| SKU-20 | 300 | 0 | 300 | 288 | -$300 (Recount!) |
| SKU-30 | 180 | 20 | 160 | 159 | -$15 (Within tol.) |
| SKU-40 | 220 | 0 | 220 | 226 | +$240 (Overage!) |
Expected Floor Stock & Dollar Shrinkage Variance
Deduct unreceived inbound in-transit units from ERP book stock, then multiply Physical - Expected_Floor by standard unit cost.
=(D2 - (B2 - C2)) * E2Real-Time Collaborative Cycle-Count Review in Arte Sheets
During month-end inventory closes, Arte Sheets supports real-time multi-user editing with live cursors and avatars, role-based invitations, Suggest-only permissions for floor counters proposing bin adjustments, and threaded cell comments with @mentions and notifications so finance controllers can approve material write-offs in place.
=IF(ABS(F2) >= $I$2, "Recount_Required", "Matched_OK")Try the example
Use the original values or change them to test your understanding.