An average day at a provider's office can include meeting with customers, developing prospective referral sources, educating physicians, reviewing financial information, bargaining with vendors, and participating in the latest legislative soap opera. With so little time for billing and collecting payments for services provided, extreme efficiency in managing accounts receivable is paramount. Thankfully, readily available spreadsheet and database programs can be used to create a more targeted workflow, allowing billing personnel to focus on areas that are most likely to yield quick cash flow.

Commercially available billing software is often limited to the standard reports deemed important by computer programmers. For example, an accounts receivable by payor report provides a nice aggregation of tens of thousands of records, along with an agreeable snapshot of where accounts receivable (AR) stand. It gives little guidance, however, as to the quality of sales, effectiveness of collections, or the cause of recurring problems. To get this guidance, a provider usually allocates manpower to manually analyze the reports, or asks the software vendor to create custom report queries within the software. Both are time-consuming and costly. Instead, you can directly access thousands of records and create simple tools in Excel that point billing personnel in the right direction.

THE EXPORT

Andrea Stark

First, get relevant raw data into Excel. Virtually all commercially available billing software packages provide some functionality to export data or reports to an electronic format. Most of these electronic reports can be easily opened or accessed using Excel. Common formats used to save exported data include .xls for Excel and .csv for comma separated values. Look for report/export options that include raw data on sales and payments by each patient, service date, and procedure code—as opposed to reports that aggregate the sales and payment data into a calculated balance.

While large raw data reports may seem worthless as analytical tools, they provide a limitless opportunity to manipulate our own calculations and aggregations. Using raw data, we can answer targeted questions about trends and tailor our responses efficiently and effectively.

To ensure that your data is complete and free of duplication, balance it to your accounts receivable report. Using the sum function (=SUM(value1,value2)), add all of the applicable sales, payments, and adjustments included in the raw data for the period of time under analysis (such as June 2008).

Example 1A

Next, add your total sales to the month's beginning AR balance (for June, the beginning AR balance would fall on May 31), and deduct your total payments and adjustments. The figure you arrive at should match the end of month (June 30) figure per your AR report.

Example 1B

If the two figures do not match, you should determine what information is duplicated or missing before moving on with your analysis.

At ClaraVista LLC, our billing software allows us to export a file that has the details associated with each individual service provided to each patient. Details include: service dates, submitted amounts, primary and secondary payors, applicable allowable amounts, payments, procedure codes, modifiers, and CMN/prior authorization information. Reports in the raw data format do not include many calculated fields like aged balances.

RAW DATA

After exporting relevant raw data from the billing software, open Excel. A simple Google search will yield numerous articles on how to open and parse various data file types in Excel.

It is important the data be in a tabular format; that is, each column contains all of the information relative to its type and only its type. This is a giant data sheet, so it should not contain superfluous information like page numbers and report titles within the spreadsheet. You may also want to spend some time working with your software vendor to create an export file specifically for Excel. To create smaller files, try limiting the date range for the information being exported into Excel. Using a more robust database program such as Access or SQL is also an option.

THE ANALYSIS

Once you are sure you have a complete data set free of duplication, begin to group and analyze the data to evaluate performance and problem areas. Often, by just seeing all this data in one place, you can readily identify missing or incorrect data elements that might not be caught otherwise (look for missing/mismatched ICD-9 codes, invalid HCPCS, missing authorizations for certain payors, and the list goes on).

AVERAGE AGE OF CLAIMS BY PROCEDURE CODE

As our account managers are combing through vast amounts of AR data, it is helpful for them to analyze collection trends in regard to certain procedure codes. Where do we see decreases in AR? Where are the increases? Where are the payment shifts and what is the significance? If we see average times increasing across the board, it may indicate a problem with a specific client's procedures for providing the service, or possibly a processing issue with a specific insurance company—or an increased level of scrutiny by the payor. We also use this analysis as a monitoring component to our own quality control protocol. To analyze the age of claims by procedure code, we typically sort the data by procedure code. Then, we add a column with the following formula for each record or row (column C in example 2 below) to tell us the relative age of each claim:

Example 2

=days360(B2,today())

Now that we know the relative age of each claim, we can use simple average functions (=average(value1, value2)) to calculate the average age of claims by each procedure code (column D in example 2). Running the query over subsequent months and comparing the results will illustrate trends that your staff can focus on to perpetually improve collection results.

MAPPING

We often use Excel to map information that would otherwise have to be manually researched and entered. For example, based on standard prefixes associated with Blue Cross/Blue Shield policy numbers (or any payor's policy numbers that follow a pattern), Excel can determine the appropriate Blue payor (Blue Cross of Illinois, Blue Shield of Illinois, etc). This is handy if you have various programs in your state that require that some claims be billed to one address, and others be billed to another.

Example 3A

To do this, we first maintain a spreadsheet in Excel called Prefixes that correlates standard policy number prefixes to specific payors (example 3A).

In a separate spreadsheet containing all of our transactional data, including policy numbers (see example 3B), we use the following formula to extract the prefix shown in column D:

=LEFT(A2,3)

The LEFT function returns the first three characters, from left to right, of the targeted cell. We can then have Excel look up the proper payor based on our Prefixes spreadsheet using the following formula (column E):

=VLOOKUP(D2,Prefixes!$A$2:$B$5,2,FALSE)

The VLOOKUP function will return the payor named in our Prefix spreadsheet based on the policy prefix.

Finally, we ask Excel to identify those records where an incorrect payor is being billed (based on the policy number). We do so by including the following formula (column F in example 3B):

Example 3B

=IF(C2<>E2,"PAYOR MISMATCH","ok")

The results in column F can be sorted so billing staff can quickly identify those claims that are not paying due to data entry errors related to the payor information. This mapping function is all the more important in light of mandated use of NPI numbers, and ensuring that the payors are receiving the correct information from our billing software. Once we resolve internal mapping issues, we can tackle the payor mapping issues and ensure they have the appropriate crosswalks in their systems.

MISSING/EXPIRED AUTHORIZATION

Example 4

To avoid denials related to missing or expired authorizations, we use Excel to identify those patients for whom there is no authorization at all, or the expiration date expires in the next 90 days. You'll first need to gather the services and payors that are affected by authorization requirements into a spreadsheet. Once that data is available, we use the following formula to identify missing or expired authorizations (column C in example 4):

=IF(B2="","Missing Authorization",
IF(DAYS360(B2,TODAY())>1,"Expired Authorization",
IF(DAYS360(TODAY(),B2)< 91,
"Authorization Set to Expire Soon",
"Authorization Ok")))

Sort data by authorization status in column C, and generate worklists for maintaining current authorizations. Group requests by physician, so that when you send, referral sources can address multiple requests.

Andrea Stark is a Medicare consultant and reimbursement specialist. She can be reached via e-mail: .