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).

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

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

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

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%

Thank you for visiting cellMatrix.net, a learning resource for Microsoft Excel. Launched in March 2007, the objective of the site is to provide a user-friendly Excel resource for beginning and intermediate Excel users.

Although this site is set up like a weblog it will probably operate more like a static website. That is, my goal is not to post daily or even regularly but only when I have original material.

I hope you'll find site useful.