Variance Analysis: Formulas, a SaaS Example and a Free Excel Template

Variance analysis is the process of comparing a planned number, such as a budget or forecast, with the actual result and explaining why they differ. The variance is actual minus plan, in dollars and as a percent. A good analysis then splits that gap into its drivers, such as price, volume and mix, so the team knows what to fix.

Most guides on this topic come from cost accounting: material, labor and overhead in a factory. This one is written for SaaS finance and RevOps, where the numbers that miss are bookings, ARR and revenue. It has the formulas with Excel versions, a worked quarter that missed plan by $425,000, and a free template you can download without a form.

Download the variance analysis template (Excel, .xlsx). It has a budget vs actual summary, a price, volume and mix tab, and a monthly plan vs actual tab. All numbers in it are illustrative.

What is variance analysis?

Variance analysis measures the gap between plan and actual, then explains it. The formula is simple: Variance = Actual - Budget. The percent version divides that gap by the budget. The work is in the explanation, because a negative number on its own tells nobody what to do.

The plan can be any baseline you committed to in advance:

- The budget (or annual plan) set before the year. - The latest forecast, which moves as the quarter goes. - The prior period, such as last month or the same quarter last year. Accountants call this flux analysis.

The sign tells you the direction. Whether that direction is good depends on the line:

A favorable variance is not always good news. Spend below budget because two hires started a quarter late is favorable on paper and can cost you bookings two quarters later.

Variance analysis formulas, with Excel versions

Every variance in a revenue review comes from a handful of formulas. The table assumes budget in column B and actual in column C for the first two rows. For the driver rows, "units" means deals, seats or customers, and "price" means the matching average (average ACV per deal, price per seat). In the driver rows, B is budget units, C budget price, E actual units, F actual price, I and J budget and actual mix %, and row 9 holds the totals, the same layout as the template's price, volume and mix tab. In the last two rows, budget is in B and actual in C, with deals decided in row 2, win rate in row 4 and the budget average price in B5. Use ABS on the budget in the percent formula, or a negative budget line (an operating loss, for example) flips the sign of the result.

Price, volume and mix always add back to the total variance. If yours do not, one of the formulas is pricing volume at actual prices instead of budget prices, which counts the price effect twice.

Price, volume and mix: how a revenue variance splits

Price, volume and mix answer three different questions about a revenue miss: did we sell at the price we planned, did we sell as many units as we planned, and did we sell the products we planned? Each one points at a different owner, which is why the split matters more than the total.

- Price variance belongs to pricing and deal desk. It shows up as discounting, smaller deals inside the same plan, or pricing pressure from a competitor. - Volume variance belongs to pipeline and conversion. It is the unit change valued at the budget's average price, so price changes cannot leak into it. - Mix variance belongs to segment strategy. If the plan assumed 10% of deals would be enterprise and only 6% were, the total can miss badly even when every product sold at plan price.

Most finance teams compute price and volume and stop there. In a SaaS business with a cheap plan and an enterprise plan, that hides the biggest driver. A shift toward smaller deals looks like a price problem in a two-way split, because the average deal got smaller. It is usually a mix problem: each plan held its price, the blend changed.

Pete's view from forecasting across ORM's customers: most deals close for less than the value recorded in the CRM. He has seen pipeline with an average deal size of $80,000 where the average closed-won deal was $40,000. If the plan was built on pipeline amounts, the variance shows up as price or mix every quarter until someone fixes the assumption.

Worked example: a SaaS quarter that missed plan by $425,000

In this illustrative quarter, new ARR bookings came in at $2,095,000 against a $2,520,000 plan, a 16.9% miss, even though the team closed more deals than planned. The company sells three plans. Units are new deals; price is the average ACV per deal. The budget mix was 60% Starter, 30% Growth and 10% Enterprise. The actual mix was 70.8%, 23.6% and 5.7%. Here is the split: The mix line is the actual deals valued at budget prices ($900,000 + $750,000 + $540,000 = $2,190,000), minus the actual deal count at the budget average ACV ($2,671,200). Per plan it is (actual deals - total actual deals x budget mix %) x budget price: +$136,800 for Starter, -$204,000 for Growth and -$414,000 for Enterprise. The readback in plain English: the team sold six more deals than planned, worth $151,200 at plan prices. Discounting on Starter and Growth cost $125,000, partly offset by $30,000 of price gain on Enterprise. The miss is mix: four enterprise deals and five Growth deals that did not close, replaced by smaller Starter deals, cost $481,200. A two-way price and volume split would have blamed price for $576,200 and sent the team after discounting, the wrong fix.

The volume line, split into deal count and win rate

Volume variance itself splits into two causes: how many deals were decided, and how many of those you won. In the example, the plan assumed 250 deals decided (won or lost) at a 40% win rate, which gives the 100 planned wins. The team actually decided 290 deals and won 106, a 36.6% win rate. Pipeline generation was strong. Conversion was weak and gave back most of it. For the full method behind this split, with fixes for each cause, see why you missed your sales forecast.

Timing or permanent

Before anyone writes a cause, tag each variance as timing or permanent. If three of the four missing enterprise deals closed in the first two weeks of the next quarter, most of the mix variance is timing: the revenue moved, it was not lost. A lost deal is permanent. Mixing the two in one number hides which quarter the money comes back in, if it comes back at all. Across ORM customers, only about 20% of the pipeline dated to close in a quarter on its first day actually closes in that quarter, so timing variance is the normal case, not the exception.

How to do a variance analysis in 7 steps

Freeze the plan, calculate the gaps, filter by materiality, split by driver, tag timing, then write one cause and one action per material line. In order:

1. Freeze the baseline and the definitions. Pick the plan you compare against and do not change it mid-year. Agree which CRM field is the booking amount and what counts as new versus expansion. Pete has seen one company with nine competing amount fields (TCV, ACV, ARR, renewal amount and older fields no longer filled in after 2021). Sorting out which one was the real booking took several weeks. 2. Check that actuals reconcile. When you add up bookings for the year, the total should match what the company reports internally. A variance built on numbers finance does not recognize will be argued, not acted on. 3. Calculate dollar and percent variance for every line. Use Actual - Budget and divide by ABS(Budget). Flag each line favorable or unfavorable based on whether higher is better. 4. Apply a materiality threshold. Set a dollar floor and a percent floor before you see the results, and only analyze lines that pass both (more on this below). 5. Split each material revenue variance into price, volume and mix. Then split volume into deal count and win rate. For costs, split into rate (what you paid per unit) and usage (how many units you used). 6. Tag timing or permanent. Slipped deals, late hires and delayed invoices reverse. Lost deals, churn and permanent discounts do not. 7. Write the cause, the size and the action, then update the forecast. One short paragraph per material variance: what moved, how much of the gap it explains, and what changes. Then carry the learning into the next forecast. A variance analysis that does not change an assumption is a report, not an analysis.

The same order works mid-quarter. Asked what a CRO behind plan at week six should do first, Pete's answer is to figure out the gap: is it deal size, win rate, pipeline, time to close, or a mix? "Sell harder" does not work halfway through a quarter. You have to be targeted on what is actually not working.

How to set a materiality threshold

A materiality threshold is the size a variance must reach before anyone spends time explaining it, usually set as both a dollar amount and a percent. There is no universal number. It depends on the size of the line and how much attention your team has.

Use two floors and require both. A dollar floor alone flags every wobble on your largest line. A percent floor alone flags a 40% miss on a $3,000 line. Trintech's month-end close guide uses an example of $5,000 and 2% for a single balance sheet account; a revenue review for a company with a $10 million quarter needs much larger floors. The template defaults to $50,000 and 5% as an illustration.

Set the threshold before the results are in. If you set it after, the threshold tends to move so favorable variances get a sentence and unfavorable ones get a page. Favorable variances above the threshold still get commentary: a quarter that beat plan because deals were pulled forward is a different result from one that beat plan on new pipeline. For how to write those paragraphs, see revenue variance commentary.

The variance analysis template (Excel)

The free template has four tabs, and every number in it is illustrative. Yellow cells are inputs, white cells are formulas, and check cells must read 0. It opens in Excel and Google Sheets (File, Import). Download the template (.xlsx) How to use it with the example:

1. On Summary, set the two thresholds in C3 and C4. The example uses $50,000 and 5%. 2. Mark each line Yes if higher is better (revenue) or No if lower is better (costs, churned ARR). The favorable flag follows that column. 3. Enter budget and actual for each line. New ARR bookings pulls from the Price-Volume-Mix tab, so the two always agree. 4. On Price-Volume-Mix, enter budget and actual deals and average ACV per plan. The example's $425,000 miss splits into -$95,000 price, +$151,200 volume and -$481,200 mix, and the check column reads 0. 5. Enter deals decided (won plus lost) under the volume split. The example shows +$403,200 from deal count and -$252,000 from win rate. 6. On Monthly, phase the annual plan by your own seasonality. In the example, Q3's three months add to the same $2,520,000 plan and $2,095,000 actual. 7. Write commentary only for lines marked Material = Yes. In the example that is new ARR bookings, churned ARR, services revenue, sales and marketing, and operating income.

Why SaaS revenue variances happen

The most common reason a SaaS plan misses is that the business or market changed and the plan is still built on the old assumptions. That is the mechanism Pete sees most often. A variance analysis is how you see which assumption broke, because each driver line maps to a different change. The table mirrors how Pete reads a slipping quarter. Pressure on deal size or win rate is usually a sign of competition. Low deal count is a pipeline generation problem, so marketing and SDR teams are the place to look. And in 2026, cycle length has been the biggest culprit, as buyers across ORM's customers delayed decisions.

The external data says the same assumptions break often. Average B2B deal values fell 21% in 2023 (Ebsta and Pavilion, 2024), a price and mix variance waiting to happen in any plan built on 2022 deal sizes. 36% of deals slipped past their close date in 2025 (Ebsta, 2025), which is timing variance. And the median B2B SaaS company grew 26% in 2024, nine points below its planned growth rate (Maxio, 2025).

Pulling deals forward to close a gap creates its own variance next quarter. You usually discount to get the deal early, which is price erosion now. And you shrink the starting pipeline for the next quarter, which has to be made up with more pipeline generation.

Budget, forecast or last period: which comparison to use

Use budget vs actual to grade the plan, forecast vs actual to grade the forecast, and period over period to spot a trend. They answer different questions, so keep them in separate columns instead of choosing one. A forecast miss and a plan miss are separate grades (see forecast vs budget). The plan can be unrealistic while the forecast was accurate, and the reverse. If you only track one, you cannot tell a planning problem from a forecasting problem. For the forecast-side metric on its own, see revenue variance, the gap between forecast and actual revenue.

For a board, Pete's rule is that a number on one slide must show up on the next, or come with a clear reason it changed. It should roll up the same way from region to total, be traceable down to the deals, and come with a range (low, on target, upper) rather than one point.

Common variance analysis mistakes

Most bad variance analysis comes from a moving baseline, an even monthly split, or offsetting errors that net to zero.

- Phasing the plan evenly by month. The third month of a quarter is the largest and the first is the smallest across ORM's customers, and Q4 is usually the biggest quarter, followed by Q2, Q3 and Q1. An even 1/12 split makes month one look like a miss and month three look like a beat, every quarter. - Comparing against a plan that keeps moving. Re-baselining the budget each quarter makes the variance shrink and the analysis useless. Keep the original budget and add the latest forecast beside it. - Reading only the total. A company can land close to plan in total because enterprise came in well short and mid-market came in well over. That is not accuracy. It is two errors canceling out. Run the analysis by segment. - Splitting price and volume without mix. As the example shows, a mix shift gets blamed on price and sends the team after the wrong fix. - Calculating volume at actual prices. That double counts the price effect, and price, volume and mix no longer add back to the total. - Explaining every line. Commentary on 40 lines means nobody reads any of it. Use the threshold. - Changing definitions between plan and actual. If bookings meant ACV in the budget and TCV in actuals, the variance is a definition change, not a business result.

Where ORM fits

Variance analysis looks back at a closed period. The earlier you can see a variance forming, the more of it you can still fix. ORM snapshots pipeline every day, so it sees what changed and when: a deal that moved stage yesterday, an amount that dropped, a close date that slipped. Those changes are often the first sign of a price, mix or timing variance. ORM targets 95% forecast accuracy from day 1 to day 90 of the quarter, without manual adjustments, which makes the forecast-vs-actual column a number you can plan around. If you want to see it on your own data, see how ORM works.