Five ways pivot tables improved our bottom line

My favorite analytic tool in Microsoft Excel is called the pivot table. Rather than relying on the confusing and canned reports provided by our practice management system and EHR, I can use pivot tables to see the data I want to see, sort the data how I want to sort it, and produce actionable and meaningful reports. 

For those unfamiliar with pivot tables, pivot tables are a very powerful way to summarize, sort, filter, and group data. They are a feature of Microsoft Excel that allow one to analyze data (e.g., a report of billed charges and reimbursements) in a spreadsheet with ease. Pivot tables allow users to ‘pivot’ or quickly rotate data between rows and columns to analyze information. They also allow users to stack or nest data in rows or columns.  

Managers can quickly organize and sort information while including or excluding the data displayed. Changing the way the pivot table displays practice information adds insight into practice operations. In this article, I share five ways that I have used pivot tables to benefit our practice.  

1. Provider utilization

Like many specialty practices covering multiple hospitals, we had assigned physicians to certain hospitals and had not changed this resource allocation for several years.  Using data from our practice management system and EHR, I created a pivot table looking at the return on investment for each physician’s hospital work. Within the pivot table, I showed procedures, consults, RVUs, and other comparative information. The table demonstrated significant differences in utilization and resulted in changes to our hospital coverage. Not coincidentally, revenues increased.

2. Referrals

In the past, we used our canned practice management system reports to look at referrals by provider. These reports were not timely nor useful. We now have a pivot table linked to our practice management system server that is updated daily. It permits us to study referral trends by referring physician or practice, receiving physician or practice, or type of referral. It permits us to see changes on a real-time basis and address referral changes quickly. This data has enabled us to improve our service by being more responsive to concerns of referring physicians and has contributed to increased referrals.

3. E/M coding

Even as Medicare prepares to ‘simplify’ Evaluation and Management (E/M) coding guidelines, physicians continue to be audited by Medicare and private payers for overcoding. We use a pivot table to evaluate coding patterns, both relative to Medicare data (Medicare E/M by Specialty 2016) and among our providers. This data has identified probable undercoding and overcoding, permitting us to address both through additional education. I also find that sharing this comparative data among my providers results in ‘right-sizing’ the coding of those who historically code very conservatively. As a result, revenues have increased.  

4. New office location 

We created a pivot table that permitted us to analyze patient origin data by zip code, referring physician, payer mix, and more. This information was used to identify the location of a new office in the suburbs. We also used this pivot table to target marketing for the new office. The office has been busy since day one without impacting the referrals of our other offices.  

5. Reimbursement

Analyzing your fee schedules using canned reports is tedious at best. If you’ve tried to run a report showing what each payor pays for a set of CPT codes, you know what I mean. A pivot table allows one to slice and dice information by code by payer to look at variances, identify underpayments, and negotiate better terms. I have used data from our pivot table to improve reimbursement across the board for some payers and for certain codes for other payers.    

Pivot tables help me help my doctors have a more profitable practice. I recommend two essentials for those interested in learning more about pivot tables. First, THE book on using pivot tables in your practice is Better Data, Better Decisions; Using Business Intelligence in the Medical Practiceby Nate Moore and Mona Reimers. It is available for purchase as an e-book Better Data, Better Decisions or a softcover Better Data book.  

Second, Nate Moore offers free step-by-step tutorials on using both Excel and Excel pivot tables on his website. These three- to four-minute videos use de-identified data from medical practices and build on each other.

Pivot tables have made our practice better.  I hope they help you accomplish the same.

Lucien W. Roberts, III, MHA, FACMPE, is administrator of Gastrointestinal Specialists, Inc., a 25-provider practice in Central Virginia. He may be reached at lroberts@gastrova.com. He is not affiliated with Moore Solutions nor does he receive any compensation directly or indirectly from Nate Moore, Mona Reimers, or Moore Solutions.