Inventory Reconciliation Template, With a Worked Example

Inventory reconciliation is comparing what your books say you have with what a count says you have, item by item, and explaining every difference before you adjust anything. A difference often isn't a loss: it's a record that was never entered, a delivery nobody received in the system, a batch nobody recorded, a market sale, a damaged case in the bin. So the order matters: find what explains each difference, enter the missing record with its real date, recount where the count looks wrong, and only then adjust what's left. In the example below, a month-end count differs from the books on six items, by $7.19 overall, but once a missing receipt, an unrecorded batch, a market sale and a write-off are entered, only $3.91 is unexplained, the same $3.91 that the month's value check shows. Below is how to reconcile, the example, the value check, and a free template.

The short version

• Compare books and count, item by item, in quantity and in value.
• For each difference, ask what record is missing before calling it a loss.
• Recount anything that looks wrong; counting errors are differences too.
• Enter missing receipts, batches, sales and write-offs with their real dates.
• Adjust only what's left, with a reason, so the books match the shelf.
• Check the value: opening plus purchases less cost of goods against the value counted.
• TaroStack records every movement as it happens and values a count's differences before you post them. More below.

How to reconcile, step by step

1. Count everything you're reconciling, on the same day, with movement stopped or noted.
2. Compare each item's count with the books, and work out the difference and its value.
3. Recount any item whose difference looks too big or too round.
4. Explain each difference with the record that's missing: a receipt, a batch, a sale, a transfer, a write-off, a unit mistake.
5. Enter the missing records with their real dates, so the history is right, not only the total.
6. Adjust what's still unexplained, with a reason, so the books match the shelf.
7. Check the value against the month's purchases and cost of goods.

How to run the count itself is in how to count inventory, and the formula for a difference's size is in inventory variance formula.

A worked example

Windward Roots, the made-up taro business I use in these examples, counts at the end of October:

Of six differences, four are missing records and one was a counting mistake. The kūlolo sold at Saturday's market, the batch of chips made on the 28th, the coconut milk delivered on the 29th and the four damaged bags all happened; they weren't written down where the books could see them. The trays, half a kilo of taro and five labels are the real, unexplained losses: $3.91. Recording the missing four with their dates is what keeps next month's numbers right, and the batch's cost right. Why records go missing, and where to look, is in why inventory does not match.

The value check

The books can be checked from the other side, in money:

With the missing records entered, the value check and the reconciliation agree on the $3.91 that nobody can explain. If they hadn't, the next place to look would be costs: a delivery received at the wrong price, or freight left out. How stock value is worked out is in what is inventory valuation.

Get the template

Download the inventory reconciliation template. Type each item's books and count with its cost; for each difference, choose what explains it and how much, and the sheet shows what's left unexplained and its value. A second sheet checks the value against the month's purchases and cost of goods. It's an ordinary Excel file with no macros, and it works in Google Sheets, LibreOffice and Numbers.

Where this stops working

A monthly reconciliation finds last month's missing records, after the fact, from memory. Somebody has to remember that the chips were made on the 28th, how many cans really came on the 29th, and what sold on Saturday, and by then the batch's cost, the supplier's invoice and the market's numbers are all a month old. The reconciliation takes a day, and next month there are new gaps.

How TaroStack does it

TaroStack records each movement where it happens, so the books and the shelf have fewer reasons to disagree.

Every movement, when it happens. Receiving adds stock as the delivery comes in, with or without a purchase order, and flags over-receiving. Recording a batch takes ingredients off and adds the product. Sales and shipments take stock out, and adjustments carry their reason: lost, damaged, expired, spoiled, sample, found, correction or customer return. Every movement is a permanent record of who, when and why, and corrections are recorded as corrections and can be undone.

Counts that show the money first. Counting works on a phone, tablet or printed sheet, with no signal, shows what each difference is worth before you post it, and suggests what to count first: what it would cost to be wrong, times how much has happened since anyone looked. Shipping, selling, making and moving can't take stock below zero, so a missing receipt shows up when someone tries to use what the books say isn't there.

And value you can check. Stock value by site and by month comes from FIFO cost layers, landed costs included, and losses are reported by reason, item and site.

Counts, adjustments with reasons, receiving, batches and stock value are on every plan, from $49 a month. The first 30 days are free, with no credit card. If you'd like a hand setting up, ask, and we'll do it with you.

Questions people also ask

How do you reconcile inventory?

Count, compare each item with the books, recount anything doubtful, explain each difference with the record that's missing, enter those records with their real dates, adjust what's left with a reason, and check the value against purchases and cost of goods.

What causes inventory reconciliation differences?

Often missing records: deliveries not received in the books, batches not recorded, sales not recorded, damaged stock thrown away without a write-off, and unit mistakes. Counting errors and real losses make up the rest.

What is an inventory reconciliation template?

A sheet with each item's book quantity, counted quantity, the difference and its value, what explains it, and what's left unexplained, plus a check of the stock value against the period's purchases and cost of goods.

How often should inventory be reconciled?

At least at each month end or full count. Counting a few items every week, a cycle count, finds missing records while people still remember what happened.