Accounts Receivable Performance Analysis
Introduction
What is Revenue Cycle Management (RCM)? Healthcare facilities provide care to the community and must have a process in place to run a financial healthy organization. Revenue cycle management is essential for healthcare organizations to maintain an overall good financial health. This process involves managing administrative and clinical tasks which includes the following: claims processing, payment collection, denial management, and revenue generation. This process helps identify, manage, collect patient service revenue, and streamline the process. The following flowchart shows what a typical lifecycle looks like in revenue cycle management
Project Background
My task for this project was to review Superlative Dermatology clinic's account receivable data and provide insight to the management team. A/R receivables is part of the RCM lifecycle, which refers to the outstanding reimbursement owed to the provider for treatment. This financial responsibility can come from the patient or the insurance company. Maintaining a proper A/R management is crucial for the provider to run their business with a healthy profit margin, otherwise longer an accounts receivable goes unpaid, it is more unlikely the provider will be able to collect their payment. My analysis will be used to determine the financial health of this provider in terms of their A/R management.
Data
This dataset has been provided to the students of the Revenue Cycle Management Analyst Accelerator curriculum by Parable Academy.
Company Background
Superlative Dermatology is a leading skincare company known for its innovative solutions and exceptional results. With a team of experts, they offer advanced treatments and products to address diverse skin concerns, setting new standards in the field. Established in 2005, Superlative Dermatology is a trusted name in the industry, dedicated to revolutionizing skincare practices.
Business Questions
The analysis of the data was used to answer the following questions:
​
1) What is the aging distribution?
2) What is the payer distribution?
3) What is the A/R over 90 days?
4) What is the DSO?
5) Are there problem payers? What are they and what can you tell me about this?
6) Are there any other dimensions that point to potential collections, billing, or other process problems? What are they and can you show me?
​
Data Quality
My approach to data quality was to use Power Query to ensure the accuracy of the data and correct any data problems (data types, duplicate data, etc.)
Then I used the data dictionary that was provided to have a better understanding of each column. Finally, I created additional fields such as age category & dollar category to group the customer.
​
Analysis
First, I created the metrics that I will be using to track for this report. The follow calculations were used to track the metrics for this report:
​
The following explains how each metric is being tracked:
Total AR - Represents the total accounts receivable money. The total A/R that can be collected is $513,625.
Total AR over 90 days - Represents the amount of money that has not been collected after 90 days from the transaction date. The total AR that has not been collected during the first 90 days is $109,131.
AR % over 90 days - Represents the total percentage of accounts receivable dollar that has not been collected after 90 days from the transaction date. 21% is the percentage of total AR that has not been collected in the first 90 days.
DSO (Days Sales Outstanding) - The average time it takes the clinic to collect their money for a service that was provided. On average, it takes 33 days for Superlative Dermatology to collect their reimbursement from payers.
The following visualizations were created to track the aging distribution, payer distribution, and accounts receivable over 90 days.
The aging distribution is used to know when the accounts receivable money was collected. During the first 30 days, it shows $275,000 was collected.
The payer distribution shows the breakdown where the accounts receivable money is coming from. This also shows the breakdown of accounts receivable collected under 90 days and under 90 days. Commercial/Managed Care and Self-Pay shows to have the most accounts receivables that can be collected over 90 days.
Insight
The clinic is in overall good shape. Here are my insights where the clinic is excelling and where improvements can be made:
The aging distribution of accounts receivables. 74% of the aging revenues fall under 60 days, or less and 79% of aging revenue fall under 90 days or less. Almost 80% of accounts receivable is collected under 90 days which is a great collection rate and on average it takes the clinic 33 days to collect their reimbursement.
​
​
The following payer distribution shows 65% of the aging revenues fall under the commercial/managed care & self pay category where AR is over 60+ days. This can be an area where the clinic can improve by improving their collection rate from Managed Care & Self-pay.
Recommendation
-
27% of accounts receivable are 60+ days. Need to improve the collection process to get under the 20% bench mark that is set by the company. Commercial managed care and self-pay make up 65% of payers of account receivable dollars that is collected beyond 60+ days.
-
Almost 32.65% of accounts receivable are not assigned to the business office or clinic to follow up. This can be improved by making sure all accounts receivables are assigned to the business office.