Smart Business Great Medicine: Bugs in the Spreadsheets

Analyzing the Business

Once you have seen your patients and managed your money, you’re then faced with the monthly meeting. For most practices, this includes a review of the financials. Unless you have an accounting degree, the spreadsheets often provide little truly actionable information. We must change this!

Sadly, most of us spend a great deal of time reviewing productivity reports. When the meeting starts, most physicians turn to the productivity reports, make sure the metrics they understand seem acceptable, and then tune out. Productivity reports are important but only when taken in context and analyzed in a meaningful way.

For years we followed this same pattern. We produced monthly productivity reports and numerous graphs. There was extensive supporting documentation including number of procedures performed, visit types, and even relative value unit (RVU) analysis. Lots of numbers, pretty graphs, and comparisons to prior reporting cycles are combined for a binder of information—unfortunately, these amount to little more than eye candy.

Although we felt like we were analyzing our practice, we wasted untold hours reviewing and considering the meaning of the numbers. Unfortunately, we weren’t analyzing the practice from a business perspective. In fact, the data told me little more than the profit and loss analysis when we need far more (Figure 1). As the practice of medicine is pushed into ever-decreasing margins, the importance of effective analysis has grown to paramount importance.

We Were Told There Would Be No Math!

Physicians are not trained in formal accounting techniques. Medical training does not prepare a physician to understand standard spreadsheets, much less develop and fully leverage financial data to fully analyze the business and lead us toward improved profitability. All too often, practice management is delegated to someone from the front office who has outshone others in the office. These individuals also have no formal training enabling them to perform even rudimentary financial analysis. In practices with trained business managers and office administrators, few have the needed combination of extensive accounting training, spreadsheet development expertise, business analytics background, and a true understanding of the practice of medicine. Neither the physicians nor the managers assisting them possess the skills sets to fully assess financial performance and create spreadsheets with the graphics needed to communicate performance to the rest of the business in an optimized fashion.

Consultants may be called in for an assessment. These groups typically focus on the coding and billing aspects of the practice. Although coding and billing metrics are certainly important, in isolation, they give only some of the information a practice needs to diagnose and treat problems. Consultations can become quite expensive. Other consulting groups can be hired to help start accounting practices or do one-time analyses. The consultants may be outstanding or poor and often lack true understanding of the health care environment. As a result, the recommendations from consultants often are followed for only a short time, if at all. The potential benefits, if any, can’t be fully realized. Either way, each round of consulting costs money. If you choose to bring in consultants, make sure they will provide meaningful practice enhancements that are simultaneously easily enacted, and sustainable for the long term.

Regaining Control or Gaining it for the First Time

First and foremost, we must have a system that enables us to understand and therefore, run the practice. We cannot be left in the position of the practice and numerous other external forces running us. We need integrated financial and operational spreadsheets that are easily understood and effectively show the health of the practice. These same spreadsheets must be easy to confirm. The data needs to be obtained and displayed in a manner allowing us to see possible embezzlement, areas of waste, and opportunities for both savings and growth.

Although every practice has specific issues, isolated to that practice, methods for monitoring financial health can be created with spreadsheets and graphics common to most practices. Once a monitoring system has been deployed, the practice can personalize the analysis with its own particular components.

What do we need the spreadsheets to accomplish? Before we build them, we need to fully understand both what we want and what we need. First, we need the spreadsheets to help us understand our performance from a financial perspective. This information can also provide perspective on organizational health and care delivery. We need the data to help us understand the problems we face. Careful analysis of available financial and operational data also helps identify the people we should trust and expose those we should not trust. Finally, truly effective spreadsheets should help us draw a map for how to get from where we are to where we want and need to be.

The financials must simultaneously be easy to use, easy to read, easy to understand, and very powerful. Furthermore, they must be able to be modified and run regularly without the cost of accountants and intuitive enough to be completed by someone with only basic accounting and bookkeeping skills. To optimize your financial security, it is also essential that the original data behind the spreadsheets can be easily confirmed.

The Basics

The initial financial report and spreadsheets need to present a simple overview of the practice in a quick and meaningful manner. The best presentation should include highly intuitive graphics with supporting spreadsheets.

Start with an analysis of physician productivity (Figure 2). Most of us have been conditioned to simply look at the total billing and the collections for the month. These are 2 numbers that should be presented, are important, and expected. The presentation shouldn’t stop there, however. Total billing and collections need to be put in context. Comparisons to the prior months, same month from the prior year, and rolling averages are helpful to monitor for trends. Even the basic package should include assessments of billing, collections, write-offs, days in accounts receivable, and total office expenses. Furthermore, these numbers should be presented as total per month and total per day worked. Further examples and downloadable spreadsheet components are available on our website, www.smartbusinessgreatmedicine.com.

Supporting documentation of the clinic overhead detailing office expenses from applicators to Z-packs must be available and coherent. Profit and loss statements should always be available with the ability to drill down into the fields to double-check entries such as rent payments, insurance payments, and tax payments. Examples of this process are also available on the website.

Furthermore, make sure the spreadsheets and supporting documentation are available at least one week before the monthly review. This will allow time for the information to be carefully reviewed to look for business trends and clues suggesting the possibility of embezzlement.

Going to the Next Level

Making the changes we are suggesting can enhance your understanding of the business health of the practice. This should be accomplished initially, but why stop there? It would be like lowering blood pressure and cholesterol levels, but never having the stent implanted.

Linking the billing and collections data from the productivity analysis is a start. More importantly, having the simple face spreadsheet so that you can drill down into the data is even more important. Being able to bore into the data is well within the scope of the typical practice management software, although it is not something commonly used by physicians themselves to monitor the health of the practice. However, even this is not the kind of analysis we need. We must be able to examine the data from total monthly billing down to the single visit level into the collections for that encounter and back out to the total collections and overhead (Figure 2).

Standard spreadsheets are very helpful and can track and display most of this data. Going to the next level requires leveraging business intelligence software and even machine learning algorithms. Sounds impressive. Sounds complicated. On a certain level, it is, but once the system has been created, its use is not that much different from standard productivity analyses. Just because something sounds challenging doesn’t mean we should skip it. This type of system can help you make far more intelligent business decisions while helping you guard against theft. Even the presence of these systems serves as a deterrent.

A Dashboard

An average practice could easily be losing several hundred thousand dollars a year by not really understanding the data their office generates. The good news is there are a wide variety of tools to manipulate and visualize the data (Figure 3). There are software packages that can be purchased but there are also applications that a medical practice already has or can free download. Microsoft Excel is a tremendous application for data analytics and visualization. One of its most powerful features are pivot tables which allow the user to easily extract and summarize large amounts of data very quickly. Another application is Microsoft Power BI which allows the user to easy create a dashboard (Figure 3)the summarizes data and can be updated in real time.

There are 3 ultimate goals of a medical practice financial dashboard: 1) operational dashboards provide information for front-line employees to manage the practice on a daily basis, 2) tactical dashboards provide weekly or monthly information that summarize information to highlight anomalies, opportunities, or problems so action can be taken, and 3) strategic dashboards are for the leadership of the practice to provide leading indicators, past performance, and prediction of expenses and revenue. It is important when designing a dashboard to clearly define the purpose and typical uses for the dashboard. Every dashboard needs to provide actionable information. A poorly designed dashboard is nothing more a pretty picture. A well designed dashboard can help you run your practice more efficiently.

The Game Plan

First and foremost, get started. From wherever you are on the spectrum, move to the next level. The cost of improving your spreadsheet and the analysis of the data acquired is low. Change is rarely easy, even for the person instigating the change. Resistance to change is a natural response, but it can also be a warning sign. Remain vigilant.

The general approach will require personalization for optimal outcome. Once you have achieved an effective system, the work is not done. There are always small things to work on and the system is always evolving. We must not only evolve with change, we need to try to anticipate change and make preparations for it.