Skip to main content

How to Use MCPAR Public Use Files

Managed Care Program Annual Reports (MCPARs) Public Use Files (PUFs) are available on data.medicaid.gov and can be accessed and analyzed in several ways:

  • View on data.medicaid.gov: Filter data to a specific program submission and/or Question ID to review individual data elements.
  • Download data: Use the data.medicaid.gov API or download data in .CSV format for use in statistical tools or spreadsheet applications such as Microsoft Excel or Google Sheets.

Working with MCPAR Data

The cases below provide step-by-step examples of analyses using MCPAR PUF data.

Use Case One: Overpayments

Question: What is the average overpayment amount as a percentage of premium revenue?

Steps:

  1. Filter the dataset to the MCPAR Question IDs of interest: plan_overpaymentReportingToStateDollarAmount and plan_overpaymentReportingToStateCorrespondingYearPremiumRevenue
  2. Create a pivot table to display the value of Response, including columns based on Question_ID and unique rows based on State, Program, Reporting_Period_End_Date, and Plan_or_BSS.
  3. Create a new column called “Overpayment Percentage” which calculates the value of plan_overpaymentReportingToStateDollarAmount divided by plan_overpaymentReportingToStateCorrespondingYearPremiumRevenue.
  4. Calculate the average of your new Overpayment Percentage column. This is the average overpayment percentage.

Use Case Two: Federal Database Checks

Question: Which programs reported that the state found a person or entity excluded in federal database checks? What were the entity types of each of these programs?

Steps:

  1. Filter the dataset to the MCPAR Question IDs of interest: state_excludedEntityIdentifiedInFederalDatabaseCheck and program_type
  2. Create a pivot table to display the count of Question_ID, with columns based on Response and unique rows based on State, Program, and Reporting_Period_End_Date.
  3. With the pivot table in tabular form (repeating all item labels), copy values to another sheet and filter to programs that provided a "Yes" response. The other columns will indicate the program type.

Use Case Three: Sanctions

Question: How many instances of noncompliance resulted in a civil monetary penalty?

Steps:

  1. Filter the dataset to the MCPAR Question IDs of interest: sanction_interventionType and sanction_noncomplianceInstances
  2. Use a formula to create a Numeric_Response column: set Numeric_Response = Response if Question_ID = sanction_noncomplianceInstances; if Question_ID = sanction_interventionType, set Numeric_Response = 1 if Response = "Civil monetary penalty", otherwise 0.
  3. Create a pivot table to display the minimum value of Numeric_Response, with columns based on Question_ID and unique rows based on State, Program, Reporting_Period_End_Date, and Measure_Number.
  4. Find the sum of sanction_noncomplianceInstances where sanction_interventionType = 1. This is the total number of instances of noncompliance that resulted in a civil monetary penalty.

Additional Resources