Accountio / Templates / Stock count / Inventory reconciliation

Inventory reconciliation template in Excel

Compare what you counted with what the books say, item by item, explain every variance that matters and get the adjustment approved before it is posted. This inventory reconciliation template is one Excel workbook with variance analysis by quantity and value, a cut-off list and a summary for the approver.

Finance teams
$12
In the pack
Stock count pack
Files
4, Word and Excel
Buy the pack · $12
US library · 52 packs

Get this pack and 51 more for $79

Stock count · this packBookkeeping and advisory lettersAudit and AUP lettersBook-to-tax reconciliationCompilation and review lettersIndependent contractorsCPA verification letters1040 checklistsIRS notices+ 43 more
$79$69889% off
Buy the US library · $79

Want every pack, for every region? Global library, 110 packs, $99

What the workbook looks like

Inventory reconciliation template
What the workbook coversSheet or column in the workbook
Count details and tolerance
✓
SettingsCompany, count date, currency, and the value and % of book value above which a variance needs explaining
Each item counted against book
✓
Item code, Unit cost, Book quantity, Counted quantityOne row per item, with book and counted values worked out
The size of each difference
✓
Variance quantity, Variance value, Variance %Positive when you counted more than the books show
Which variances need explaining
✓
Over tolerance?Yes when the variance is over both the value and the % limit
Why the stock is different
✓
Reason, ExplanationCounting error, cut-off, unrecorded receipt or issue, damage, loss or theft, unit of measure, pricing
Goods in the wrong period
✓
Cut-offReceived or dispatched but not in the books, or in the books but not received or dispatched
Rows still to fix
✓
CheckMissing quantities or cost, and variances over tolerance with no reason
The overall result
✓
SummaryBook and counted value, net and gross variance, items with a variance and items over tolerance
Variance by reason
✓
SummaryA total for each reason that must agree to the net variance
Sign-off before posting
✓
Approval of the stock adjustmentAdjustment to post, prepared by, approved by, dates and journal reference

When you need an inventory reconciliation

An inventory reconciliation compares the physical count with the stock records on the count date and explains the difference before the books are adjusted. A UK company that deals in goods must keep statements of stock held at each year end and the stocktaking records they come from. Use this one when:

  • You have just finished a year-end or periodic stock take
  • The stock figure in the ledger has drifted from the stock system
  • Large write-offs are posted without anyone saying why
  • Your auditor wants to see how count differences were explained and approved

How to use the inventory reconciliation template

  1. Fill in Settings. Company, count date, currency and the value and % limits above which a variance has to be explained.
  2. Enter each item. Item code, description, unit, unit cost, book quantity and counted quantity. Values and variances work themselves out.
  3. Explain the large variances. Pick a reason from the list for every row over tolerance and write a short explanation, such as a missing display unit reported to the site manager.
  4. List the cut-off items. Goods received, issued or dispatched around the count date that were booked in the wrong period, with the document number and value.
  5. Get it approved. Check the variance by reason agrees to the net variance, then the approver signs and dates it before the journal is posted.

Questions

What is inventory reconciliation?

Matching the stock you physically counted with the quantities and values in your records, then finding out why they differ. It ends with an approved adjustment that brings the books in line with the count.

How do you do an inventory reconciliation?

List each item with its book and counted quantity, value both at unit cost, and work out the variance. Investigate the variances over your tolerance, check cut-off around the count date, give each one a reason and post the approved adjustment.

What causes inventory discrepancies?

Counting errors, goods received or sold but not booked, cut-off errors around the count date, damage, loss or theft, items counted in the wrong unit of measure and wrong unit costs.

What is the difference between net and gross variance?

Net variance adds the gains and losses together, so they can cancel out. Gross variance adds up every difference ignoring the sign, which shows how accurate the records really are.

Do companies have to keep stocktaking records?

Yes, in the UK. A company whose business involves dealing in goods must keep statements of stock held at the end of each financial year and all the stocktaking statements those figures were prepared from.

Also in the stock count pack

Stock count sheetsStock count instructions for staff

See the whole pack →

Who it's for

Finance teams and practices running year-end or periodic stock takes for businesses that hold stock.

Last checked

Checked against the Companies Act 2006 accounting records rules in November 2026.