EHR and practice management (PM) systems come with built-in reporting capabilities but digesting all that information can be overwhelming. However, leveraging the power of Excel to sort and manipulate the data stored in your EHR can help you spot trends faster and implement steps to drive revenue growth.
“Excel is a great way to slice and dice your practice management data so you can really use it to improve,” says Nate Moore, CPA, MBA, an independent consultant and coauthor of “Better Data, Better Decisions: Using Intelligence in the Medical Practice.” “Excel allows you to filter, trend, and get your arms around reams of data.”
Excel offers an interactive tool called pivot tables that allow users to quickly sort, filter, and manipulate data, says Moore, who moderates the Excel Users Medical Group Management Association Community, an online resource for practice administrators. It gives users much more flexibility than an EHR, which typically offers a limited number of canned reports.
For example, your PM system can probably produce a general report on your collection rates at the front desk at the point of service. But a pivot table would allow you to slice that data in a variety of ways, such as individual employees’ collection rates by location or time of day.
In addition, you can connect Excel to the server where your data is stored so you are always working with the most current numbers, says Moore. That allows you to quickly run the same types of reports with updated data.
“A lot of practice administrators don’t run reports as often as they’d like because they take so much time to run and analyze using the PM and EHR,” says Moore. “Using Excel streamlines the process, making it more likely that reports will actually get produced.”
Moore offered a few examples of how pivot tables might be used to dig deeper into financial reports and zero in on potential problems:
1. Focus on overdue accounts. A general report on aging accounts receivable from your PM system might contain hundreds of pages, making it difficult to focus on specific trends. Exporting that data into pivot tables allows you to zero in on problem areas, such as claims overdue by 60 days categorized by insurer.
2. Gage productivity. If your compensation system is based on productivity, you can look at work relative value units by individual providers or during certain time periods.
3. Monitor workflow. Larger practices can monitor and compare activity at different locations. For example, how many patients did one employee register at a specific location vs. another employee at a different office? How many appeals or claims did each individual employee process at each office?
4. Analyze your patient base. Using a basic pivot table, you can see all of your new patients in a given year categorized by month of visit, referring physician, diagnosis code, insurance, or clinic location. Analyzing the data reveals trends, such as how many patients each physician saw in each year over the past five years.
5. Group data. You can group data to spot referral trends. For example, how many commercially insured patients did one group of referring physicians refer to each individual provider in your practice, for each of the past five years?