Denial Analytics

The Denial Analytics package allows users to develop custom financial reports based on CARC adjustment data--denial or otherwise--based on the fields and measures in the below data dictionary. Each denial of a given line will be included -- so if a $100 charge receives three full denials, each will be shown, for a total of $300 denied. This report also doesn't show the current state of denials, but rather shows posted CARCs regardless of the current state of the resulting payments. 

Once your practice has been set up to utilize this feature, you can download the connection file and get to work building new Denial Analytics reports. To do this, use the following steps:

1. Create a folder where you would like to store your Analytics reports. In this example, it's a folder on the Desktop called 'Analytics.'
   
   
2. In iSalus, navigate to Reports.
   
   
3.  Click Denial Analytics.
   
   
4.  A pop up will appear prompting you to save the report – click the down arrow next to Save, and click Save as:
    
5. In the resulting window, name your file whatever you would like your connection to be called in Excel, then save it to the folder you created in Step 1. In this example, it's called 'Analytics'.
   
   
   
   
6. Navigate to the folder and open the saved file.
   
   
7. If you receive the following warning, click Enable:
   
   
   
   
8. The Excel worksheet will open with a Pivot Table already started. At the top of the screen, click Data, then Queries and Connections:
   
   
   
   
9. You will see your saved file name as the name of the connection in the right-hand pane. Double-click this connection:
   
   
   
   
10. In the Definition tab of the Connection Properties, ensure that the Always use connection file and Save password boxes are checked (clicking Yes for confirmation), and click OK:
    
    
    
    
11. At this point you can save your report, preferably in the same folder as the connection file. If you ever move the connection file, you may need to modify the Connection file property in the Connection Properties window shown above.

1. Create a folder where you would like to store your Analytics reports. In this example, it's a folder on the Desktop called 'Analytics.'
    
2. In iSalus, navigate to Reports.
    
3.  Click Denial Analytics.
    
4.  A pop up will appear prompting you to save the report – click the down arrow next to Save, and click Save as:
   
    
   
   
5. In the resulting window, name your file whatever you would like it to be called, then save it to the folder you created in Step 1. In this example, it's called 'Analytics'.
    
    
   
   
6. Open the report that you’re wanting to connect to the cube on your own machine. At the top of the screen, click Data, then Queries and Connections:
    
    
   
   
7. You will see a connection in the right-hand pane. Double-click this connection:
    
    
   
   
8. In the Definition tab of the Connection Properties, use the Browse button to navigate to where you have saved your local connection file, and select it.
    
     
   
   
9. Back in the Definition tab, ensure that the Always use connection file and Save password boxes are checked (clicking Yes for confirmation), and click OK:
    
    
   
10. At this point you can save your report, preferably in the same folder as the connection file. If you ever move the connection file, you may need to modify the Connection file property in the Connection Properties window shown above. 

When you have completed a worksheet as desired in Denial Analytics and would like to create a new worksheet to add to your workbook, you have two options. 

First, you can copy your current worksheet. This option is best if there are certain settings or fields that you would like to carry over, to save time when creating your new PivotTable. To do this, take the following steps:

1. Right-click the name of your worksheet at the bottom of the screen and select Move or Copy:
   
   
   
2. In the window that opens, select (move to end) and Create a Copy:
   
   
   
3. Double-click the name of the new worksheet to rename as desired, and modify the new worksheet and PivotTable as needed:
   
   
   

If instead you would like to start with a brand new worksheet and PivotTable, you can take the following steps:

1. At the bottom of the screen, click the + icon to add a new worksheet:
   
   
   
2. If desired, double-click the name of the new worksheet to rename.
   
   
3. Click the top left cell in the worksheet, if it's not already selected:
   
    
4. At the top of the screen, select Insert, then PivotTable:
   
   
   
5.  On the resulting window, click Use an external data source, and click Choose Connection:
   
   
   
6. Select your Connection, hit OK, and hit OK again:
   
   
   
7. Add fields to your new PivotTable as desired.
   
   

Values

In Microsoft Excel, ‘Values’ are the actual numbers that you are able to analyze/report on.  This type of data is quantitative (numerical). You will see the possible values at the top of the PivotTable Fields list as follows.

Here are the available values: 

• Adjustments: The dollar amount that was posted as adjustments against the claim line's balance. 
• Comments: The dollar amount that was posted as a comment on the line only. 
• Count: The number of qualifying CARC/RARC lines. 
• Payments: The dollar amount that was posted as an actual payment. 
• Total Amount: The total amount of the CARC code, including Adjustment amounts, Comment amounts, and Payment amounts.

Additional Fields

The other available fields are how you might analyze or break down a number. This type of data is qualitative, grouping and dividing values out into categories. All non-date fields are grouped into folders by category. 

• Deposit Date Y-M-D: This allows a user to filter or group CARCs received based on their deposit dates. This deposit date is entered on the deposit itself. 
  • Deposit Year: The year value of the associated deposit's deposit date. 
  • Deposit Month Name: The month name of the associated deposit's deposit date.
  • Deposit Day: The day of the associated deposit's deposit date.
• Disbursed Date Y-M-D: This allows a user to filter or group CARCs received based on the disbursement date of the deposit. This is the date the associated deposit was fully disbursed, the date that the last outstanding deposit claim was finally posted. 
  • Disbursed Year: The year value of the associated deposit's disbursement date. 
  • Disbursed Month Name: The month name of the associated deposit's disbursement date.
  • Disbursed Day: The day of the associated deposit's disbursement date.
• Post Date Y-M-D: This allows a user to filter or group transactions based on when the deposit claim associated with the CARC code was posted. 
  • Post Year: The year value of the associated deposit claim's post date. 
  • Post Month Name: The month name of the associated deposit claim's post date.
  • Post Day: The day of the associated deposit claim's post date.
• Service Date Y-M-D: This allows a user to filter or group CARC codes based on the associated service dates. Please note that this is a good way to see all denials received for a certain service date range, but the denial amounts shouldn't necessarily be compared to charges or outstanding receivables for a certain range. This is due to the fact that you can receive multiple denials for a given charge, and this report shows posted CARCs regardless of the current state of the resulting payments. 
  • Service Year: The year value of the associated claim's service date. 
  • Service Month Name: The month name of the associated claim's service date. 
  • Service Day: The day of the associated claim's service date.
• Alternate Provider: Fields related to the alternate provider on the claim. 
  • Alternate Provider Reverse Name: The claim alternate provider’s name in the format “[Last Name], [First Name]”.
• Attending Provider: Fields related to the attending provider on the claim. 
  • Attending Provider Reverse Name: The claim attending provider’s name in the format “[Last Name], [First Name]”.
• Claim Information: General information regarding the claim the CARCs are posted to.  
  • Claim ID: The unique ID for the claim.
  • Claim Level: The current level of the claim—set to Primary, Secondary, Tertiary, Statement, or Completed. 
  • Claim Status: The current Claim Status. 
  • Claim Submission Type: The current Submission type for the claim, Electronic or Paper. 
  • Claim Substatus: The text description for the current Claim Substatus.
  • Claim Type: The current Claim Type (Medical, Dental, EPSDT, DME, etc.) on the associated claim. Note: Medical is the default Claim Type on all claims.
• Claim Primary Payer: The primary payer assigned to the relevant claim. 
  • Claim Primary Financial Class: The financial class of the claim’s primary payer. 
  • Claim Primary Payer Name: The name of the claim’s primary payer. 
• Claim Secondary Payer: The secondary payer assigned to the relevant claim.
  • Claim Secondary Financial Class: The financial class of the claim’s secondary payer. 
  • Claim Secondary Payer Name: The name of the claim’s secondary payer. 
• Claim Tertiary Payer: The tertiary payer assigned to the relevant claim.
  • Claim Tertiary Financial Class: The financial class of the claim’s tertiary payer. 
  • Claim Tertiary Payer Name: The name of the claim’s tertiary payer. 
• Date Fields: Additional date fields available if needed to add in any of the field sections of the report. 
  • Deposit Date: The associated deposit's full deposit date.
  • Deposit Month Name: The month name of the associated deposit's deposit date.
  • Deposit Year: The year value of the associated deposit's deposit date.
    
  • Disbursed Date: The associated deposit's full disbursement date.
  • Disbursed Month Name: The month name of the associated deposit's disbursement date.
  • Disbursed Year: The year value of the associated deposit's disbursement date.
  • Post Date: The associated deposit claim's full post date. 
  • Post Month Name: The month name of the associated deposit claim's post date.
  • Post Year: The year value of the associated deposit claim's post date.
  • Service Date: The associated claim's full service date.
  • Service Month Name: The month name of the associated claim's service date.
  • Service Year: The year value of the associated claim's service date.
• Deposit Claim Information: Posting information related to the Deposit Claim.  
  • Posting Claim Level: The posting Claim Level from the Deposit Claim.
    
  • Posting Claim Status: The posting Claim Status from the Deposit Claim. 
  • Posting Remit Status: The remit status from the Deposit Claim.
• Deposit Information: Fields related to the CARCs' originating deposit.   
  • Create User: The user who created the associated deposit.
    
  • Deposit ID: The Deposit ID for the associated deposit. 
  • Deposit is Posted: Shows whether the deposit has been sent to posting. 
  • Deposit Model: The model type of a given deposit (Standard, Converted, or Legacy). 
  • Deposit Type: The type of the deposit (EOB, ERA, Import, or Statement).
  • Statement ID: The statement ID associated with the deposit. 
• Deposit Payer: Fields related to the payer on the deposit. 
  • Deposit Payer Financial Class: The financial class for the payer on the deposit.
    
  • Deposit Payer Name: The name of the payer on the deposit. 
• Location: Fields related to location, whether patient location, service location, or business unit. 
  • Business Unit: The business unit allocation for the claim.
    
  • Patient Location: The Patient Location currently on the claim.
  • Service Location: The Service Location currently on the claim.
• Ordering Provider: Fields related to the ordering provider on the claim.
  • Ordering Provider Reverse Name: The ordering provider’s name in the format “[Last Name], [First Name]”. 
• Patient Address: The fields that comprise the address of the patient on the claim. 
  • Patient Address 1: Line 1 of the patient’s address. 
  • Patient Address 2: Line 2 of the patient’s address.
  • Patient City: The patient’s city. 
  • Patient State: The patient’s state. 
  • Patient Zip: The patient’s zip code. .
• Patient Demographics: Information relevant to the patient on the claim. 
  • Patient DOB: The patient’s date of birth. 
  • Patient Email: The patient’s email address. 
  • Patient Gender: The patient’s gender.
  • Patient ID: The patient's chart number. 
  • Patient is Active: ‘1’ if the patient is active, ‘0’ if the patient has been made inactive. 
  • Patient is Living: ‘1’ if the patient is living, ‘0’ if the patient is deceased. 
  • Patient Reverse Name: The patient’s name in the format “[Last Name], [First Name]”.
• Payment Information: Information relevant to the payment resulting from the CARC code. 
  • Payment Type: The payment type assigned to the payment upon posting. 
  • Payment Type ID: The payment type ID assigned to the payment upon posting. 
  • Posting Type: Denotes whether this CARC was posted as a payment, an adjustment, or a comment. 
• Procedure: Information related to the procedure code that the CARC was posted on.  
  • Code: The procedure code on the claim line. 
  • Code Modifiers: The description of the procedure code on the claim line, truncated to 30 characters.
  • Code with Desc: The full description of the procedure code on the claim line. 
  • CPT Group: The Code Group for the procedure code on the claim line. 
  • Diagnoses: The full diagnosis list for the procedure code on the claim line. 
  • Primary Dx with Desc: Shows both the primary diagnosis code on the claim line and its short description.
• Reason Codes: Information related to the CARC/RARC codes that were posted.  
  • CARC: The individual CARC code that was posted. 
  • CARC is Denial: 'Y' if the CARC code is marked as a denial. Otherwise, set to 'N'.
  • CARC with Description: The posted CARC code with its description.
  • RARC: The RARC codes that were posted. 
  • RARC with Description: The posted RARC codes with their descriptions. 
• Referring Provider: The referring provider on the claim.
  • Referring Provider Reverse Name: The referring provider’s name in the format “[Last Name], [First Name]”. 
• Rendering Provider: The rendering provider on the claim.
  • Rendering Provider Reverse Name: The rendering provider’s name in the format “[Last Name], [First Name]”.
• Supervising Provider: The supervising provider on the claim.
  • Supervising Provider Reverse Name: The supervising provider’s name in the format “[Last Name], [First Name]”.

The Denial Analytics package has a pre-defined set of fields that will be displayed when drilling through into detail for most measures. When looking at a table of data, any number can be double-clicked to drill through into the detail for that cell.

This will bring up an additional sheet in the same Excel workbook with the following fields: 

• Patient ID: The patient's chart number. 
• Patient Reverse Name: The patient’s name in the format “[Last Name], [First Name]”.
• Rendering Provider Reverse Name: The rendering provider’s name in the format “[Last Name], [First Name]”.
• Service Location: The Service Location currently on the claim.
• Service Date: The Service Date of the associated claim. 
• Post Date: The Post Date of the associated transaction.
• Claim ID: The unique ID for this claim. 
• Code: The procedure code on the claim line.
• Code Modifiers: The modifiers on the claim line. 
• Diagnoses: The list of diagnosis codes on the claim line. 
• Deposit Payer Name: The payer on the deposit that this CARC/RARC was posted on. 
• Deposit ID: The unique ID for the deposit that this CARC/RARC was posted on. 
• CARC: The Claim Adjustment Reason Code that was posted. 
• RARC: The Remittance Advice Remark Code that was posted to give more information about the adjustment. Many CARCs will be received with no associated RARC. 
• Payment Type: The payment type that was associated with this CARC line at time of posting. 
• Payments: The amount associated with this CARC line that was posted as a payment.
• Adjustments: The amount associated with this CARC line that was posted as an adjustment against the balance of the line. This will most commonly happen with expected contractual adjustments like CO-45's. 
• Comments: The amount associated with this CARC line that was posted as a comment-only amount, not being a payment or a taken adjustment. These are most commonly associated with denial CARCs. 
• Total Amount: The total amount associated with this CARC line, whether it was posted as a payment, adjustment, or comment.

Here is an example dashboard that can be built with the new Denial Analytics package. With slicers connected to the below, it's easy to see each of these charts filtered to only a subset of payers, rendering providers, CARC codes, and CPT groups. 

Here are a few examples of reports that can be created with the new Billing Analytics. 

Denials by Payer by Class for Post Month

This report shows denial counts, adjustment amount, comment amount, and payment amount for CARCs posted over a certain range, grouped by Deposit Payer Financial Class and CARC Code. 

1. First, drag the desired fields to the PivotTable areas. Please note that 'Values' is not a field that you need to add to the Columns area -- it's automatically populated if more than one measure is added to the Values section, to denote that you'll have columns associated with each value.  
   
   
   
2. Next, apply the desired post date filter by clicking on the filter icon where the filter is listed at the top of the report. I also filtered to see only where CARC is Denial is "Y", so that I'm only looking at denials and not my contractual adjustments like CO-45's.
   
   
   
3. You can now see the desired output, filtered to the desired post date month:
   
   

Denial Codes Adjusted

1. First, drag the desired fields to the PivotTable areas:
   
   
   
   
2. Next, apply the desired post date filter by clicking on the filter icon where the filter is listed at the top of the report. I also filtered to see only where CARC is Denial is "Y", so that I'm only looking at denials and not my contractual adjustments like CO-45's.
   
   
   You can now see the desired output, actual adjustment amounts for different CARCs. You can see in the below that CO-102's and OA-59's are reversals of adjustments, with a positive value. However, it does show that CO-97's have an actual adjustment against a balance -- that dollar amount can be double-clicked to explore further.