In my opinion, many Finance and Accounting textbooks present the mechanics of variance analysis as being way too complicated. The analysis is usually presented as a series of different formulas depending on which textbook you read. Although at a detailed level the formulas are the same, algebraically they're presented in different manners which can lead to confusion. Below are three examples from the textbooks:

Example 1:

Example 2:

Example 3:

Why can't it be as easy as this?

First calculate the actual vs. standard total sales (cells E9 and F9). Second, calculate the percentage change between the actuals and standards (cells G9 - G11). Finally, perform the analysis as presented in the yellow highlighted cells.

In the example below, total actual vs. standard sales increased $20,000. There are two components of variance -> price / unit (rate) and quantity. At the component level, the actual vs. standard price / unit (rate) is identified as going up 20%. The actual vs. standard quantity is identified as going up by 50%.

The advantages of using the last method are:

• Intuitively it makes more sense because the presentation is simple. You can easily see that if the price / unit increases 20%, the dollars component of variance needs to reflect that increase. Likewise, if the quantity component increases 50% the dollars component needs to reflect it.
• You don't need to remember formulas.
• The method can be easily applied to any number of variances. For example, in the Healthcare industry gross revenue variances may include five or more components of variance (work days, fees, volume, payer mix, acuity, etc.). If remembering the formulas for two or three components of variance is difficult, try memorizing the formulas for a five or more components.

In Combining Chart Types, Adding a Second Axis, Katherine Fifer of the Excel Team Blog describes in detail how to create the chart using Excel 2007.

It's been my experience that understanding the embedded chart object model can be difficult. Understanding how to declare variables based on that model can be even more difficult. Below is a guide that I refer to when attempting to isolate selected parts of the embedded chart object model. The guide uses a simple embedded line chart as an example. Each example can be put in a standard code module.

To name an embedded chart, first activate the chart (click on it once) and then run the following:

Sub NEC()
    ActiveChart.Parent.Name = "Cht1"
End Sub

The example below declares the variable called "Cht" as a ChartObject. To select an embedded chart:

Sub SEC()
    Dim ChtObj As ChartObject
    Set ChtObj = ActiveSheet.ChartObjects("Cht1")
    ChtObj.Select
End Sub

The following three examples isolate the first series for an embedded line chart:

Example 1 - declares the variable called "Cht" as a ChartObject. To select the source values for series one in an embedded line chart:

Sub SeriesEx1()
    Dim ChtObt As ChartObject
    Set ChtObj = ActiveSheet.ChartObjects("Cht1")
    ChtObj.Chart.SeriesCollection(1).Values = Range("A1:A5")
End Sub

Example 2 - declares the variable called "Cht" as a Chart. To select the source values for series one in an embedded line chart:

Sub SeriesEx2()
    Dim Cht As Chart
    Set Cht = ActiveSheet.ChartObjects("Cht1").Chart
    Cht.SeriesCollection(1).Values = Range("A1:A5")
End Sub

Example 3 - declares the variable called "Srs" as a SeriesCollection. To select the source values for series one in an embedded line chart:

Sub SeriesEx3()
    Dim Srs As SeriesCollection
    Set Srs = ActiveSheet.ChartObjects("Cht1").Chart.SeriesCollection
    Srs(1).Values = Range("A1:A5")
End Sub

The three examples above show that the object model is built in a hierarchy, or levels, with ChartObject at the highest level i.e. ChartObject -> Chart -> SeriesCollection -> an so on.

The next two examples attempt to isolate the points for the first series for an embedded line chart. However, it's confusing because the first example refers to the series object while the second refers to the points object. When the macros are run, their results will appear in the immediate window of the visual basic editor.

Example 1 - declares the variable called "Srs" as a series object. To capture the value for the first point in series one in an embedded line chart (refers to the series object):

Sub PointsEx1()
    Dim Srs As Series
    Set Srs = ActiveSheet.ChartObjects("Cht1").Chart.SeriesCollection(1)
    Debug.Print Srs.Values(1)
End Sub

Example 2 - declares the variable called "Pts" as a points object. To count the number of points in series one for an embedded line chart (refers to the points object):

Sub PointsEx2()
    Dim Pts As Points
    Set Pts = ActiveSheet.ChartObjects("Cht1").Chart.SeriesCollection(1).Points
    Debug.Print Pts.Count
End Sub

Although not the same, the modeling for many of the other embedded chart types (column, bar, etc.) works very similar to the above.

Last weekend I spent several hours searching Excel's Help and the Internet trying to come up with a procedure to reverse the items in a one-dimensional array. I was using an array as the source for a listbox load and the loop below to load the items into the listbox:

For Each Item In Array1
     frmTrace.ListBox1.AddItem Item
Next Item

Unfortunately this routine loads the data from low to high instead of high to low. After spinning my wheels, I posted my problem to the Microsoft Programming Newsgroup and it was solved within hours. I received three responses and all three worked. Below is the simplest correction:

For Each Item In Array1
     frmTrace.ListBox1.AddItem Item, 0
Next Item

Thanks to those in the Programming Newsgroup who helped me with this.

A post sometime ago to the Microsoft Excel Charting Discussion Group asks . . . how can I automatically flag duplicate values on an XY scatter chart with a different colored point? My thought was to write a procedure that identified the duplicates via a helper column in the worksheet, accessed the chart, and then recolored the duplicate points. Assuming an XY scatter chart based on the values in the first and second columns, I added a third helper column. The helper column concatenates the X and Y values.

I renamed the chart "Cht1". Assuming a single X and Y series, the code below flags the duplicates. In the example, the marker size for each duplicated point is increased to 10 and the color of the marker is switched from blue to green.

Sub FlayXYDups()

    On Error Resume Next
    Dim Rng As Range
    Dim Cell As Range
    Dim Cnt As Integer
    Dim UniqueValues As New Collection
    Dim ErrColor As Integer
    Dim Cht As ChartObject

    Set Cht = ActiveSheet.ChartObjects("Cht1")

    ErrColor = 10

    Cnt = 0

    For Each Rng In Range("D3:D20")
        Cnt = Cnt + 1
        UniqueValues.Add Rng.Value, CStr(Rng.Value)
        If Err.Number = 457 Then
            With Cht.Chart.SeriesCollection(1).Points(Cnt)
                .MarkerSize = 10
                .MarkerForegroundColorIndex = ErrColor
                .MarkerBackgroundColorIndex = ErrColor
            End With
        Rng.Interior.ColorIndex = ErrColor
        End If
        Err.Number = 0
    Next Rng

End Sub

The chart showing duplicated values looks like this:

The procedure also flags the duplicates on the spreadsheet.

Below are a series of formulas to help forecast the remainder of a year given partial annual results and total annual forecast number. In this first example, two years of history are provided (2006 and 2007). The numbers for January through June are known. A 4% annual growth is projected for 2007 to 2008. The known data is in black font and the predicted data is in red.

The known data is in black font and the predicted data is in red. The formula in cell C9 is:

=SUM(C9:D9)/SUM($C$9:$D$14)*($E$15-SUM($E$3:$E$8))

It is copied through the range C9:C14. To predict 2009, the formula in cell F3 is:

=AVERAGE(C3:E3)/AVERAGE($C$15:$E$15)*$F$15

It is copied through the range F3:F14.

The same kind of logic can be used to predict monthly averages based on an annual average. As with the first example, two years of history are provided (2006 and 2007). The numbers for January through June are known. A 3% annual growth is projected for 2007 to 2008. The known data is in black font and the predicted data is in red.

The known data is in black font and the predicted data is in red. The formula in cell C9 is:

=SUM(C9:D9)/SUM($C$9:$D$14)*($E$15*12-SUM($E$3:$E$8))

It is copied through the range C9:C14. As with the first example, to predict 2009 the formula in cell F3 is:

=AVERAGE(C3:E3)/AVERAGE($C$15:$E$15)*$F$15