The goal is to create a synthetic revenue cycle dataset that mimics real-world scenarios: a patient dimension, a contracted fee schedule and other adjustments by payers, plans, and CPT with multiple-procedure logic, and write-offs. Below are four sheets created from the built code:
10 providers with a performance tier each, 5 facilities, 7 carriers with their plan lists, 17 CPT codes with descriptions and weights, 15 modifiers, and 10 diagnoses. It's all mapped to where a CPT that appears on a claim line will always resolve to a description, a service line, a fee schedule row, and a set of legal modifiers.
Two things make the output look like a real panel. The provider tier travels with the provider into adjudication, so a claim’s denial odds depend on who rendered it: Gold clears 85% clean, Bronze 45%, and the CPT weights are deliberately unequal. Equal, all 17 codes come up 5.9% of the time, which sends as many chest X-rays through the door as office visits. Weighted, 99213 and 99214 take 43% of the lines.
# 1 - FOUNDATIONAL DATA
first_names = ["Alex","Jordan","Taylor","Morgan","Riley","Casey","Jamie","Avery",
"Parker","Quinn","Cameron","Reese","Skyler","Rowan","Drew","Sam",
"Noah","Mia","Liam","Emma"]
last_names = ["Nguyen","Garcia","Smith","Johnson","Brown","Davis","Miller","Wilson",
"Anderson","Martinez","Lee","Patel","Clark","Lewis","Young","Hall"]
# PROVIDER PERFORMANCE TIERS
provider_tiers = {
"Provider 01": "Gold",
"Provider 02": "Gold",
"Provider 03": "Silver",
"Provider 04": "Silver",
"Provider 05": "Bronze",
"Provider 06": "Silver",
"Provider 07": "Gold",
"Provider 08": "Bronze",
"Provider 09": "Silver",
"Provider 10": "Gold",
}
providers = list(provider_tiers.keys())
provider_creds = ["MD","DO","NP","PA","DPT"]
posted_by_users = ["A. Lee","J. Patel","C. Nguyen","M. Garcia","R. Smith"]
facilities = ["Clinic A","Clinic B","Hospital OP","Telehealth","ASC 1"]
# Insurance: Listing Carriers
carriers = [
("Aetna", ["Aetna Choice POS II","Aetna HMO","Aetna PPO"]),
("UHC", ["UHC Choice Plus","UHC Navigate","Surest","UMR"]),
("Cigna", ["Cigna OAP","Cigna PPO","Cigna HMO"]),
("Anthem/BCBS", ["Anthem PPO","BCBS HMO","Blue Cross PPO"]),
("Medicare", ["Medicare Part B"]),
("Medicaid", ["State Medicaid"]),
("Sedgwick", ["Sedgwick WC"]),
]
dx_pool = [
("M54.50", "Low back pain, unspecified"),
("M54.2", "Cervicalgia"),
("M25.511","Pain in right shoulder"),
("M25.512","Pain in left shoulder"),
("M54.16", "Radiculopathy, lumbar region"),
("R07.9", "Chest pain, unspecified"),
("F41.1", "Generalized anxiety disorder"),
("F32.A", "Depression, unspecified"),
("E11.9", "Type 2 diabetes mellitus without complications"),
("I10", "Essential (primary) hypertension"),
]
dx_desc_lookup = {c:d for c,d in dx_pool}
cpt_desc = {
"99213": "Office/outpatient visit, established patient, low/moderate MDM",
"99214": "Office/outpatient visit, established patient, moderate/high MDM",
"99203": "Office/outpatient visit, new patient, low/moderate MDM",
"99204": "Office/outpatient visit, new patient, moderate/high MDM",
"93000": "Electrocardiogram, routine ECG with interpretation and report",
"80053": "Comprehensive metabolic panel",
"85025": "Complete blood count (CBC) with automated differential",
"36415": "Collection of venous blood by venipuncture",
"71046": "Chest X-ray, 2 views",
"83036": "Hemoglobin A1c",
"90791": "Psychiatric diagnostic evaluation",
"90834": "Psychotherapy, 45 minutes with patient",
"G2211": "Visit complexity add-on (primary care / longitudinal care)",
"J3030": "Injection drug code (dataset placeholder)",
"J1100": "Injection, dexamethasone sodium phosphate",
"J1885": "Injection, ketorolac tromethamine",
"96372": "Therapeutic/prophylactic/diagnostic injection; SC/IM",
}
cpt_pool = list(cpt_desc.keys())
# REALISTIC CPT DISTRIBUTION WEIGHTS
cpt_weights = {
"99213": 0.25, # Very common
"99214": 0.20, # Common
"99203": 0.12, # Moderate
"99204": 0.08, # Moderate
"93000": 0.02, # Rare
"80053": 0.03, # Uncommon
"85025": 0.02, # Uncommon
"36415": 0.01, # Rare
"71046": 0.01, # Rare
"83036": 0.03, # Uncommon
"90791": 0.04, # Moderate
"90834": 0.08, # Common
"G2211": 0.05, # Moderate
"J3030": 0.02, # Uncommon
"J1100": 0.02, # Uncommon
"J1885": 0.02, # Uncommon
"96372": 0.02, # Uncommon
}
modifier_desc = {
"25": "Significant, separately identifiable E/M service on same day as procedure",
"26": "Professional component",
"50": "Bilateral procedure",
"51": "Multiple procedures",
"52": "Reduced services",
"53": "Discontinued procedure",
"59": "Distinct procedural service",
"76": "Repeat procedure by same physician/qualified health professional",
"77": "Repeat procedure by another physician/qualified health professional",
"95": "Synchronous telemedicine service via real-time audio/video",
"RT": "Right side",
"LT": "Left side",
"GP": "Services delivered under outpatient PT plan of care",
"GT": "Telehealth via interactive audio/video (legacy payer usage)",
"TC": "Technical component",
}
# INITIAL INJECTION RULE SET UP
J_CODES = {"J3030", "J1100", "J1885"}
INJ_ADMIN = "96372"This is the layer that decides 99203 is a new medical visit while 99213 is a follow-up, that 90791 is a behavioral intake while 90834 is behavioral follow-up, and that G2211 is the add-on marking an integrated visit. This is where the new-patient ratio on the report comes from.
def get_encounter_type_service_line_patient_status(cpt: str):
"""
Map CPT code to:
1. encounter_type: 10 possible types
2. service_line: Medical, Behavioral, or Integrated
3. patient_status: New Patient or Returning Patient
Returns: (encounter_type, service_line, patient_status)
"""
# NEW PATIENT MEDICAL VISITS
if cpt in {"99203", "99204"}:
return ("New Medical", "Medical", "New Patient")
# ESTABLISHED PATIENT MEDICAL VISITS (Follow-up)
if cpt in {"99213", "99214"}:
return ("Follow-up Medical", "Medical", "Returning Patient")
# NEW BEHAVIORAL - Psychiatric Evaluation
if cpt == "90791":
return ("New Behavioral", "Behavioral", "New Patient")
# FOLLOW-UP BEHAVIORAL - Psychotherapy
if cpt == "90834":
return ("Follow-up Behavioral", "Behavioral", "Returning Patient")
# INTEGRATED CARE - Complexity Add-on
if cpt == "G2211":
return ("Integrated Visit", "Integrated", "Returning Patient")
# DIAGNOSTIC - Imaging and ECG
if cpt in {"93000", "71046"}:
return ("Diagnostic", "Medical", "Returning Patient")
# LABORATORY TESTS
if cpt in {"80053", "85025", "83036"}:
return ("Labs", "Medical", "Returning Patient")
# ANCILLARY SERVICES - Phlebotomy/Collection
if cpt == "36415":
return ("Ancillary", "Medical", "Returning Patient")
# INJECTION - Drugs and Administration
if cpt in {"J3030", "J1100", "J1885", "96372"}:
return ("Injection", "Medical", "Returning Patient")
# FALLBACK (should not occur with defined CPT pool)
return ("Ancillary", "Medical", "Returning Patient")The dictionary carries 71 CARC entries keyed on the group and code as a pair, not on the number alone, because the group changes what the code means. Keying on the number alone collapses that distinction and misroutes the balance.
carc_desc = {
("CO","109"): "Claim not covered by this payer/contractor; send to correct payer/contractor.",
("CO","129"): "Payment denied - Prior processing information appears incorrect.",
("CO","131"): "Claim specific negotiated discount.",
("CO","150"): "Info submitted does not support this level of service.",
("CO","16") : "Claim/service lacks info needed for adjudication. Remark Code required when appropriate.",
("CO","184"): "Prescribing/ordering provider not eligible to prescribe/order service billed.",
("CO","193"): "Original payment decision is being maintained; processed properly the first time.",
("CO","197"): "Payment denied/reduced for absence of precertification/authorization.",
("CO","210"): "Pre-cert/authorization not received in a timely fashion.",
("CO","222"): "Exceeds contracted maximum hours/days/units by provider for this period.",
("CO","226"): "Information requested from Billing/Rendering Provider not provided or insufficient/incomplete.",
("CO","231"): "Mutually exclusive procedures cannot be done in the same day/setting.",
("CO","234"): "This procedure is not paid separately. At least one Remark Code must be provided.",
("CO","236"): "Procedure/procedure-modifier combo not compatible with another per NCCI.",
("CO","250"): "Incorrect attachment/document received; expected attachment/document still missing.",
("CO","251"): "Attachment/documentation received did not contain required content to process service.",
("CO","252"): "Attachment/documentation is required to adjudicate this service. Remark Code required.",
("CO","253"): "Reduction in Federal Spending Due to Sequestration.",
("CO","26") : "Expenses incurred prior to coverage.",
("CO","272"): "Coverage/program guidelines were not met.",
("CO","288"): "Referral absent.",
("CO","29") : "Time limit for filing has expired.",
("CO","299"): "Billing provider not eligible to receive payment for service billed.",
("CO","4") : "Procedure code inconsistent with modifier used or required modifier missing.",
("CO","45") : "Charges exceed your contracted fee arrangement.",
("CO","5") : "Procedure code/bill type inconsistent with place of service.",
("CO","55") : "Denied: experimental/investigational by payer.",
("CO","56") : "Denied: procedure/treatment not deemed proven effective by payer.",
("CO","59") : "Charges are adjusted based on multiple surgery rules or concurrent anesthesia rules.",
("CO","97") : "Benefit included in payment/allowance for another service/procedure already adjudicated.",
("CO","B9") : "Services not covered because patient is enrolled in a Hospice.",
("CO","B13"): "Previously paid. Payment may have been provided in a previous payment.",
("CO","B15"): "Payment adjusted: qualifying service/procedure not received/adjudicated.",
("CO","P12"): "Workers' compensation jurisdictional fee schedule adjustment.",
("OA","18") : "Duplicate claim/service.",
("OA","23") : "Payment adjusted due to impact of prior payer(s) adjudication (COB).",
("OA","94") : "Processed in Excess of charges.",
("OA","A1") : "Denied. At least one Remark Code must be provided.",
("PI","15") : "Authorization number missing/invalid/does not apply.",
("PI","16") : "Claim/service lacks information needed for adjudication. Remark Code required when appropriate.",
("PI","27") : "Expenses incurred after coverage terminated.",
("PI","167"): "Diagnosis(es) not covered.",
("PI","204"): "Service/equipment/drug not covered under patient's current benefit plan.",
("PI","59") : "Charges adjusted based on multiple surgery rules/concurrent anesthesia rules.",
("PI","P12"): "Workers' compensation jurisdictional fee schedule adjustment.",
("PR","119"): "Benefit maximum for this time period or occurrence has been reached.",
("PR","151"): "Payer deems the info submitted does not support this many services.",
("PR","187"): "Health Savings Account payments.",
("PR","200"): "Expenses incurred during lapse in coverage.",
("PR","204"): "Service/equipment/drug not covered under patient's current benefit plan.",
("PR","243"): "Service not authorized by network/primary care provider.",
("PR","222"): "Exceeds contracted maximum hours/days/units by provider for this period.",
("PR","227"): "Info requested from patient/insured/responsible party not provided or insufficient/incomplete.",
("PR","242"): "Services not provided by network/primary care providers.",
("PR","272"): "Coverage/program guidelines were not met.",
("PR","275"): "Prior payer(s) patient responsibility not covered.",
("PR","40") : "Charges do not meet qualifications for emergent/urgent care.",
("PR","50") : "Non-covered: not deemed a medical necessity by payer.",
("PR","55") : "Denied: experimental/investigational by payer.",
("PR","96") : "Non-covered charge(s).",
("PR","A1") : "Denied. At least one Remark Code must be provided.",
}Remark codes attach only where the CARC has one mapped.
rarc_desc = {
"M15": "Services/tests bundled as components of the same procedure.",
"M62": "Missing/incomplete/invalid authorization number.",
"N115": "Decision based on a Local Coverage Determination (LCD).",
"M127": "Missing medical record/documentation for this service.",
"N130": "Consult plan benefit documents/guidelines for restrictions for this service.",
"N386": "Decision based on a National Coverage Determination (NCD).",
"N211": "You may resubmit with appropriate documentation.",
"N519": "Invalid combination of HCPCS modifiers.",
}
carc_to_rarc = {
("CO","29") : ["N130"],
("CO","4") : ["N519"],
("CO","197"): ["M62"],
("CO","97") : ["M15"],
("PI","16") : ["M127","N211"],
("PR","50") : ["N115","N386"],
("CO","56") : ["N115","N386"],
("CO","55") : ["N115","N386"],
("PR","A1") : ["N211"],
("OA","A1") : ["N211"],
}A write-off reason for every write-off.
WRITE_OFF_REASONS = [
"Small Balance",
"No Authorization",
"Timely Filing Limit",
"Benefit Maxed",
"Not Billed Out",
"Medical Necessity",
]This layer standardizes money, fabricates policy and coverage context, and populates the line-level clinical and administrative fields, diagnoses, modifiers, units, and remittance codes, in a consistent, spreadsheet-friendly format.
None, removes duplicates, and sorts modifiersBilled, contracted, and allowed are three different numbers on the same line. Billed is what the practice charges. Contracted is that charge times the payer’s factor, 0.78 for Aetna down to 0.60 for Medicaid. Allowed is contracted after the multiple-procedure rule, which pays the second and later procedures at 50% for most payers, 75% for Medicaid, and full rate for workers’ compensation.
payer_contract_factor = {
"Aetna": 0.78, "UHC": 0.75, "Cigna": 0.73, "Anthem/BCBS": 0.76,
"Medicare": 0.68, "Medicaid": 0.60, "Sedgwick": 0.85,
}
mprr_eligible_cpts = {"93000","71046","96372","J3030","J1100","J1885"}
payer_mprr_multiplier = {
"Aetna": 0.50, "UHC": 0.50, "Cigna": 0.50, "Anthem/BCBS": 0.50,
"Medicare": 0.50, "Medicaid": 0.75, "Sedgwick": 1.00,
}
contracted = money(billed * payer_contract_factor[payer] * plan_factor(plan))
contracted_mprr = money(contracted * mult)The schedule is built once, before any claim exists: 16 plans crossed with 17 CPT codes, 272 rows, held in a lookup. A claim line reads its row. It never prices itself.
CO-45 is the gap between what a practice charges and what the contract allows. CO-59 is the multiple-procedure reduction, which is arithmetic off the fee schedule. Both post before the scenario branch runs, so whatever the payer decides next comes off the allowable, not off the charge.
contractual = max(billed_total - contracted_base_total, 0)
add_adjustment(adjs, "CO", "45", contractual)
# MPRR CO-59
if mprr_reduction_amt > 0:
add_adjustment(adjs, "CO", "59", mprr_reduction_amt)
# Scenario selection with provider tier weights
scenario_weights_dict = get_scenario_weights_for_provider(provider_tier)
scenario = random.choices(scenarios, weights=weights, k=1)[0]Payment rate varies by payer, which is why the payer mix chart separates the way it does. Government plans pay materially less on the same allowable.
rates = {
"Medicare": (0.60, 0.70), # government pays less
"Medicaid": (0.45, 0.55),
"Aetna": (0.82, 0.92), # commercial pays more
"Anthem/BCBS": (0.82, 0.92),
"Sedgwick": (0.75, 0.85), # workers' comp
}Scenario weights come from the provider tier declared back in the framework. The first version of this generator gave every provider the same denial profile, so every provider denied at the same rate and there was no variation to find. Gold clears 85% clean; Bronze clears 45% and carries the authorization and documentation problems.
if provider_tier == "Gold":
return {"clean": 0.85, "no_auth": 0.01, "missing_info": 0.015,
"bundled": 0.01, "timely_filing": 0.005, ...}
else: # Bronze
return {"clean": 0.45, "no_auth": 0.04, "missing_info": 0.08,
"bundled": 0.06, "timely_filing": 0.04, ...}Every denial view on the dashboard drops CARC 45, 59, and 253 for the reason above. Left in, they swamp the count and the denial rate stops meaning anything. Taken out, 38,030 workable denials remain across 157,750 charge lines, a 24.1% rate, and the top codes are the ones a biller actually touches: 16 missing information, 50 medical necessity, 97 bundled, 96 non-covered, and 119 benefit maximum.
Power Query splits the raw ledger two ways: 5 dimension tables, one each for patient, provider, facility, payer, and encounter type, and 3 fact tables that carry the numbers with ID columns pointing back to those five. Names and MRNs stay in the patient dimension, so the facts hold keys instead of identity.
The Excel report sits on a Power Pivot model with the five dimensions loaded and linked, and a new fact table joins without rework. Because the join is built once, the model answers questions never built into it. Its cells pull a named measure instead of adding up a range of cells, and the formula builds that name from the column header above it. One formula fills twelve months across eleven measures.
=GETPIVOTDATA("[Measures].["&B$36&"]",$A$21,
"[Fact_FinSum_ClaimLvl].[DOS (Month)]",
"[Fact_FinSum_ClaimLvl].[DOS (Month)].&["&$A37&"]")