Recreating an Accounts Receivable Database
The Situation
A physical therapy provider with over 170 locations in the Northeast was in financial forbearance with little visibility on their cash forecast. With only six weeks of cash left, it was in need of additional capital. Its lenders were willing to provide the Company with funds if it could provide a reliable view of its accounts receivables. Since the Company was built through a rollup strategy, its systems were not fully integrated and its existing accounts receivable database was unreliable. Accordion was engaged to rebuild the entire database from the original sources to enable the Company to provide its lenders with an accurate accounts receivable balance.
Services
Big Data Analytics
Project Management Oversight
100 Day Planning
The Execution
- Worked with the Company and its external vendors to gather data from the original sources, including receiving insurance remittances from the insurance claims processors.
- Used extract, transform, and load (ETL) tools to cleanse the data and extract additional data sets that were in non-standard formats.
- Created a relational database using the claims data set, insurance remittances, and additional lookup tables involving gross charges and expected fees based on treatment codes.
- Developed the algorithms that determined each payor class (i.e. commercial insurance, Medicare, workers comp, etc.), the expected fee for each patient, the amount to be transferred to the patient or secondary insurance, and the ultimate accounts receivable balance for the payor with the Company management and its revenue cycle management team.
- Translated the algorithms into DAX (Power BI) code so that the entire database could be rebuilt claim by claim.
- Built a collectability model, which took the rebuilt database and applied collectability rules to determine the actual amounts that could be collected by the Company.
The Results
With its business acumen combined with technical capability, Accordion was the only consulting firm that the Company engaged with that could provide the requested deliverables. The Company was able to provide its lenders with a more accurate view on its accounts receivable balance, and the lenders agreed to provide additional financing.