Overview
The client is a global real-estate servicing company with $10B in revenues. They operate in every US State and approximately 60 countries.
Business Challenges
After a series of acquisitions and a migration to a new ERP, there were material differences between the A/R ledger (listing of all invoices and corresponding balances) and the general ledger (the accounting system). The organization did not know whether items were simply missing from the accounting system or whether real receivables were missing on the A/R ledger, which prevented the organization from taking a direct hit to the P&L without research.
This organization was attempting to review nearly 14 million individual pieces of data to determine which items were creating the differences between the general ledger and A/R ledger. They had been attempting to do this using Microsoft Excel and through manual review of pdf documents – however, the quantity of data was overwhelming.
DLC Approach
Utilizing the R statistical software, the DLC team found commonalities in the debit and credit transactions and were able to link them to extracts from the original source systems.
During the initial phase of the project, DLC consultants created extracts of the data from the current accounting system and reviewed files from the legacy system.
During this time, the team wrote code in R to load and summarize all data. DLC consultants used these summaries to compare to summary files to ensure completeness of data.
Over the next two months, the team performed analysis and matching exercises of the data in order to identify transactions that had been cleared from the ledger, leaving about 100 transactions that were causing the reconciling issue. DLC consultants also discovered that while most of the issues were the result of acquisition and conversion activity, there were new reconciling items each month.
Over the next two weeks, the DLC team performed detailed testing of these reconciling items to identify the cause and to trace the final clearing transactions. Over the next month, the team developed, tested, and documented an excel-based reconciling method to quickly identify reconciling transactions.
The Results
This system allowed the client to identify new reconciling items on the first business day of the month in order to book accruals to ensure the financial statements are correct.
The DLC team found the discrepancy in the general ledger was within only about 100 transactions and that the A/R ledger appeared correct. Once these erroneous transactions had been identified, DLC consultants were able to identify the reporting periods, customers, and subsidiary companies the entries related to. With this information, the DLC team could then propose journal entries to leadership to clear. The team found that while a majority of the discrepancies related to the acquisition transactions, there were other items that were causing reconciling issues each month that would clear the subsequent month.
DLC consultants provided the client with a listing of all open and cleared transactions as of the project end date for import into the accounting system. Lastly, the team developed and documented a simpler reconciliation model in Excel that the client staff will use monthly to reconcile the account and research new discrepancies.
Before DLC
- Large and unusable quantity of data
- Manual review of AR
- Reconciliation issues
After DLC
- Less discrepancies in data
- Standardized processes for AR
- Reconciliation model that quickly identifies reconciling transactions
