In prior posts, we’ve looked at some examples of production reports. These reports were generated by pulling charge, RVU, and encounter data from the practice management system. Likewise, I recently posted some examples of financial and accounting data that was pulled from the accounting system database. In those examples, we looked at revenue, expenses, and income.

In some cases, however, we need to be able to look at the relationships between the financial data and the production data. For instance, while it’s helpful to look at total work RVUs or total encounters for your providers, it can be very useful to look at what your providers total revenue or expenses are per work RVU or per encounter. These metrics can shed a lot of light on how well the practice is performing financially in terms of its production.

In this post, I’d like to show how you can pull data from both the PM and Accounting systems to create more robust analyses. Remember, with BI tools such as PowerPivot or Power BI, data can be pulled from multiple sources. In this case, I’ve pulled data from a PM system as well as an accounting system.

The key to pulling data from multiple sources is linking or “relating” the data from each source.  For instance, the accounting system stores the provider names as Classes using a 5-digit code. The PM system, on the other hand, uses the provider’s name. In order to relate the data between the two systems, we would have to link the 5-digit class code of the accounting system to each provider name in the PM system. We won’t get into the details of exactly how this is accomplished. Just know that it can and must be done.

In the examples below, you can see that the first two tables are pulling in production information, such as total work RVUs and total encounters (current year [CY] and prior year [PY]). Moving along to the right, we start to see comparative analyses, such as total FFS revenue per work RVU as well as total expenses per work RVU. To calculate those metrics, the system must pull from both the accounting and the PM systems.  

Looking further down, we can see some other production-related information, such as the E&M distribution and payer mix. Towards the bottom of the report, there is a graph showing the provider’s profit/loss trend.  

The next section compares similar measures across all providers.

This project was done in Excel using PowerPivot. As always, once the measures are built and the report is formatted, you simply need to refresh the data each month. As is the case with all the other examples I’ve posted, you can easily add slicers to quickly change the data, provider, location, etc.

The output of this report is a single page (front and back) report that gives the providers a good idea of both their productivity and financial performance. Of course, we are only scratching the surface on this post. Having the ability to use multiple data sources to generate reports opens up endless possibilities.