Budget vs Actual Variance, Favorable/Unfavorable Logic & Margin Bridges

Who this is for

Stage 6: Financial Reporting, Reconciliation & Executive Scorecards — Designed for learners mastering budget vs actual polarity & dual materiality gates, gross-to-net bridges, blended margin & price-volume decomposition, dynamic ytd reforecasting (take/drop) & quota run-rates, sumifs opex rollups, common-size p&l & cross-foot checksums through interactive spreadsheet examples.

What you will practise

• Calculate Revenue Variance (Actual - Budget) in D2:D5 and guarded Variance % rounded to 4 decimals in E2:E5 (returning 0 when Budget is 0).
• Compute Profit-Impact Variance in E2:E5 using IF on Account Type (B2:B5), then label each row "Favorable" (>0), "Unfavorable" (<0), or "On Plan" (=0) in F2:F5.
• Compute Variance (C - B) in D2:D5, then flag "Explain" vs "Pass" in F2:F5 when BOTH ABS(D2) >= $G$2 AND ABS(E2) >= $G$3.
• Calculate Net Revenue in F2:F5 as Gross (B) minus SUM(C:E), then compute Net Realization Ratio rounded to 4 decimals in G2:G5.
• Compute Unit Margin % in E2:E5 via ROUND((B2 - C2) / B2, 4) and Line Gross Profit in F2:F5 via (B2 - C2) * D2, then compute Blended Portfolio Margin % in E6 from Total GP / Total Revenue.
• Calculate Volume Variance in F2:F4 via (E2 - D2) * B2 and Price Variance in G2:G4 via (C2 - B2) * E2, then verify total revenue variance in H5 via SUM(F2:G4).
• Compute Budget Blended Margin % in E2, Actual Blended Margin % in E3 (using SUMPRODUCT, SUM, ROUND), and Mix Shift Impact (E3 - E2) in E4.
• Pull Active Month Actuals in H3:H5 using INDEX(B3:G3, 1, $B$1), then compute Dynamic YTD Totals in I3:I5 using SUM(TAKE(B3:G3, 1, $B$1)).

Your learning path

1. Sum by month and year with SUMIFS and EOMONTH

   Aggregate daily transaction logs into monthly totals dynamically from a Year and Month cell without helper columns.

2. Sum and find MAX of every Nth horizontal column (repeating Budget vs Actual blocks)

   Sum or find the maximum across columns selected by repeated headers, such as Actual and Budget.

3. Check if Multiple Columns Are Equal (Row Audit & SUMPRODUCT Count)

   Verify whether three or more system export columns agree on every row using chained pairwise AND(B2=C2, B2=D2) tests and count fully synchronized rows across a table with SUMPRODUCT.

4. Sum of Squares & Paired Vector Differences (SUMSQ, SUMXMY2, SUMX2MY2 & SUMX2PY2)

   Compute vector sum of squares (SUMSQ), squared Euclidean distance (SUMXMY2), difference of squares (SUMX2MY2), and combined squared energy (SUMX2PY2) transparently with SUMPRODUCT.

5. SUMIFS & SUMIF across horizontal columns (multi-row column headers)

   Sum wide horizontal budget/actual rows left-to-right where Row 1 Quarter and Row 2 Scenario headers match criteria—without transposing—using horizontal SUMIFS.

6. Sum, Max & Average a Variable Column by Header (CHOOSECOLS + MATCH)

   Select a numeric column by a quarter header and calculate its sum, maximum, minimum and average with missing-header checks.

7. Combine Duplicate Rows & Sum Values Non-Destructively (UNIQUE, COUNTIF & SUMIFS)

   Consolidate repeated SKU or customer line items into one summary row per key while preserving raw transaction audit trails and reconciling grand totals.

8. Count, sum and find the maximum in a selected column with CHOOSECOLS

   Select a metric by its unique header, then use SUMPRODUCT to count qualifying rows or total a region and MAX for all regions.