How to reconcile a stock count with inventory records

Prepare the count

Count sheets often have the same SKU on several lines (counted in two bays). Sum them first:

Counted  =SUMIFS(Count[Qty], Count[SKU], [@SKU], Count[Location], [@Location])
Variance =[@Counted] - [@[System qty]]
Value    =[@Variance] * [@[Unit cost]]

Worked example

POT-03 shows zero counted: recount before writing it off — a whole SKU missing is more often a missed bay than a theft. LID-04 was counted but the system shows none: probably a goods receipt not booked.

Two warehouses or channels

Stack each location's stock report with a Location column, then pivot by SKU to see total stock and the split. To compare marketplace sales with stock, join the sales export (units sold per SKU) to opening stock and expected closing stock = opening − sold + received.

Checks before posting adjustments

• Recount every line with a variance above a threshold (units or value).
• Check open goods receipts and despatches around the count time — cut-off errors look like variances.
• Confirm units of measure (each vs case of 6).
• Summarise the total value of adjustments for sign-off.

Inventory turnover — how fast stock sells — uses the same stock and sales data: see calculating inventory turnover from stock and sales sheets.

In SQL

SELECT COALESCE(s.sku, c.sku) AS sku,
       COALESCE(s.qty, 0) AS system_qty,
       COALESCE(SUM(c.qty), 0) AS counted
FROM system_stock s
FULL OUTER JOIN count_lines c ON c.sku = s.sku
GROUP BY 1, 2
HAVING COALESCE(s.qty, 0) <> COALESCE(SUM(c.qty), 0);

The full outer join keeps SKUs that were counted but aren't in the system, and SKUs in the system that nobody counted.

Step by step

1. Freeze stock movements, or record the exact cut-off time, before counting.
2. Export the system stock report at the cut-off with SKU, location, quantity and unit cost.
3. Collect count sheets or scanner exports into one table with SKU, location and quantity.
4. Build the list of every SKU and location from both sides, so nothing is missed.
5. Add Counted, Variance and Value with the formulas above, then sort by absolute value.

In Excel 365 and Google Sheets, build the combined SKU list with =UNIQUE(VSTACK(System[SKU], Count[SKU])) in Excel or =UNIQUE({System!A2:A; Count!A2:A}) in Sheets. In Excel 2019, paste both SKU columns under each other and use Data → Remove Duplicates. Use the SKU and location together as the key if the same SKU sits in several bays.

Second example: unit-of-measure errors

CUP-06 showed a variance of −300 until the count was converted from cases to each; NAP-50 turned from −979 into a genuine surplus of 50. Add a pack-size column from the item master and convert counts before calculating variances.

Troubleshooting

Checking the result and cycle counts

Before posting, check that total counted units equals the sum of all count lines, and that the system total in your sheet equals the stock report total. The net adjustment value is the sum of the Value column; report gains and losses separately as well, because a small net figure can hide large offsetting errors.

For rolling cycle counts, keep a log of each count with its date, so you can see which SKUs haven't been counted for a while and which keep showing variances. Comparing several count files is the same stacking job described in combining Excel files into one, and the reconciliation method is in the reconciliation guide.

Doing it in Power Query

If counts arrive as several scanner files, Power Query avoids copying and pasting: load the count files from a folder, group by SKU and location summing quantity, then merge with the system stock report using a full outer join so both uncounted and unknown items stay in the result. Replace nulls with 0 on both quantity columns before adding the variance. The folder approach is described in combining files from a folder with Power Query.

Which variances to recount

Set the threshold in value rather than units: a variance of 10 on a 0.60 lid matters less than a variance of 1 on a 400 item.

Common mistakes

• Taking the system report after movements restarted, so sales during the count look like losses.
• Using selling price instead of cost to value variances.

Reporting the count

A short sign-off summary usually covers: SKUs counted, SKUs with a variance, total gains and total losses in value, the net adjustment, and the largest five variances with their explanations. Keep the count sheets and the reconciliation workbook together, so an auditor can trace any adjustment back to the original count line and the system figure it was compared with.

Frequently asked questions

How do I compare a stock count with system stock in Excel?

Sum counted quantities per SKU and location with SUMIFS, put them next to the system quantity, and calculate the variance in units and value.

What should I do with large stock variances?

Recount, check receipts and despatches around the count time, and confirm units of measure before adjusting stock.

How do I combine stock from two warehouses?

Stack the stock reports with a location column and pivot by SKU.

How often should I reconcile stock?

Full counts once or twice a year are common, with rolling cycle counts of fast-moving or high-value SKUs in between.

How do I find SKUs that weren't counted?

List every SKU from the system report and flag those with a COUNTIF of zero in the count lines, then recount them before adjusting.

What if the count was in cases and the system in units?

Multiply counted cases by the pack size from your item master before calculating the variance.

Should I value stock variances at cost or selling price?

At cost, using the same unit cost your inventory system uses for valuation, so the adjustment matches what the accounts will post.

Related guides

• How to reconcile a rent roll with rent payments
• How to reconcile a VAT return with your sales ledger
• How to reconcile shipping carrier invoices with your orders
• How to do a three-way match in Excel
• How to compare budget vs actuals in Excel