Physical count vs ERP book stock — inventory stock audit reconciliation for every SKU in under 60 seconds.
| SKU Code | Item Description | UOM | Phys Qty | Rate (Rs) | Value (Rs) |
|---|---|---|---|---|---|
| RM-STL-001 | MS Steel Plate 10mm 8×4 ft | Nos | 240 | 4,250 | 10,20,000 |
| RM-CU-014"> | |||||
| RM-CU-014 | Copper Wire 2.5sqmm 90m Coil | Nos | 132 | 580 | 76,560 |
| RM-AL-022 | Aluminium Sheet 1.5mm 4×8 ft | Nos | 60 | 2,950 | 1,77,000 |
| FG-PMP-208 | Centrifugal Pump 3HP 2-inch | Nos | 45 | 12,800 | 5,76,000 |
Year-end physical stock verification is one of the most labor-intensive close activities for mid-market manufacturers and distributors running 50–500 active SKUs. Manual inventory stock audit processes break in four distinct ways that a spreadsheet VLOOKUP cannot catch reliably.
A warehouse team doing a year-end count at a 300-SKU factory produces a handwritten or keyed floor tally. Finance then manually VLOOKUPs the tally against the Tally or SAP book-stock extract — row by row, across bins — consuming 16–32 controller-hours before the reconciliation is reviewable by the statutory auditor.
A single off-by-one in a VLOOKUP range, a transposed SKU code, or a mismatch in unit-of-measure naming silently drops a matched row from the variance column. Controllers discover formula bugs only during the statutory auditor's walkthrough — at which point the working paper must be rebuilt under time pressure, delaying the sign-off on inventory balances.
A naive Excel reconciliation computes variances only for rows present in both sheets. An SKU in the book stock register that was never counted on the floor — common for obsolete stock or off-site pallets — produces no row in the delta column. Under Ind AS 2, paragraph 34, these items may require a write-down to net realisable value, but they are invisible in manual spreadsheet reconciliations.
When a moving average rate in Tally differs from the rate implied by the floor count — due to an unapplied price update or a mis-keyed GRN — the variance appears as a rupee delta in the value column alongside quantity shortages. Manual spreadsheets collapse all exceptions into one undifferentiated column; cost accounting never sees the rate issue because it is hidden inside a bigger shortage number.
The typical aggregate book value at risk in an unreviewed inventory reconciliation exception queue for a mid-market manufacturer with 300 active SKUs — carrying shortages, obsolete stock, and rate-costing errors that compound quarter over quarter. Under SA 501 (Audit Evidence — Specific Considerations for Inventories), the statutory auditor must obtain sufficient evidence on existence and condition; an uncleaned exception queue is a qualification risk.
Four distinct regulatory obligations converge on the year-end inventory count. Each one creates a specific evidence requirement the controller must satisfy before the statutory auditor can issue a clean opinion.
Inventories must be written down to NRV when they are damaged, wholly or partly obsolete, or when selling prices have declined. The physical count is the primary evidence that triggers this write-down — a shortage below tolerance or a book-only item that was never found must be actioned before the closing balance sheet is finalised.
ICAI SA 501 requires the external auditor to attend the physical count, observe counting procedures, and perform independent test counts — then cross-reference results against the entity's own book records. A reconciled working paper with a row-level trace from physical count to book stock to exception class is the minimum artifact that satisfies this requirement.
The 2018 revision of the ICAI Guidance Note on Audit of Inventories explicitly requires that the auditor verify count-date alignment between the physical count and the book stock extract. A GRN posted on 01-Mar against a 28-Feb cut-off inflates book stock and understates the variance — the count-date alignment check catches this before it reaches the auditor.
US mid-market entities following ASC 330 face the same lower-of-cost-or-NRV constraint as Ind AS 2 adopters. Internal audit teams operating under IIA Standard 2320 (Analysis and Evaluation) must document the counting methodology and variance resolution. The same reconciliation workflow serves both Indian and US contexts, with the exception classification adapted to the applicable standard.
Seven deterministic passes from two uploaded Excel sheets to a fully classified exception queue — in under 60 seconds regardless of file size within the 50–500 SKU mid-market range.
Accepts the Physical Count sheet and Book Stock extract uploaded together in one run. Cadel classifies each file by its column headers — Physical Qty + Count Date identifies the floor tally; Book Qty + Moving Avg Rate + As Of Date identifies the ERP extract — without requiring the user to label files manually.
Both files are parsed and their SKU Code columns are normalised: leading and trailing whitespace is stripped, case is standardised, and codes like RM-CU-014 and RM-CU-014 are treated as identical. This prevents false-mismatch exceptions caused by data-entry inconsistencies between the warehouse team and the ERP entry clerk.
Every SKU in the Physical Count is looked up in the Book Stock register, and every SKU in the Book Stock register is looked up in the Physical Count — producing three distinct populations: matched on both sides, physical-only (no book record), and book-only (no floor count). Both unmatched populations are surfaced independently so neither class is hidden.
For each matched pair, four deterministic rules run in sequence: Qty Within Tolerance (physical vs book qty ≤ 1 unit or 1%), Rate Within Tolerance (floor rate vs moving average ≤ 5%), Value Within Tolerance (physical value vs book value ≤ Rs 100), and Count Date Alignment (count date = book as-of date, same cut-off).
Every failed check is classified into one of four named exception classes so each item routes to the correct functional owner: Shortage — possible theft/wastage (Qty rule, physical < book), Excess — cut-off / GRN not posted (Qty rule, physical > book), Rate Variance (Rate rule), and Book-only — not found on floor or Physical-only — no book record (unmatched populations).
For every exception, the workflow computes the variance in both units and rupee value — for example, SKU RM-CU-014 shows −18 Nos and −₹10,440 as a shortage. This gives the management-explanation queue a fully quantified item so the controller can prioritise by value-at-risk without opening the source files.
The final output is a structured working paper: a variance summary table (total SKUs matched, exception count by class, aggregate value at risk) and a full-reconciliation tab with one row per SKU, columns for physical data, book data, unit variance, value variance, and exception class — formatted to the standard expected under SA 501 and the ICAI Guidance Note on Audit of Inventories.
Six synthetic scenarios that exercise every failure mode we have seen in real-world inventory data. Each produces a deterministic outcome an auditor or controller can verify in seconds.
Two consolidated sample files for a single stock-take cut-off at Acme Corp Pvt Ltd's main warehouse (28-Feb-2026). Both sheets cover the same ~60 SKUs and share the SKU Code identifier, so most rows reconcile cleanly while a handful plants every exception class.
Columns: SKU Code, Item Description, Location / Bin, UOM, Physical Qty, Rate (Rs), Physical Value (Rs), Count Date. Most rows tie to the book stock; specific rows plant a shortage (RM-CU-014), a rate variance (RM-AL-022), an excess (FG-PMP-208), and a physical-only SKU with no book record.
Columns: SKU Code, Item Description, UOM, Book Qty, Moving Avg Rate (Rs), Book Value (Rs), As-Of Date. The counterpart to the physical count; includes book-only SKUs that never appear on the floor (including RM-OBS-099) which surface as missing-on-floor write-off candidates under Ind AS 2, Para 34.
In the clean-tie scenario, SKU RM-STL-001 (MS Steel Plate 10mm 8×4 ft) returned all four reconciliation checks as match: Quantity Within Tolerance (240 Nos = 240 Nos), Rate Within Tolerance (₹4,250 = ₹4,250), Value Within Tolerance (₹10,20,000 = ₹10,20,000), and Count Date Alignment (2026-02-28 = 2026-02-28). The working paper recorded a nil variance for this SKU with no exception raised.
In the shortage scenario, the workflow correctly extracted a physical quantity of 132 coils for RM-CU-014 (Copper Wire 2.5sqmm 90m Coil) against a book quantity of 150 coils, computed the shortfall as 18 units valued at ₹10,440, and placed the item in the exception queue under the Shortage — possible theft/wastage class. The rate-variance scenario extracted RM-AL-022 (Aluminium Sheet 1.5mm 4×8 ft) with a book moving average rate of ₹2,750 per sheet and a floor rate of ₹2,950 — a 7.3% divergence exceeding the 5% tolerance. The workflow isolated this SKU as a Rate Variance exception and reported the absolute and percentage rate gap for cost-accounting review, independently of the quantity reconciliation column.
The most operationally significant finding in the sample data was the Book-only — not found on floor case: SKU RM-OBS-099 appeared in the book stock register at its full moving average value but had zero rows in the physical count sheet, indicating the item had never been counted and may represent obsolete stock that has not been written down under Ind AS 2, paragraph 34. In a naive Excel reconciliation, this exception produces no variance row at all — because a VLOOKUP-based formula only computes deltas for rows present in both sheets. The bilateral join ensures it surfaces as an actionable item with the full book value at risk clearly stated. Across all six edge-case scenarios in the sample data, the workflow produced 100% extraction accuracy on structured Excel inputs and zero false-positive exceptions on rows that reconcile cleanly.
For a mid-market finance team doing a year-end physical count with 200–500 active SKUs, this inventory stock audit workflow replaces two to four person-days of controller time with under 60 seconds of compute time — and catches exception classes that spreadsheet VLOOKUPs structurally cannot surface.
The bilateral SKU join surfaces every item in the book stock register that was not counted on the floor as a named exception with its full book value at risk. A VLOOKUP-based spreadsheet produces no row for these items; the controller would need a separate anti-join query to find them. This is the highest-value exception class in mid-market manufacturing audits because obsolete stock that has never been written down compounds quarter over quarter under Ind AS 2, paragraph 34.
Separating Rate Variance from Quantity Shortage means cost accounting sees the moving-average discrepancy on its own, not buried inside a larger rupee variance. A costing error that collapses into a shortage number stays invisible to the cost accountant and gets resolved as a write-off when the correct resolution is a price update in Tally Prime or SAP Business One.
The output working paper carries the run timestamp, source file names, and a row-level trace from each exception back to its physical count and book stock source rows. It drops directly into the controller's year-end audit file and satisfies the documentary evidence requirement under SA 501 and the ICAI Guidance Note on Audit of Inventories without reformatting — reviewable by the statutory auditor without a week of back-and-forth data requests.
The questions controllers, auditors, and warehouse managers ask most often before deploying automated inventory reconciliation.
Two files for the same cut-off: the Physical Count sheet (your warehouse floor tally, with SKU Code, Location, Physical Qty, Rate and Count Date) and the Book Stock extract from your ERP or Tally (SKU Code, Book Qty, Moving Avg Rate, Book Value and As-Of Date). Drop them together — Cadel classifies each by its columns automatically.
It reconciles on SKU Code. Every SKU present in both sheets is paired, then quantity, rate and value are compared against tolerance and the count date is checked against the book as-of date. SKUs found on only one side are surfaced as unmatched — physical-only or book-only — so neither population is hidden from the exception queue.
Quantity must agree within 1 unit or 1%, the floor rate must be within 5% of the moving-average rate, and physical value must be within Rs 100 of book value. The count date must equal the book as-of date (same cut-off). Anything outside these bands is flagged with the specific rule name so the controller knows exactly which tolerance was breached.
Into four named break types: Shortage — possible theft/wastage (physical qty below book qty beyond tolerance), Excess — cut-off / GRN not posted (physical qty above book qty), Rate Variance (quantity matches but per-unit cost diverges from moving average), and unmatched-side exceptions (missing in book for counted-but-never-booked items; missing on floor for booked-but-never-found items). Count-date misalignment is flagged as a Warning on the matched row.
The floor count is below the book quantity beyond tolerance — typically pilferage, breakage, an un-recorded issue, or a mis-count. Cadel flags it with the unit variance and value at risk; you investigate, confirm the cause, and pass the write-down or quantity correction under Ind AS 2, paragraph 34 where the loss is material.
It is reported as missing on floor — a write-off or re-count candidate carrying its full book value at risk. The reverse case, a SKU counted on the floor with no book record, is reported as missing in book, usually an un-booked goods receipt or a consignment item inadvertently included in the count. Both appear as named exceptions in the queue with the full identifier and value.
Yes. Upload a Physical Count covering only the bins you cycle-counted against the matching Book Stock subset for those SKUs; the workflow reconciles whatever SKUs overlap and reports the rest as unmatched. The output working paper will clearly show which SKUs were in scope and which were not, so the auditor can distinguish the cycle count from a full stock-take.
No. Your documents are processed only to produce this workflow's reconciliation results and are never used to train any model. Each run is isolated; your SKU codes, quantities, and values do not persist beyond the session or influence results for other organisations.
