Sign In Book a demo
Return Back

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

Variance analysis is one of your most powerful tools. Here’s why.

person
Charlie Liu

August 2, 2021 3 min read

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

In financial planning, variance is the comparison of two or more sets of data. It measures how far off KPIs are from what was expected. Variance analysis is the practice of analyzing the magnitude of these deviations and understanding why they happened.

Table of contents

Summary

This post explains variance analysis for Finance teams. We briefly look into its statistical foundation before describing how to implement it in popular FP&A use cases like budget vs. actuals analysis, price-volume variance analysis, and mix analysis. After that, we look at some options available to visualize this data. To illustrate the concepts and to help you apply them to your organization, we’ve also provided a template you can use.

We also cover some of the limitations and shortcomings of variance analysis, especially with conventional spreadsheets. One of those limitations is having up-to-date actuals from your accounting system, sales data from your CRM, and potentially other systems as well. This trips up and slows down many Finance teams.

Need a financial planning solution?

Without integration, the variances you measure will always be out of date, and the FP&A team will waste many cycles chasing the latest data, or reporting inaccurately. OnPlan helps Finance teams do variance reports in a snap (and much more!), while continuing to use Excel syntax to maintain total flexibility.

Book a Demo

What is Variance Analysis?

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

Simple enough. But calculating variance is a bit more complicated, and if you need a refresher on that, we’ve written a primer below on statistical variance in Excel. To go deeper, we recommend this free MIT course.

How do you do 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 wanted 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 want to 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:

If you only have a sample dataset, use the Exel function VAR.S instead. This 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. 

Standard deviation is the square root of variance, but Excel 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, for example, it is used to assess the distribution of the return of a portfolio; 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 a statistical measure across a sample or population, they’re talking about how far off financial KPIs are from what was expected. 

As we mentioned in the introduction, you 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/pessimistic sales assumptions, for example.

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 these 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?

There is a favorable variance when you have generated more revenue or incurred fewer costs than expected. Conversely, unfavorable variance is defined as generating less revenue and incurring costs above your budgeted amount.

However, the terminology can be a bit misleading, because not all unfavorable variances are bad: 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 both variance 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 you need to take to hit your goals. By comparing the budget you created with actual performance, you can get an understanding of where your assumptions weren’t right.

But it 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 -5% variance, you know you sold 5% less than you projected, but you don’t know why, or what (if anything) 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.

Consider the context:

  • What was driving your business’ 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 are inordinately more powerful and enable you to identify and address the root cause.

Unfortunately, many FP&A departments spend the lion’s share of their time focusing on forecasting and budgeting and, as a result, don’t get to the deeper questions nearly 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 the company, it is easier to correct and reduce future variances. The end game is your actual variance moving towards zero over time as you consistently assess driver inputs and outputs.

Budget vs actuals (BvA) variance analysis

BvA variance analysis is the most common use for variance analysis. In a BvA analysis, you focus on what you budgeted and compare that to actual costs and revenues. If this is the type of analysis you are looking for, you’re in luck: we’ve written a piece on that with a dedicated template you can use. 

It is one thing to know that your KPIs are higher or lower, but unless you’ve done a deep dive, the reasons for increases or decreases may not be so easily understood. The following two uses of variance analysis are more sophisticated. We’ll look at revenue generation and analyze the variance due to price, volume, and product mix. 

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 question: “how much did revenue change as a result of prices that were different than forecast? As a result of volume different than forecast?”

Price and volume variance analysis example & template

Using our template, we’ll show you how to do price and volume variance analysis with a realistic example.

We created our variance analysis template in Microsoft Excel and adapted it for Google Sheets as well. You can download the template here and replace the sample data with your own if you’d like.

Struggling to keep up with high volumes of data?

In practice, conducting regular variance analyses requires large amounts of up-to-date data from your financial, sales, and potentially other systems. Without a system that can integrate with these systems, your FP&A team will always be 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

Consider a fictitious company, ThreeMix, with three products (probably not a coincidence!) called Product 1, 2, and 3 (note: you can update these in the template to your own product and services names in the Input | Lists sheet.)

OnPlan, Variance Template, Calcs | Variance Sheet

In its budget, ThreeMix forecast 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 more units than forecast (35), and because they sold at a higher price ($228). 

Price and volume variance analysis answers the question: What is the breakdown of the additional $1,980 revenue? How much is due to volume increases, and how much to price increases? The image below visualizes the logic. 

Here’s what each color represents:

  • 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 change in volume)
  • Light blue is volume variance (volume change with no price change)
  • Orange is variances due to a combination of price and volume 

The variance due to price change is 

Effect due to price change = (Actual Price – Budget Price) x Actual volume

or

($228-$200) x 35 units = $980 

And the variance due to volume change is

Effect due to volume change = (Actual Volume – Budget Volume ) x Budget price

or

(35 units – 30 units) x $200 = $1,000

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

So what we can conclude is that Product 1 revenue was higher, and Price and Volume both share roughly 50% of the credit for the higher revenue. If ThreeMix was our company we’d then want to:

  • Understand if there is more scope for price increases. Since we’ve 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 product available? Was there something in the market driving higher natural demand from customers? Did our marketing program have unanticipated focus on Product 1?
  • Adjust our forecasts going forward. If internal factors like marketing execution drove higher volumes, we need tighter coordination with marketing to ensure we incorporate their plans into our forecasts. If external factors, we need to consider if 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. 

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.

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. A higher proportion of sales from a high-priced product will create a positive mix effect on revenue, for example.

When we add a mix variance analysis for ThreeMix, we find that the higher revenue is much 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 go 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 example in the 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

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

Column B calculates the actual volume sold across all products (120 units) at the budget mix level (20%), or 24 units for Product 1. 

OnPlan, Variance Template, Calcs | Variance Sheet

 

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:

  1. They add up to $1,000, or the volume variance we calculated earlier.
  2. The overall volume of all products went down from 150 to 120. Note below that the volume impact is negative for all Products. But the mix impact is positive overall, reflecting a shift from the lowest cost product (Product 2) toward higher cost products.
OnPlan, Variance Template, Calcs | Variance Sheet

Looking at ThreeMix’s offering holistically, products 1 and 3 have a positive mix effect and 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 with regards to pricing, as all three products have a positive price effect. This means that ThreeMix has room to increase prices across the board, even if volumes come down (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! We’d love to help or get your feedback. Book a demo or contact us at [email protected]

https://www.youtube.com/watch?v=lzL3CJOFx4s&list=PLcTJrNjeesqEQyNQDDRY0_EudDMzXxmMO&index=1

Visualizing Variance Analysis: How to Communicate Your Findings to Drive Action

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?

Option 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 at all. The bar-line combination chart is better, but it’s hard to tell which numbers are for what and where the line’s data point falls when more products are added into the mix and labels start overlapping.

Option 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 depicted above. But this chart also doesn’t communicate any conclusions about price, volume, or mix, or make obvious recommended actions like continued price increases.

Option 3: Waterfall Charts

Our verdict: The charts above serve the purpose of conducting a direct comparison between budget and actual figures. But when you require a complete insight into what’s driving the changes in revenue and you need to start asking questions like: “how is my product mix affecting my revenue?,” the best way to visualize this is a waterfall chart.

Our template dashboard includes a waterfall chart, where you’re able to analyze by product, product category, and product status (such as launched, discontinued, etc.) across monthly, quarterly, and annual timeframes. 

OnPlan, Variance Template, Dashboard 

Limitations and shortcomings

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. 

Analysis results come too late to make a difference in future actions 

The accounting staff most likely analyze the variances at the end of each month in your company. Your management team will then receive 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.

Variance analysis is labor-intensive

Experienced financial analysts can conduct relatively simple, streamlined variance analyses. It will quickly tell you what the variance is, but not necessarily why, without more digging. This is where the process can become time-consuming. This is a worthwhile investment only if your team time uses the “why” information effectively and efficiently,

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, what you will be comparing actuals to, is not helpful. It is therefore essential that the budgeting process follows best practice and is as close a depiction to what is planned as possible.

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 from a variance to the source data to spot what’s really going on.

In other words, with OnPlan, variance analysis can be a daily, continuous part of your financial management practice, and keep your company on plan.

***

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

Give your spreadsheets superpowers with OnPlan
  • The same Excel/Google syntax you're used to
  • Tailored dashboards for your key stakeholders
  • Scenario building that takes minutes
  • Streamlined Budget vs. Actuals analysis
Book a demo bg

Overcome Variance Analysis hurdles with OnPlan

You can book a demo with OnPlan by clicking here. Or simply use the button below.

Book a demo
bg
person

6

Publications
About Author
Charlie Liu

As the Director of Professional Services, Charlie is responsible for high fidelity CPM software implementations and educating clients on industry best practices. Charlie has over 10 Years of experience in the Corporate Performance Management industry across a wide range of responsibilities including professional services, product management, and presales engineering.

Show more