Over time I've found that the interpretation of percent change is one of the most simple and misunderstood calculations in finance. As an example, take the following data:

The formula in cell C3 is

=(B3-B4)/B4

This is the formula that I've found most people use. The formula works great when you're comparing positive numbers. However, a problem occurs when you bring in the negatives. Looking at cell C11, is it correct to say that the increase from -45 to -35 is -22.2%? If -35 is a larger number than -45, how can the increase be a negative?

If you look at all numbers, positive and negative, as being on a single scale the calculation is easier to see. Now we'll take the same data above and apply a slightly different formula:

The formula in cell C3 is

=(B3-B4)/(ABS)B4

When you look at column C, all of the percent increases are positive percentages. Likewise, when you look at column D all of the percent decreases are negative percentages. Positive percentages correspond to moving up the number scale and negative percentages correspond to moving down the number scale.

To conclude, the formula that includes Excel's ABS function is the correct formula. The ABS function insures that the denominator in the formula is always positive which allows the formula to return the correct percentage change. If you use this formula and think of the number scale, you'll never have to worry about the calculation of percent change and / or how to explain it to those that may not understand how to interpret it.

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.

Sub ExportChart()

    Dim Cht As ChartObject
    Dim Path As String
    Dim FileName As String
    
    Set Cht = ActiveChart.Parent

    Cht.Height = 210
    Cht.Width = 336
    FileName = "2008032301.gif"
    Path = "C:\Program Files\"
    
    ActiveChart.Export Path & FileName

End Sub

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:

=INDEX(Range_of_Values_To_Look_Up,MATCH(Look_Up_Value,Range_of_Look_Up_Values,0),1)

or

=INDEX(B2:B6,MATCH(B8,D2:D6,0),1)

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.