Variance Analysis - Two Components

The analysis of variances is a very important part of identifying financial statement change. Variances can usually be identified in terms of components, or individual parts, that make up a whole. They could be measured when comparing two points-in-time or as a single point in time against a goal, or plan.

Depending on what you're measuring, there can be an infinite number of variance components that make up the total variance explanation. Most of the time you can apply the 80/20 rule to your explanation - that is 80% of the variance can usually be explained by several major components and the remaining 20% being explained by the remainder. I've found that in most cases if you can explain 80% - 90% of the variance that's usually adequate for management.

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

What is interesting about the calculation is that the percent changes on the volume and rate components are not additive. Section "C" provides that proof as well as how those percentage tie out to the total (see the green shaded cells).

Extract Unique Values in a Range

Depending on how your data is grouped there are a series of options available in Excel to capture unique values. You can do so with filters, formulas, and VBA.

The macro below captures unique values from a range that spans multiple rows and columns. In the example, the source range is "A1:B20" and the output range starts with cell "D1". The macro works because, by definition, the collection variable will only accept unique values.

Sub ListUnique()

    Dim Rng1 As Range
    Dim Rng2 As Range
    Dim Cell As Range
    Dim UniqueValues As New Collection

    Set Rng1 = Range("A1:B20")
    Set Rng2 = Range("D1")

    On Error Resume Next
    For Each Cell In Rng1
        UniqueValues.Add Cell.Value, CStr(Cell.Value)
    Next Cell

    For Each Item In UniqueValues
        Rng2.Value = Item
        Set Rng2 = Rng2.Offset(1, 0)
    Next Item

End Sub

Pivot Table Fill

By their nature pivot tables do not show rows that repeat. The pivot table shows the first unique row descriptor and then repeats blank cells until the next unique descriptor is shown. You might need to show all descriptor values - for example if you want to load the results of a pivot table into an ACCESS database.

If you first copy the pivot table as values, you can then apply the macro below to the descriptor range. The macro loops through the descriptor range and fills the empty cells with the descriptor values.

Sub PivotTableFill()
    Dim Rng As Range
    For Each Rng In Range("A1", "C100")
        If IsEmpty(Rng) = True Then
            Rng.Value = Rng.Offset(-1, 0).Value
        End If
    Next Rng
End Sub

Excel 2007 Changes

Below are selected results of a search regarding changes in Excel 2007 versus previous versions:

• Changes in Excel 2007 from Microsoft Technet
• Microsoft Office Excel from Wikipedia
• A Short Explanation of the Office 2007 Ribbon from Wikipedia
• Support for Macros in Excel 2007 from Directions on Microsoft
• A Visual of the Excel 2007 Add-Ins Tab from Directions on Microsoft

Percent Change

Calculate percent change with ABS function.

In my opinion, the calculation of percent change is one of the most misunderstood calculations in finance.  Most of the time the calculation is correct if both of the numbers compared are positive.  The problem arises when one or both of the numbers is negative.  If the denominator happens to be negative the calculation will be incorrect.  Unfortunately the calculation is so widely used that most people don’t realize there’s an error until it’s too late.

To insure that the sign in the denominator is always positive, apply Excel’s “ABS” function to the denominator.  If you make this a habit, your calculation of percent change will always be correct.

(a-b)/abs(b)

For example, if the price of gas rises from $2.50 / gallon to $2.75 / gallon, the price has risen 10%.

( $2.75 - $2.50 ) / abs ( $2.50 ) = 10%

Let’s assume there is a credit on the financial statement and the beginning balance of an account was -$5.  The next month the balance in the account is $10.  The percent change in the account between the two months is calculated as:

( $10 - (-$5)) / abs ( -$5 ) = 300%

You can modify the formula by adding an error trap to help insure that the results are reasonable.  For example, to show an “N/A” for any change greater than or less than 200% or when either of the values being compared are zero, apply the following formula

=IF(OR(B1=0,C1=0),"N/A",IF(ABS((B1-C1)/ABS(C1))>2,"N/A",(B1-C1)/ABS(C1)))