Customer Overview
InsuranceDekho, India’s leading insurance broker, connects millions of customers with insurers across the country. With partnerships spanning 35+ insurers, the company processes massive volumes of policy data and commissions every month.
To ensure accuracy and transparency in financial operations, InsuranceDekho needed a reliable solution to reconcile its internal MIS commission data with commission entries from insurer statements.
Problem Statement
At InsuranceDekho, reconciling commissions had become one of the Finance Operations team’s most daunting tasks. Every month, the team had to match data from its massive MIS files — often containing over 550,000 rows and 130 columns — with insurer statements from more than 35 different insurers.
And yet, the toughest challenge wasn’t just about handling data volume or complexity — it was about time. The Finance Ops team was buried under the weight of reconciliation work itself, leaving little bandwidth to focus on what really mattered: curing mismatches, resolving disputes with insurers, and improving overall financial processes. Instead of driving efficiency, reconciliation had become a bottleneck.
Reconciliation Requirements / Challenges
- Massive Data Volumes – MIS monthly file contained 550,000+ rows and 130 columns, far exceeding Excel’s capabilities.
- Multiple File Formats – Insurer statements arrived in varied formats: .xls, .xlsx, .pdf.
- Historical Backlog – Reconciliation required covering two years of MIS data against insurer statements.
- Renewal Handling – Every policy renewal needed separate reconciliation.
- Duplicate Policies Across Months – Policy numbers could appear multiple times across months due to renewals, adjustments, or endorsements.
- File replacements – Insurers sometimes replaced old files with corrected ones, requiring reverting earlier one and processing the new one.
- Cross-Referencing Needs – Ability to correlate insurer-specific data from a single MIS file with multiple insurer statements.
- Categorization of Records – Reconciled records needed to be bucketed into categories:
- Open -> No matching record found in Insurer Statements
- Foreign -> No matching record found in MIS data
- Settled -> Matching record found in MIS & Insurer Statements, and commission difference was within threshold of 5 rupees
- In Progress – Deficit -> Matching record found in MIS & Insurer Statements, and commission was booked higher in MIS
- In Progress – Surplus -> Matching record found in MIS & Insurer Statements, and commission was received higher in Insurer Statement
- Multi-column matching rules – In some insurers, reconciliation required matching on two columns:
- Try matching on Policy Number,
- If not found, try Old Policy Number,
- If no match found on both, log mismatch against Policy Number only.
- Fallback logic for reconciliation – For example:
- Match using POL_NUM_TEXT,
- If missing, fallback to ALTERNATE_POL_NUM.
- Policy number cleansing across insurers – Policy numbers often contained special characters in random places, leading zeros, or trailing junk characters. These had to be standardized before matching.
- Derived commission amounts – Commission amounts were not always in a single column. They needed to be calculated from multiple columns in both MIS and insurer statements before reconciliation.
Reporting Requirements
- Large-scale Reporting – Generate reports covering up to 2 years of reconciliation data (≈30 lakh records) in Excel format.
- Insurer Statement Status Reports – Reports showing reconciliation status of a specific insurer statement over time.
- Insurer-wise Reconciliation Reports – Combine MIS and insurer data to get a holistic insurer-level view based on insurer column in MIS.
- Status-based Reports – Ability to filter and generate reports like:
- Open records in last month,
- Settled records in last quarter, etc.
BoxPay Reconciliations Solution Setup
InsuranceDekho leveraged BoxPay’s Complex Reconciliations configuration to handle the wide variety of reconciliation challenges. This setup was carefully tailored to support insurer-specific rules, fallback logic, and high data volumes.
The solution enabled:
- Two-way reconciliation between a single MIS file and multiple insurer statements, ensuring accurate mapping across insurers.
- Support for heterogeneous file formats (.xls, .xlsx, .pdf) so reconciliations could work seamlessly without format conversion hassles.
- Intelligent handling of duplicates and renewals, ensuring each policy renewal, adjustment, or endorsement was reconciled separately without conflicts.
- Multi-step matching logic:
- Attempt matching on primary policy number column (e.g., Policy Number).
- If no match, check secondary column (e.g., Old Policy Number).
- If both fail, log mismatch against Policy Number for traceability.
- Configurable fallback parameters, e.g., match with POL_NUM_TEXT, but if missing, fallback to ALTERNATE_POL_NUM.
- Data cleansing rules for policy numbers, standardizing them by removing special characters, unnecessary trailing data, and leading zeros, ensuring insurer-to-MIS comparisons were reliable.
- Derived commission calculations, where commission values were computed from multiple columns in both MIS and insurer statements before reconciliation.
- Flexibility to revert reconciliations and re-upload corrected files whenever insurers discarded old data and provided updated versions.
- Custom categorization of reconciled records into statuses like Open, Settled, Foreign, In Progress – Deficit, and In Progress – Surplus.
Outcomes Achieved
- 90% reduction in manual reconciliation efforts
- Faster & Accurate Reconciliations – Automated reconciliation replaced manual, error-prone processes.
- Ops Team Efficiency – Teams now focus on curing mismatches with insurers instead of spending time reconciling raw data.
- On-Demand Reporting – Ops teams can generate reconciliation reports anytime, without waiting for manual collation.
- Data Flexibility – Teams can revert and re-upload files whenever insurers issue revised data, ensuring agility.
Business Impact
With BoxPay Reconciliations, InsuranceDekho transformed a highly manual, complex, and large-scale reconciliation challenge into a scalable, automated, and accurate process. This has boosted operational efficiency, improved collaboration with insurers, and enabled greater financial control.
A Platform Built for Every Industry
While this solution was tailored for InsuranceDekho, our Generic Reconciliation Platform is used across various industries, including payments and lending, insurance, and beyond.
With API-first design, white-label options, and configurable rule engines, we help enterprises of all sizes bring order to financial operations, without having to build a recon infrastructure from scratch.
Ready to Automate Your Reconciliation?
If you’re tired of juggling spreadsheets, chasing vendors, and running manual checks, it’s time to talk. Please feel free to signup or talk to your account manager if you are already using BoxPay – Getting Started with BoxPay: How to Register / Sign Up
Let BoxPay handle the complexity, so you can focus on what really matters.