Aging Report Analysis

This document provides a comprehensive analysis of the PCC aging report functionality, including data quality assessment, business process mapping, and historical trend analysis.

Data Quality Assessment

Age Distribution Analysis

The aging report shows a significant concentration of balances in the 90+ days category (91% of total outstanding balances). To better understand this pattern, we performed a deeper analysis of the 90+ days category:

Age Bucket	Transaction Count	Percentage
91-180 days	168,751	9.51%
181-365 days	412,466	23.24%
1-2 years	693,401	39.06%
Over 2 years	500,548	28.20%

This analysis reveals that:

• Only 9.5% of transactions in the 90+ days category are between 91-180 days old
• 67.3% of transactions are over 1 year old
• 28.2% of transactions are over 2 years old

Transaction Types in 90+ Days Category

The distribution of transaction types within the 90+ days category provides insights into the nature of these aged balances:

Transaction Type	Description	Transaction Count	Total Amount	Percentage
R	Revenue	79,933	$240,298,698.19	115.14%
A	Adjustment	565,222	$51,791,411.03	24.82%
CR	Credit Revenue	61,282	$11,517,345.95	5.52%
CO	Copay	245,044	$0.06	0.00%
D	Debit	37,178	$0.00	0.00%
X	Payment	8,460	-$187,787.12	-0.09%
XR	Payment Revenue	125,364	-$1,129,905.72	-0.54%
C	Credit	16,615	-$45,316,257.75	-21.71%
CA	Credit Adjustment	636,068	-$48,274,040.02	-23.13%

Key observations:

• Revenue transactions (R) make up the largest portion of outstanding balances
• There are significant credit adjustments (CA) and credits (C) that offset some of the outstanding balances
• The percentage exceeds 100% because the calculation is based on the net balance (positive amounts minus negative amounts)

Year Distribution of Aged Transactions

The distribution of transactions by effective year provides insights into when these aged balances originated:

Effective Year	Transaction Count	Total Amount
2020	31,290	$4,038,892.18
2021	117,436	$14,882,896.15
2022	214,469	$21,923,410.86
2023	568,099	$63,168,713.46
2024	790,470	$92,791,865.09
2025	53,402	$11,893,686.88

Key observations:

• Most aged transactions are from recent years (2023-2024)
• There's a significant increase in transaction volume and amounts in 2023 and 2024
• The presence of 2025 transactions (future dated) suggests that some transactions are being entered with future effective dates

Potential Data Quality Issues

Based on our analysis, several potential data quality issues may be contributing to the high percentage of aged balances:

1. Unclosed Transactions: There may be a significant number of transactions that should have been closed or reconciled but remain open.

2. Offsetting Transactions: The presence of large positive and negative amounts suggests that there may be offsetting transactions that should be matched and cleared.

3. Future-Dated Transactions: The presence of transactions with 2025 effective dates indicates that some transactions are being entered with future dates.

4. Incomplete Write-offs: There may be uncollectible balances that have not been properly written off.

5. System Migration Issues: The pattern could indicate data migration issues if the system was migrated from another platform.

Financial Workflow Analysis

Transaction Types and Their Meanings

The PCC system uses several transaction types to track different financial activities:

Transaction Type	Description	Purpose
R	Revenue	Charges for services provided to clients
D	Debit	General debit transactions
A	Adjustment	Adjustments to client accounts (positive)
CA	Credit Adjustment	Negative adjustments to client accounts
C	Credit	General credit transactions
X	Payment	Payments received from payers
XR	Payment Revenue	Payments received that are tied to revenue
CO	Copay	Patient copayment amounts
CR	Credit Revenue	Credits related to revenue

Invoicing Process

The invoicing process in PCC works as follows:

1. Revenue Generation:

   • The system creates 'R' (Revenue) transactions when services are provided to clients
   • These transactions are linked to specific clients and payers
   • The effective_date field indicates when the service was provided

2. Invoice Creation:

   • Transactions are grouped into invoices (ar_invoice table)
   • Each invoice is associated with a specific client and payer
   • Invoices track the total_amount, balance_due, and total_payments

3. Invoice Transactions:

   • The ar_invoice_transaction table links specific transactions to invoices
   • This allows for detailed tracking of what services are included on each invoice

Payment Processing

PCC handles payments from different sources in distinct ways:

1. Insurance Payments:

   • Payments from insurance payers (Medicare, Medicaid, etc.) are recorded as 'X' or 'XR' transactions
   • These payments are linked to the original invoice and the specific payer
   • The system tracks which payer made the payment and applies it to the appropriate client account

2. Patient Liability/Private Pay:

   • Patient liability is tracked through specific payers (e.g., payer ID 108 in our sample database)
   • When insurance doesn't cover the full amount, a 'D' (Debit) transaction is created for the patient liability amount
   • This creates a balance that the patient is responsible for
   • When the patient makes a payment, it's recorded as an 'X' or 'XR' transaction against the patient liability payer

3. Copayments:

   • Copayments are tracked using 'CO' transactions
   • These are often transferred between payers (e.g., from Medicare to a secondary insurance or to patient liability)

Adjustment Processing

Adjustments are a critical part of the financial workflow:

1. Positive Adjustments:

   • Recorded as 'A' transactions
   • Used to increase the amount owed (e.g., late fees, additional charges)

2. Negative Adjustments:

   • Recorded as 'CA' transactions
   • Used to reduce the amount owed (e.g., contractual adjustments, write-offs)

3. Contractual Adjustments:

   • When insurance pays less than the billed amount due to contractual agreements
   • These appear as 'CA' transactions and reduce the balance due

Aging Calculation

The aging report calculates how long balances have been outstanding:

1. Age Buckets:

   • Current: Not yet due or due today
   • 1-30 Days: Overdue by 1-30 days
   • 31-60 Days: Overdue by 31-60 days
   • 61-90 Days: Overdue by 61-90 days
   • 90+ Days: Overdue by more than 90 days

2. Aging Calculation:

   • Based on the difference between the effective_date and the reference date
   • The effective_date represents when the service was provided or when the charge became effective
   • The reference date is typically the current date but can be set to any date for historical analysis

Complete Financial Workflow

Based on our analysis, here's the complete financial workflow in PCC:

1. Service Provision and Charge Creation:

   • Services are provided to clients
   • 'R' (Revenue) transactions are created with the appropriate amount
   • These are assigned to the primary payer (e.g., Medicare, Medicaid)

2. Insurance Billing:

   • Invoices are created and sent to the primary payer
   • The system tracks these invoices and their status

3. Primary Insurance Payment:

   • When the primary insurance pays, 'X' or 'XR' transactions are created
   • If the insurance pays less than billed, 'CA' (Credit Adjustment) transactions are created for contractual adjustments
   • If there's a remaining patient responsibility, 'D' (Debit) transactions are created for patient liability

4. Secondary Insurance Billing:

   • If there's a secondary insurance, the remaining balance may be billed to them
   • This creates additional transactions for the secondary payer

5. Patient Billing:

   • Any remaining balance becomes patient liability
   • This is tracked through specific patient liability payers in the system
   • Patients are billed for this amount

6. Payment Collection and Application:

   • Payments are received and recorded as 'X' or 'XR' transactions
   • These are applied to the appropriate invoices and reduce the balance due

7. Aging and Collections:

   • The system tracks how long balances have been outstanding
   • Collection activities are initiated based on aging buckets

Business Process Mapping

Collections Process

The PCC system includes a collections module that integrates with the aging report. Key components include:

Collections Tables

1. ar_collections_templates: Defines collection templates for different payer types

   • Templates exist for Private, Medicare A, Medicaid, Medicare B, Co-Insurance, Hospice, and Managed Care
   • Each template specifies collection parameters like threshold amounts and account generation types

2. ar_collection_call: Tracks collection calls made to clients/payers

   • Stores information about who made the call, when it was made, and the outcome

3. ar_collection_call_txs: Links collection calls to specific transactions

   • Tracks which transactions were discussed during collection calls
   • Includes flags for whether transactions were sent to collection agencies

4. ar_collections_agencies: Stores information about external collection agencies

Aging Views

The system includes several views that support the aging report functionality:

1. view_ar_aging: Primary view for the aging report

   • Combines data from ar_transactions, ar_batch, and ar_applied_payment_history
   • Handles special cases like applied payments and adjustments

2. view_ar_aging_rollup2: Aggregated view of aging data

   • Likely used for summary reports and dashboards

3. view_ods_ar_aging: ODS (Operational Data Store) version of the aging report

   • Suggests that aging data is replicated to a reporting database

Business Process Flow

Based on the database structure, the aging report appears to be used in the following business processes:

1. Regular Aging Analysis:

   • Running the aging report (via sproc_ar_aging) to identify outstanding balances
   • Categorizing balances by age buckets (Current, 1-30, 31-60, 61-90, 90+)
   • Analyzing by payer, client, and transaction type

2. Collections Process:

   • Using aging data to identify accounts for collection
   • Creating collection calls based on aging thresholds
   • Tracking collection activities against specific transactions
   • Potentially sending accounts to external collection agencies

3. Financial Reporting:

   • Using aging data for financial reporting and forecasting
   • Tracking trends in outstanding balances over time
   • Potentially calculating reserves for doubtful accounts

4. Performance Monitoring:

   • Tracking collection effectiveness by payer type
   • Monitoring aging trends to identify process improvements

Historical Trend Analysis

Transaction Volume Trends

Analysis of transaction counts by year and month shows a clear upward trend:

Year	Average Monthly Transactions	Total Transactions
2020 (partial)	7,577	30,306
2021	9,371	112,456
2022	15,925	191,095
2023	44,527	534,329
2024	67,945	815,338
2025 (partial)	38,633	193,165

Key observations:

• Transaction volume has increased significantly year over year
• 2024 shows the highest transaction volume, with an average of nearly 68,000 transactions per month
• The presence of 2025 transactions indicates future-dated entries

Balance Trends

Analysis of outstanding balances by year and month shows a similar upward trend:

Year	Average Monthly Balance	Total Balance
2020 (partial)	$1,011,210	$4,044,839
2021	$1,239,457	$14,873,483
2022	$1,812,726	$21,752,707
2023	$5,294,936	$63,539,233
2024	$7,776,890	$93,322,685
2025 (partial)	$6,326,824	$31,634,122

Key observations:

• Outstanding balances have increased significantly year over year
• The average monthly balance in 2024 is nearly 8 times higher than in 2020
• The rate of increase in balances is consistent with the increase in transaction volume

SQL Scripts for Aging Report Analysis

We've created several SQL scripts to help analyze the aging report data:

1. analyze_aging_data_quality.sql: Queries to analyze the quality of aging report data, including age distribution, transaction types, and potential data issues.

2. analyze_aging_business_processes.sql: Queries to analyze the business processes related to the aging report, including collections templates, collection call activity, and collection effectiveness.

3. analyze_aging_historical_trends.sql: Queries to analyze historical trends in the aging report data, including transaction volume, aging distribution, and payer/client trends.

Recommendations for Using the Aging Report

Based on our analysis, here are recommendations for effectively using the aging report and optimizing the financial workflow:

1. Regular Aging Analysis:

   • Run the aging report at least monthly using the sproc_ar_aging stored procedure
   • Use the view_ar_aging view for custom queries and analysis
   • Pay special attention to the 90+ days category, which contains the majority of outstanding balances

2. Collections Process Integration:

   • Use the collections templates (ar_collections_templates) to define collection strategies by payer type
   • Track collection activities using the ar_collection_call and ar_collection_call_txs tables
   • Monitor the effectiveness of collection efforts by tracking changes in aged balances

3. Data Quality Maintenance:

   • Regularly review transactions in the 90+ days category, especially those over 1 year old
   • Identify and resolve offsetting transactions that should be matched
   • Consider a write-off policy for uncollectible balances
   • Review and correct any future-dated transactions

4. Performance Monitoring:

   • Track aging trends over time to identify improvements or deterioration
   • Compare aging by payer type to identify problematic payers
   • Set targets for reducing the percentage of balances in the 90+ days category

5. Financial Reporting Integration:

   • Use aging data to calculate reserves for doubtful accounts
   • Include aging trends in financial reports to management
   • Consider the impact of aged balances on cash flow projections

6. Transaction Type Management:

   • Ensure proper use of transaction types (R, D, A, CA, C, X, XR, CO, CR) according to their intended purpose
   • Regularly audit transaction types to ensure they're being used consistently
   • Create clear documentation for staff on when to use each transaction type

7. Payment Processing Optimization:

   • Implement a systematic approach to applying payments to the oldest balances first
   • Ensure proper tracking of patient liability amounts through dedicated payers
   • Regularly reconcile payments against invoices to identify unapplied payments

8. Adjustment Workflow Improvement:

   • Develop clear policies for when to use adjustments (A and CA transactions)
   • Implement approval workflows for adjustments over certain thresholds
   • Regularly review adjustment patterns to identify potential process improvements

9. Invoice Management:

   • Ensure timely creation and submission of invoices to payers
   • Track invoice status and follow up on unpaid invoices
   • Analyze denial patterns to identify opportunities for improvement in the billing process

Conclusion

The aging report is a critical tool for financial management in the PCC system. By understanding its structure, data quality issues, and integration with business processes, users can more effectively manage accounts receivable and improve collection outcomes.

The high percentage of balances in the 90+ days category suggests that there may be opportunities for process improvements in collections and account reconciliation. Regular monitoring and maintenance of the aging data can help identify and address these issues.