Variance analysis formula: What it is and how to do it (with Excel template)

A variance analysis in finance is the comparison of two or more sets of data. It measures how far off a company’s KPIs are from what was expected. To measure the magnitude of these deviations and understand why they happened, FP&A teams use a variance analysis.

Our guide to variance analysis for Finance teams covers how variance analysis is calculated using a formula for variance analysis. We also teach you how to implement a variance analysis using popular FP&A use cases, like budget vs. actuals analysis, price-volume variance analysis, and mix analysis. 

We also compare some of the ways to visualize this data, and we illustrate concepts to help you apply them to your organization. Finally, we explain some of the limitations and shortcomings of variance analysis, especially when using conventional spreadsheets rather than FP&A software tools.

Table of contents

• What is variance analysis?
• How to calculate statistical variance analysis in Excel
• What is variance in FP&A?
• What are favorable and unfavorable variances?
• How do you perform variance analysis?
• Popular uses for variance analysis
• 3 ways to visualize data in variance analysis
• Limitations of variance analysis
• Conclusion: Conduct continuous variance analyses with OnPlan

What is variance analysis?

As the name suggests, variance is a measure of variability. In statistics, it’s a measure of the distance of a set of data points around their arithmetic average, or the mean. The further the distance, the greater the variance.

It sounds simple enough, but calculating variance is a bit more complicated. If you need a refresher, read our primer below on statistical variance in Excel. To go deeper, we recommend the free MIT course, Statistical Thinking and Data Analysis.

How to calculate statistical variance analysis in Excel

Calculating statistical variance depends on whether you’re working with a data set that is complete or one that’s just a sample. A complete dataset is called the “population.” 

For example, let’s say you want to calculate the variance of standardized test scores for all the test takers in a given year. If you have data on everyone, you can calculate the population variance. On the other hand, maybe you just have data for a sample of 1,000 test takers. In that case, you’d use a different formula for sample variance.

In Excel, the VAR.P function (VAR.P(number1,[number2],…) calculates the variance if the range of values represents the entire population. Excel uses the following formula to calculate it:

 Population variance = Σ(xi – μ)2 / N where:

• Σ = The sum
• xi = The ith value in the dataset
• μ = The population mean
• N = The total number of observations

If you only have a sample dataset, use the Exel function VAR.S instead. The Excel formula’s syntax is the same, but the statistical formula is ever so slightly different, for esoteric statistical reasons.

Another function that might be useful is VARA, which also calculates the sample variance, but includes text values and logicals in its reference. Excel has a variance formula with standard deviation, which is the square root of the variance, and it includes dedicated functions for standard deviation—STDEVP for a whole population dataset and STDEV for a sample dataset.

Statistical 2variance forms the backbone of theoretical frameworks in Corporate Finance and Investment. In modern portfolio theory (MPT), for example, it’s used to assess the distribution of the return of a portfolio, where the mean can be thought of as the expected return and the variance as the risk.

What is variance in FP&A?

When FP&A pros talk about variance, they’re also concerned about the distance between values from a central point. But rather than referring to a statistical measure across a sample or population, they’re talking about how far off financial KPIs are from what was expected. 

Finance professionals draw up financial budgets and operating plans before a reporting period, usually a fiscal or calendar quarter or year. These budgets are forward-looking and typically fixed. As the period plays out, actual performance will vary compared to your projections because of changing economic conditions, accounting errors, or overly optimistic or pessimistic sales assumptions.

These static budgets usually represent the base case scenario that a company uses to benchmark expenses and revenues. As the financial period progresses, management needs to understand how actual performance compares to what they assumed in the expense budget and revenue forecasts.

The difference between the planned and actual numbers is variance, and it’s crucial to minimize it, especially if you’re a startup or small business.

What are favorable and unfavorable variances?

A variance analysis can expose both favorable and unfavorable variances. When your company generates more revenue or incurs fewer costs than expected, it’s considered a favorable variance. Conversely, unfavorable variance happens when a company generates less revenue and incurs higher costs than the budgeted amount. However, this terminology can be a bit misleading.

Not all unfavorable variances are bad because spending more than planned in one area may create a favorable variance elsewhere. For example, spending more than expected on preventive maintenance might make sense if it extends the life of a significant fixed asset on your balance sheet. 

It’s also important to note that the conventional variance formula for revenue variance differs from expense variance. The math is rudimentary but subtle.

Here are the expense and revenue variance analysis formulas:

• Revenue Variance = Actual – Budget
• Expense Variance = Budget – Actual

How do you perform variance analysis?

Business goals are future-focused. Budgets take these long-term plans and divide them into digestible chunks by considering them yearly, quarterly, and monthly. Creating a budget gives you insights into the actions needed to hit your goals.

By comparing the budget you created with actual performance, you can better understand where your assumptions were or weren’t right.

However, a variance analysis alone doesn’t tell you everything you need to know to course correct. For that, you need to dig deeper than the quantitative answer. For example, if a half-yearly revenue figure has a negative five percent variance, you know you sold five percent less than you projected. However, the calculation itself can’t tell you why this happened or what to change so it doesn’t happen again. 

Just like a check engine light tells you to check with your mechanic but doesn’t tell you the exact issue, that variance is just an alert telling you where to start asking questions.

When analyzing your variance calculations, ask yourself contextual questions, such as:

• What was driving your revenue during that period?
• Were product sales down due to a change in consumer preference?
• Did a marketing campaign not result in as many leads as you expected?

These insights can be powerful and they enable you to identify and address the root cause or missed projections.

Unfortunately, many FP&A departments spend the lion’s share of their time focusing on forecasting and budgeting and don’t get to the deeper questions as often as they should. Analyses call for a more rigorous approach that includes everyone involved in any aspect of FP&A—from analysts to C-suite executives—to answer the “why.” The strongest finance teams closely collaborate with the rest of the business so they can learn fast, iterate, and pivot.

Once you ingrain this practice into standard business operations, it’s easier to correct and reduce future variances. The end game is your actual variance moving toward zero over time as you consistently assess driver inputs and outputs.

Popular uses for variance analysis

A variance analysis is a useful financial analytics practice in a variety of applications. Below we review three of the common ways financial professionals use variance analysis to help gain insights into financial performance.

1. Budget vs. actuals (BvA) variance analysis

BvA variance analysis is the most common use for variance analysis. In a BvA analysis, you look at what you budgeted and compare that to actual costs and revenues. If you’re interested in conducting this type of analysis, read our guide to budget variance analysis and download our budget vs. actuals template.

A BvA variance analysis can easily tell you whether your KPIs are higher or lower than benchmarked. But unless you’ve done a deep dive, the reasons for increases or decreases may not be so easy to understand. The next two uses of variance analysis are more sophisticated and can help you glean insight into these more complex questions.

2. Price and volume variance analysis

Price and volume variance analysis looks at revenue performance and breaks it into price and volume categories. It allows you to answer the questions of how much did revenue change as a result of prices or volume that were different than forecast? 

Using our price and volume variance analysis template, you can learn how to do price and volume variance analysis with a realistic example. We’ve also adapted our variance analysis Excel template for Google Sheets. 

Consider a fictitious company, ThreeMix, with three products called Product 1, 2, and 3. You can update these in the template using your own product and service names in the Input | Lists sheet.

In its budget, ThreeMix forecasted Product 1 sales of 30 units at $200 each, for revenue of $6,000. Actual Product 1 revenue recorded that month was $7,980 both because ThreeMix sold five more units than forecast and because they sold for $28 more each than budget.

Using a price and volume variance analysis, the FP&A team can decipher how much of the additional $1,980 revenue is due to volume increases and how much to price increases.

The image below visualizes the logic. 

Below is the meaning of each color:

• Green is the budgeted revenue—budgeted price x budget volume
• All colors together are actual revenue—actual price x actual volume
• Dark blue is revenue from price variance—price change with no volume change
• Light blue is volume variance—volume change with no price change
• Orange is variances due to a combination of price and volume 

To calculate the variance due to price, follow the below formula:

• Effect due to price change = (Actual Price – Budget Price) x Actual volume
• Using the example numbers: ($228-$200) x 35 units = $980 

To calculate the variance due to volume change, use this formula:

• Effect due to volume change = (Actual Volume – Budget Volume ) x Budget price
• Using the example numbers: (35 units – 30 units) x $200 = $1,000

If you’re reading carefully, you’ll notice we lumped the intersection in orange in with the price change in dark blue for simplicity.

Based on the example, we can conclude that price and volume both share roughly 50 percent of the credit for the higher revenue of Product 1. 

If we were the Finance managers at ThreeMix, we’d then want to take the following steps:

• Understand the scope for further price increases: Since we were able to increase price and volume at the same time, it seems the market is telling us there’s plenty of demand for Product 1. Increasing prices adds directly to the gross margin since there are no additional costs.
• Understand why volume exceeded expectations: Did our competition not have sufficient product available? Was there something in the market driving higher natural demand from customers? Did our marketing program have an unanticipated focus on Product 1?
• Adjust our forecasts going forward: If internal factors like marketing execution drove higher volumes, we need tighter coordination with the marketing department to ensure we incorporate their plans into our forecasts. If external factors were at play, we need to consider whether we expect those conditions to continue.
• Consider upstream impacts on production and distribution: It’s great to sell higher than anticipated volumes, but if you don’t adjust your production plans, you might find yourself with market demand you can’t fulfill. 

A price and volume variance analysis allows you to delve deeper into your revenue figures. Even from this simple example, you can see how to extract data that quantifies your pricing, promotion, and production strategies.

In practice, conducting regular variance analyses requires large amounts of up-to-date data from your financial, sales, and other systems. Without a system that can integrate with these systems, your FP&A team will continue working with out-of-date data, wasting time updating it, and delivering inaccurate insights. 

OnPlan enables you to model in real-time via live synchronization with many popular systems while using the Excel syntax you are accustomed to. Book a demo today.

3. Mix variance analysis

Mix variance analysis adds another dimension to conducting variance analysis. It splits volume variance effects into two parts—the part due to a change in the proportion of one product relative to all products and the part due purely to volume changes holding the proportion fixed. 

A mix variance occurs because a company does not sell each of its products and services at the same price. For example, a higher proportion of sales from a high-priced product will create a positive mix effect on revenue.

Returning to ThreeMix as our example, when we perform a mix variance analysis, we find that the higher revenue is more due to mix than to volume directly. If one product’s price is above the weighted-average price, and the share of sales from that product goes up, there will be a positive mix variance on revenue.

All else equal, ThreeMix should focus more on such products going forward. A price and volume variance analysis alone wouldn’t uncover this insight.

Let’s dig back into the BvA template to see this in action.

Adding mix into the analysis isn’t straightforward—it complicates how we calculate the volume effect. 

In the Calcs|Variance sheet of the template, we used the formulas below to calculate the volume variance and mix variance. You use the former to measure the difference between the actual quantity sold and the budgeted amount you expected to sell. The latter measures the difference in unit volumes in the actual sales mix from the planned sales mix. 

• Effect due to volume = (Actual volume at the Budget Mix – Budget Volume) x Budget Price
• Effect due to mix = (Actual Volume – Actual Volume at the Budget Mix) x Budget Price

Next, we calculate the mix of each product in the portfolio for budgeted and actual volume. In Product 1’s case, the budget mix is 20 percent (30 units/150 units) and the actual mix is 29.2 percent (35 units/120 units).

Column B calculates the actual volume sold across all products, which is 120 units, at the budget mix level of 20 percent,  or 24 units for Product 1. 

The volume revenue variance is the difference between column A and column B multiplied by the budget price. The mix variance is the difference between column B and column C multiplied by the budget price.

For product 1:

• Volume variance effect = (24 units – 30 units) x 200 = ($1,200)
• Mix variance effect = (35 – 24) x 200 = $2,200

These numbers make sense intuitively for two reasons:

1. They add up to $1,000, or the volume variance we calculated earlier.
2. The overall volume of all products decreased from 150 to 120. 

Note that though the volume impact is negative for all Products, the mix impact is positive overall, reflecting a shift from the lowest-cost product (Product 2) toward higher-cost products.

Looking at ThreeMix’s offering holistically, Products 1 and 3 have a positive mix effect. Even if the company leaves price and volume unchanged, it will generate more revenue from selling more of these products. 

Moreover, they can further optimize their strategy, specifically regarding price, as all three products have a positive price effect. Therefore, ThreeMix has room to increase prices across the board, even if volumes decrease, as happened in Product 2, because enough customers are willing to pay a higher price that revenues will grow.  

Variance analyses like these can also help you be more proactive with your performance management. For example, if you invest in marketing to improve the mix, it can be used as a KPI to evaluate performance and set a predetermined target.

There is no one-size-fits-all approach to conducting variance analysis and you might want to add other dimensions to it to see which effects you should focus on. If our template doesn’t meet your variance analysis requirements, reach out to us for assistance or send feedback to [email protected]

3 ways to visualize data in a variance analysis

Now that we’ve analyzed the data, we need to decide how to best communicate our conclusions. Which charts can tell the story most clearly to our executive team?

The following are three different ways you can visualize variance analysis data and our verdict on the pros and cons of each.

1. Bar graphs

Our verdict: A line chart is great for showing and comparing trends, but this chart doesn’t explain what’s driving the differences. The bar-line combination chart is better, but it’s hard to tell which numbers represent what. It’s also difficult to make sense of where the line’s data point falls when more products are added into the mix and labels start overlapping.

2. Side-by-side comparisons using Power BI

Our verdict: Power BI, and the third-party add-ons that make use of its infrastructure, work fine when handling simple relationships between tables in a financial model, such as the one in the image above. However, the side-by-side comparison chart doesn’t communicate any conclusions about price, volume, or mix, or make obvious recommended actions like continued price increases.

3. Waterfall charts

Our verdict: The charts above serve the purpose of conducting a direct comparison between the budgeted and actual figures. However, when you need complete insight into what’s driving the revenue changes, a waterfall chart is the best way to visualize this data. Waterfall charts clearly reveal the answer to the important question, “how is my product mix affecting my revenue?”

Our BvA variance analysis template dashboard includes a waterfall chart. You can use it to analyze your data by product, product category, and product status, such as launched or discontinued, across monthly, quarterly, and annual timeframes. 

Limitations of variance analysis

As with all data and analysis, variance analysis is only practical if you use the analysis to course correct. There are three primary barriers to watch out for when using variance analysis. 

1. Analysis results arrive too late

The accounting staff of your company most likely analyze the variances at the end of each month. Your management team then receives the analyzed actual results. While it may not be possible to speed up this process, your accounting team can flag any concerning findings before you finalize the formal reporting. In many companies, that feedback loop needs to be shorter for the data to be more meaningful and useful for course correcting.

2. Variance analysis is labor-intensive

Experienced financial analysts can conduct relatively simple, streamlined variance analyses. They can quickly tell you what the variance is but not necessarily why without more digging. This is where the process can become time-consuming.

A variance analysis is a worthwhile investment only if your team uses the “why” information effectively and efficiently.

3. Budgets aren’t always realistic

Finally, budgets are not always the result of an objective, technical process. Often the budget is a function of competing interests, compromise, and company politics. When this is the case, the figures you compare actuals to are not helpful. Therefore, it’s essential that the budgeting process follows best practices and is as close of a depiction as possible of what is planned.

Conduct continuous variance analyses with OnPlan

With OnPlan, you can overcome all these hurdles. Variance analysis is built-in and immediate, and integrations to your financial and sales systems ensure that actuals are always up to date. Building budgets that tie in sales, marketing, and product operational plans is easy, so your forecasts are realistic in the first place.

Variance analyses can be as detailed as your financial models, and you can easily drill down from a variance to the source data to spot what’s really going on.

With OnPlan, you can perform variance analysis as a daily, continuous part of your financial management practice and keep your company on plan.

Want to dive deeper into essential FP&A topics? Check out OnPlan’s most popular posts of all time here:

• SaaS profit margins: how to calculate them and what are the benchmarks?
• An Introductory guide to rolling forecasts 
• Sales forecasting in Excel: How to do It + free template download
• How to use a headcount planning model to determine your next office location
• Best practices for building a remote-first workforce planning model
• Bookings vs billings vs revenue: What’s the difference?
• What does the SaaS magic number mean for business growth?
• How CFOs can prepare for the Great Resignation

Sources:

1. https://ocw.mit.edu/courses/15-075j-statistical-thinking-and-data-analysis-fall-2011/
2. https://www.investopedia.com/terms/m/modernportfoliotheory.asp