Spreadsheets

Customer paid less than the invoice: a difference column

Add Invoice, Received and Difference columns, then split each gap into labelled reasons such as deductions, bank charges and short payment.

Editorial illustration for Customer paid less than the invoice: a difference column

When a customer pays less than the invoice, subtract what you received from what you billed to get a Difference column, then split that gap into labelled reasons: agreed deduction, bank charge, credit note, short payment. The aim is that every rupee of difference has a name, and whatever is left unexplained is the amount you chase.

What is the basic layout?

  • A: invoice number
  • B: customer
  • C: invoice amount
  • D: amount received
  • E: difference = C2-D2
  • F: explained, the total of reasons from a separate list
  • G: unexplained = E2-F2
  • H: action = IF(ROUND(G2,2)=0, "Explained", "Chase")

Using ROUND in the action formula stops a stray fraction of a paisa from keeping a row stuck on Chase.

How do you record the reasons?

Do not squeeze reasons into more and more columns. Keep a second tab called Reasons with three columns: invoice number, reason and amount. One invoice can have several rows. The reason column is a dropdown with values you agree on, such as Agreed discount, Credit note, Bank charge, Deduction by customer and Short payment. Then, on the main tab, in F2:

=SUMIFS(Reasons!$C$2:$C$500, Reasons!$A$2:$A$500, A2)

To see how much each reason accounts for across all invoices, use =SUMIFS(Reasons!$C$2:$C$500, Reasons!$B$2:$B$500, "Bank charge").

What does a made-up set of invoices look like?

InvoiceInvoicedReceivedDifferenceExplained byUnexplained
INV-31050,00048,7001,300Bank charge 350, discount 500450
INV-31124,00024,0000Nothing needed0
INV-31236,00035,100900Credit note for returns 9000
INV-31318,50017,0001,500None yet1,500

INV-310 is the one that rewards the method. The difference is 50,000 less 48,700 which is 1,300. A bank charge of 350 plus a discount of 500 explains 850, leaving 450 unexplained, so that row says Chase. INV-312 is fully explained by a credit note, and INV-313 has no explanation at all, so the full 1,500 is the amount to ask about.

Who should you ask, and what should you ask?

Start with the customer's remittance advice, if they sent one. It often lists deductions line by line. If not, ask a short, specific question: you billed an amount, you received a smaller one, which invoice or deduction accounts for the gap? Include the invoice number, date and both amounts. A polite email with the numbers settled tends to get a quicker answer than a vague reminder.

Keep the split honest by matching the sign. Differences can also run the other way: a customer who pays more than the invoice has overpaid, and the Difference column shows a negative number. Treat that as an advance or a refund due, label it, and do not net it silently against another invoice. Your sheet is more trustworthy when overpayments are as visible as shortfalls.

Should you write the difference off?

Small leftovers sometimes are not worth the chase. Decide on a rule, for example "below ₹100 gets a note and is cleared", and apply it consistently. A written-off amount should still appear in the Reasons tab with the label Written off, so that your invoice records add up and you can later see how much you wrote off in total.

Monthly check

  1. Sort by Unexplained, largest first.
  2. Send the questions for the top three or four.
  3. Update the Reasons tab as answers come in.
  4. Confirm that Explained plus Unexplained equals Difference for every row.
SPENDIXAI JOURNAL

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

Explore your reports