How do I decompose a sales variance into price and volume in Google Sheets?

The standard decomposition has 3 components: price variance = (actual price – budget price) × budget volume; volume variance = (actual volume – budget volume) × budget price; and the cross-term = (actual price – budget price) × (actual volume – budget volume). All 3 must sum to the total revenue delta or your table won't foot. Store budget assumptions in a dedicated `Assumptions!` tab and reference them with absolute addressing (`Assumptions!$D$2`) so the formula is auditable. For a $4.2M actual vs. $4.6M budget variance, expect the volume component to dominate in most B2B SaaS scenarios.

What's the best way to analyze contribution margin by SKU across multiple tabs?

Use `SUMIFS` with date criteria referencing a period-start cell in your Assumptions tab: `=SUMIFS('P&L'!$E:$E,'P&L'!$B:$B,SKUs!$A2,'P&L'!$C:$C,">="&Assumptions!$B$3)`. Run this for both revenue and COGS columns, subtract, and you have gross profit per SKU without pivot tables. The cross-tab approach means your margin analysis stays live as actuals roll in—no manual refresh required. In typical multi-SKU models, the top 2–3 SKUs by contribution margin often represent 65–75% of total gross profit, which is the concentration stat that matters most for product mix discussions.

How do I handle mix shift in a revenue variance analysis?

Mix shift is the difference between your actual blended margin and what blended margin would have been if SKU weights held constant from the prior period. Calculate the "prior-period weight" blended margin using SUMPRODUCT of prior-period revenue shares × current-period SKU margins. Subtract from actual blended margin. A 360-basis-point compression on $4.2M revenue translates to roughly $151K in lost gross profit relative to the prior mix—which is the number that matters for a margin bridge, not the percentage point itself.

Why does my pipeline-to-forecast analysis keep missing by 15–20%?

CRM-default stage probabilities (typically 20%, 40%, 60%, 80% by stage) rarely match actual historical close rates for your specific business. If your "Proposal Sent" stage closes at 34% historically but your CRM shows 60%, every deal at that stage is over-weighted by 26 points. Audit your last 18 months of closed-won and closed-lost deals by stage, calculate actual conversion rates, and replace CRM defaults in your Sheets model. Apply these as a lookup table against your pipeline export rather than using the raw CRM probability field. Stage-adjusted forecasts typically reduce miss rates to 5–8% on a 90-day horizon.

How do I build a sales analysis that ties out across a 3-statement model?

Revenue in your P&L needs to reconcile with the sales analysis tab and, through working capital assumptions, with your balance sheet's accounts receivable movement. The typical break: AR is modeled as DSO × (revenue / 365), but if your sales analysis shows a mix shift toward longer-payment enterprise deals, DSO should step up in the same period. Use a named range or cell reference from your sales analysis decomposition tab to drive the DSO assumption—`=SalesAnalysis!$H$12`—rather than hardcoding it. That way a mix shift in sales automatically flows through to AR, cash, and FCFF without a manual update.

Analyzing Sales in Google Sheets: FP&A Framework

The mechanics live in Google Sheets. The judgment is yours.

Price × Volume: The Sales Analysis Formula Nobody Shows You

Every revenue variance decomposes into four buckets: pure price, pure volume, price-volume interaction, and mix. Most analysts stop at "volume was down 850 units" and call it done. That's halfway.

The full decomposition:

Component	Formula	Example ($)
Price variance	(Actual Price – Budget Price) × Budget Volume	+$68K
Volume variance	(Actual Vol – Budget Vol) × Budget Price	–$382K
Price-volume mix	(Actual Price – Budget Price) × (Actual Vol – Budget Vol)	+$14K
Mix shift	Σ(SKU margin × volume shift)	–$40K
Total		–$340K

The cross-term (price × volume interaction) is small but real. Leaving it out means your two primary variances won't foot to the total—which is the kind of thing your CFO notices at 7 AM before the board pack call.

In a live model, price and volume assumptions sit in Assumptions!, actuals come from Actuals!, and the decomposition sheet pulls both:

// Price variance for SKU row i
=('Actuals'!D2-Assumptions!$D$2)*Assumptions!$C$2

// Volume variance for SKU row i
=('Actuals'!C2-Assumptions!$C$2)*Assumptions!$D$2

Wire these across every SKU with a SUMPRODUCT at the bottom and you have a defensible variance table in under 30 minutes.

Contribution Margin by SKU: The Sales Analysis View That Matters

Revenue analysis without margin is noise. A 13.5% revenue variance looks bad. A 13.5% revenue variance driven entirely by a low-margin SKU that was intentionally deprioritized looks fine—actually looks good. You don't know which one you have until you run contribution margin by SKU.

The formula that does the work:

// Contribution margin by SKU, pulling from P&L and Sales tabs
=SUMIFS('P&L'!$E:$E,'P&L'!$B:$B,SKUs!$A2,'P&L'!$C:$C,">="&Assumptions!$B$3)
-SUMIFS('P&L'!$F:$F,'P&L'!$B:$B,SKUs!$A2,'P&L'!$C:$C,">="&Assumptions!$B$3)

Where column E is revenue, column F is COGS, and Assumptions!$B$3 is the period start date. This gives you gross profit per SKU without touching pivot tables.

In a recent board pack scenario: software licenses at 71% gross margin represented only 38% of unit volume but 71% of gross profit. Hardware accessories—58% of volume—contributed 18% of gross profit at an 11% GM. When hardware promotions drove a volume surge in Q3, blended GM compressed 360 basis points. Revenue was up 6%. GM dollars were flat. The board presentation that led with revenue growth missed the point entirely.

SUMIFS across tabs catches this. A single-tab pivot doesn't.

Mix Shift: Where the Story Gets Complicated

Mix is the sneakiest variance because it's not a failure—it's a structural change that might be intentional. If enterprise deals closed slower in Q2 and SMB velocity picked up, you'll show volume growth with margin compression. Neither is wrong. Both need explanation.

The mix variance calculation requires a baseline: what would blended margin be if every SKU maintained its prior-period weight? That's your "pure price/volume" baseline. Actual blended margin minus that baseline is mix.

// Expected blended GM% at prior-period mix
=SUMPRODUCT(SKUs!$C$2:$C$20, SKUs!$D$2:$D$20) / SUM(SKUs!$D$2:$D$20)

// Actual blended GM% this period
=SUMIFS('Actuals'!$E:$E,'Actuals'!$A:$A,">="&Assumptions!$B$3)
 / SUMIFS('Actuals'!$D:$D,'Actuals'!$A:$A,">="&Assumptions!$B$3)

In a bank syndicate DCF, mix shift between product lines can move your terminal value assumptions meaningfully—a 5-point blended GM compression on a $28M revenue business running at 7x EV/Revenue changes enterprise value by roughly $9.8M. Mix isn't a footnote item.

Pipeline Conversion: Translating CRM Data Into a Forecast

As of April 2026, most mid-size companies run their CRM in HubSpot or Salesforce and their financial model in Sheets. The two systems don't talk automatically, which means pipeline-to-revenue conversion analysis gets done manually, late, and with a 15–20% margin of error on any 90-day forecast.

The honest approach: pull stage-weighted pipeline from your CRM export, apply historical conversion rates by stage, and run it against your booked revenue. The formula structure:

// Stage-weighted pipeline contribution to revenue forecast
=SUMPRODUCT(
  Pipeline!$C$2:$C$200,      // deal value
  Pipeline!$D$2:$D$200,      // stage probability %
  (Pipeline!$E$2:$E$200>=Assumptions!$B$3)*   // close date filter
  (Pipeline!$E$2:$E$200<=Assumptions!$B$4)
)

Where Assumptions!$B$3:$B$4 is your forecast window. If your historical close rate for "Proposal Sent" deals is 34% but your CRM default is 60%, you're systematically over-forecasting by 26 points on that stage. That compounds fast across a $3.8M pipeline.

McKinsey's B2B commercial analytics research consistently finds that companies using stage-adjusted (rather than CRM-default) conversion rates improve forecast accuracy by 20–30 percentage points over a rolling 12-month period—the specific finding appears in their "Revenue Growth Management" practice publications. The math isn't complicated. The discipline of applying it quarterly is.

Where AI Speeds This Up

The decomposition framework above takes about 4 hours to build from scratch in a new model. The formulas aren't hard—the time goes into reading column structures, tracing which tab holds what, and writing SUMIFS with the right criteria combinations.

ModelMonkey cuts that setup time significantly. You describe what you want—"decompose the Q2 revenue variance by SKU into price, volume, and mix components"—and it reads your existing tab structure, identifies the relevant columns, and writes the cross-tab SUMIFS. For a board pack with 8 linked tabs, that's the difference between an afternoon and 20 minutes. It won't replace judgment on what the variance means, but it removes the mechanical friction of wiring it up.

The custom instructions feature is worth setting up: tell it your preferred percentage formatting, your period-start convention, and which tabs hold actuals vs. assumptions. It'll apply them consistently across every formula it writes.