These entries showed how to perform a variance analysis given two, three, or four components of variance:

• Variance Analysis - Two Components
• Variance Analysis - Three Components
• Variance Analysis - Four Components

The logic illustrated in these posts can be applied to annual and monthly forecasting models. The example below illustrates how one could build a monthly salary forecasting model given a salary, FTE, and paid day history and projection. The example assumes that the salaries and FTEs for the period spanning January 2007 through April 2007 are given. The task is to project salaries on a per-month basis given a 4% salary increase that occurs in October 2007, a 5% salary increase that occurs in August 2008, an increase of 1 paid day between 2007 and 2008, and an increase of 11 FTEs that occur between 2007 and 2008.

The cells shaded yellow provide inputs to the template. Of note is the trend in salary rate which is shown in the chart below. The white bars show the actual rate and the blue bars show the projected rate. The salary rate, given the FTE, paid day, and average salary calculations, proves to be a step-function as it moves from $1.10 to $1.15 to $1.20 based on when the salary increases occur.

In addition to salaries, models that forecast and spread revenue, discounts, benefits, supplies, services, and more can be created based on the logic in this template.

This next variation of the color-bars macro allows you to assign colors to the chart bars based on the background colors of selected cells. For example, assume the screenshot below:

The macro below loops through the department column and assigns a color to the bars based on that department.

Sub ColorBars()

    Application.ScreenUpdating = False

    Dim Rng As Range
    Dim Cnt As Integer
    Dim Color As Integer

    Cnt = 1

    For Each Rng In Range("B3:B10")
        Color = Rng.Interior.ColorIndex
        Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt)
        Pts.Interior.ColorIndex = Color
        Cnt = Cnt + 1
    Next Rng

End Sub

The final product looks like the chart below.

Recently at work I was asked to create a bar chart that showed various divisions grouped by departmental assigned colors. For example, there are four departments assigned - Internal Medicine, Surgery, Medicare Specialties, and Other. Within each of these departments are various divisions. The task was to show each division as a bar and color that bar according to what department they were assigned to.

This can be done fairly easily (and without code) using an individual column assigned to a department. The rows consist of divisions and the data is assigned to the cell where the division and department intersect. The problem with this approach is that the chart source consists of as many series as there are departments. It is also difficult to maintain the correct colored bars if the data needs to be sorted.

In an effort to keep things simple my thought was to use just one series of data and use a macro to color the bars associated with that series. As an example, the following screenshot shows a worksheet with three columns - department, division, and revenue.

The macro below loops through the department column and assigns a color to the bars based on that department.

Sub ColorBars()

    Application.ScreenUpdating = False

    Dim Rng As Range
    Dim Cnt As Integer

    Cnt = 1

    For Each Rng In Range("B3:B10")
        Set Pts = ActiveChart.SeriesCollection(1).Points(Cnt)
        If Rng.Value = "IM" Then
            Pts.Interior.ColorIndex = 24
        ElseIf Rng.Value = "SURG" Then
            Pts.Interior.ColorIndex = 15
        ElseIf Rng.Value = "MS" Then
            Pts.Interior.ColorIndex = 19
        ElseIf Rng.Value = "OTHER" Then
            Pts.Interior.ColorIndex = 35
        End If
        Cnt = Cnt + 1
    Next Rng
End Sub

The final product looks like the chart below.

A recent post to the Microsoft Excel Charting Discussion Group asks "how can I show pounds on the primary axis and kilograms on the secondary axis with tick marks that correspond to each other?" I'm thinking the chart should look something like the one below:

The solution requires calculating a primary and secondary axis that are proportional to each other. The illustration below shows those calculations:

Finally, I've found that the VBA procedure below is the easiest and most accurate method to set each axis. Add the procedure to a regular module, select the chart, and run the macro to set the axis.

Sub SetAxis()
    ActiveChart.Axes(xlValue).Select
    With ActiveChart.Axes(xlValue)
        .MaximumScale = Range("C26").Value
        .MinimumScale = Range("C28").Value
        .MajorUnit = Range("C29").Value
    End With
    ActiveChart.Axes(xlValue, xlSecondary).Select
    With ActiveChart.Axes(xlValue, xlSecondary)
        .MaximumScale = Range("D26").Value
        .MinimumScale = Range("D28").Value
        .MajorUnit = Range("D29").Value
    End With
    ActiveChart.Deselect
End Sub

You end up with four series when you really only want to show two. At this point double-click on a series. In the Format Data Series dialog box, you can hide two of the four series via the Patterns tab.

There may be times when you want to present a chart with labels aligned evenly on a horizontal plane. As an example, begin with the chart below:

The procedure below will loop through each point in the series and evenly position the data labels associated with those points.

Sub AlignDataLabels()
  Dim Cnt As Long
  Set Srs = ActiveChart.SeriesCollection(1)
  With Srs
    For Cnt = 1 To .Points.Count
        Srs.Points(Cnt).DataLabel.Top = 135
    Next
  End With
End Sub

The end-result looks like the chart below. All of the data labels are evenly positioned near the base of the bars.

Calculating the variances on four components of change are presented in this third installment of variance calculations.

In the example, how do you explain the increase in revenue versus plan given the change in volume, severity, and work days? In this example the change in volume, severity, work days, and rate make up four components, or 100%, of the variance. These components are calculated in section "A" of the template below.

As presented before, note that the percent changes on the volume, severity, work day and rate components are not additive. However, you can see a pattern in how the percent change reconciliation relates to the data in Section "A". Section "C" provides the proof as well as how those percentage tie out to the total (see the green shaded cells).