Course: Advanced Excel
Topic: Performing Variance and Percentage Analysis
Course Link: Advanced Excel Training – JustAcademy
Demo Link: Register for a Course Demo
Website: JustAcademy Official Website
Variance and percentage analysis are important techniques in Advanced Excel used to compare actual business performance with planned, budgeted, or previous-period results. They help businesses identify differences in revenue, expenses, profit, sales, production, and other performance indicators.
Variance analysis measures the numerical difference between two values. Percentage analysis expresses a difference or a value as a percentage, making it easier to understand the relative size of a change.
For example, if a company expected monthly sales of ₹200,000 but achieved ₹240,000, the sales variance is ₹40,000 and the percentage variance is 20%. This indicates that actual sales were 20% higher than the planned amount.
Excel makes this analysis efficient by using formulas, cell references, conditional formatting, charts, and PivotTables to analyze large datasets and communicate business performance clearly.
Variance analysis is the process of comparing two values to determine the difference between them. In business reporting, the comparison is commonly between actual results and budgeted or expected results.
The difference can reveal whether a business performed above or below its target. However, a positive variance is not always favorable: higher revenue may be desirable, while higher expenses may be undesirable.
Variance = Actual Value − Budgeted Value
For example:
The positive variance indicates that sales exceeded the budget by ₹20,000.
| Variance Type | Description | Example |
|---|---|---|
| Sales Variance | Difference between actual and budgeted sales. | Actual sales are higher than planned sales. |
| Revenue Variance | Difference between actual and expected revenue. | Revenue increases due to greater demand. |
| Expense Variance | Difference between actual and budgeted expenses. | Travel expenses exceed the budget. |
| Profit Variance | Difference between actual and planned profit. | Profit falls below the monthly target. |
| Production Variance | Difference between actual and planned production output. | Production exceeds the target quantity. |
| Period-over-Period Variance | Difference between results from two periods. | October sales compared with September sales. |
Percentage analysis expresses a value as a proportion of another value or measures the relative change between two values. It is useful when comparing departments, products, months, budgets, or business units of different sizes.
For example, an increase of ₹10,000 may be significant for a small department but relatively minor for a large department. Percentage analysis provides additional context for evaluating the change.
Percentage Variance = (Actual − Budgeted) ÷ Budgeted × 100
Example:
Actual revenue was 25% higher than budgeted revenue.
Before applying formulas, organize the data into clearly labeled columns. A consistent structure helps reduce formula errors and makes reports easier to maintain.
| Column | Heading | Purpose |
|---|---|---|
| A | Department | Identifies the business department. |
| B | Budgeted Sales | Shows the planned sales value. |
| C | Actual Sales | Shows the achieved sales value. |
| D | Sales Variance | Calculates the difference between actual and budgeted sales. |
| E | Variance % | Calculates the percentage difference. |
Enter the column headings in row 1 and your department data in rows 2 onward.
Suppose budgeted sales are in cell B2 and actual sales are in cell C2.
=C2-B2This formula subtracts budgeted sales from actual sales.
Example:
| Budgeted Sales | Actual Sales | Variance |
|---|---|---|
| ₹50,000 | ₹60,000 | ₹10,000 |
| ₹80,000 | ₹70,000 | -₹10,000 |
| ₹100,000 | ₹100,000 | ₹0 |
A positive value means actual sales exceeded budgeted sales. A negative value means actual sales fell below budget. A zero variance means actual and budgeted sales were equal.
=C2-B2 in cell D2.Percentage variance shows the difference relative to the budgeted amount.
=IFERROR((C2-B2)/B2,0)Use this formula in E2 when B2 contains the budget and C2 contains the actual value. Then format E2 as Percentage and copy the formula down the column.
The formula calculates the relative variance. The IFERROR function returns zero if the calculation produces an error, such as division by zero. In a formal financial report, you may prefer to display "N/A" or "Not meaningful" when the budget is zero rather than showing 0%.
| Budget | Actual | Variance | Percentage Variance |
|---|---|---|---|
| ₹50,000 | ₹60,000 | ₹10,000 | 20% |
| ₹80,000 | ₹70,000 | -₹10,000 | -12.5% |
| ₹100,000 | ₹120,000 | ₹20,000 | 20% |
| ₹40,000 | ₹40,000 | ₹0 | 0% |
Format percentage results using the Home tab's Percentage Style button or press Ctrl+Shift+% in Windows Excel. Adjust decimal places when greater precision is needed.
Variance signs alone do not determine whether a result is good or bad. Interpretation depends on what is being measured.
| Metric | Positive Variance Usually Means | Negative Variance Usually Means |
|---|---|---|
| Revenue | Actual revenue exceeded budget. | Actual revenue fell below budget. |
| Sales Volume | More units sold than planned. | Fewer units sold than planned. |
| Expenses | Actual expenses exceeded budget, often unfavorable. | Actual expenses were below budget, often favorable. |
| Profit | Actual profit exceeded the target. | Actual profit fell below the target. |
| Production Cost per Unit | Actual unit cost exceeded the budgeted unit cost, often unfavorable. | Actual unit cost was lower, often favorable. |
Important: Higher expenses are not always automatically unfavorable. For example, an expense increase may result from higher sales volume. A meaningful assessment should consider the business context, budget assumptions, and operational performance.
A company wants to compare the sales performance of three departments against their monthly budgets.
| Department | Budgeted Sales | Actual Sales | Variance | Variance % |
|---|---|---|---|---|
| Electronics | ₹200,000 | ₹240,000 | ₹40,000 | 20% |
| Clothing | ₹150,000 | ₹135,000 | -₹15,000 | -10% |
| Furniture | ₹100,000 | ₹110,000 | ₹10,000 | 10% |
| Total | ₹450,000 | ₹485,000 | ₹35,000 | 7.78% |
The total percentage variance is calculated using the total actual and budgeted values: ₹35,000 ÷ ₹450,000 × 100 = 7.78%. It is not the simple average of the department percentages.
Management can investigate the reasons for the clothing shortfall and identify practices contributing to stronger performance in other departments.
Percentage change is used when comparing one period with another, such as current-month sales versus previous-month sales.
=(Current Period-Previous Period)/Previous PeriodFor example, if September sales were ₹100,000 and October sales were ₹125,000, the percentage change is:
=(125000-100000)/100000Result: 25% increase.
If October sales were ₹80,000 instead, the percentage change would be -20%, indicating a decrease compared with September.
When the previous-period value is zero, percentage change is undefined using the standard formula. Report the new value separately or label the percentage change as not meaningful.
Expense variance analysis compares actual expenses against budgeted expenses to identify overspending or savings.
Assume a business has the following monthly expense report:
| Expense Category | Budget | Actual | Variance (Actual − Budget) | Interpretation |
|---|---|---|---|---|
| Rent | ₹50,000 | ₹50,000 | ₹0 | On budget |
| Marketing | ₹30,000 | ₹36,000 | ₹6,000 | Overspending |
| Travel | ₹20,000 | ₹15,000 | -₹5,000 | Below budget |
| Utilities | ₹10,000 | ₹12,000 | ₹2,000 | Overspending |
| Total | ₹110,000 | ₹113,000 | ₹3,000 | Net overspending |
The company spent ₹3,000 more than the total budget. Marketing and utilities contributed to overspending, while travel expenses were below budget.
For expense reports, it can be useful to calculate a separate favorable/unfavorable status because a positive value for Actual − Budget usually represents overspending rather than a favorable result.
The IF function can automatically classify sales variances as Above Budget, Below Budget, or On Budget.
=IF(D2>0,"Above Budget",IF(D2<0,"Below Budget","On Budget"))Place this formula in F2 if D2 contains the variance. Copy it down to classify the remaining rows.
For expense analysis, reverse the interpretation if a positive variance represents overspending:
=IF(D2>0,"Unfavorable",IF(D2<0,"Favorable","On Budget"))These labels assume the variance is calculated as Actual − Budget. If the organization uses Budget − Actual, adjust the logic accordingly.
Conditional formatting makes important differences easier to identify by applying visual formatting according to cell values.
For sales, positive variance may be highlighted as favorable and negative variance as an alert. For expenses, positive Actual − Budget variance usually indicates overspending, so the colors should reflect the metric's meaning.
A variance report provides a structured overview of planned and actual performance. It should make it easy to identify where performance differs from expectations.
Use Excel Tables to keep the report organized and make formulas easier to copy. PivotTables can summarize variances by department, product, region, or month. Charts can help stakeholders compare performance visually.
| Problem | Possible Cause | Solution |
|---|---|---|
| Incorrect variance result | Actual and budgeted cells are reversed. | Confirm that the formula uses Actual − Budget. |
| Percentage variance shows an error | The budgeted value is zero. | Use IF or IFERROR and report the case as not meaningful when appropriate. |
| Percentage displays as a decimal | The cell is formatted as General or Number. | Apply Percentage formatting. |
| Positive expense variance is marked favorable | The sign has been interpreted without considering the metric. | For Actual − Budget expenses, treat positive variance as overspending unless context indicates otherwise. |
| Total percentage is incorrect | Individual percentages were averaged without weighting. | Calculate total variance divided by total budget for an overall percentage. |
| Formula is not copied correctly | Cell references or formula ranges are incorrect. | Check relative and absolute references and verify copied formulas. |
| Conditional formatting uses the wrong colors | Rules do not match the metric's meaning. | Review the rules for revenue, expenses, and profit separately. |
| Large variance has no explanation | Analysis stopped at calculating the difference. | Investigate price, volume, timing, cost, and operational factors. |
Create a worksheet containing budgeted sales and actual sales for five products.
Enter sales data for six months and compare each month with the previous month.
Prepare an expense report for rent, marketing, travel, utilities, and salaries.
Build a report for several departments and calculate budgeted totals, actual totals, variance, and overall percentage variance. Use a PivotTable or summary formulas to present the results by department.
Variance analysis compares actual values with budgeted, planned, or previous-period values to identify numerical differences and evaluate performance.
When comparing actual with budget, the common formula is Actual Value − Budgeted Value.
Subtract the budget from the actual value, divide the difference by the budget, and format the result as a percentage. The standard calculation requires a non-zero budget.
A favorable variance is a difference that benefits the business. Higher-than-budgeted revenue is generally favorable, while lower-than-budgeted expenses are generally favorable.
No. Its meaning depends on the metric and formula. A positive Actual − Budget variance for expenses usually indicates overspending.
Absolute variance shows the numerical difference, while percentage variance expresses the difference relative to the comparison value.
Subtract the previous month's value from the current month's value, then divide by the previous month's value. If the previous value is zero, the standard percentage-change formula is undefined.
Departments may have very different budget sizes. For an overall percentage variance, divide the total variance by the total budget to obtain a budget-weighted result.
It highlights important differences using formatting rules, helping users identify areas that may require investigation.
Investigate the underlying causes, validate the data, assess the business impact, and recommend an appropriate corrective or preventive action.
Performing variance and percentage analysis is an essential Advanced Excel skill for evaluating business performance, tracking budgets, comparing sales, monitoring expenses, and supporting financial decisions. Excel formulas make it possible to calculate differences quickly, while percentage calculations provide context for comparing performance across different periods and departments.
Effective analysis goes beyond calculating numbers. It requires interpreting favorable and unfavorable results correctly, checking formulas, investigating significant variances, and communicating findings through clear reports and visual summaries.
Explore the Advanced Excel Course: View Advanced Excel Training at JustAcademy
Register for a Course Demo: Register for a JustAcademy Demo
Official Website: Visit JustAcademy
