Stock reconciliation template
By the Analistable team · Updated · 2 min read
Paste system stock (SKU, quantity, unit cost) on one sheet and your count lines, by location, on another. The Reconciliation sheet adds up counts per SKU, works out quantity and value variance, labels each line Match, Short, Over or Not counted, and the Count sheet flags SKUs missing from the system.
Part of our guide: How to reconcile data in Excel
What the template does
A stock take produces count sheets, often several per SKU because items sit in more than one location. The system has one figure per SKU. Reconciling them means adding the counts up per SKU, comparing, and putting a value on the difference so you know which variances matter. The method is from the reconciliation guide; the stock count walkthrough covers the investigation side.
Sheet-by-sheet guide
| Sheet | Columns | You fill in? |
|---|---|---|
| Instructions | Step, What to do | No |
| System stock | SKU, Description, System qty, Unit cost | Yes |
| Count | SKU, Location, Counted qty, In system? | Yes (first three) |
| Reconciliation | SKU, Description, System qty, Unit cost, Counted qty, Variance, Variance value, Status | No (formulas) |
How to fill it in
- Export stock on hand at the count cut-off time and paste it on System stock.
- Enter count lines on Count: one line per SKU per location. Several lines for one SKU are fine.
- Copy the Reconciliation formulas down to the number of SKUs, and keep the Total row below them.
SKUs must be stored the same way on both sheets. A code like 00125 typed as a number loses its zeros; see numbers stored as text.
How the formulas work
Counted quantity adds every location line for the SKU; variance is counted minus system:
=SUMIFS(Count!$C$2:$C$1000,Count!$A$2:$A$1000,A2)
=ROUND(F2*D2,2)The status separates a real zero from an SKU nobody counted:
=IF(COUNTIFS(Count!$A$2:$A$1000,A2)=0,"Not counted",IF(F2=0,"Match",IF(F2<0,"Short","Over")))Worked example
| SKU | System qty | Counted qty | Variance | Variance value | Status |
|---|---|---|---|---|---|
| SKU-100 Blue mug | 40 | 38 | -2 | -7 | Short |
| SKU-103 Apron | 15 | 17 | 2 | 16 | Over |
| SKU-105 Tote bag | 50 | 44 | -6 | -16.8 | Short |
| SKU-107 Napkins | 100 | 0 | -100 | -90 | Not counted |
| Total (all 8 SKUs) | -97.8 |
The other four SKUs match. SKU-100 was counted in two locations (22 + 16). SKU-108 appears on the Count sheet as Not in system: stock that exists physically but was never set up. Recount Not counted lines before booking any adjustment.
Limits and when to move on
- The template assumes one site. For several warehouses, add a Site column and condition, or combine branch inventory counts first.
- Movements between the system snapshot and the count (sales, receipts) show up as variances; freeze movements or adjust for them.
- For thousands of SKUs or recurring counts across sites, Analistable can join the count and system exports in your browser and list variances above a value threshold on request.
Frequently asked questions
- What is the difference between Not counted and Short?
- Short means the SKU was counted and fewer were found. Not counted means there is no count line at all, which is usually a missed shelf rather than missing stock.
- Can I use it for a cycle count of a few SKUs?
- Yes. Paste only the SKUs in the cycle on System stock so uncounted items elsewhere are not flagged.
- Does it post adjustments to my stock system?
- No. It is a spreadsheet for working out variances; you book the approved adjustments in your system.