Solving the problem of Manual Reconciliation using eventing based system
Being a rapidly growing, fast-paced start-up, Halodoc is continuously evolving & handling a lot of financial transactions every single day. This increasing scale in the number of transactions brings two new problems :
- Reconciliation plays a key role in ensuring the financial integrity of the company through proven accounting practices.
- Reconciliation becomes all the more important since compounded minor errors will result in discrepancies in large sums of money.
- Building a system that proactively looks for discrepancies to improve overall customer experience becomes necessary.
The Current Reconciliation Workflow
Currently, Reconciliation happens every week, and Finance Team will do the final reconciliation at the end of the month before Monthly Closing (Day 1-7 of the Month). In a weekly reconciliation, transaction data will be grouped based on the Weekly Transaction Period: Monday to Sunday.
1. Generating Raw Data Reports
The finance Team will pull all data that has different attributes that are needed to perform reconciliation, also amount on each report should be compared to achieve a Balance between the Payment Amount (money paid by user) and Adjustment (money paid by other sources such as Sponsored, Coupon, Insurance, etc);
2. Find Transaction Amount Discrepancies in Reconciliation Table
The data from different data sources above then will be merged into one spreadsheet resulting in Reconciliation Raw Data Table, for each item finance team will be looking into 3 things (listed below) to find Transaction Amount Discrepancies by doing the calculation.
- Order Attributes and payments sources from generated raw data reports
- Voucher Details
- Insurance details
3. Finding the Root Cause of Amount Discrepancies/Balance Sheet Error
The finance team will look at the Order Attributes and payment sources from generated Raw Data reports, voucher details, insurance details. After checking all these reports finance team will find the root cause of discrepancies.
- Inefficient Manual Process - To find Amount Discrepancies and Balance Sheet errors, the Finance team has to create a Reconciliation Table in the spreadsheet and look into 3 different reports just to find the root causes of the amount discrepancy.
- Human error - Due to human errors preparing a balance sheet for all transactions could lead to making the migration process even longer.
- Compare previous transaction data - With this current process, it's really hard to compare/calculate various details such as total money in, money out across different date range.
- Aggregation of transactional data based on different filters - The finance team wants to know various details such as "Amount Paid by User", "Refund Amount", "Bank In Amount" etc with a different filter such as payment type, doctor type, etc calculating all these are not easy in current recon system.
The motivation behind building a new recon-system
1. Simplified Workflow
- A new reconciliation system is expected to automatically detect the discrepancies
- Correcting the amount discrepancies should be easy during the migration process
- The cause of the discrepancies amount and the original amount should be stored in the persistence store.
- The reports for reconciliation details should be used by Finance Team during the closing process across divisions.
- The finance team can pull reports based on applying different filters across the different date range.
- The migration report should be used as it is during the migration process.
Building a new reconciliation system using an event-based approach
With this event-based system, we are generating events from the different applications when the key events occur such as
- whenever any order gets completed/closed we are generating order_complete/order_closed event,
- in case of payment settlement we are generating payment_success/refund_success in payment application.
Based on each event we have a different event processor which populates the data in the reconciliation table. So now it's a "single source of truth" of all the details for all transactions.
To present a single view of all transaction we came up with the following table :
1. Reconciliation - Store the details of the order amount, the amount paid by the user, refund amount, voucher details, insurance and etc.
2. Line items - Here we store details related to order metadata.
3. Balance-adjustment - All the balance correction information stored here
High Level architecture
Here we choose Kafka as messaging broker from which we process each event. There are some systems in Halodoc which are sending events through Kafka and SNS. For those events which send through SNS, we have added Lambda as a subscriber and from this lambda, we are sending these events to Kafka brokers. Once these events got reached in Kafka corresponding event handler will process each event and order reconciliation information stored in RDS.
Features provided by the new recon system
- A single place for all financial data: With the new system all the financial transaction data such as bank charges, voucher detail, insurance details, payment information from the different sources are now present in one place. Now finance team does not have to check for multiple reports.
- Generation of the report becomes easy: With the new system financial team can apply different filters based on custom date range, payment method, payment status, balance status, etc, and pull the reports.
- Finding discrepancies becomes easier: As all data are present in one place, finding discrepancies becomes easier. Discrepancies such as double payments, partial refunds become easier for the finance team to find out the cause, and doing reconciliation becomes easier. This way source system also get corrected due to these discrepancies
- Finding discrepancies and correcting it in the source system: Discrepancies due to double payment and partial payment can be easily found out as a resolution refund initiated to the customer.
- Correcting source systems on the basis of patterns seen: Patterns of discrepancies can be identified and the source systems updated to make them self correcting in nature thus eliminating the financial discrepancy at source itself.
- Resolving discrepancies: Finance team now fetches the data from different time range by applying a filter of unbalanced records. With each record, they can make the correction and mark them as balance with three root causes
- Mark Discrepant Amount as Coupon Expense
- Mark Discrepant Amount as Other Variant Expense
- Mark Discrepant Amount as Refund
These are the first steps towards automating financial reconciliation. With this manual reconciliation will go away and this saved a lot of human work for the finance team. We have done this for one of the service type implementation plans is to automate all financial reconciliation.
We are always looking out for top engineering talent across all roles for our tech team. If challenging problems like this, that drive big impact, enthrall you, do reach out to us at firstname.lastname@example.org
Halodoc is the number 1 all-around Healthcare application in Indonesia. Our mission is to simplify and bring quality healthcare across Indonesia, from Sabang to Merauke. We connect 20,000+ doctors with patients in need through our Tele-consultation service. We partner with 3500+ pharmacies in 100+ cities to bring medicine to your doorstep. We've also partnered with Indonesia's largest lab provider to provide lab home services, and to top it off we have recently launched a premium appointment service that partners with 500+ hospitals that allow patients to book a doctor appointment inside our application. We are extremely fortunate to be trusted by our investors, such as the Bill & Melinda Gates Foundation, Singtel, UOB Ventures, Allianz, GoJek, and many more. We recently closed our Series B round and In total have raised USD 100million for our mission. Our team works tirelessly to make sure that we create the best healthcare solution personalized for all of our patient's needs, and are continuously on a path to simplify healthcare for Indonesia.