A rapidly growing healthcare company (the Company) needed assistance reconciling over 500,000 transactions encompassing millions of data points. Cherry Bekaert’sFinancial Planning and Analysis (FP&A) advisors developed an automated solution using Microsoft’s Power Query.
Assessing the Situation
Rather than an enterprise resource planning (ERP) system, the Company was using SQL Server, a database management tool, to reconcile its commercial insurance receivables balance. As data was extracted from SQL Server and presented in various Excel files, the Company’s accounting team was dealing with multiple exports encompassing thousands of rows in each file that they would then need to combine, reconcile, and calculate journal entries.
Recognizing that this approach was no longer sustainable, the Company engaged Cherry Bekaert for assistance and to provide recommendations to improve accounting processes, as the business had significantly outgrown manual spreadsheet management.
Revenue Cycle Management (RCM) is a crucial process for businesses, especially in the healthcare sector, to manage the financial aspects of patient care from initial contact to final payment. Efficient RCM processes are essential for the financial sustainability of healthcare organizations, as they help ensure accurate billing, timely reimbursement, and optimal financial performance while maintaining regulatory compliance and patient satisfaction.
Discovery and Approach to Enhance Data Accuracy and Accounting Processes
During the discovery phase of this project, the Cherry Bekaert FP&A team reviewed existing processes to identify possible risks and areas of improvement. It quickly became evident that the Company needed a more effective method for tracking historical order data used in calculating journal entries. The main issues identified were:
- Over 500,000 transactions with no single source of truth
- Inconsistent processes around order documentation, billing, changes in inventory and claims management
- A manual general ledger (GL) account reconciliation process
- Use of cumbersome Excel files, with the majority of time spent on updates and accuracy checks rather than analysis and insights, making it challenging to review billing errors and changes at the order level
Based on these findings and leveraging our knowledge and experience in database construction and management, the Cherry Bekaert Risk & Accounting Advisory Services team took on a three-phase approach. Through this approach, the team developed solutions to mitigate risks and prevent discrepancies in future transaction reconciliations.
Phase One: Reconcile 500,000 historical order transactions to the GL, highlighting known variances
Phase Two: Advise on constructing an accounting data repository in SQL Server, built by the Company’s IT team, to replace manual and cumbersome processes in Excel
Phase Three: Create new accounting process documentation based on findings from the earlier phases
Automated Reconciliations in Microsoft’s Power Query
The reconciliation phase required cleansing and aligning millions of data points from multiple sources. Completing this task using Excel alone would have been impractical, as nested formulas and linking massive datasets would likely cause the files to crash. Instead, Cherry Bekaert streamlined the reconciliation process using Power Query.
Power Query, a feature native to Excel, allows data to be processed in more digestible pieces using its data language, “M” code. Once data is processed, it can easily be updated or reprocessed with the same steps. This capability enabled our team to handle large-scale, uniform data processing while efficiently incorporating additional data as needed. We quickly identified common variances, including:
- Transactions missing between datasets
- Duplicated transactions within datasets
- Variances in amounts between months
- Discrepancies between data and recorded revenue
- Transactions flagged for updates, but never changed
The team was also able to process data from subsequent months in reconciliation with significantly less time spent after the initial buildout. After identifying variances, our team collaborated with the client and determined which sources accurately tracked data points for each transaction. Using this knowledge, we developed a query in Power Query to build a data repository establishing a database that reconciled to the GL. As a result, Cherry Bekaert uncovered a $1M variance over the course of four years of data.
For future months, the newly created repository provided enough information to support reporting for both operations and accounting.
Solutions With the Future in Mind
The Company was so impressed with the reconciliation that it requested Cherry Bekaert’s assistance to ensure the successful build and launch of the internal financial operations repository and new reports. This involved partnering with the Company’s IT team, analyzing existing data structures and developing system requirements. The Company’s IT team used this information to create a living repository aggregation schedule to ensure data completeness and accuracy for audits.
By integrating industry knowledge, data analysis tools and prior experience, Cherry Bekaert was able to delve deep into the data and provide actionable solutions. These solutions delivered clean, repeatable results, ultimately saving the company the cost of a new ERP system.
Supporting Value Creation
Cherry Bekaert bridged the process and data gaps by implementing an automated revenue recognition process for the Company’s accounting team. This automation allowed the team to spend less time on data preparation and accuracy and more on value-creating activities, such as analysis and decision support. Our experience in data analytics, accounting automation and RCM quickly gained the trust of the Company’s leadership, paving the way for ongoing advisory and process support as they continue to grow rapidly.
