What is Percent Variance?
Percent variance is a measure of how much a value differs from a reference value, expressed as a percentage. It is often used to compare actual results with expected results, or to analyze changes over time.
The formula for percent variance is:
For example, if the actual sales revenue for a month is $120,000 and the expected sales revenue is $100,000, the percent variance is:
This means that the actual sales revenue is 20% higher than the expected sales revenue.
How to Calculate Percent Variance in Excel
To calculate percent variance in Excel, you can use the following steps:
- Enter the actual value and the reference value in two adjacent cells, such as A1 and B1.
- In another cell, such as C1, enter the formula:
=(A1-B1)/B1 - Format the cell as a percentage by clicking the Percent Style button on the Home tab, or by pressing Ctrl+Shift+5.
- The result will show the percent variance between the actual value and the reference value.
Example: Calculating Percent Variance for Monthly Sales Revenue
Suppose you have the following data for the monthly sales revenue of a company in 2023:
| Month | Actual Sales Revenue | Expected Sales Revenue |
|---|---|---|
| Jan | $120,000 | $100,000 |
| Feb | $110,000 | $105,000 |
| Mar | $115,000 | $110,000 |
| Apr | $125,000 | $115,000 |
| May | $130,000 | $120,000 |
| Jun | $135,000 | $125,000 |
To calculate the percent variance for each month, you can use the following steps:
- Enter the data in a worksheet, such as Sheet1, starting from cell A1.
- In cell C1, enter the formula:
=(B1-A1)/A1 - Copy the formula down to cell C6 by dragging the fill handle or by pressing Ctrl+D.
- Format the cells in column C as percentages by clicking the Percent Style button on the Home tab, or by pressing Ctrl+Shift+5.
- The result will show the percent variance for each month, as shown below:
| Month | Actual Sales Revenue | Expected Sales Revenue | Percent Variance |
|---|---|---|---|
| Jan | $120,000 | $100,000 | 20.00% |
| Feb | $110,000 | $105,000 | 4.76% |
| Mar | $115,000 | $110,000 | 4.55% |
| Apr | $125,000 | $115,000 | 8.70% |
| May | $130,000 | $120,000 | 8.33% |
| Jun | $135,000 | $125,000 | 8.00% |
Other Approaches to Calculate Percent Variance in Excel
There are other ways to calculate percent variance in Excel, such as using the VAR.P function, the VAR.S function, or the Analysis ToolPak add-in. However, these methods are more suitable for calculating the variance of a population or a sample, rather than the variance between two values. For more information, you can refer to the following links:
- VAR.P function
- VAR.S function
- Calculate variance using the Analysis ToolPak
