Recently the topic of primary and secondary axis scales was discussed at the PTS Blog. The discussion starts with a reference to Stephen Few's March 2008 Visual Business Intelligence Newsletter titled Dual-Scaled Axis in Graphs - Are They Ever the Best Solution . Stephen starts his article by concluding that He then goes on to cite a series of examples. Finally, at the end of his article he states that "I certainly cannot conclude, once and for all, that graphs with dual-scaled axes are never useful; only that I cannot think of a situation that warrants them in light of other, better solutions. I invite you to propose viable exceptions, which I will welcome with open arms."

From a healthcare finance perspecitive, it has been my experience that these charts are great tools for those who know how to build them and for those audiences that know how to interpret the underlying data. Below are several comments I have concerning Stephen's article:

Comment #1 - It's not necessarily the chart with two axes that's bad, it's the data behind it. In the newsletter, most of Stephen's graphs illustrate the relationship between revenue and units sold. I would argue that is comparison is flawed from the beginning and, as such, this type of chart should not be used. My reasoning is that, although it seems like the data is should be related, when you drill down into the data you might find out that the correlation is not as close as you might have thought. Depending on the data:

• What defines a unit?
• Are the units the same unit each quarter or are there multiple types of units being charted as one?
• If there are multiple units, how are the changes in volume accounted for within the sales mix?
• Won't price increases distort the data over time?
• How does the chart account for decreases in purchasing power over time i.e inflation over time?
• If the data is measured by quarter and one of the quarters includes a leap year, how do you account for the extra day?

In my opinion, I think an argument can be made that a dual axis chart showing revenue and units sold is probably not the the best use of a dual-axes chart. That being said I do have to admit that I do use this type comparison at a top-level where the components of variance might be considered immaterial. If a dual-axes chart is to be used the audience should be educated on how to interpret the underlying data and how to recognize and address possible flaws before decisions a made.

Comment #2 - If you are going to present a dual-axis chart, the axes need to be proportional. That means doing the math for each axes to make sure it is in fact proportional to the other.

Comment #3 - As Stephen observed, line graphs are the best presentation for this type of chart.

Comment #4 - "I can’t think of a single case when there isn’t a better solution than a graph with a dual-scaled axis." In my line of work, we've found that these graphs are very useful for auditing calculations. For example, you are tasked with building a projection of revenue and discounts for the next year. In a healthcare environment, revenue and discounts both contain the same components of variance i.e. work days, fee increases, volume changes, payer mix changes, and changes in service mix or acuity. The calculations the build all of these components can get very complex. Rather than attemping to proof each calculation that builds the projection, this chart can quickly pick up areas of possible error.

In the example below, it's obvious that there's problem with June calculations because the lines are not in sync for that month.

Comparing the growth in same-type volumes (for example Radiology vs. Laboratory volumes) is another example.

To conclude, there may in fact be better charts for displaying certain types of data. But if the audience understands the relationships between the data I think there is a place for these charts. And like the example above shows, there's most definitely a place for these charts when performing audits.

To add images of embedded charts to this weblog, I use the VBA procedure below. To run the procedure, first make sure the specifications are correct. You can make changes to the chart height, width, file name, and file path. When all of the inputs are correct, click on (activate) the embedded chart and run the macro.

Please note that the height and width are not really needed. I've added this code only because I like to make sure all of the charts are exactly the same size.

Gross revenue can be simply defined as charges billed. Net revenue, on the other hand, is more reflective of the revenue you expect to actually receive. In a healthcare environment, the spread between gross and net revenue can be fairly significant depending on who pays the bill.

A bar chart provides a simple visual comparison of gross vs. net revenue. Although the stacked bar chart initially seemed like the best choice to me, over time I've found that a regular bar chart with the bar overlap set to 100% works well too.

In this example, the goal is to chart the change in gross and net revenue in the Commercial / Contract and Government areas for the annual periods 2006 and 2007. Below is how I've set up the data:

The Chart Wizard produces the following:

The next task is to overlay the bars by setting the overlap to 100%. To do so, go to the Format Data Series dialog box - Options tab and change the overlap to 100%.

Finally, with a series of minor formatting changes the chart looks like this:

The chart shows the following:

• The Government area is above the blue line and the Commercial / Contract area is below the blue line.
• The Commercial / Contract revenue grew while the Government gross revenue showed no increase and reimbursement decreased.
• The Commerical / Contract reimbursement is significantly higher than the Government reimbursement. As a result, the Commercial / Contract area looks much more attractive from a financial perspective.

Recently I've had to use formulas that perform left-sided lookups at work. The combination of Index and Match in a formula will do so, but I always seem to have a hard time remembering how to build the formula. Recently I put together this color-coded example to help:

The formula in cell C8 looks like this:

or

If you need to report statistics or financial data in a standard template each month, consider using Excel as a simple database and the SUMPRODUCT function within formulas as the engine that drives the reporting. For example, many financial reports include the following standard data elements:

• Current month and year-to-date results.
• The variance of current month and year-to-date results against a plan.
• Current month and year-to-date results measured against the same month last year and year-to-date.

As soon as the data is compiled, many analysts enter the data into a spreadsheet. They then change the formulas in the report to reference the data that needs to appear for the most current month. Given the manual changes to formulas, the risk of error is increased.

Rather than recreating formula references each month, the spreadsheet can be set to work much more efficiently. By changing the month in one cell and the year in another, the SUMPRODUCT function when used within formulas can automatically update the entire report.

The following example is based on two sheets in a workbook:

• The first sheet, called "dBase", contains the data needed to feed the report.
• The second sheet, called "Rpt", contains the actual report. It also contains two cells that allow the user to update the report by changing the year and the month.

Beginning the example, the screenshot below is a very simple example of the data that might be needed for the typical financial statement. The worksheet in which the data resides is called "dBase":

The report below contains the elements as described in the bullets above. The worksheet in which the report resides is called "Rpt".

SUMPRODUCT formulas are included in the formulas within the report. Those formulas act as month and year-to-date lookups.

To build the SUMPRODUCT formulas, I've first created two named ranges. The first named range, called "Year", covers the range B4:B29 of the dBase sheet. The named range dialog box for the formula named "Year" looks like this:

The second named range, called "Month", covers the range C4:C29 of the dBase sheet. The named range dialog box for the formula named "Month" looks like this:

Now it's time to build the report. The SUMPRODUCT function within formulas looks up the report data based on the entries into cells E4 (the Year) and E5 (the Month). The formulas are as follows:

To conclude, this setup allows allows the user to enter data into the dBase sheet. The user can then automatically update the report for any month and year via inputs to the month (cell E4) and year (cell E5) inputs in the Rpt sheet.

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.

Many companies produce reports at a top-level, department, division, and/or cost center level. When Excel is used to build these reports, the normal practice seems to be to create a new sheet for each department, division, and/or cost center. Each sheet contains the data and calculations for that area. A single top-level reporting sheet is then created to which each department, division, and/or cost center sheet links to. When it's all over and done the workbook can become very large, prone to error, and difficult to audit.

An easier and more accurate approach is to use the combination of lookup formulas and a VBA loop to build the report. In this example, only four sheets in the Excel file are illustrated (it could be only one if designed that way). The first sheet, called "dBase", contains the raw data for each department, division, and/or cost center. The second sheet, called "Calcs", contains a single set of calculations needed to build the final report. The third sheet, called "Load", contains a summary of the data from the Calcs sheet needed for the final report. The final sheet, called "Report", is the final report.

The example begins with a screenshot of the sheet called "dBase" (for database). The "dBase" sheet contains ten departments. The Commerical / Contract and Government volumes for each department is provided for the years 2007 and 2008. The final report requires that you show the components of payer mix variance for each department.

The next screenshot shows the sheet called "Calcs". The blue cell (C2) contains the name of the department. The yellow cells contain lookup formulas that reference the "dbase" sheet.

For example, the lookup formula in cell E8 is:

The lookup formula in cell F8 is:

The next screenshot shows the sheet called "Load". The purpose of this sheet is to reference all of the values that need to appear in the final report into one row of data.

The data that appears in the "Load" sheet is referenced to the green cells in the "Calcs" sheet.

Finally, a VBA procedure is run to create the report. A loop is created that, when combined with the lookup formulas, does the following:

• The VBA procedure loads the name of the department into cell C2 of the "calcs" sheet.
• The lookup formulas in the calcs sheet reference the data from the "dBase" sheet for that particular department.
• The payer mix calculations are performed.
• The payer mix data is referenced from the "calcs" sheet into row of data in the "Load" sheet.
• The data from the "Load" sheet is copied as values into the final report.

In this example, the procedure described above is performed ten times - once for each department as referenced in the range B8:B17 of the "Report" sheet. The VBA code that performs the work is below:

After the macro has been run, the final report looks like this:

The advantages of using lookup formulas and VBA loops to build the report are these:

• Only one set of calculations (the "Calcs" sheet) needs to be audited for accuracy. This is especially advantageous as the number of departments, division, etc. needed on the final report grows.
• The audit risk concerning the workbook is reduced due to less sheets and data ranges needing to be proofed.
• The physical size of the workbook is reduced which make it easier to maintain and transfer to others via email, web, etc.
• The VBA procedure runs fast and the report is produced almost instantaneously.

Potential disadvantages of using lookup formulas and VBA loops to build the report are these:

• If you need to pass the workbook on to someone that has no experience with VBA, the issue of who can maintain the workbook may come up.
• If your manager is clueless when it comes to lookups or VBA, he or she may prefer the "many sheets and calculations" approach so they can understand it (runs hand-in-hand with the first bullet).

Below are interesting items from around the web:

The PTS Blog recently illustrated three VBA techniques for chart formatting:

• VBA Conditional Formatting Charts by Series Name
• VBA Conditional Formatting of Charts by Value
• VBA Conditional Formatting of Charts by Series Label

Process Trends provides an interesting writeup on how to create Cycle Charts. Also included is a downloadable template with VBA code that automatically creates these charts.

Andy Pope recently provided a method for building Conditional Line Charts Using Formulas:

The Excel Team Blog provides a writeup on chart templates for Office 2007:

Have you seen those star raters and wonder how they work? Chris Curtis provides a link to a CSS Based Star Ranking System: