Bank Reconciliation Automation for a Multi-Entity Retail Organization

CUSTOMER

A North American multi-location retail organization operating across physical stores and online channels.

The organization manages multiple legal entities, payment providers, and bank accounts through Microsoft Dynamics 365 Business Central. Its finance team handles daily settlement and reconciliation activities across approximately 20 bank accounts.

CHALLENGE

The finance team faced a manual and fragmented bank reconciliation process.

Payment and settlement information from a major payment service provider was received through daily reports. The data was imported into an Azure SQL Managed Instance, while Business Central maintained the corresponding bank ledger entries.

The main challenges included:

• Bank statement data required manual preparation before Business Central reconciliation.
• Detailed payment transactions did not align directly with the lump-sum bank ledger entries.
• Finance teams performed reconciliation activities manually using Excel.
• Approximately 20 bank accounts followed similar reconciliation processes.
• Payment transactions originated from both physical stores and online sales.
• A payment reference number existed in the data but was not part of the standard Business Central reconciliation matching process.
• After reconciliation, the SQL database also needed to know which transactions had been successfully reconciled.
• The existing process created additional manual work and increased the risk of duplicate or missed reconciliation.

The organization needed a scalable approach that worked within its existing Business Central SaaS and Azure SQL environment.

COGNIC’S SOLUTION

Cognic designed an automated bank reconciliation workflow connecting Azure SQL Managed Instance with Microsoft Dynamics 365 Business Central.

The solution focuses on automating the movement, transformation, matching, and status synchronization of reconciliation data.

1. Automated SQL Data Retrieval

The solution retrieves eligible payment and settlement transactions from Azure SQL Managed Instance. The process identifies open transactions and prevents previously reconciled records from being imported again.

2. Data Transformation

Payment provider data is transformed into the structure required by Business Central Bank Reconciliation. The transformation layer handles relevant transaction information such as transaction date, settlement date, amount, payment method, payment reference, external document number, store or location information, and transaction identifiers.

3. Business Central Integration

A Business Central extension integrates the SQL-based data source with the existing bank reconciliation process. A configurable bank account setting identifies accounts where statement data should come from the SQL Managed Instance. This allows the finance team to work through the existing Business Central reconciliation interface rather than maintaining a separate reconciliation system.

4. Reconciliation Matching

The workflow supports matching between imported bank statement lines and Business Central bank ledger entries. The matching process uses available transaction information such as amount, transaction date, payment reference, external document number, payment method, and other available transaction identifiers. The approach also supports exception handling where transactions require manual review.

5. Reconciliation Status Synchronization

After reconciliation is completed in Business Central, the solution updates the corresponding SQL records. The SQL database stores the reconciliation status so future imports retrieve only outstanding transactions. This creates a two-way process: SQL Managed Instance → Business Central → Reconciliation → SQL Managed Instance.

6. Multi-Account and Multi-Entity Support

The solution follows a common configuration model across approximately 20 bank accounts and two Business Central companies. Rather than creating a separate solution for each location, the architecture supports configuration-based replication across bank accounts using the same core workflow.

BUSINESS BENEFITS

• Reduced manual bank reconciliation work.
• Less dependency on Excel-based reconciliation.
• Faster processing of daily settlement data.
• Better consistency across multiple bank accounts.
• Reduced risk of duplicate transaction processing.
• Improved visibility of reconciled and outstanding transactions.
• Better synchronization between Business Central and Azure SQL.
• A scalable foundation for additional payment providers.
• A standardized reconciliation process across multiple entities and locations.

TECHNOLOGY USED

• Microsoft Dynamics 365 Business Central SaaS
• Business Central Extensions
• Azure SQL Managed Instance
• SQL data transformation
• Payment settlement data integration
• Automated reconciliation workflows
• Configurable bank account processing

OUTCOME

Cognic’s approach transformed a manually driven reconciliation process into an integrated workflow between the organization’s existing Azure SQL and Business Central environments.

The architecture provides a reusable model for processing bank reconciliation across multiple accounts and entities while keeping the finance team’s existing Business Central workflow at the center of the process.