Spreadsheets

Reconcile a supplier's statement of account with your ledger

Reconcile a supplier's statement of account with your ledger: match on invoice number, list timing differences, missing credit notes and disputed items.

Editorial illustration for Reconcile a supplier's statement of account with your ledger

To reconcile a supplier's statement with your ledger, put the supplier's list of invoices, credit notes and payments next to yours and match them on the document number. Whatever is on one side only falls into a few usual groups: an invoice you have not booked, a payment still on its way, or a credit note one side has not recorded. List each, and the two balances should reconcile.

How do you lay out the two lists?

On a tab called Supplier, copy the statement: date in A, document number in B, description in C, amount in D, with invoices positive and credit notes and payments negative. On a tab called Mine, copy your ledger for that supplier in the same shape. Sign convention is the part that goes wrong most, so confirm that a rising balance on both sides means you owe more.

Which formulas find the differences?

On the Supplier tab, in E2, filled down:

=IF(COUNTIF(Mine!$B$2:$B$300, B2)=0, "Not in my ledger", IF(SUMIFS(Mine!$D$2:$D$300, Mine!$B$2:$B$300, B2)=D2, "Matched", "Amount differs"))

On the Mine tab, in E2, point the other way:

=IF(COUNTIF(Supplier!$B$2:$B$300, B2)=0, "Not on supplier statement", IF(SUMIFS(Supplier!$D$2:$D$300, Supplier!$B$2:$B$300, B2)=D2, "Matched", "Amount differs"))

Document numbers need to be consistent. A supplier's INV/77 and your INV-77 will not match unless you tidy them first with SUBSTITUTE or by retyping.

What does the reconciliation look like in rupees?

Say the supplier's statement shows a balance of ₹2,48,000 and your ledger shows ₹1,98,500. The difference is ₹49,500. These are made-up figures. Start from your balance and add what explains the gap:

ItemWhere it appearsEffect
Balance per my ledgerMy books1,98,500
Invoice INV-4471 not yet booked by meSupplier statement only+36,000
Credit note CN-118 for returned goods, not yet issued by the supplierMy ledger only+8,500
Payment sent on 29 Nov, not yet creditedMy ledger only+5,000
Balance per supplier statementSupplier2,48,000

The arithmetic closes: 1,98,500 + 36,000 + 8,500 + 5,000 = 2,48,000. If you reach the supplier's figure with items you can each name, you are reconciled. If a gap remains, you are not.

How should you sort the differences?

  • Timing: a payment or invoice dated near the statement date that the other side will record next period.
  • Missing document: an invoice or credit note you never received. Ask the supplier for a copy.
  • Disputed: a price, quantity or tax difference. Hold it aside with a note of what you believe is right.
  • Error: a posting mistake, a duplicate or a wrong amount. Fix it on whichever side is wrong, once you have the paper.

What do you send back to the supplier?

A short reconciliation note is enough: your balance, their balance, and the list of reconciling items with dates, document numbers and amounts. Ask them to confirm each item. Keep the reply with the working sheet so that the following quarter starts from a known position.

A routine for the next statement

  1. Request the statement as of a fixed date, such as month-end.
  2. Run both match formulas and filter for anything not Matched.
  3. Write a reason beside each unmatched row.
  4. Send the reconciling items and note who owes the next action.

Doing this quarterly for your largest suppliers is a sensible compromise between effort and risk. If amounts relate to GST or tax deducted at source, confirm the treatment with your CA.

SPENDIXAI JOURNAL

Useful ideas start with a clearer picture of your own spending.

Explore your reports