Reconciling Card Settlements Without an ERP: One Weekly Spreadsheet

Reconciling card settlements is checking that what customers were charged actually reached the bank. In a business with no ERP it usually does not happen at all, and small gaps then accumulate for months until somebody notices. The process that holds is weekly, takes twenty minutes, and runs in one spreadsheet with seven columns.

Why are the amounts never identical?

This is the wrong assumption that defeats every first attempt. The amount landing in the bank almost never equals the sales total, and that is normal:

Anyone who understands those six rows can explain almost any gap. Anyone who does not goes hunting for an error that does not exist.

The spreadsheet: seven columns

A minimal layout that works:

1. Deposit date - as it appears at the bank.
2. Deposit amount - what actually landed.
3. Transaction dates inside the batch - a range.
4. Gross transactions - per the clearing report.
5. Fees - per the same report.
6. Refunds - offset within the batch.
7. Gap - a calculated column: gross minus fees minus refunds, against the deposit amount.

Column seven is the entire process. When it is zero, you are done. When it is not, there are only three suspects: a transaction that fell into another batch, a refund not counted, or an unusual fee.

The weekly process, in twenty minutes

1. Download the week's clearing report, as a file rather than a screenshot.
2. Download that week's bank transactions.
3. Match each deposit to a batch - usually by amount and date.
4. Fill in the first six columns.
5. Look at the gap column and handle only the rows that are not zero.
6. Write a note explaining each gap, so next time takes less.

Step six is what turns this from a chore into something that shortens: after a month, most gaps repeat and already have a ready explanation.

What to do with several payment providers

This is the common state in a business with a shop and a website, and reconciliation stays simple if you separate:

• A separate sheet, or at minimum a "provider" column, per source.
• Never mix two providers' batches in one row, even when the deposits landed the same day.
• Check every provider exports a file; one that only shows a screen makes the process entirely manual.
• Total by provider at month end, to see each one's relative cost.

The last point is a useful by-product: after three months this spreadsheet tells you exactly how much you paid each provider in fees, which is a good number for a renewal conversation.

What to do when you find a real gap

• A small one-off gap: note it, move on, and check whether it repeats. Not every shekel is worth an hour.
• A recurring gap of the same size: almost always a fee you did not understand or a service you did not know you were paying for.
• A transaction charged and never deposited: go to the provider with the transaction number and date.
• A deposit with no matching transactions: usually a refund or a correction from an earlier period.
• A gap growing month over month: the one red flag that justifies stopping and investigating immediately.

When the spreadsheet stops being enough

• More than three providers or channels.
• Meaningful volume in foreign currency.
• A monthly transaction count too high for visual checking.
• More than one person needing to work on the reconciliation.
• When you start asking profitability questions rather than only matching questions.

Until then, a spreadsheet is the right answer rather than a compromise. The next step is described in options for reconciling card transactions, and the broader bank reconciliation context is in the daily bank reconciliation process.

What reconciliation reveals besides errors

• What clearing actually costs as a percentage of turnover, rather than what the agreement says.
• How many refunds there are - a number almost no small business knows by heart.
• Whether settlement timing matches the agreement - the basis for a conversation with the acquirer.
• Seasonality in the gaps, pointing at holidays or at an internal pattern.

Who does this in the business

In a small business this is half an hour a week for whoever already touches the money - the owner, the bookkeeper, or an administrator. Three rules decide whether it actually happens: fix a day and time rather than "when there is time"; make sure whoever does it has access to both the clearing report and the bank account, because without one of them it is impossible; and define who is told when an unexplained gap appears. The third rule is what separates a process that produces action from a spreadsheet that merely fills up.

The mistake every first attempt makes

The first attempt almost always fails in the same place: trying to match a single transaction against a bank line. That will never work, because the bank does not receive transactions - it receives batches. A 340-shekel transaction will not appear in the bank as 340 shekels; it is absorbed into a deposit of 12,480. Anyone who understands that in the first row finishes the reconciliation in twenty minutes; anyone who does not gives up after an hour and decides it is too complicated.

What to do in the first month

The first month always takes longer, and that is fine. Three things that shorten it:

• Start with a single week, not a whole month. If the spreadsheet works on one week, it works on the rest.
• Do not try to explain historical gaps. Start from today forward; what came before stays as it is.
• Write down every explanation you find. By month two, eighty percent of gaps are already familiar.

And what you do not need: to start with a tool or software. An empty spreadsheet and one file from the provider are enough for the first week, and if the process does not survive two weeks in a spreadsheet, it will not survive in a system either.

Sources

• Shva - the Israeli card clearing infrastructure
• Bank of Israel - payment and settlement systems
• Cardcom - reports and clearing solutions