The following chart-related add-ins were recently updated:

• Chart Patterns Fill Add-In - AJP Excel Information
• Change Series Formula Add-In - Peltier Technical Services
• Box and Whisker Plots Add-In - Peltier Technical Services
• Waterfall Charts Add-In - Peltier Technical Services

Each of these add-ins is available as a free download via the links above.

Updates to selected sites with RSS feeds are available via the RSS Updates page of this site.

Excel's "Add Trendline" option automatically provides an equation for the Linear Regression option. The model below shows how you can manually create that equation if needed. Also included is a calculation for correlation.

The formula for the slope (cell F18) is:

=((D13*G13)-(E13*F13))/(D13*H13-E16)

The formula for the Y intercept (cell F19) is:

=(F13-F18*E13)/D13

Although not a part of the linear regression calculations, you might find it useful to know how to manually calculate the correlation of the X and Y data. The formula to calculation correlation (cell F20) is:

=(((D13*G13)-(E13*F13))/SQRT((D13*H13-E16)*(D13*I13-F16)))^2

A chart showing the original points and trendline is below:

The Excel-generated trendline formula appears at the top right side of the chart. The manually calculated trendline in the example starts in cell F26 and is copied through the range F26:F33. That formula is below:

=$F$18*E26+$F$19

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.

Today I've added an additional category to this weblog. The category, titled "Healthcare Analysis", will contain entries showing how to utilize Excel to perform financial analysis and budgeting in a Healthcare environment. All of these entries are based on real on-the-job requirements and requests.

To frame the discussion, I've worked for a worldwide Healthcare provider in the Phoenix / Scottsdale area for about ten years. It is a combined Clinic and Hospital system with approximately 4,000 employees in Arizona and 42,000 worldwide. As a result many of the examples will be very high-level.

This simple procedure loads the values in the range A1:A5 into an array. The Debug.Print statement prints the contents of the array in the Immedicate Window of Visual Basic Editor.

Sub LoadArrayFromRange()

    Dim arrRange()
    Dim Rng As Range

    x = 0

    For Each Rng In Range("A1:A5")
        ReDim Preserve arrRange(x)
        arrRange(x) = Rng.Value
        x = x + 1
    Next Rng

    For Each Item In arrRange
        Debug.Print Item
    Next

End Sub

I'm not sure where I found this so unfortunately I can't credit the original author.

One of my responsibilities at work is to produce a monthly statistics package and top-level commentary. A single spreadsheet serves as a data repository and there are approximately 50 charts based on differing data that we use to visually present our monthly results. All of the charts are designed to be dynamic. By dynamic I mean that they automatically update as statistics are entered into the spreadsheet. The "upgrade" to dynamic charting vs. manual updates has probably saved at least one to two days of work as well as decreased the chance of error.

One of the easiest charts to automate is an actual vs. plan chart. The chart below is an example of a simple actual vs. plan dynamic chart:

The data source for the chart is below:

The monthly data is entered into the yellow shaded range C4:C15.

The chart is composed of three different chart types - a simple Line chart, an Area chart, and an XY Scatter chart. The actual data in the range F4:F15 is the base of the Line chart. The formula in cell F4 is below:

=IF(C4,C4,NA())

This formula is copied into each cell in the range F4:F15. The chart source data - series dialog box for the actual numbers looks like this (note that the example is on Sheet2 of the spreadsheet):

The plan numbers in the range G4:G15 simply refer to the numbers in the range D4:D15. The plan data uses the Area chart type. The chart source data - series dialog box for the plan numbers looks like this:

The next task is to automate the positioning of the data labels. In this case, we want the data labels to reflect the actual and plan numbers for the current month. The XY Scatter chart is used for data labeling positioning. Two XY Scatter points are used to track the actual and plan data points for the current month. The first XY data point reflects the actual month number and is based on the range J5:J6. Formulas are used to automatically update the data labels as new data is entered for the current month. The formula in cell J5 (X Axis point) is:

=COUNT($C$4:$C$15)

The formula in cell J6 (Y Axis point) is:

=OFFSET($C$3,COUNT($C$4:$C$15),0)

The chart source data - series dialog box for the actual XY Scatter point numbers looks like this:

Finally, the second XY data point reflects the plan month number and is based on the range J10:J11. As with the actual data labels, formulas are used to automatically update the data labels as new data is entered for the current month. The formula in cell J10 (X Axis point) is:

=COUNT($C$4:$C$15)

The formula in cell J11 (Y Axis point) is:

=OFFSET($D$3,COUNT($C$4:$C$15),0)

The chart source data - series dialog box for the plan XY Scatter point numbers looks like this:

In another variation of the chart above, the plan is shown going into the future only. The chart is made up of a Line chart with two series and an XY Scatter chart with two data label points. Below is an example:

The data source for the chart is below:

In this example, all formulas and chart sources are the same except for the formulas in range G4:G15. The formula in cell G4 is:

=IF(COUNT($C$4:C4)=COUNT($C$4:$C$15),D4,IF(C4,NA(),D4))

This formula is copied into each cell in the range G4:G15.

What's nice about using dynamic techniques such as these is that so many people don't. If you're lucky enough to work for one of these people, they'll still think that it takes more time than it really does to complete the work. As a result, set your email to send something to him or her every 1/2 hour and take the rest of the afternoon off. Unfortunately, once the secret's out they'll want everything now.