Kemlat Header
Kemlat
  • Home
  • About
  • Services
    Business & TransformationA clear view of the problem and a plan you can deliver.8 Project & DeliveryGovernance that connects plans to results.3 ERP & CRMConnected processes and data in systems that fit your work.2 Digital ProductsWebsites, apps and platforms built around your needs.8 AI & AutomationPractical solutions for tasks that should take less time.8 Quality EngineeringMore confidence in every release.3 Cloud & SecurityA dependable foundation for your digital work.5 Managed ServicesStability, support and improvement after launch.3 Brand & CreativeAn identity and experience that tell your story clearly.4 Digital GrowthBetter visibility and decisions based on performance.5 Games & ImmersiveInteractive experiences for learning and for brands.4 Training & EnablementSkills your team can put to work.3
    All services56

    The KEMLAT Method

    KEMLAT
    Build your projectPick the services you need and get an approximate cost range in a minute.
  • Work
  • The Method
  • Insights
  • Contact
ARGet in touch
  • Home
  • About
  • Business & Transformation Project & Delivery ERP & CRM Digital Products AI & Automation Quality Engineering Cloud & Security Managed Services Brand & Creative Digital Growth Games & Immersive Training & EnablementAll services Build your project
  • Work
  • The Method
  • Insights
  • Contact
Get in touch
العربية [email protected] +962 79 122 3241
Check the Main Menu location in Apppearance->Menus->Display Location.
Data Reconciliation Between Bank, ERP and Spreadsheets - Kemlat Insights
Kemlat2026-10-21T17:03:14+03:00
All insightsاقرأ بالعربية
Intelligent Automation

Data Reconciliation Between Bank, ERP and Spreadsheets

When bank, ERP and spreadsheet figures never quite agree, finance pays in hours. Here is why the differences appear and how to automate most of the matching safely.

25 October 20265 min read
Share
Data Reconciliation Between Bank, ERP and Spreadsheets - Kemlat Insights

Every finance team knows the feeling of a reconciliation that is almost right. The bank says one figure, the ERP (enterprise resource planning) system says another, and the spreadsheet someone keeps on the side says a third. Finding the difference takes an afternoon, and next week it happens again.

Reconciliation is one of the best candidates for automation in finance, because most of the matching follows clear rules. The trick is to understand why the numbers drift apart before building anything.

Why the numbers never agree on their own

Take a homeware retailer in Amman with nine stores, accounts at two banks, card payments through a payment acquirer, and an online store using a payment gateway. Cash is deposited daily, cards settle in batches, and online orders settle a few days later. Each source records the same money differently.

Cause of differenceWhat it looks like
TimingCard sales recorded in the ERP on Thursday reach the bank on Sunday or Monday
NettingThe acquirer pays one amount per batch after deducting fees, while the ERP holds each sale at its full value
GroupingOne bank deposit covers cash from three days of a store’s sales
Missing entriesBank charges, interest or a direct debit appear on the statement but have not been posted
Reference problemsA customer transfer arrives with no invoice number, or with the name of a person rather than the company
Genuine errorsA receipt posted to the wrong customer, a duplicated entry, a typing mistake in an amount

Only the last row is a real error. The others are predictable patterns, and predictable patterns can be handled by rules. That is where automation earns its place.

Designing the matching rules

Automated reconciliation works by applying matching rules in order, from the most certain to the least. A practical hierarchy looks like this:

  1. Exact match on reference and amount. A bank line with an invoice number and an amount that both match one open item. These can be matched automatically with high confidence.
  2. Exact amount, close date, same counterparty. No reference, but the amount matches one open item from the same customer within a few days. Usually safe to match, often with a quick review.
  3. Known groupings. A card settlement that equals the sum of a day’s card sales for a store, less the agreed fee rate. Rules can calculate the expected net amount and match the batch.
  4. Recurring items. Bank charges, rent, salaries and loan instalments that appear every month with similar amounts and descriptions. Rules can propose the posting.
  5. Everything else goes to a person. Partial payments, unexplained receipts and amounts that match more than one open item should never be matched by guesswork.

Start with conservative rules and loosen them only when the results prove reliable. It is better to send a few extra items for review than to match something incorrectly.

Rule of thumb: an automated reconciliation should never hide a difference. Every item is either matched by a stated rule, matched by a named person, or listed as open with its age.

Getting the data in without retyping

Automation needs digital data from every source. Many banks in the region provide statement downloads as CSV (comma-separated values) or Excel files. Some also offer corporate clients standard electronic statement formats, such as the older MT940 format from the SWIFT banking network or the newer camt.053 format under the ISO 20022 standard. These follow a fixed structure, so software reads them more reliably than a spreadsheet export whose columns can change. Card acquirers and payment gateways usually offer settlement reports listing each transaction in a batch. Ask your banks and providers what they offer, and whether downloads can be scheduled rather than done by hand.

From there, the options depend on your systems. Many ERPs, including Odoo, SAP Business One and Microsoft Dynamics, include bank reconciliation features with configurable matching rules. Where the ERP falls short, or where data from spreadsheets and external reports must be combined, Power Query, a small database with scripts, or a workflow tool can prepare and match the data before it reaches the ERP.

Before switching anything on, run one month’s reconciliation by hand with the new rules written down beside you. It shows which rules match cleanly, which produce false matches, and which sources need better references, such as asking key customers to quote invoice numbers on their transfers.

Spreadsheets deserve a specific mention. If a store manager keeps a daily cash sheet that never enters a system, it will always be the hardest source to reconcile. Replacing that sheet with a simple structured form, even a shared one, makes it machine-readable and removes a whole category of differences.

Handling exceptions and keeping control

Automation handles the bulk of the matching, but its bigger benefit is that the remaining exceptions become visible and manageable. For the retailer, a daily exception list might show twenty open items out of several hundred bank lines, each with its age and a suggested reason.

  • Review exceptions daily, not monthly. A difference found the next day is easy to explain. The same difference found three weeks later can take hours.
  • Assign ownership by type. Store managers explain cash deposit differences, the e-commerce team explains gateway differences, and finance handles bank charges and unidentified receipts.
  • Keep an audit trail. Record which rule or person matched each item, and when. This matters for internal control and for external auditors.
  • Separate duties. The person who records receipts should not be the only person who approves reconciliation adjustments.
  • Watch the trend. If exceptions grow, a rule may have broken, a provider may have changed a report format, or a store process may have slipped.

For the retailer, the practical effect is that store managers hear about a short cash deposit the next morning, while they still remember the day, rather than at month-end when nobody can reconstruct what happened.

Reconciliation as a daily routine, not a month-end crisis

A good place to start is the source that causes most differences today, often card settlements or one busy bank account. Write the matching rules for that source, run them by hand for a month, then automate them. Once matching is automated, month-end close shortens and finance knows each morning what has arrived and what is missing.

Our Intelligent Automation service designs and builds these data reconciliation workflows: bringing bank, ERP and other sources together, defining matching rules your finance team understands, and leaving clear exception lists that keep control where it belongs.

Found this useful?Share it with your team or someone who needs it.
  • WhatsApp
  • LinkedIn
  • X
  • Facebook
  • Telegram
  • Email
Related service

Intelligent Automation

We automate the repetitive, the manual and the error-prone, so your team focuses on work that needs human judgement.

Also related: Enterprise Systems

Explore our services

Related insights

Automated Alerts for Contract Renewals, Licences and Expiry Dates - Kemlat Insights
Intelligent Automation

Automated Alerts for Contract Renewals, Licences and Expiry Dates

Expired licences, missed notice periods and lapsed permits usually come down to dates nobody was reminded about. One register and a few escalating alerts fix that.

15 Nov 2026 · 5 min read
Robotic Process Automation: Where It Fits and Where It Does Not - Kemlat Insights
Intelligent Automation

Robotic Process Automation: Where It Fits and Where It Does Not

Robotic process automation (RPA) bots operate software the way a person does. That makes them useful for systems that cannot be connected any other way, and…

14 Nov 2026 · 5 min read
Automating School and Training Centre Administration - Kemlat Insights
Intelligent Automation

Automating School and Training Centre Administration

Admissions, fee reminders, attendance notices and certificates follow predictable rules, which makes them good candidates for automation. Start with one process and a reliable student record.

13 Nov 2026 · 5 min read

Kemlat Footer
Kemlat
Kemlat

We help organisations transform complex operations into clearer ways of working, practical solutions, and measurable outcomes.

Quick Links

  • Home
  • About Kemlat
  • Our Services
  • Selected Work
  • The Method
  • Contact Us

Services

  • Business & Transformation
  • ERP & CRM
  • Digital Products
  • AI & Automation
  • Cloud & Security
  • Digital Growth
  • All services
  • Build your project

Get in Touch

Email [email protected]
Location Almadina Almunawwara St., Amman, Jordan
WhatsApp & Phone +962 79 122 3241

© 2026 Kemlat. All rights reserved.

Privacy Policy Terms of Use
Kemlat Method

Request a Service

Tell us what you need and we will get back to you within one business day.

By sending this form you agree to our privacy policy.

Request received

Thank you. Our team will review your request and contact you within one business day.