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:**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:

## 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”**This removes the cents from view as we don’t need to be that exact in this analysis.*twice*.**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.