The 240-Unit Gap in the Monthly Stock Reconciliation
A documentation assistant at a Chandigarh third-party logistics firm prepares the monthly stock reconciliation for a client. The workbook imports a warehouse-system CSV into a sheet called Stock, about 9,400 rows with columns for SKU code, description, batch and quantity, and pulls opening and closing balances from a master sheet using VLOOKUP. This month the reconciliation shows a net shortage of 240 units, and three SKUs appear as not found. The client's audit is in two days. The assistant notices four things: some SKU codes begin with a zero, such as 004512, while the master sheet stores them as numbers; two SKUs appear twice in the master because a new batch line was added last month; the VLOOKUP formula has no fourth argument; and a few rows were pasted from a PDF invoice with merged cells and trailing spaces. The manager is travelling and wants the file by 5 pm. The client has already queried the 240-unit gap.
1. Diagnose the likely causes of the 240-unit gap and the three not-found errors, linking each symptom to a specific spreadsheet problem.
Read the worked answer
Four distinct faults, each producing a different symptom.
- VLOOKUP with no fourth argument. The default is approximate match (TRUE). For any SKU not present exactly, Excel returns the nearest lower code and its balance instead of an error. This produces plausible-looking but wrong figures and is the most likely single source of a broadly distributed 240-unit gap. Only codes below the smallest master value return #N/A, which is why just three SKUs show as not found rather than a large block.
- Leading zeros. The CSV holds 004512 as text; the master holds 4512 as a number. Approximate or exact lookup treats them as different values, so the row either fails or matches something adjacent. The stored quantity may also be read as text and excluded from SUM totals.
- Duplicate SKUs in the master. VLOOKUP returns only the first match. The batch line added last month is invisible, so its quantity is omitted and the closing balance understates stock.
- Pasted PDF rows. Merged cells shift values by a column, and trailing or non-breaking spaces make 004512 and 004512 different strings to Excel. Those rows fail to match and their quantities drop silently out of the total.
The 240 units is therefore probably not one error but the net of several offsetting mis-matches, which is exactly why it looks like a real stock loss.
2. Set out a step-by-step fix and the checks you would run before the file leaves your desk.
Read the worked answer
Work on a copy and keep the original untouched, because the client may later ask how the number changed.
- Import properly. Re-load the CSV through Power Query or Get Data and set the SKU column to Text on import, so leading zeros survive.
- Normalise keys. TRIM, remove non-breaking spaces and pad the code to a fixed six characters. Identical strings on both sides are the precondition for any lookup.
- Fix the lookup. Replace VLOOKUP with SUMIFS on SKU (or XLOOKUP with an explicit match mode), so duplicate master rows are summed instead of silently ignored.
- Rebuild the pasted block. Re-key the PDF-sourced rows rather than pasting, and unmerge any merged cells.
- Recalculate and compare the total to the warehouse system's stock report for the same cut-off, and physically verify the three flagged SKUs.
Checks before sending:
- Row counts: CSV rows = reconciled rows = rows in the final sheet.
- No #N/A and no blank matched balances.
- No duplicate SKU-batch combinations.
- Balance identity: opening + receipts - issues = closing, on the total and on a sample of ten SKUs.
- The sum of line variances reconciles to the reported net figure.
- Version and date recorded in the file name and a short change log.
3. Draft the message to the client and the note to your manager. Explain what each must contain and what must be left out.
Read the worked answer
Both messages are short, factual and timed, and neither contains a guess dressed as a finding.
To the client (email, before 5 pm): acknowledge the query, state that the 240-unit variance is being re-examined against a data-integrity issue in the reconciliation file and not yet confirmed as a physical stock difference, give the specific time the corrected file will arrive, and state what will accompany it (corrected reconciliation, list of SKUs affected, confirmation of the physical count on three SKUs). Do not blame the warehouse system, do not name the individual who pasted the rows, and do not state a corrected number that has not been verified.
To the manager (short message): what was found (approximate-match lookups, duplicate master rows, text/number key mismatch), what is being done, the revised delivery time, and the one decision needed - whether the client should be told today that the variance may be a reporting error rather than a stock loss. Three or four lines, no narrative.
Left out of both: speculation about theft or shrinkage before verification, technical detail the client does not need, and any promise that the gap will disappear. If the corrected figure still shows a real shortage, the same messages must remain true.