Variance Analysis in Excel: Explaining the Difference, Not Just Measuring It
Actual minus budget is one formula. Working out how much of the difference came from volume, how much from mix, how much from price and how much is timing — that is the part the report is actually for.
Short answer
The number is the easy part
Budgeting and reporting both produce a variance column almost as a by-product. The column says the figure moved. It does not say why, and the why is what the person reading it needs in order to do anything about it.
| What is asked | What the variance column gives | What the analysis has to add |
|---|---|---|
| Revenue is ₹2.8 lakh above budget | The figure | How much of that is more units, and how much is a better price |
| Payroll is ₹6.4 lakh over | The figure | How much is a headcount increase, how much is a revision, how much is one month extra |
| Gross margin is down 1.8 points | The figure | Whether the cause is discounting, product mix, or input cost |
Step 1: decide what is material
Explaining every row is not analysis — it is transcription. The first decision is which rows deserve an explanation.
| Threshold | Catches | Misses on its own |
|---|---|---|
| Money only — above ₹50,000 | Anything large | A 40% overrun on a small line that grows into a habit |
| Percentage only — above 10% | Anything that moved a lot proportionally | A 4% variance on a large line that is the whole problem |
| Both together | The rows worth a sentence | Almost nothing that matters — which is the point |
Two thresholds, applied together
Step 2: the types of variance
| Type | Means | What to do about it |
|---|---|---|
| Volume | More or less was sold or produced than planned | Ask whether the cause continues — this is the effect that compounds |
| Mix | The same total volume, but a different combination of products | Look at the product-level margin, not the total |
| Price or rate | Sold at a different price, or bought at a different rate | Separate a deliberate decision (a discount scheme) from a market movement |
| Timing | The cost or the revenue falls in a different period | State when it is expected to reverse — a timing variance is not a problem |
| One-off | A single event that will not recur | Isolate it so the underlying run rate stays visible |
| Error | A misposting, a duplicate, an entry in the wrong period | Correct it rather than explaining it |
The distinction that matters most in practice is timing against everything else. A cost that moved from March to April is not a variance to manage; it is a variance to name and forget. An error should be corrected, not explained — a report that carries an explanation for a figure that was simply wrong teaches people to write explanations rather than check.
Step 3: the volume, mix and price decomposition
This is the technique that turns a variance column into an answer. It works on revenue, on cost, and on any figure that is a quantity multiplied by a rate.
| Product | Budget units | Budget rate (₹) | Budget (₹) | Actual units | Actual rate (₹) | Actual (₹) |
|---|---|---|---|---|---|---|
| Product A | 6,000 | 300 | 18,00,000 | 8,000 | 290 | 23,20,000 |
| Product B | 4,000 | 600 | 24,00,000 | 3,200 | 600 | 19,20,000 |
| Total | 10,000 | 420 (avg) | 42,00,000 | 11,200 | — | 42,40,000 |
Volume rose 12% and revenue rose 0.95%. A variance column showing ₹40,000 would tell you nothing about why. The decomposition does.
| Effect | Formula | Arithmetic | Amount (₹) |
|---|---|---|---|
| Volume | (Actual qty − Budget qty) × Budget avg price | (11,200 − 10,000) × 420 | +5,04,000 Fav |
| Price | (Actual price − Budget price) × Actual qty, per product | A: (290 − 300) × 8,000 = −80,000; B: (600 − 600) × 3,200 = 0 | −80,000 Unfav |
| Mix | Total variance − Volume effect − Price effect | 40,000 − 5,04,000 − (−80,000) | −3,84,000 Unfav |
| Total variance | Actual − Budget | 42,40,000 − 42,00,000 | +40,000 Fav |
What the numbers are saying
The workbook for this is small: one row per product, four effect columns, and a total row that must agree with Actual minus Budget. Build the check row before you build the explanation — a decomposition that does not tie back is a wrong decomposition, and the difference is usually one effect computed on the wrong quantity or the wrong price.
Doing the same for cost lines
Cost variance decomposes the same way with the terms reversed in meaning: a volume effect on a cost line represents spending more because more was produced, which is often expected rather than a problem. The two-part split that matters most for a cost line is usually rate (did we pay more per unit) and efficiency (did we use more units per unit of output). The arithmetic is identical; only the labels change.
Step 4: build the bridge
A waterfall chart is what a decomposition looks like when it is drawn, and in Excel it is a stacked column chart with one series made invisible.
Lay out three columns
Work out the running totals
Insert a stacked column chart
Hide the base series
Set the first and last bars
Label and colour
| Step | Base (₹) | Value (₹) | Running total (₹) |
|---|---|---|---|
| Budget | 0 | 42,00,000 | 42,00,000 |
| Volume effect | 42,00,000 | 5,04,000 | 47,04,000 |
| Mix effect | 43,20,000 | 3,84,000 | 43,20,000 |
| Price effect | 42,40,000 | 80,000 | 42,40,000 |
| Actual | 0 | 42,40,000 | 42,40,000 |
Step 5: write the sentence
A bridge shows the shape of the answer; a sentence is the answer. One sentence per material variance, in the form: what moved, by how much of the total, and why.
| Rather than | Write |
|---|---|
| Revenue higher than budget | Revenue is ₹40,000 ahead of budget, but the gain is entirely volume — a ₹3.84 lakh mix shift towards Product A and a ₹80,000 lower realisation offset a ₹5.04 lakh volume gain |
| Salaries over budget | Salaries are ₹6.4 lakh over budget: ₹4.1 lakh is nine additions from July, ₹1.6 lakh is the annual revision effective October, and ₹0.7 lakh is a one-off settlement that will not recur |
| Margin down | Gross margin is 1.8 points below budget, of which 1.3 points is the change in product mix and 0.5 points is the discount scheme introduced in August |
The test of a good sentence
What is hard to explain, and what to do with it
| Situation | Why it is hard | The honest treatment |
|---|---|---|
| Several causes at once | The effects are not independent of each other | Split into two or three named effects and show the rest as unexplained |
| A restated prior period | The comparison is against a number that has since changed | Recompute the comparison on the restated basis, and say so in a note |
| A discontinued product | The comparison includes something that no longer exists | Show it separately rather than inside a product group |
| A price change mid-period | The single average price hides two different prices | Split the period, or weight the average correctly |
| An error in the budget itself | The variance is real but the cause is the plan | Say so — a budget error is a finding, not an excuse |
The residual line deserves one more sentence. If volume, mix and price account for ₹39.2 lakh of a ₹40 lakh variance and the remaining ₹80,000 cannot be attributed, show an ₹80,000 line called unexplained. The temptation is to spread that ₹80,000 across the other effects so the bridge looks complete. Resist it: a bridge that always adds up perfectly is a bridge that has been adjusted to add up, and the first time somebody tests it, the report loses credibility that takes months to rebuild.
Common mistakes
- Explaining everything. A pack with forty explained variances has no explanations, because nobody reads forty of them.
- Mixing bases. Computing the volume effect at the actual price and the price effect at the budget quantity — the effects then double-count and the total will not tie.
- Ignoring mix. It is the effect most often missing and the one most often behind a “flat” figure that should not be flat.
- Treating timing as a problem. It reverses. Saying so in the report is what stops it being investigated twice.
- A waterfall chart with no labels. A bridge without numbers is a shape, and the reader cannot quote a shape in a meeting.
- Not tieing the effects back to the total. The check is the analysis; without it you have a set of plausible figures that may not be the right ones.
- Variance presented without a direction. Whether above budget is good depends on whether the line is income or expense — see the favourable/unfavourable convention in the monthly MIS guide.
A different question with a similar name
Everything above explains a movement against a plan or a prior period, where the same records are being compared at two points in time. There is a second kind of question that gets called a variance and is not the same thing at all: why our records disagree with somebody else’s — the ledger against the bank, the books against a vendor statement, the purchase register against a tax return.
That is a matching problem, not a decomposition problem. The methods for it are in the reconciliation guide, and the practical difference between doing it in a spreadsheet and having it done consistently is what Piloteq Automate is about. Recognising which of the two you are dealing with is worth doing before opening the workbook.
If you would rather not build this by hand
When the variance is between two sets of records
Volume, mix and price explain a movement against a plan. A different question — why our records disagree with theirs — is a matching problem. Piloteq Automate handles that one: the same rules each period, tolerances you set, and a reason recorded against every difference.
- ✓Group matching for one payment across many invoices
- ✓Amount and date tolerances you configure
- ✓Missing records reported for both sides
- ✓Every exception carries a reason
Frequently asked questions
What is variance analysis in accounting?+
Explaining why a figure differs from the figure it is being compared against. The arithmetic — actual less budget — takes one formula. The analysis is working out how much of the difference came from selling more, how much from the mix of what was sold, how much from price, and how much was simply timing that will reverse next month.
How do I decide whether a variance is worth investigating?+
Use two thresholds together, not one. A variance is worth explaining when it is large in money and large as a proportion — say more than fifty thousand rupees and more than five percent of the budget. Money alone flags a small percentage on a big line; percentage alone flags a large percentage on a line too small to matter.
How do I split a variance into volume and price?+
The volume effect is the change in quantity valued at the budget price. The price effect is the change in price valued at the actual quantity. In Excel: (actual quantity minus budget quantity) multiplied by budget price, plus (actual price minus budget price) multiplied by actual quantity. The two together equal the total variance, which is how you check the arithmetic.
What is a mix variance?+
The part of a variance caused by selling a different combination of products than planned, holding total volume and prices aside. If volume grows twelve percent but the growth is all in the cheaper product, revenue can be almost flat. The mix effect is usually the residual after the volume and price effects are accounted for.
How do I make a waterfall chart in Excel?+
Build a three-column table — step name, base and value — then insert a stacked column chart on it. Set the base series fill to No Fill so only the visible part of each bar shows, and add data labels. The base for each step is the running total after the step; for a decrease, the base is the running total after the decrease and the value is the size of the decrease.
Why does my variance explanation not add up to the total variance?+
Because the effects were computed on different bases, or because something is genuinely unexplained. Whatever cannot be attributed should be shown as a residual line named exactly that — unexplained. A variance analysis that appears to add up because the residual was quietly folded into the largest line is worse than one that admits the gap.