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.

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

The use of conditional formatting is great for presenting data via worksheet cells in a "chart" form. A simple model is probably the best way to illustrate this concept. Assume you're working in a business with a high degree of seasonal variation. In this example, a clinic in Phoenix, Arizona currently runs three MRI scanners. The manager of the Radiology Department believes that they need another one but can't tell when. Volumes are high in the winter months because patients from the northern U.S. go to the southwest during the winter. The volumes taper off in the summer months because those same patients go back home to beat the desert heat. Each scanner can handle six exams per workday. However, downtime due to maintenance makes each scanner dependable at approximately 65% capacity. There are an average of 22 workdays per month and the expected annual exam growth is 3%.

Given the assumptions above, a simple model can be built that visually shows the seasonal variations.

By applying conditional formatting to the data table, you can easily see when the volumes exceed the average expected exams per month. The conditional formatting formula for the data table monthly spread looks like this:

The average annual expected volumes in column Q use the same conditional formatting formula except the formatting is set to red as opposed to tan.

The conditional formatting allows you to easily see that the demand for Radiology exams will exceed the capacity of the three machines starting in February of 2006. On an average annual basis, the total demand for exams will exceed the total machine capacity in 2010.

Given the visual that the conditional formatting provides, a more educated decision can be made concerning when to purchase the next machine.

When forecasting it's often useful to know what the compounded rate of change over a given period of time is for a selected variable. As an example, let's say a pharmacy dispenses 50ml of saline in period 1. The following periods it dispenses 57.17ml, 59.25ml, 64.23ml, 65.18ml, and 71.50ml. The actual rates of change for each period are 14.34%, 18.50%, 28.46%, 30.36%, and 43.00% respectively. What formula can you use to calculate a compounded rate of change which will in turn allow you to build a simple linear forecast of how much saline will be dispensed into the near future?

Simply put, the formula is:

((Last Period Data / First Period Data) ^ (1 / Number of Periods)) - 1 

Given the example above, the formula returns a compounded rate of change equaling 7.42%.

The compounded rate of change is calculated as:

(( 71.50 / 50.00 ) ^ ( 1 / 5)) - 1

The result can be proved out as follows:

Finally, a chart visually shows the actual growth (blue line) vs. the compounded growth (red line):

Given the compounded growth rate of 7.42%, the screenshot below shows how the rate is applied to the historical data to estimate two periods into the future (periods six and seven).

The chart has been modified to include the projection periods six and seven:

Please note that this is a very simplistic example that is useful for statistical forecasting only.

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.

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