Budget Variance Analysis

Every lesson in this course has been forward-looking — projecting, forecasting, planning. Budget variance analysis looks in both directions at once. It compares what you planned against what actually happened, quantifies the gap, explains why it exists, and feeds that understanding back into the next forecast cycle. It is the mechanism that turns a one-time forecast exercise into a continuously improving planning system.

What Variance Analysis Is and Why It Matters

A budget variance is the difference between a forecasted or budgeted value and the actual result. Calculating it is straightforward arithmetic. The discipline of variance analysis is in the interpretation — understanding whether a variance is favorable or unfavorable, whether it is structural or one-off, and whether it signals a problem with execution or a problem with the original assumption.

The three questions variance analysis must answer:

• How large is the gap? Absolute and percentage variance for every key metric;
• Is it favorable or unfavorable? Higher-than-forecast revenue is favorable. Higher-than-forecast cost is unfavorable. The sign convention must be applied consistently;
• Why did it happen? A variance explained is actionable. A variance left unexplained will repeat in the next forecast cycle.

Setting Up the Variance Structure

Your dataset covers 2022–2024, giving you three years of actuals to compare against forecasted values. For the variance analysis, you will compare two things:

• In-sample variance — how well did your Section 2 backtest forecasts predict the 2024 actuals you held back? This is the accuracy measurement from Lesson 2.5, now reframed as a management reporting exercise.
• Budget vs Actual — comparing the full-year 2024 actuals against what a budget set at the start of 2024 would have projected, using your assumption-driven model from Lesson 3.1 initialized with end-of-2023 data.

Create a Variance_Analysis sheet with a structured comparison table:

Metric	Budget	Actual	Variance ($)	Variance (%)	Flag
Total Revenue	forecast	actual	actual−budget	variance/budget	FAV / UNF
Total Cost	forecast	actual	actual−budget	variance/budget	FAV / UNF
Gross Profit	forecast	actual	actual−budget	variance/budget	FAV / UNF
Gross Margin %	forecast	actual	actual−budget	n/a	FAV / UNF
Marketing Spend	forecast	actual	actual−budget	variance/budget	FAV / UNF
Operating Profit	forecast	actual	actual−budget	variance/budget	FAV / UNF

Calculating Variances Correctly

The sign convention for variances must be consistent and explicit. Use the favorable = positive convention throughout:

Revenue variance (higher actual is good):

=Actual_Revenue - Budget_Revenue
Favorable if positive, Unfavorable if negative

Cost variance (lower actual is good — convention flips):

=Budget_Cost - Actual_Cost
Favorable if positive (spent less than planned), Unfavorable if negative

Profit variance (higher actual is good):

=Actual_Profit - Budget_Profit
Favorable if positive

The Flag column applies consistent labeling:

Revenue and Profit flag:
=IF([@Variance_$]>0, "FAV", IF([@Variance_$]<0, "UNF", "ON TARGET"))

Cost flag (sign flipped):
=IF([@Variance_$]>0, "FAV", IF([@Variance_$]<0, "UNF", "ON TARGET"))

Apply conditional formatting to the Flag column:

"FAV"       → green fill, dark green text
"UNF"       → red fill, dark red text
"ON TARGET" → blue fill, white text

Monthly Variance Tracking

Annual variances hide monthly patterns. A full-year revenue variance of zero could mean the business was on track all year, or it could mean a terrible first half offset by a strong second half — two very different business stories with very different implications for the next forecast.

Build a monthly variance table alongside the annual summary:

Monthly Revenue Variance:
=Forecast_Input_Table[@Total_Revenue] - Budget_Monthly_Revenue[@same_period]

Where Budget_Monthly_Revenue is a 12-row table containing what your model would have projected for each month of 2024, initialized with end-of-2023 assumptions.

Add three analytical columns to the monthly table:

Cumulative Variance — running total of monthly variances, showing whether the year-to-date position is improving or deteriorating:

=SUM($D$2:D2)     → expanding range running total of variance column

Variance as % of Budget — normalizes the variance to make months comparable regardless of seasonality:

=[@Monthly_Variance] / [@Budget_Monthly_Revenue]

Rolling 3-Month Variance Trend — smooths month-to-month noise to reveal whether variance is systematically improving or worsening:

=IFERROR(AVERAGE(OFFSET([@Monthly_Variance],-2,0,3,1)),"")

Decomposing Variance into Price and Volume Effects

A revenue variance can be caused by two completely different things: selling more units than planned (volume effect) or selling at a higher price than planned (price effect). Treating them the same way leads to wrong conclusions and wrong corrective actions.

Volume Variance — the revenue impact of selling more or fewer units than budgeted, at the budgeted price:

=( Actual_Units - Budget_Units ) * Budget_Unit_Price

Price Variance — the revenue impact of selling at a different price than budgeted, on the actual volume sold:

=( Actual_Unit_Price - Budget_Unit_Price ) * Actual_Units

Verification — the two components must sum to the total revenue variance:

=Volume_Variance + Price_Variance = Total_Revenue_Variance

Add a validation cell confirming this reconciliation. If it does not equal zero, there is a formula error somewhere in the decomposition.

This decomposition is available in your dataset because you have both Units_Sold and Unit_Price columns. Calculate it at the product category level for maximum insight:

Volume Variance (Software, 2024):
=( SUMIFS(Business_Data[Units_Sold], Business_Data[Product_Category], "Software",
          Business_Data[Year], 2024)
 - Budget_Software_Units )
 * Budget_Software_Unit_Price

Price Variance (Software, 2024):
=( SUMIFS(Business_Data[Revenue], Business_Data[Product_Category], "Software",
          Business_Data[Year], 2024) /
   SUMIFS(Business_Data[Units_Sold], Business_Data[Product_Category], "Software",
          Business_Data[Year], 2024)
 - Budget_Software_Unit_Price )
 * SUMIFS(Business_Data[Units_Sold], Business_Data[Product_Category], "Software",
          Business_Data[Year], 2024)

Regional and Segment Variance Analysis

A total variance tells you the size of the problem. A segmented variance tells you where it came from. Build a variance breakdown by the three dimensions available in your dataset:

By Region:

=SUMIFS(Business_Data[Revenue],
        Business_Data[Region], [@Region],
        Business_Data[Year], 2024)
- [@Budget_Revenue_by_Region]

By Product Category:

=SUMIFS(Business_Data[Revenue],
        Business_Data[Product_Category], [@Category],
        Business_Data[Year], 2024)
- [@Budget_Revenue_by_Category]

By Customer Segment:

=SUMIFS(Business_Data[Revenue],
        Business_Data[Customer_Segment], [@Segment],
        Business_Data[Year], 2024)
- [@Budget_Revenue_by_Segment]

The segment that explains the largest share of the total variance is where investigation should be focused. Use a contribution analysis to identify it:

Variance Contribution % (per segment):
=[@Segment_Variance] / SUM(ABS(all segment variances))

Sort by absolute contribution percentage descending — the top two or three segments almost always explain the majority of the total variance.

Using Variance to Improve the Next Forecast

Variance analysis closes the forecasting loop. The findings from comparing 2024 actuals to forecasts directly improve the 2025 forecast assumptions. Build a structured assumption review table that documents each finding:

Assumption	2024 Forecast	2024 Actual	Variance	2025 Adjustment	Rationale
Revenue Growth Rate	8.0%	actual CAGR	gap	revised rate	explanation
Gross Margin %	42.0%	actual margin	gap	revised margin	explanation
Cost Ratio	calculated	actual ratio	gap	revised ratio	explanation
Marketing Ratio	calculated	actual ratio	gap	revised ratio	explanation

The 2025 Adjustment column feeds directly back into the Assumptions sheet. This is not a manual override — it is an evidence-based revision grounded in measured forecast error.

Building the Variance Waterfall Chart

The most effective visualization for variance analysis is a waterfall chart that bridges the gap between budget and actual. It shows not just the total variance but how much each factor contributed to it.

Waterfall bridge structure (revenue example):
Starting bar:    Budget Revenue (full positive bar)
Middle bars:     Volume Variance (positive or negative)
                 Price Variance (positive or negative)
                 Mix Variance (positive or negative, if applicable)
Ending bar:      Actual Revenue (full positive bar — should equal budget + all variances)

To build this in Excel:

1. Set up a table with five rows: Budget, Volume Variance, Price Variance, Mix, Actual
2. Insert → Waterfall Chart
3. Right-click the Budget and Actual bars → Set as Total
   (this makes them full bars rather than floating segments)
4. Color positive variance bars green, negative variance bars red
5. Add data labels showing the dollar value of each component

This chart communicates the entire variance story in one visual — suitable for executive presentations without any accompanying explanation.

---

Task 1: Build the Variance Analysis Table

Goal: Compare 2024 actuals against forecasted values and calculate variances at multiple levels of detail.

1. Create a new sheet called Variance_Analysis.
2. Build the annual summary table with all six metric rows from this lesson. For Budget values, use the outputs of your assumption-driven forecast model initialized with end-of-2023 data — set Growth_Rate to the 2022–2023 CAGR and run the model for 2024.
3. For Actual values, use SUMIF structured references against Business_Data filtered to Year = 2024.
4. Calculate Variance ($), Variance (%), and Flag columns using the sign conventions from this lesson.
5. Apply three-color conditional formatting to the Flag column and to the Variance (%) column.
6. Add a reconciliation check confirming that Gross Profit variance = Revenue variance − Cost variance. Flag it green if it reconciles to zero, red if not.

🔧 Task 2: Build the Monthly Variance Table and Decomposition

Goal: Track variance month by month, decompose revenue variance into price and volume effects, and identify the segments driving the largest variances.

1. Build the monthly variance table for all 12 months of 2024 with Actual, Budget, Variance ($), Variance (%), Cumulative Variance, and Rolling 3-Month Trend columns.
2. Apply conditional formatting to the monthly Variance ($) column — green for favorable months, red for unfavorable. Count the number of favorable vs unfavorable months using COUNTIF.
3. Build the price and volume decomposition table for all five product categories. Verify that Volume Variance + Price Variance = Total Revenue Variance for each category using a reconciliation column.
4. Build the regional and segment breakdown variance tables. Add a Contribution % column to each and sort by absolute contribution descending.
5. Build the assumption review table documenting 2024 forecast vs actual for each key assumption and the proposed 2025 adjustment. Update the named cells in your Assumptions sheet with the revised values.
6. Build the waterfall bridge chart showing the budget-to-actual revenue reconciliation with Volume, Price, and any remaining unexplained variance as bridge components. Set Budget and Actual bars as Totals and color positive bridges green, negative bridges red.