Your sales sheet says you sold ₦412,000 this week. Your bank app, your fintech wallet and your mobile money account together show ₦389,500 received. Where did ₦22,500 go? Maybe a customer never paid, maybe a transfer landed in the wrong account, maybe you forgot to record a refund. Without a method, you will never know, and the gap grows every week.
Reconciliation is the plain habit of matching what you recorded as sold against what actually arrived. This guide shows a simple way to do it in a spreadsheet, where AI tools can help safely, and how to turn the skill into paid work for other small businesses.
What reconciliation actually checks
You are asking three questions for every period (a day, a week or a month):
- Did every sale marked "paid" produce money in an account?
- Did every credit in an account match a sale, a customer deposit or something else you can explain?
- Do the totals agree after you allow for known differences such as transfer charges, cash you kept, and supplier payments?
Anything that fails a question goes on a short "unmatched" list. Your job is to clear that list, not to make the totals look equal by force.
Step 1: Set up three tabs
Open a spreadsheet in Excel, Google Sheets or a free phone spreadsheet app. Make three tabs.
Tab 1: Sales
Columns: Date, Order ID, Customer name, Item, Amount, Payment method (cash, bank transfer, wallet, mobile money, POS, pay on delivery), Status (paid, part-paid, unpaid), Matched? (leave blank for now).
If your orders live in WhatsApp chats, first turn them into rows. Turn a Messy WhatsApp Order List Into a Clean Sales Report walks through that step.
Tab 2: Money In
Columns: Date, Account (bank name, wallet name, mobile money), Amount received, Sender name or narration, Reference, Matched order ID (blank for now).
Tab 3: Unmatched
Empty for now. This is where problems go, with a note and an owner (you, a staff member, the customer).
Step 2: Get your statements out of the apps
Most bank apps and fintech apps let you view or download a statement for a date range. Mobile money services (M-Pesa in Kenya, MoMo services in Ghana, and wallet or agent accounts in Nigeria) usually give a transaction history too, either in the app, by a code on your phone or from the provider's website. Menus differ by provider and change often, so check your own app's help section.
Copy only credits into the Money In tab. Debits belong in a separate expenses record. If you can only screenshot, type the entries manually; do not skip this just because it is slow. One careful half hour weekly beats three confused hours monthly.
Two habits that save you later:
- Use the same date format everywhere. Mixed formats break matching.
- Keep the sender's name exactly as it appears. Customers often pay in a spouse's, friend's or company's name, so write a note on who the sale was for.
Step 3: Match by amount, date and name
Start with exact matches. In the Sales tab, add a helper column that counts how many money-in rows have the same amount. If Amount is in column E on Sales and the received amounts are in column C on Money In, the formula in a new column is:
=COUNTIF('Money In'!C:C, E2)
A result of 1 means one matching credit exists. 0 means nothing arrived at that amount. 2 or more means you need to look at date and name, because several customers may have paid the same price.
Then go through rows by hand where the count is not 1. When you confirm a match, type the Order ID into the "Matched order ID" column on Money In and put "Y" in "Matched?" on Sales. Doing it by hand for the awkward ones is normal and not a failure of your spreadsheet.
The usual reasons a match fails
- Transfer or withdrawal charges. The customer sent ₦15,000 but ₦14,950 reached you, or the reverse because the customer covered the fee.
- Part payments. Two credits of ₦7,500 against one ₦15,000 order.
- Someone else paid. The name on the credit does not match the customer.
- Delayed credit. The sale was on Friday, the money posted Monday.
- Combined payments. One credit covers two orders.
- Reversals. A transfer was credited then pulled back.
- Cash never banked. Marked paid, but the cash is in the shop drawer or was spent.
A worked example (hypothetical)
Take a hypothetical provision shop with a week's sales sheet showing ₦412,000. Payment methods recorded: ₦120,000 cash, ₦210,000 bank transfer, ₦82,000 wallet and mobile money.
| Line | Amount (₦) |
|---|---|
| Sales sheet total | 412,000 |
| Less cash sales (checked against cash counted, ₦118,000) | -120,000 |
| Expected electronic receipts | 292,000 |
| Actual credits in bank and wallets | 271,500 |
| Gap | 20,500 |
The cash is already ₦2,000 short, which goes on the unmatched list. For the electronic gap of ₦20,500, matching row by row finds three things: a ₦12,000 order the customer said they paid but no credit exists (the customer sent a screenshot that the owner should verify in the app, not trust); ₦6,500 in transfer charges the owner absorbed over 13 small payments at ₦500 each; and a ₦2,000 under-payment on one order. Total explained: ₦20,500.
The ₦12,000 is the real lesson. A screenshot is not proof of payment, since fake transfer screenshots are a common trick. Confirm in your own account balance or statement before handing over goods.
Step 4: Use AI for the boring parts, carefully
General AI chat tools and the built-in assistants in spreadsheets can help with formulas, cleaning messy narrations and suggesting ways to flag mismatches. Good uses:
- Ask how to write a formula that flags rows where the amount matches but the date differs by more than three days. Describe your columns, not your data.
- Ask how to split a narration column so the sender's name sits in its own column.
- Ask for a way to highlight duplicates automatically.
Do not paste real account numbers, customers' names with phone numbers, or full statements into a public AI tool. Use a dummy sample with fake names and amounts to test the formula, then apply it to your real sheet. Using AI at Work Without Leaking Customer Data covers this in detail. And always check any AI formula on five rows you can verify by eye before trusting it on five hundred.
Step 5: Build a weekly routine
- Pick a fixed time, such as Sunday evening or Monday morning.
- Record the week's credits from every account in Money In.
- Run the matching formula and clear the easy matches.
- Work through the unmatched list, messaging customers politely: "We have not seen the ₦12,000 for order 0457. Can you send the bank's transaction reference?"
- Write the closing numbers: sales, received, unmatched, cash counted.
- Move unresolved items to next week with a date by which you will give up or chase.
For mixed personal and business accounts, add a "Not business" label on credits like family transfers. Better still, separate the accounts. Reconciliation on a mixed account is guesswork.
Mistakes that quietly wreck the numbers
- Forcing the total to balance. Adding a vague "miscellaneous" line hides theft, errors and bad debts.
- Reconciling monthly only. By then nobody remembers who paid what.
- Trusting the customer's alert screenshot. Check your own app.
- Ignoring small gaps. A recurring ₦500 shortfall per order is a fee you should price in.
- Deleting rows. Mark errors and reversals instead, so there is a trail. If a dispute arises, a clean record helps you; see Keeping a Work Log That Protects You in a Pay Dispute for the same principle.
Turning this into a service
Many shop owners know they should do this and never do. If you can set up the three tabs, run the weekly match and hand back a one-page summary of what is unmatched, that is a service a small business can understand. Start with one friendly shop, do two weeks free or at a low rate as a trial, and keep their data confidential. For pricing logic, read How to Price a Spreadsheet Cleanup Job for a Small Business, and for the formulas that clients notice, Spreadsheet Skills That Get Small-Business Clients Paying.
Frequently asked questions
Do I need accounting software for this?
No. A spreadsheet is enough for a small shop. Accounting software helps once you have many daily transactions or staff, but the matching habit is the same.
What if my POS terminal settles in bulk?
Record each settlement as one credit, then compare it against the POS sales slips for that period. The settlement may be lower than the sales because of charges and may arrive a day or more later.
How long should I keep records?
Keep at least the current year's sheets and statements easy to reach. Tax and record-keeping rules differ by country, so check your revenue authority's website or ask a licensed accountant.
Should I reconcile daily?
If you handle many transfers, a five-minute daily check of that day's credits against sales makes the weekly job much shorter.
Your next step
Download last week's statements from every account you use, paste the credits into a new Money In tab, and match just one day. Once that single day balances, repeat for the rest of the week.
Results from any side hustle or online work vary widely, and nothing here is a promise of income. Be wary of anyone who asks you to pay upfront to start earning.