With an Insurance Analysis report, you can get a breakdown of:
the insurance carriers you’re collecting from
how many patients per each insurance carrier
the average amount collected from families by each insurance carrier
This data will provide you and the practice additional insight to help make informed decisions about which insurances to stay in network with, and which insurances may need to be reevaluated.
How to Run an Insurance Analysis Report
Watch the video below or follow along with the step-by-step instructions to run an Insurance Analysis Report:
First, use the Patient Finder to export a patient list
Login to Analytics and click on the Patient Finder
Click Start From Scratch
Select Edit Filters
Click Add Filter and then choose Primary Insurance
Click the Filter Type dropdown and select Has Any Value
Click Save
Click on Actions then click Export Table
Select the bubble next to All Columns, then click in the Export Name field and type “DI Insurance Analysis”
Click Save
Click Done
Exit out of the Patient Filters window by clicking on the X in the upper right corner. Select Exports under Patients
Click Download on the file you just exported
Next, edit the CSV file in Excel (or spreadsheet software of choice)
Open the downloaded CSV file in your spreadsheet software of choice (Microsoft Excel, Google Sheets, Numbers, etc).
These instructions are written with Microsoft Excel in mind. Please note that if you’re using a different software, there may be minor differences in the instructions.
Save the file in the software’s preferred format for easier manipulation of the data (in this example, Excel).
When you open this spreadsheet, it is going to have a lot of additional information. You only need two columns. Look for the column title with the following values and keep those columns:
Primary Insurance
Patient Family Value
Delete every other column from the spreadsheet.
At the bottom, select the sheet and rename the sheet “Original Data”.
Create a new tab at the bottom. Rename it “Analysis with Formulas”.
Add the following column titles in row 1:
Column A: Insurance Company
Column B: Number of Patients or # Patients
Column C: Sum APV or Sum of Annual Patient Value
Column D: Expected APV by Insurance or Expected Annual Patient Value by Insurance
Next, select the Original Data sheet.
Select Cell A2 to the bottom of the data set in the column.
SHORTCUT: Select Cell A2 and hold Shift+Command+down arrow (for Mac) or Shift+Control+down arrow (for PC).
For this example, we will assume the data set ends at cell A1800.
Copy your selection.
Select the Analysis with Formulas sheet.
Select Cell A2 and paste.
Now remove the duplicates from this column:
Select Column A.
Find the “Remove Duplicates” tool in the Data tab at the top.
Select Continue with the current selection
Click Remove Duplicates...
Check My list has headers and make sure Column A is selected.
Click OK.
Click OK on the alert.
Now, create formulas
Now to create some formulas. Make sure you are on the “Analysis with Formulas” sheet of your workbook.
"Count If" Formula
First, we’re going to use a formula to count the number of patients with each specific insurance. To do this, we’re going to use a “Count If” formula. “Count If” looks at a given range of values and will count the cell if it matches the given criteria.
In its simplest form, COUNTIF says:
=COUNTIF(Where do you want to look?, What do you want to look for?
=COUNTIF(range,criteria)
Now that you see what the “Count If” formula does, let’s use it!
Select Cell B2 and type in =COUNTIF(
Click on the Original Data sheet and select cells A2 to the bottom of the data set in the column.
Remember SHORTCUT: Select Cell A2 and hold Shift+Command+down arrow (for Mac) or Shift+Control+down arrow (for PC).
For this example, we will assume the data set ends at cell A1800.
Type a comma , to continue the formula.
Select Cell A2 on the Analysis with Formulas sheet.
Type a closed parenthesis ) and push the Enter key to complete the formula.
Click on Cell B2 and check the fx (formula) box at the top to make sure it looks like the following:
=COUNTIF(‘Original Data’$A$2:A1800,’Analysis with Formulas’!A2)
Note: this assumes the end of the data range is Cell A1800.
Note: Whenever you see ‘text’! In a formula, that means that it’s referencing that sheet of your workbook. So this formula, the Range references the “Original Data” sheet and the Criteria references the “Analysis with Formulas” sheet.
Now that you have the formula created, apply it to the rest of the column. Click on the Cell B2 and double click the black box at the bottom right of the cell. That will apply the formula to the rest of the column.
"Sum If" Formula
Second, we’re going to use a new formula to sum the annual patient value of patients with each specific insurance. To do this, we’re going to use a “Sum If” formula. “Sum If” looks at a given range of values and if it matches the given criteria, will sum the data of a different range of values.
In its simplest form, SUMIF says:
=SUMIF(where do you want to look?, What do you want to look for?, add up all the values if desired criteria are met)
=SUMIF(range, criteria, [sum_range])
Now that you see what the “Sum If” formula does, let’s use it!
Select Cell C2 and type in =SUMIF(
Click on the Original Data sheet and select cells A2 to the bottom of the data set in the column.
Remember SHORTCUT: Select Cell A2 and hold Shift+Command+down arrow (for Mac) or Shift+Control+down arrow (for PC).
For this example, we will assume the data set ends at cell A1800.
Type a comma , to continue the formula.
Select Cell A2 on the Analysis with Formulas sheet.
Type another comma , to continue the formula.
Click on the Original Data sheet and select cells B2 to the bottom of the data set in the column.
Remember SHORTCUT: Select Cell B2 and hold Shift+Command+down arrow (for Mac) or Shift+Control+down arrow (for PC).
Type a closed parenthesis ) and push the Enter key to complete the formula.
Click on Cell C2 and check the fx (formula) box at the top to make sure it looks like the following:
=SUMIF(‘Original Data’$A$2:A1800,’Analysis with Formulas’!A2,’Original Data’!B2:B1800)
Note: this assumes the end of the data range is Cell A1800 and Cell B1800.
Note: Whenever you see ‘text’! In a formula, that means that it’s referencing that sheet of your workbook. So this formula, the Range references the “Original Data” sheet, the Criteria references the “Analysis with Formulas” sheet, and the [sum_range] references the “Original Data” sheet.
Now that you have the formula created, apply it to the rest of the column. Click on Cell C2 and double click the black box at the bottom right of the cell. That will apply the formula to the rest of the column.
"Expected APV by Insurance" Formula
Next, we need to create a simple formula for the Expected APV by Insurance column. This will tell us that if a patient has “x” specific insurance, we can expect to receive “y” amount in dollars per year.
We want to divide the value in Cell C2 by the value in cell B2 to get the expected APV per patient.
In Cell D2 type =
Click Cell C2
Type / (for division)
Click Cell B2 and hit Enter
Now that you have the formula created, apply it to the rest of the column. Click on the Cell D2 and double click the black box at the bottom right of the cell. That will apply the formula to the rest of the column.
Next, organize and clean up the data
Congratulations! You have now completed all of the fields needed in the “Analysis with Formulas” sheet. Now, let’s prepare the data to be organized.
Create a new tab at the bottom. Rename it Analysis.
Click on the Analysis with Formulas sheet and click on the box with the triangle to the left of Column A and above Row 1 to select all the cells in the sheet.
Copy your selection.
Click on the Analysis sheet.
Select Cell A1
Right click, choose Paste Special then click Values & Source Formatting.
Now, click on Cells B2, C2, and D2. You’ll notice that in the fx bar at the top. If you’ve done this right, you will only see a number value rather than a big formula. This is because we have only pasted the final value of the data and not the formula from the previous sheet.
Now time to clean up the visual look of the data.
Hover over the gray vertical bar in between columns A and B and double click to adjust the width of the column to show the full name of all insurances in the column.
Select Columns C and D
Click Home then select the $ icon. This formats them as currency.
Click on the button that looks like “.00 -> .0” twice. This removes the cents from view as we don’t need to be that exact in this analysis.
Select columns A through D, click on Sort and Filter on the Home tab and select Filter.
Now you can filter by column.
If you want to see the insurances in alphabetical order, click on the dropdown arrow in Cell A1 and select Ascending.
Take a closer look and notice that some insurances will have many entries. This is due to either different locations of insurance (Delta Dental vs Delta Dental of California) or typos/data entry differences/mistakes.
For this insurance analysis, we don’t care about insurance providers that provide service to one or a few individuals.
Click on the dropdown arrow in Cell B1 and select Ascending. This will show you the insurance providers ranked from fewest patients to most patients.
Scroll down and choose an arbitrary point of “if this number of patients has this insurance, we’ll analyze it”. My recommendation is a cutoff in the range of 15-25 patients.
Find the number you chose and select the row that number is in.
Scroll to the top and click Row 2 while holding the shift key. That will select everything in between your selection.
Then right click and select Delete. This will only leave the insurances with enough patients to analyze.
Lastly, add final touches
Now let’s take the final steps for presentation before sharing this information with your client.
Click on the dropdown arrow in Cell D1 and select Descending. This will sort the list by which insurances have the highest Expected APV by Insurance value at the top.
Select Column D
On the Home tab up top (should already be selected), click on Conditional Formatting, select Color Scales and select an option that has green at the top and red on the bottom.
Now, you have every insurance company that has “x” number of patients, the total collections from those patients in the last 365 days, and the per patient collections from the last 365 days in that office. At this point, now that you have the data, you can draw your own conclusions and provide recommendations to the practice if you like.
Alternative use: If you have a client that wants to evaluate one insurance company, filter by that specific insurance at the end before you delete all the additional rows.