A reconciliation workbook: the columns that make variances traceable

Many reconciliations end in a spreadsheet that only its author can read: a handful of columns, formulas overwritten with numbers and a comment cell that says "check". Two weeks later nobody knows whether the 12 in row 340 is a count, an adjustment or a typing error. A workbook with the right sheets and columns turns the same data into something anyone can follow from the book to the shelf.

Keep raw data apart from working data

Six sheets are enough, and each can be short.

• Info: the name of the count, the cut-off date and time, the source files with the time each was exported, the version and the author.
• Book: the export from 1C, ERP or Excel, pasted as received and never edited.
• Scan: the scan log with location, item, quantity, time and counter.
• Recon: the working sheet, one line per item key.
• Unmatched: lines that appear in only one source, either the book or the count.
• Log: changes made after the first version, with date, person, what and why.

Raw sheets are protected or coloured differently, and never sorted or corrected in place.

The columns of the Recon sheet

1. Item key: the single code used in both sources, stored as text.
2. Description and unit.
3. Location or zone: the location if the book is kept by location, otherwise the main storage place.
4. Book quantity: pulled from the Book sheet by formula.
5. Counted quantity: summed from the Scan sheet by formula.
6. Difference: counted minus book. State the sign convention in the header; negative means shortage.
7. Unit cost and value of the difference: the difference multiplied by the cost.
8. Recount quantity and the final quantity.
9. Cause code, chosen from a fixed list.
10. Owner: the named person who will investigate.
11. Status: open, explained, recounted, to adjust or closed.
12. Comment and reference: a document number or a photo file name.

Formulas and checks

• Book and counted quantities are never typed. They come from a SUMIFS formula on the item key, so the sheet updates when the data is exported again.
• Put control totals at the top. The quantity total of the Book sheet must equal the book total on the Recon sheet plus the unmatched book lines, and the same holds for the count. If it does not, lines have gone missing somewhere, most often because of a trailing space in a code. For illustration, a Book sheet total of 48,310 pieces, a Recon total of 48,250 and 60 pieces on the Unmatched sheet make a correct check.
• Check that no item key appears twice. A repeated key means the quantity is counted twice.
• Filter the lines where the difference is not zero, and show the number of open lines at the top of the sheet.
• Protect the formula columns and colour the columns where people type, so that nobody overwrites a formula with a number.

Cause codes and status come from lists

Make the cause and status columns drop-down lists (data validation) instead of free text. Six causes are enough: entry error, timing, location, counting error, real shortage and unexplained. Free text cannot be sorted or totalled later.

For illustration, item AB-1042, a set of brake pads, has a book quantity of 120 and a count of 108, a difference of -12. At 45 USD a set, that is -540 USD. A similar pad, AB-1043 at the same price, shows a book quantity of 80 and a count of 92, so +12. Both lines get the cause "location", the same owner and the comment "pair AB-1042 and AB-1043, see photo". In value terms the two lines cancel each other out.

Summary

A good reconciliation file is not the one with the most columns, but the one in which the source of every number can be found. Smart Inventory hands over a variance list with book quantity, counted quantity, difference in units and value, location and notes on one sheet, together with the raw scan log and a file for ERP or 1C entry, so your own staff can carry on in the same file.