Inventory and ledger reconciliation template in Excel

Inventory and ledger reconciliation template in Excel

The stock card and the ledger talk about the same inventory, but they carry it along different paths. The stock card goes document by document: every receipt, every issue and every return is recorded with its date and its cost, and the balance moves the moment the movement is entered. The ledger looks at inventory from its accounts and only recognises a movement when someone posts it. That is why the two amounts almost always look alike without being the same, and why the day they stop matching the real problem begins: nobody knows where the difference was born.

This inventory and ledger reconciliation template in Excel takes that problem head on. It is not a box with two totals that get subtracted at the end and leave a figure with no explanation behind it. It is a bridge: a sheet of fixed lines that walks inventory from the opening balance to the closing balance and puts the two figures in neighbouring columns, the one from the stock card and the one from the ledger, so that the difference is worked out on its own and shows up line by line, on the item where it really started.

What the bridge between stock card and ledger is

A bridge is a reconciliation: an orderly explanation of why two records of the same fact do not come to the same amount. Its structure is simple. The opening inventory goes at the top, then the movements that increase it and the ones that decrease it, and the closing balance at the bottom, which the sheet works out on its own. Every one of those lines is filled in twice: once with the amount the stock card shows and once with the amount the ledger shows for the same item.

The difference is not hunted at the end, it is shown on every line. The sheet subtracts the two columns line by line, so whoever opens it sees straight away that the gap is not spread across the whole bridge but concentrated in one or two items. That change of focus is what makes the exercise useful. An unlocated difference gets corrected blind and comes back the following month; the same difference sitting on the shrinkage line points at a missing record that can be fixed today.

It is worth saying from the start: the goal is not to cover the difference up or force it to zero with a last-minute adjustment. The goal is to find the line where it was born, leave it explained and fix the source.

The five columns of the sheet

The sheet has five working columns. The first carries the name of the bridge line. The next two are the amounts being compared, side by side so that the eye jumps from one to the other without effort. The fourth is the difference per line and it works itself out. The fifth is free text and exists to write down, in words, why that line does not match: it is the part that keeps the reconciliation from ending up as a table of numbers with no memory.

While the item and explanation columns hold text, the others hold numbers, and both value columns are always filled using the same yardstick: the stock card through its valuation method and the ledger through the inventory accounts for the period. If each column is filled with a different logic, the comparison stops making sense from the first row.

The bridge lines, one by one

The bridge has fixed lines and they are not made up along the way: they are always the same. Opening balance first. Then receipts for purchases. Then issues for sales and shrinkage and adjustments, which subtract. Then transfers and returns, which carry a sign because they can add or subtract depending on the direction they move. Then three free lines for other items that do not fit above. And the closing balance at the end, which the sheet works out by adding the opening balance and the receipts and subtracting the issues and the shrinkage.

Issues for sales go at cost, not at the selling price

If there is one mistake that throws this bridge out more than any other, it is writing the invoiced amount of the sale on the issues line. An inventory issue is valued at the cost of the goods that went out, that is, at what it cost to buy or produce them, never at the price they were sold for. The selling price is income for the business and has nothing to do with the value of the inventory.

When the stock card column is filled with the invoice amount, the difference against the ledger comes out huge and false, and the exercise loses all its value: you end up chasing a gap that is really a building mistake in the bridge and not a missing record. Both columns have to talk about the same cost. For the same reason a customer return is valued at the cost of what comes back and not at the invoice price, and an issue for a sample, internal use or a transfer is also valued at cost.

The quick way to check it is to look at where the difference sits. If it turns up large on the issues line and in the period with the highest sales, it is almost always this: somebody typed the price instead of the cost. Before looking for the error in the ledger, check that the stock card column is at cost.

The worked example, line by line

The example that comes with the instruction sheet is filled in like this. The stock card shows an opening balance of 800,000 plus 2,400,000 of receipts for purchases minus 2,600,000 of issues for sales minus 50,000 of shrinkage and adjustments, and lands on a closing balance of 550,000. The ledger, with the same first three movements, lands on 600,000. Both columns start from the same opening balance, add the same purchases and subtract the same issues at cost; the only line that does not match is shrinkage and adjustments.

Read from top to bottom, the outcome is clear. The difference does not appear as a closing gap with no owner, but as -50,000 on the shrinkage and adjustments line. The explanation fits in one sentence and goes in the last column: the shrinkage is not yet recorded in accounting. Once the record is posted, the ledger closing balance drops to 550,000 and the closing difference comes to zero.

The sum of the differences and the line that checks it

Here is the detail that makes the bridge work as a control and not as a plain comparison: every line shows its difference in the same direction as the closing balance. Put another way, the lines that subtract, that is the issues and the shrinkage, carry their difference sign-flipped, because their effect on the balance runs the opposite way. Ignore that flip and the difference column stops being comparable with itself.

The consequence is the best proof that the bridge is built right: the sum of the differences of all the lines must come to exactly the closing difference. That is why the sheet carries, below the check block, one line that adds those differences together and another that compares them with the closing difference and flags it when they do not match. If the sum does not agree with the closing difference, there is no need to look further: some line was left with its sign the wrong way round.

That control line is what stops the classic mistake of adding shrinkage as a positive when it is really subtracting. It is worth reading it before signing the exercise off.

The most common causes of a difference

• Shrinkage, damage and expiry that the stock card recorded and the ledger has not.
• Adjustments from a physical count made in only one of the two sources.
• Transfers between warehouses or sites noted only on the stock card.
• Purchases and sales from the last day of the period recorded in one source and not in the other.
• Differences in the inventory valuation method between one record and the other.
• Typing mistakes, units converted wrongly or unit costs that are out of date.

Shrinkage, adjustments and transfers are the usual causes, and nearly all of them share the same shape: a movement that exists in one source and not in the other. That is why every line with an amount in the difference column is explained in the last column, never left blank.

How the result is read

The closing difference must be zero. When it is not, the status line says Check and the closing difference shows how much is still to be reconciled; that figure is the target, not the problem. The right walk is from top to bottom down the difference column: the lines that are not zero are the origin, and the ones at zero are behind you.

Where the difference sits points to the diagnosis. If it stays only on the opening balance, the carry-over comes from earlier periods and has to be fixed at the opening of this one. If it shows on shrinkage or on transfers, it is a record missing in one of the two sources. If it shows on purchases or on sales, it is usually a document from the end of the period that stayed in a single source. And if it is large and concentrated on the issues line, check first that those issues are at cost.

What this template is and what it is not

This sheet is an internal control tool and a talking point between the inventory team and the accounting team. It is not an official document, it does not replace the financial statements or a filing, and it is not there to cover a gap up: it is there to explain it. The file preloads no tax or levy figure, because the bridge does not need any to do its job.

With the bridge filled in, the conversation stops being "it does not match" and becomes "this is the line that is missing". That shift is what saves hours every month. It is also the template that ties the inventory cluster to the accounting one: both columns come from the valued stock card and from the inventory accounts, and the cost being compared is the same cost of sales that closes the income statement. After working with this sheet, the natural next step is to review the valued stock card, inventory and cost of sales templates as well, since those are where the two columns come from. At Kardex Tauro the bridge is always built before the period close is handed over.