Introduction
Date of Service (DOS) to Date of Reconciliation is a critical metric for Revenue Cycle Management. The time it takes to process a claim can have a significant impact on cash flow. This vignette demonstrates how to calculate the average days in accounts receivable (AR) for a medical practice.
Definitions
Date of Service: The date a patient receives medical services.
Date of Release: The date a claim is released for billing.
Date of Submission: The date a claim is submitted to the payer.
Date of Acceptance: The date a claim is accepted by the payer.
Date of Adjudication: The date a claim is adjudicated by the payer.
Date of Reconciliation: The date a claim is reconciled by the medical practice.
The Lifecycle of a Claim
- Provider Lag: Date of Service - Date of Release
- Billing Lag: Date of Release - Date of Submission
- Acceptance Lag: Date of Submission - Date of Acceptance
- Payment Lag: Date of Acceptance - Date of Adjudication
- Days in AR: Date of Release - Date of Adjudication
# Balances, when zero, add net payment and adjustments
# Payment and adjustments should be a percentage of the balance, equal to one
(x <- mock_claims(15000))#> # A tibble: 15,000 × 10
#> claimid payer charges balance date_ser…¹ date_rel…² date_sub…³ date_acc…⁴
#> <chr> <fct> <dbl> <dbl> <date> <date> <date> <date>
#> 1 00064 UnitedHe… 277. 0 2024-07-07 2024-07-09 2024-07-12 2024-07-23
#> 2 00102 Oscar 55. 0 2024-07-07 2024-07-17 2024-07-17 2024-08-05
#> 3 00134 Medicaid 61. 61. 2024-07-07 2024-07-18 2024-07-24 2024-07-31
#> 4 00242 American 200. 0 2024-07-07 2024-07-09 2024-07-10 2024-07-16
#> 5 00337 Allianz 199. 0 2024-07-07 2024-07-20 2024-07-23 2024-07-29
#> 6 00395 Molina 124. 124. 2024-07-07 2024-07-17 2024-07-21 2024-07-27
#> 7 00449 Oscar 396. 0 2024-07-07 2024-08-04 2024-08-08 2024-08-19
#> 8 00484 Molina 182. 182. 2024-07-07 2024-07-12 2024-07-18 2024-08-03
#> 9 00487 GuideWell 75. 0 2024-07-07 2024-07-11 2024-07-15 2024-07-19
#> 10 00580 Athene 237. 237. 2024-07-07 2024-07-15 2024-07-20 2024-07-25
#> # ℹ 14,990 more rows
#> # ℹ abbreviated names: ¹date_service, ²date_release, ³date_submission,
#> # ⁴date_acceptance
#> # ℹ 2 more variables: date_adjudication <date>, date_reconciliation <date>
(x <- prep_claims(x))#> # A tibble: 15,000 × 13
#> claimid payer charges balance date_ser…¹ aging_bin dar days_rel…²
#> <chr> <fct> <dbl> <dbl> <date> <fct> <dbl> <dbl>
#> 1 00001 Univ. Healthca… 467. 0 2024-05-22 0-30 24 1
#> 2 00002 BCBS WY 263. 263. 2024-06-01 0-30 26 3
#> 3 00003 Humana 30. 30. 2024-06-14 0-30 22 1
#> 4 00004 Medicare 7.5 7.5 2024-06-10 0-30 23 2
#> 5 00005 American 83. 0 2024-07-04 31-60 36 3
#> 6 00006 Mass Mutual 186. 186. 2024-06-19 0-30 28 5
#> 7 00007 HCSC 177. 177. 2024-04-14 0-30 30 7
#> 8 00008 Mass Mutual 118. 118. 2024-04-25 31-60 34 6
#> 9 00009 Wellcare 55. 55. 2024-04-26 31-60 44 13
#> 10 00010 New York Life 132. 0 2024-05-22 31-60 36 7
#> # ℹ 14,990 more rows
#> # ℹ abbreviated names: ¹date_service, ²days_release
#> # ℹ 5 more variables: days_submission <dbl>, days_acceptance <dbl>,
#> # days_adjudication <dbl>, days_reconciliation <dbl>, dates <list>
summarise_claims(x) |>
glimpse()#> Rows: 1
#> Columns: 9
#> $ n_claims <int> 15000
#> $ gross_charges <dbl> 1996234
#> $ ending_ar <dbl> 1319105
#> $ mean_release <dbl> 8.007933
#> $ mean_submission <dbl> 2.992733
#> $ mean_acceptance <dbl> 7.5078
#> $ mean_adjudication <dbl> 15.01487
#> $ mean_reconciliation <dbl> 2.214623
#> $ mean_dar <dbl> 34.27453
x |>
group_by(
year = year(date_service),
month = month(date_service)
) |>
summarise_claims()#> # A tibble: 4 × 11
#> year month n_claims gross_charges ending_ar mean_rele…¹ mean_sub…² mean_acc…³
#> <int> <int> <int> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2024 4 3194 424745. 278117. 8.0 3.0 7.5
#> 2 2024 5 5351 721214. 476128. 8.0 3.0 7.6
#> 3 2024 6 5274 694532. 463579. 7.9 2.9 7.5
#> 4 2024 7 1181 155743. 101281. 8.0 3.0 7.4
#> # ℹ abbreviated names: ¹mean_release, ²mean_submission, ³mean_acceptance
#> # ℹ 3 more variables: mean_adjudication <dbl>, mean_reconciliation <dbl>,
#> # mean_dar <dbl>
x |>
group_by(
year = year(date_service),
quarter = quarter(date_service)
) |>
summarise_claims()#> # A tibble: 2 × 11
#> year quarter n_claims gross_char…¹ ending_ar mean_rel…² mean_sub…³ mean_acc…⁴
#> <int> <int> <int> <dbl> <dbl> <dbl> <dbl> <dbl>
#> 1 2024 2 13819 1840491. 1217824. 8.0 3.0 7.5
#> 2 2024 3 1181 155743. 101281. 8.0 3.0 7.4
#> # ℹ abbreviated names: ¹gross_charges, ²mean_release, ³mean_submission,
#> # ⁴mean_acceptance
#> # ℹ 3 more variables: mean_adjudication <dbl>, mean_reconciliation <dbl>,
#> # mean_dar <dbl>
x |>
group_by(
year = year(date_service),
month = month(date_service),
aging_bin
) |>
summarise_claims()#> # A tibble: 12 × 12
#> year month aging_bin n_claims gross_charges ending_ar mean_rele…¹ mean_sub…²
#> <int> <int> <fct> <int> <dbl> <dbl> <dbl> <dbl>
#> 1 2024 4 0-30 1106 145094. 97525. 4.4 2.1
#> 2 2024 4 31-60 2076 278433. 180082. 9.9 3.5
#> 3 2024 4 61-90 12 1218. 510. 20. 6.2
#> 4 2024 5 0-30 1834 245423. 164266. 4.5 2.1
#> 5 2024 5 31-60 3504 474441. 311295. 9.9 3.5
#> 6 2024 5 61-90 13 1350. 567. 18 5.1
#> 7 2024 6 0-30 1866 241399. 165359. 4.4 2.1
#> 8 2024 6 31-60 3387 450547. 296956. 9.8 3.4
#> 9 2024 6 61-90 21 2586. 1263. 19. 5.9
#> 10 2024 7 0-30 403 52596. 36031. 4.3 2.0
#> 11 2024 7 31-60 773 102179. 65251. 9.9 3.5
#> 12 2024 7 61-90 5 968. 0 19. 5
#> # ℹ abbreviated names: ¹mean_release, ²mean_submission
#> # ℹ 4 more variables: mean_acceptance <dbl>, mean_adjudication <dbl>,
#> # mean_reconciliation <dbl>, mean_dar <dbl>
x |>
group_by(
year = year(date_service),
quarter = quarter(date_service),
payer
) |>
summarise_claims()#> # A tibble: 54 × 12
#> year quarter payer n_claims gross_ch…¹ ending_ar mean_rel…² mean_sub…³
#> <int> <int> <fct> <int> <dbl> <dbl> <dbl> <dbl>
#> 1 2024 2 Univ. Heal… 528 73771. 48781. 8.1 3.0
#> 2 2024 2 BCBS WY 472 62095. 42728. 8.2 3.2
#> 3 2024 2 Humana 533 69274. 44326. 7.7 3.1
#> 4 2024 2 Medicare 489 67676. 43976. 7.7 2.8
#> 5 2024 2 American 512 66607. 42299. 8.2 3.0
#> 6 2024 2 Mass Mutual 528 70643. 45577. 8.0 3.1
#> 7 2024 2 HCSC 527 71427. 47349. 8.1 2.8
#> 8 2024 2 Wellcare 493 63505. 38206. 8.1 2.9
#> 9 2024 2 New York L… 512 69661. 44720. 7.6 2.8
#> 10 2024 2 Allianz 512 73092. 49016. 8.0 2.9
#> # ℹ 44 more rows
#> # ℹ abbreviated names: ¹gross_charges, ²mean_release, ³mean_submission
#> # ℹ 4 more variables: mean_acceptance <dbl>, mean_adjudication <dbl>,
#> # mean_reconciliation <dbl>, mean_dar <dbl>Average Days in AR
Monthly Calculation
tibble(
date = date_build(2024, 1:12),
gct = rpois(12, 250000:400000),
earb = rpois(12, 290000:400000)
) |>
avg_dar(
date,
gct,
earb,
dart = 35,
by = "month")#> # A tibble: 12 × 15
#> date gct earb ndip adc dart dar dar_pass ratio_id…¹ ratio_ac…²
#> <date> <int> <int> <int> <dbl> <dbl> <dbl> <lgl> <dbl> <dbl>
#> 1 2024-01-01 2.5e5 2.9e5 31 8066. 35 36. FALSE 1.1 1.2
#> 2 2024-02-01 2.5e5 2.9e5 29 8606. 35 34. TRUE 1.2 1.2
#> 3 2024-03-01 2.5e5 2.9e5 31 8050. 35 36. FALSE 1.1 1.2
#> 4 2024-04-01 2.5e5 2.9e5 30 8349. 35 35. TRUE 1.2 1.2
#> 5 2024-05-01 2.5e5 2.9e5 31 8054. 35 36. FALSE 1.1 1.2
#> 6 2024-06-01 2.5e5 2.9e5 30 8336. 35 35. TRUE 1.2 1.2
#> 7 2024-07-01 2.5e5 2.9e5 31 8061. 35 36. FALSE 1.1 1.2
#> 8 2024-08-01 2.5e5 2.9e5 31 8051. 35 36. FALSE 1.1 1.2
#> 9 2024-09-01 2.5e5 2.9e5 30 8353. 35 35. TRUE 1.2 1.2
#> 10 2024-10-01 2.5e5 2.9e5 31 8067. 35 36. FALSE 1.1 1.2
#> 11 2024-11-01 2.5e5 2.9e5 30 8303. 35 35. FALSE 1.2 1.2
#> 12 2024-12-01 2.5e5 2.9e5 31 8052. 35 36. FALSE 1.1 1.2
#> # ℹ abbreviated names: ¹ratio_ideal, ²ratio_actual
#> # ℹ 5 more variables: ratio_diff <dbl>, earb_target <dbl>, earb_diff <dbl>,
#> # gct_pct <dbl>, earb_pct <dbl>Quarterly Calculation
tibble(
date = date_build(2024, 1:12),
gct = rpois(12, 250000:400000),
earb = rpois(12, 285500:400000)
) |>
avg_dar(
date,
gct,
earb,
dart = 35,
by = "quarter")#> # A tibble: 4 × 15
#> date earb gct ndip adc dart dar dar_pass ratio_id…¹ ratio_ac…²
#> <date> <int> <int> <int> <dbl> <dbl> <dbl> <lgl> <dbl> <dbl>
#> 1 2024-03-01 285675 7.5e5 91 8244. 35 35. TRUE 0.38 0.38
#> 2 2024-06-01 285325 7.5e5 91 8233. 35 35. TRUE 0.38 0.38
#> 3 2024-09-01 285589 7.5e5 92 8167. 35 35. TRUE 0.38 0.38
#> 4 2024-12-01 285249 7.5e5 92 8156. 35 35. TRUE 0.38 0.38
#> # ℹ abbreviated names: ¹ratio_ideal, ²ratio_actual
#> # ℹ 5 more variables: ratio_diff <dbl>, earb_target <dbl>, earb_diff <dbl>,
#> # gct_pct <dbl>, earb_pct <dbl>