Below are the calculations to perform a simple variance analysis of discounts against plan in a healthcare setting. Selected cells are color coded to make the calculation easier to understand. The input cells reside in the range E3:F4.

In the example, the components of gross revenue are volume, payer mix, and acuity. The discount rate, by definition, includes the payer mix and acuity components. As a result, the "plug" is volume.

In Components of Healthcare Variances, five high-level components of revenue variance were identified. Those five components are fees, work days, pure volume, payer mix, and acuity. In Components of Healthcare Variances Part 2, it was shown that three of the five high-level components of revenue variance are contingent on volume. Of the three volume components, only the work day component is readily identifiable. This post will present a method for calculating the pure volume and payer mix components.

Before presenting the calculation it's important to understand the current state of Healthcare today. At a very high level and from a financial perspective there are only two types of patients. The first type of patient can be called a "Nominal" patient. Patients in the Nominal group generally carry insurance or pay cash for services performed by the healthcare provider. Each year healthcare providers can pass increases in their costs to the Nominal patients by raising fees. The cost side includes salaries and benefits for the healthcare provider's employees, general inflation, supply and drug costs, building costs, etc. The second type of patient can be called a "Government" patient. Government patients generally carry Medicare or other government provided healthcare plans. The problem with the Government group is that healthcare providers historically have not been able to pass their costs on to this group. Fee increases for Medicare must be approved annually by Congress. Historically the increases that are approved do not keep up with general inflation let alone increases in salaries for physicians, nurses, pharmacists, and other technical staff, general supply and drug costs, etc. As a result, many healthcare providers are trying to shift their practices away from lower reimbursing patients like the Medicare group to higher reimbursing Nominal patients just to stay in business. Shifts from Nominal to Government or Government to Nominal patients are refered to as changes in payer mix.

In the example below, the total volumes of procedures performed and workdays for the Nominal and Government payer groups are given. Those numbers are input into the yellow shaded cells.

The first step to calculating the pure volume and change in payer mix is to calculate the total volumes and the change in total volumes. The total volume can be calculated by simply adding together the Nominal and Government volumes. The change is simply the percentage difference in the two years i.e. 2007 vs. 2006. Those calculations are highlighted in green below.

Step 2 is to identify the "pure volume" change. Pure volume is the change in volume exclusive of any change in work days. In the example above, total volumes decrease by 0.3%. However, that change includes an increase of 0.4% in work days. As a result, the pure volume change must be something less than -0.3%. The cells shaded green below show the components of the calculation of pure volume.

The formula in cells H12 and K12 is:

=($N$8-$N$10)/(1+$N$10)

The calculation can be proven by the following:

Pure Volume Change + Work Day Change + (Pure Volume Change * Work Day Change) = Total Volume Change 

or

-0.7% + 0.4% + (-0.7% * 0.4%) = -0.3%

Step 3 is to calculate the payer mix percentages in 2006 and 2007 for each payer group. Using the Nominal group for 2007 as the first example, the components of the payer mix calculation as shown below in green:

The formula in cell F14 is:

=F8/$L8

Continuing step 3, use the Government group as an example. Again, the components of the payer mix calculation as shown below in green:

The formula in cell I14 is:

=I8/$L8

Moving on to step 4, calculate the percentage change in payer mix percentages. The components of these calculations are shown in green below:

The formula in cell H14 is:

=IF(ISERROR((F14-G14)/ABS(G14)),0,(F14-G14)/ABS(G14))

The formula in cell K14 is:

=IF(ISERROR((I14-J14)/ABS(J14)),0,(I14-J14)/ABS(J14))

Finally, step 5 computes the components of volume associated with each of the three areas of variance within each payer group. In other words, we can now calculate and assign volumes to the pure volume, work day, and payer mix components of variance within the Nominal and Medicare groups. In the screenshot below, match the colors in the variance analysis range F19:G25 against the percent changes above. The volumes associated with each component of change is a simple multiplication on the individual component of change.

Note how the change in payer mix (shown in row 24 - cells shaded green) nets to zero. By definition a change in mix only shifts volumes between groups. The net change of zero is exactly what to expect.

Now that the analysis is complete what does it reveal? Total volumes dropped by -0.3%. However, there was one additional work day in 2007 vs. 2006 which accounted for a volume increase of 0.4%. As a result, pure volume decrease by -0.7%. Finally, there was a favorable shift in payer mix. There were 1,045 units of volume that moved from the lower reimbursing Medicare group to the higher reimbursing Nominal group.

The post titled Components of Healthcare Revenue Variances identifies a series of high-level gross and net revenue components of change. Of the five components listed (fees, workdays, volume, payer mix, and acuity), three of the five components can be calculated using "pure volume".

The term "pure volume" is a result of how data flows across most revenue systems. Most revenue systems have the ability to report only three of the five components above - volume, acuity, and revenue. For example, if a patient is seen for a normal consultation in a clinic (prefessional) setting, the physician will bill a volume of one (for one consultation performed), an RVU (acuity factor) of 2.33 (a productivity factor relating to the time and complexity of the consultation performed), and revenue of $150 (the amount billed for the consultation performed). The work day and payer mix components are actually a subgroup of volume and must be calculated.

Below is a simple model that illustrates how those variance components might be broken out:

The yellow shaded cells are inputs. The five variance components are listed in column B. The actual components that you can get from a system are listed across in row two.

Column D shows that the volume component extracted from the revenue system can actually be broken down into three subcomponents - pure volume, work days, and payer mix.

In Healthcare financial analysis, it is very important to know who your new patients are vs. your existing patients. New patients are usually defined as a patient that has been seen in the current year but has not been seen for some time period before. For example, a new patient might be a patient that was seen this year (2007) but has not been seen for three years prior (2004, 2005, and 2006). Even if the patient was seen in December 2003, he would still be considered new if seen again in 2007 as per the definition.

Given this definition, the challenge becomes "given X amount of records, how do I write a formula or create a process to count new vs. established patients?" Fortunately Excel makes this easy via the use of pivot tables. For very large amounts of records, Microsoft Access can perform the same function via crosstab queries.

For example, you're boss asks you to quicky identify and count the new vs. established patients for 2007. His definition is that a new patient is one that has been seen at least once in 2007 but not seen for at least two years prior. In other words, a new patient would be one seen in 2007 but not in 2005 or 2006. You are provided a list of medical record numbers (patient numbers), the year in which they were seen, and the charge incurred. Below is that list:

Rather than trying to build formulas to break down the new vs. established patients, first create a pivot table on the entire data set. In practice, the data would probably include millions of records so Microsoft Access and a crosstab query would be the tool of choice. Keep the medical record numbers in the first column while pivoting the years across the columns. The Excel pivot table, or Access crosstab query, would look like this:

At this point it becomes easy to identify the new and established patients. The new patients are highlighted in yellow while the established patient are highlighted in green. As per the definition, the new patients incurred charges in 2007 but not for 2005 or 2006. Conversely, the established patients incurred charges in 2007 but also incurred charges in either 2005 or 2006.

At a very high-level, the components of healthcare gross and net revenue can be broken down into these areas:

• Fees - the amount charged for a service.
• Volume - the units of service charged.
• Work Days - the amount of days worked in a particular period.
• Payer Mix - the type of insurance reimbursement that a patient may present.
• Acuity - the level of complexity of a service performed.

Complicating the matter, the total change in actual Volume is made up of Pure Volume, Work Days, and Payer Mix.

Payer Mix can be broken down into five different areas:

• Commercial Payers - the amount reimbursed for a service by Commercial insurance payers.
• Contract Payers - the amount reimbursed for a service by payers who have contracted with the healthcare provider.
• Employees - the reimbursement received for employee provided healthcare services.
• Medicare - the reimbursement received for Medicare provided healthcare services.
• Other Government - the reimbursement received for Government provided healthcare services exclusive of Medicare.

Given the components above, there can be many variations of the types of budget and variance analysis that can be performed. In future posts, I'll show some of the Excel modeling and charting that I've used to analyze and explain these components.