Excel is the Skill You Need or Need to Improve

Learning Pivot Tables in Excel is Timely

It’s Time to Improve Your Excel Skills

Excel (or any spreadsheet program, try OpenOffice if you don’t have Excel) should be the go-to tool for any medical practice manager who is tasked with data analysis.

Examples of some of the data you should be analyzing in your practice:

  • What are my net collection percentages by payer?
  • Am I receiving reimbursement at cost plus for any vaccines and injectables I am supplying to patients?
  • Do I know the potential value of a contract offered by a payer or an Independent Physician Association?
  • What is the cost of adding a new physician, NP, PA or service line to my practice?

Your practice management system may already crunch numbers for you, but:

  1. Is it exactly the information you need?
  2. Is it in the format in which you need it?
  3. Is all the data I need to analyze found inside the practice management system?

What if you don’t trust the information coming from your practice management system? Many managers don’t. One of the first rules to data analysis is “Know What You Are Looking At”.  Are you confident that the data you received is the data you asked for? You may need a conversation with your practice management system support team to be sure you understand where the system is pulling data from and if it is the date you want.

A clear understanding of how your practice management system filters and reports your data is critical to producing INFORMATION. Data only has the potential to become information when it is accurate and actionable.

How to learn Excel or improve your Excel skills:

If you know only enough Excel to get by, Nate Moore’s series on Excel is a great place to start. Because he is in the healthcare field, his examples make sense. His videos (new ones regularly) are free here.

I first wrote about Massive Open Online Courses (MOOCs) back in 2013 here and the list of offerings just keeps growing. Coursera offers buckets of free courses, including courses on Excel like these:

For those of you that have the basics of Excel under your belt, proceed to learning about Pivot Tables. They will become your new best friend.

Big list of all kinds of free courses here.

12 Ways to Supercharge Your Practice in 2012: #1 Create a Practice Dashboard


Happy New Year!

What will your practice achieve in the coming year? Many people make resolutions to improve themselves when the new year rolls around, but what about your practice? With all the changes in the industry, it can be tempting to just “hang on tight” through all the speculation and uncertainty, but technology and strong leadership will allow the highest performing practices and groups to get ahead and cement their market position in trying times.

To help your practice be a leader in the market, Manage My Practice is presenting a series of 12 articles outlining strategies, (or “Resolutions”, if you will) to take your practice to the next level in the coming year. Look for the next article on Thursday, and share your practice’s resolutions, and ideas for 2012 below! Don’t want to miss a single article? Type your email address in the upper right-hand corner box and get the articles fresh off the presses into your inbox.


What is a Practice Dashboard?

You’ve probably heard the adage ‘You can’t manage what you can’t measure!” The Dashboard is a way to capture key pieces of data in your practice and demonstrate your management skills to your stakeholders.

A Practice Dashboard is a one-page look at the key indicators being monitored that are necessary for the practice to thrive financially. As an administrator, I have typically presented the Dashboard Report (sometimes called a Snapshot Report) to the physicians at the monthly meeting. The Dashboard keeps the physicians operating at a high level and usually keeps them from descending into the deep detail that can derail a monthly meeting like nobody’s business!

How can I create my own Practice Dashboard?

Some Practice Management (PM) Systems have a dashboard built into the system, but many don’t. It’s not too hard to create your own dashboard by running some reports and entering the data into your Dashboard on Excel, Word, or PowerPoint, or OpenOffice equivalents.

What should be included in my Practice Dashboard?

There are a couple of basics, then there are lots of extras from which you can select the measures that will be important to your group. Here are the basics:

  • Gross charges, collections and adjustments for the month and year-to-date (YTD) and the same month last year and YTD last year.

Bar Graph of an Example Portion of a Practice Dashboard Showing Gross Charges, Collections, and Adjustments

What is it?

These are the basic measures of the productivity of the month (charges), but keep in mind that the collections and adjustments are from work that was produced sometime between 15 days and 15 months ago. As long as you realize that, it is fine to consider collections and adjustments a “monthly measure.” If you have ancillaries, you will break these out separately – radiology, lab, allergy, sleep center, etc.

Where do I get it?

This information should come directly from your practice management month-end report, and once you’ve been producing your dashboard for awhile, you’ll be comparing previous years from your own reports. You may also collect the previous year’s information from old reports, some financial statements have this, some physician compensation reports have this information, and some PMs allow you to run this information in summary form from previous months.

  • Number of new patients and established patients this month and YTD and the same month last year and YTD last year.

Bar Graph of an Example Portion of a Practice Dashboard Showing the Breakdown of New and Established Patients

What is it?

It is the measure of your practice’s health. If you do not have any new patients coming to the practice, your practice will slowly die. Only physicians on a schedule to slow down and/or retire should close their practice to new patients. On the other hand, if you don’t have enough established patients coming to the practice, you may be in danger of doing too much phone medicine and not seeing patients face-to-face often enough. Depending on the specialty, you may have many new patients and few established (surgical specialties) and patients only need to return to see you annually or only if they have problems.

Where do I get it?

Hopefully your practice management system will calculate this information for you, but some systems still don’t. You may have to have staff complete a worksheet counting patients daily.

I also like to list the number of patients who left the practice, broken into a few categories such as “moving”, “changing doctors” and “declined to say” and of course “discharged from the practice.” Some practices also like to track # of deceased patients.

  • Aged Accounts Receivable  (typically written as A/R)

Bar Graph of an Example Portion of a Practice Dashboard Showing Accounts Recievable Breakdown by Age

What is it?

It is a breakdown of the % of dollars in standard categories (Current- less than 30 days, 31 – 60 days, 61 – 90 days, and 91 days and over) and the measurement of how long it takes for a service you’ve provided to be paid in full. For Medicare patients, you should receive payment 15 days from their receipt of a clean claim. Most states have prompt payment laws that require a clean claim to be paid in 30 days for commercial carriers. What ages monies past 60 or 90 days are patients on payment plans, and any services that payers have “under review” for payment. Also, if your claim is denied and you need to appeal it, or if your claim wasn’t clean to start with, most likely it will run into 60 or 90 days.

Where do I get it?

Every PM system will print out your Aged A/R as a part of your end-of-month routine, and hopefully, on-demand whenever you want it. Your aged A/R is a snapshot of the money that is owed to the practice at any moment in time, so it changes every time a service is provided and every time a patient or payer gives you a payment.

Another indicator that goes with your Aged A/R is your days in A/R.  Most practices strive for days of <30. This is an easy number to calculate.

  • Arrive at your average daily charges by dividing your charges for the last 3 months by 90.3 days.
  • Divide your total A/R  by your average daily charge.
  • If your average daily charge is $1,000 and your total A/R is $60,000, your Days in A/R is 60. It takes you 60 days to receive payment in full for a service.


  • Collection % – how much of what you can collect, do you collect?

One of the most talked about and least understood metrics is the collection %. There are two reasons it is misunderstood.

One is because most people talk about a monthly collection percentage, as if the money you collect this month relates to the charges you billed this month. Some of it does – the co-pays, deductibles and co-insurance definitely applies to this month’s services, but the insurance payments most often do not. This is how you can collect more than 100% in a month – by having lower charges and higher collections, you can achieve more than 100% collections! Not. Nope. No such thing.

Over the course of 12 months, it tends to even out, so you can use your annual collection % with a degree of confidence, but the very best way to know what you are collecting is to use a report that applies the payments (regardless of when they arrive) to the services in the month they were rendered. If your PM system doesn’t have this report, ask for it.

The second is because gross charges are not necessarily “real” numbers. Most practices set a retail fee schedule at a point to capture the most any payer will reimburse them. So, a practice could set its fees anywhere and never really expect to charge or to collect what I call “funny money.”

What is it?

Your collection percentage is your net collections divided by your net charges. Net collections = gross collections minus refunds. Net charges = charges minus any contractually obligated write-offs. Contractual write-offs are the difference between the practice fee schedule and the allowable fee schedule you’ve agreed to accept. A 95% or better collection rate is considered excellent, depending on what, if any, other write-offs you include with your contractual adjustments.

Where do you get it?

If you have only one category of write-offs (sometimes called adjustments) in your PM system, you are going to find it hard to calculate this percentage. You may want to introduce new write-off categories for 2012 and have your staff post contractual adjustments to one category and other types of adjustments to several other new categories. See my article here for in-depth information on creating write-off categories and managing write-offs.

If you have your contracted adjustments separated, follow the formula above to get your collection percentage. Some practices will use a rolling 12-month figure for their collection percentage (i.e. ignoring the most recent month, use the last 12 month’s cumulative total net collections divided by the total net charges.)


Here are optional dashboard items you can use:

  • New patient referral breakdown by type (referred by doctor, patient, employee, website, direct mail, TV, radio, yellow pages, etc.) We’re going to be talking more about this in one of our other “12 for 2012”, so stay tuned.
  • Cumulative money turned over to third-party collectors and money collected. I don’t think this is a great metric, because if your collection agency is collecting a lot for you – why weren’t you able to do it yourself?
  • Status of any loans (remaining principal) or line of credit outstanding
  • Sales and returns, if you are selling anything in the practice – medication, vitamins, supplements, books, beauty products, etc.
  • Appointments – % of appointments filled, % of no-shows, % of appointments booked the same day or the day before.
  • Money collected at check-in and check-out versus what should have been collected
  • What number makes your physicians crazy that you could measure and manage?


A few other suggestions for your Dashboard:

  • Don’t use so many metrics that you have to print the report extremely small or make it into a 3 or three page report. A Dashboard Report is one page. Control yourself.
  • Use a variety of charts and graphs to illustrate your key indicators – remember that many people find it difficult to absorb a lot of written or numeric information in a short amount of time, but using charts will make it easier.
  • Use color to give visual cues – if the areas that you are doing well are in green and the ones you are not doing well in are in red, it will help your audience to focus on the important numbers.
  • Make sure you know “why” before you introduce your report. Why are we doing better? Why are we doing worse? What can been done to improve the numbers?
  • Some physicians like to get their monthly Dashboard before the meeting so they have time to review it and jot down their questions. Some might also appreciate going over the Dashboard one-on-one with you before the meeting so they feel more in control of the situation.


Guest Author Frank Trew From DataPlus: Ten Ways to Improve Your Bottom Line by Analyzing the Data from Your Practice Management System

Editor’s Note: DataPlus is MMP’s very first sponsor and I want to thank Frank and his crew for their support!  If you would like to sponsor this blog and have over 10,000 readers a month see your flash ad, contact me via email at marypatwhaley@gmail.com.


The old saying “If you can’t measure it, you can’t improve it” certainly holds true in medical practices today. With falling payer reimbursement it is more important than ever to collect every single dollar your practice is due.

Most practices have sought additional income streams by adding ancillary services. Paying close attention to data can improve decision-making for such services and can dramatically improve revenue without adding any providers or even new patients!

Having ready access to the elusive data within practice management systems can be difficult, but most systems can report the basics.  It is imperative that data is trended over a period of time so that trends can be spotted, benchmarks compared, and improvement plans developed.  Measuring data and comparing it to the MGMA Cost Survey (find it at mgma.com) is one of the best places to start.

1. Collection Rates/Ratios: Two collection rates are measured in medical practices. One is gross collections and the other is net collections, the latter being the most important.

A gross collection rate is payments divided by charges and will depend on an artificial number – how high the charges are set above negotiated allowables – making it not particularly meaningful.

A net collection rate, however, provides a means to benchmark the health of collection efforts. Net collections, simply stated, demonstrate what percentage of collectible dollars (after negotiated contract write-offs)  a practice is actually collecting.   A net collection rate above 95 percent ”“when calculated correctly – denotes a healthy practice.

2.   Denials: Denials are a significant portion of the cost of running a practice in that services that are provided but not paid for reduce the profitably of those that are. Accurately identifying denials and the reasons for them can help prevent them in the future, thus increasing productivity and lowering expenses. Identifying denial trends by specific payer or payer group, by CPT code, and by origin ”“ whether at the front desk, with coding errors, or in credentialing ”“ is equally important.

3.    Evaluation & Management (E & M) Bell Curve: “Overcoding” and “undercoding” are commonly used terms, but how are they measured? Bell curve trending of E&M data can quickly identify areas where providers may be under coding, resulting in lower revenues, or over coding, resulting in the potential for audits. The difference between a Level 2 and a Level 3 E&M code can mean thousands of dollars in losses per provider per year. Documentation is critical to demonstrating the level of care provided to each patient.

The traditional primary care bell curve below demonstrates that level 3 visits typically comprise about 50% of your established patient encounters, level 2 and 4 visits together about 20% each, and level 1 and 5 visits together about 10%.  When plotted on a graph and drawing a line between each, the shape resembles a bell.

4.    Bad Debt: Bad debt is defined as dollars that could have been collected, but were not.  Break this category into controllable factors and non-controllable factors.  Issues that you should have been able to control are timely filing write-offs, credentialing errors, lack of follow-up, and incorrect information provided by the patient.  Non-controllable issues are bankruptcy,  patient failure to pay, and payers retroactively denying coverage due to unpaid premiums.

Reducing bad debt by just two percent can mean tens of thousands of dollars to the bottom line of a practice. The ability to quickly identify bad debt trends facilitates the development of an improvement plan.

5.    AR Days: AR (accounts receivable) days are a measurement of the average time a dollar stays in an accounts receivable before being collected. The ability to measure, benchmark, and lower AR days provides a means to a significant increase in revenue.  Some best practices that reduce AR days are filing insurance daily, sending statements daily, collecting appropriately at check-in and check-out, working denials quickly, discounting self-insured for time of service payment in full, and using an eligibility tool to check every single patient’s insurance.

6.   Encounters: Accurately reporting and separating encounters for most practices is an arduous task of counting fee tickets or using tick sheets. Few practice management systems accurately provide this information. An encounter is much more than a service code. Being able to segregate office encounters from surgical cases, and reporting by payer, time, and location can help identify opportunities for improvement.

7.   Referral Sources: It is fundamentally prudent for specialty practices to know the origin of patient referrals. This data is rarely reliable or easily created in most practice management systems. Practices need to know not only the source of patient referrals, but also what type of patients (by insurance, by procedure, etc.) are being sent by those sources, and if the referrals from a particular source have increased or decreased over time.

8.    Payer Mix: It is not uncommon for practices to drop payors due to perception, and not because of actual data or trends. Emotions sometimes come into play and can result in a provider demanding that a payer be dropped because their rates have changed (or other perceptions). This simply does not make sense. Being able to accurately produce and graph data on major payers without hours and hours of work is of high strategic value to a well-planned business decision. It can answer questions about the impact on a practice if a particular payer is dropped, or how those patient slots would be filled.  Remember to keep adding payers to the practice when feasible; the loss of your largest payer can be minimized if many smaller ones are on board.

9.    Under Payments: One of the more significant ways to improve a practice’s revenue is the swift and accurate identification of carrier underpayments. Identification of underpayments is not simply comparing the payment to an allowable fee schedule.  Practice management systems that have any type of payment audit functionality commonly do not take into account circumstances such as modifiers, or multiple surgical procedures that payers routinely inaccurately apply, causing underpayments.  Having a system to automatically and systematically apply these rules is essential. MGMA states that providers are underpaid an average of six percent of revenue. What does that mean to a practice? The numbers can be astounding to a surgical group, and the identification and collection of those underpayments can be insurmountable.

10.    Fee Schedule Comparison: It can be difficult to determine what payers are reimbursing by contract for specific codes or ranges of CPT codes. The ability to have immediate and accurate access to this data is crucial in payer negotiations. It is important to remember that the payer already has this information and is betting that the practice does not!

It is now more important than ever for practice managers to have access to the critical information outlined above. It is also important to note that not just any one of the above Key Practice Indicators should be used to determine the financial health of your practice, but all, or a combination of them.

The buzzword among practices today is “Dashboards.” The ability to have these Key Practice Indicators in one simple report is proven to increase efficiency, as well as provide a meaningful way to present information to providers. One example of a dashboard is below.

About the author: Frank Trew is the Founder and CEO of DataPlus and  has over 25 years of practice management experience and has served in executive positions in large and small practices. In 1999, as the COO of a large orthopaedic group in Nashville, he was frustrated by an inadequate access to data that limited his ability to measure and improve the bottom line. The development of a data warehouse was the solution.

In 2000, after hearing how this data was a key practice management tool, many of Frank’s peers also wanted to use it improve their practices. DataPlus was formed as a result and has been providing MegaWest, HealthPort, and Centricity users with this unique tool ever since.

Employing a simple to use “point and click, drag and drop” reporting tool, along with an advanced Contract Management and Revenue Recovery System, DataPlus provides key management data across all specialties and throughout the United States.

Frank invites readers to visit the DataPlus website at www.mydataplus.com. Frank may be contacted via email at ftrew@mydataplus.com or by telephone at (888) 688-3282.