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:

=VLOOKUP($C$2,dBase!$A$3:$E$12,2,FALSE)

The lookup formula in cell F8 is:

=VLOOKUP($C$2,dBase!$A$3:$E$12,4,FALSE)

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:

Sub BuildReport()
    Application.ScreenUpdating = False
    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Rng3 As Range
    Dim Rng4 As Range
    Set Rng2 = Sheets("Calc").Range("C2")
    Set Rng3 = Sheets("Load").Range("B3:G3")
    Set Rng4 = Sheets("Rpt").Range("C8:H8")
    For Each Rng1 In Sheets("Rpt").Range("B8:B17")
        Rng2.Value = Rng1.Value
        Rng3.Copy
        Rng4.PasteSpecial xlPasteValues
        Set Rng4 = Rng4.Offset(1, 0)
    Next Rng1
    Sheets("Rpt").Range("A1").Select
End Sub

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:

Pie charts, as well as many other charts, can be built to show percentages of a whole. Unfortunately, many times the pie chart percentages do not tie in total to the total of the source document percentages. Microsoft acknowledges the problem via this knowledge base article titled Pie Chart Shows Incorrect Percentage Value in Excel.

As a potential work-around, I attempted to create a source document formula that works similar to the logic described in the knowledge base article. The purpose of the formula is to force the rounded percentages in the source report to always tie to the pie chart percentages.

Refer to the screenshot below:

A source range, named "Data", covers the range C4:C10. Column D reflects the rounded source document percentages. When added together, the percentages equal 101%.

Column E contains the alternative percentages that tie to the pie chart. The formula, entered as a multicell-array into the range E4:E10, is as follows:

{=IF((Data-ROW()/10^10)=LARGE((Data-ROW()/10^10),1),
1-SUMPRODUCT(((Data-ROW()/10^10)<>LARGE((Data-ROW()/10^10),1))
*(ROUND(Data/$C$11,2))),ROUND(Data/$C$11,2))}

The formula works essentially as the knowledge base article describes. It:

• Uses the ROW function to first break any ties that may occur when the same number appears two or more times.
• Using the numbers calculated by the ROW function, it then uses the LARGE function to determain which percentage is the maximum.
• If the percentage is the maximum, it subtracts the total of all of the the percentages excluding the maximum from 100%. All other percentages are calculated and rounded as normal.

In the example, the formula recognizes the rounded value for Gamma as the maximum. Because the data does not add to 100%, the formula subtracts one from the Gamma value to force the percentages to round to 100%. Likewise, the percentages in the pie chart show the same values.

To test the formula, I used the RANDBETWEEN function and a macro to quickly test 10,000 random scenarios. The formula appeared to work well with values that round in total to 99% or 101%. However, when values round to 98% or 102%, I did find occurances where the formula returns values that differ from the pie chart. In those cases Excel does not seem to assign the difference to one single value. Rather, Excel appears to pick two of the same values and distribute the difference evenly. Given the logic as described in the knowledge base article, I'm at a loss to explain why.

Below is an example where the pie chart percentages differ from the formula:

The formula calculates Beta to be the maximum and subtracts two from it to force the total to equal 100%. However, Excel subtracts one from Beta and Delta to get to 100%.

From Microsoft - Knowledge Base Article 213644:

In Microsoft Excel, you can set the Minimum and Maximum properties of the y-axis scale to any value. The following macro sets the minimum and maximum values for the y-axis scale in a chart to the maximum and minimum values of the data used to create the chart.

Sub SetScaleToMinAndMaxValues()
    Dim ValuesArray(), SeriesValues As Variant
    Dim Ctr As Integer, TotCtr As Integer
    With ActiveChart
        For Each X In .SeriesCollection
            SeriesValues = X.Values
            ReDim Preserve ValuesArray(1 To TotCtr + UBound(SeriesValues))
            For Ctr = 1 To UBound(SeriesValues)
                ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
            Next
            TotCtr = TotCtr + UBound(SeriesValues)
        Next
        .Axes(xlValue).MinimumScaleIsAuto = True
        .Axes(xlValue).MaximumScaleIsAuto = True
        .Axes(xlValue).MinimumScale = Application.Min(ValuesArray)
        .Axes(xlValue).MaximumScale = Application.Max(ValuesArray)
    End With
End Sub

It you need to use "=NA()" as a source value, the macro above fails. However, you can modify the macro to allow the array to accept only numeric values. To do so, modify the For-Next procedure as follows:

For Ctr = 1 To UBound(SeriesValues)
    If IsNumeric(SeriesValues(Ctr)) Then
        ValuesArray(Ctr + TotCtr) = SeriesValues(Ctr)
    End If
Next

Jon Peltier provides an excellent example of how to create waterfall charts that cross the x axis on his Peltier Technical Services site. These charts are especially useful for illustrating the components of variance.

Adapting Jon's waterfall charting examples to show the components of variance in a healthcare environment (for Hospitals, Clinics, etc.) requires an extra step. Normally, a positive variance would be shown with a green bar moving up and a negative variance would be shown with a red bar moving down. In a healthcare setting there's an extra twist. A positive variance can be shown with a green bar moving up or down. Likewise, a negative variance can be shown with a red bar moving up or down. Given these additional requirements, the formulas that drive the variance bars must be modified to reflect these types of changes.

To further explain, revenue provided by commercial and contract payers allows a greater reimbursement than revenue provided by government payers. As a result, an increase in the revenue provided by a commerical and contract payer would be favorable. A decrease in the revenue provided by a government payer is also favorable. Conversely, a decrease in revenue provided by commercial and contract payers may be viewed as unfavorable while an increase in revenue provided by government payers would also be viewed as unfavorable.

Below is an example of how a healthcare waterfall chart might look:

To allow for the changes in color if the variance is positive or negative, I've added a "Yes" or "No" color switch to the range C29:C33. Rather than the values determining if the color of the bar is positive or negative, the Yes-No switch allows the user to determine whether a change in a bar is positive or negative.

The example shows an overall decrease in volume. However, although the decrease in volume is normally viewed as negative, the reimbursement associated with the decrease may actually increase. The increase in reimbursement is a result of the positive commerical and contract variance combined with a negative government variance. To summarize, even though the volumes are decreasing the net revenue per unit of volume increases which allows for the possibility of greater profitability.

Since the example is somewhat complex, rather than explaining how each of the formulas work I've provided a link to the example file below.

I've recently updated the Formula Trace Tool utility to include a few new features. Behind the scenes, the formula auditing code has been streamlined to run more efficiently. If interested, please visit the Downloads page for more information.