Tabellab

Monthly P&L Variance: What to Explain to Your Manager

Lesson 1 of 20Lab

All lessons
  1. Monthly P&L Variance: What to Explain to Your ManagerLabFree
  2. Budget vs Forecast: Rolling the Latest Estimate (6+6)Lab Locked
  3. Revenue Bridge: Volume and Price Effects in a WaterfallLab Locked
  4. Headcount Cost Model: Salaries, Start Months and On-CostsLab Locked
  5. Driver-Based Forecast: Sessions × Conversion × Order ValueLab Locked
  6. KPI Pack: Margins, Year-on-Year Growth and Run-RateLab Locked
  7. Trailing Twelve Months (TTM): A Moving SUMLab Locked
  8. Ratio Snapshot: Liquidity, Leverage and Working Capital DaysLab Locked
  9. Scenario Toggle: Base, Upside and Downside with CHOOSELab Locked
  10. Sensitivity Table: EBITDA for Price and Volume ChangesLab Locked
  11. Reconciling Two Exports: XLOOKUP and the Difference ColumnLab Locked
  12. One-Page Management Dashboard: KPIs, Chart and HighlightsLab Locked
  13. From Trial Balance to P&L: Account Mapping with XLOOKUP and the Tie-Out CheckLab Locked
  14. Budget Phasing: Seasonal Monthly Budgets, YTD Variance and What Is Left to SpendLab Locked
  15. Forecast Accuracy: Errors, MAPE and Bias by Product LineLab Locked
  16. Allocating Shared Costs: Drivers, Department Contribution and Why the Driver MattersLab Locked
  17. Cash Conversion: From EBITDA to Free Cash Flow with Working Capital and a WaterfallLab Locked
  18. Capex Request: Payback, NPV and IRR for a New Store Fit-OutLab Locked
  19. Group Reporting in USD: FX Translation, Reported vs Constant-Currency GrowthLab Locked
  20. Final Project: Month-End Board Pack – YTD vs Budget and Last Year, Margins & a Combo ChartLab Locked

You are not signed in — the lab works, but your progress is not saved. Sign up

Lesson 1 of 20 is free. Unlock all 20 hands-on labs, the final exam and your certificate.

Buy course — $9.90
Read first

In Excel

  • Type the threshold once (B10) and refer to it with $B$10, so copying the formula down keeps pointing at it.
  • ABS() removes the sign, so a big favourable variance is flagged as well – it still needs an explanation.
Loading the spreadsheet…

Your first month-end: the question you will hear every month

You have just started as a financial analyst. The books for September are closed, the P&L has landed in your inbox, and your manager asks the question that every finance team asks on the first working days of every month: where are we against budget, and why? Nobody expects you to know the whole business in week one. What they do expect is a clean variance table, a clear label on every line saying whether it is good news or bad news, and a short list of the lines that actually need an explanation. This lesson shows you how to build exactly that in Excel, in a way you can reuse every month without retyping a single number.

This guide is for junior analysts, FP&A trainees, accountants moving into a finance business partner role, and anyone who has been handed a budget-versus-actual report and asked to "add some commentary". The case is a fictional retailer, Halden Outdoor Co., which sells hiking and camping gear. In the lab above you compare its September actuals with the budget for seven P&L lines, calculate the variance in USD and in percent, label each line favourable (F) or unfavourable (U), and flag the lines that cross the team's materiality threshold. The same four formulas work for a 7-line summary and for a 700-line cost centre report.

What a variance is, and why the sign convention matters

A variance is simply the difference between what happened and what was planned. The convention used in this course is variance = actual − budget. You will meet teams that use budget − actual, and both can work, but you must pick one and use it everywhere. With actual − budget, a positive number always means "more than planned" and a negative number always means "less than planned". That is easy to read, and it means you never have to remember which way round a particular report was built.

The trap is that more is not the same as better. Whether a variance is good news depends on the type of line:

  • Revenue and other income: more than budget is good. A positive variance is favourable (F), a negative one is unfavourable (U).
  • Costs and expenses: more than budget is bad, because you spent more than planned. A positive variance is unfavourable (U), a negative one is favourable (F).
  • Profit lines (gross profit, EBITDA, operating profit) behave like revenue: above budget is favourable.

This is why the lab has a Type column. Instead of writing a different formula for every row, you store the type of each line once and let a single IF formula decide which rule applies. When a new line is added next month, you just set its type, and the label is right automatically.

The second idea is materiality. A real P&L has dozens or hundreds of lines, and almost every one of them will be a little off. Your manager does not want a paragraph about a 40-dollar difference in bank charges. Teams therefore agree a rule such as "explain anything that is at least USD 10,000 or at least 10% away from budget". The absolute limit catches big lines where a small percentage is still a lot of money, such as revenue. The percentage limit catches small lines that have gone badly off track, such as a software budget that has jumped by a seventh. Both limits live in their own cells (B10 and B11 in the lab) so the rule can change without touching any formula.

The thresholds in this lesson (USD 10,000 or 10%) are an example. Your company may use different limits, different limits for revenue and cost, or limits that depend on the size of the line. Ask for the agreed rule in your first week.

Worked example: Halden Outdoor, September

Here is the finished variance table. Columns C and D are the inputs from the lab; E to H are calculated. All figures are fictional and in USD.

E2: =D2-C2 · F2: =E2/C2 · G2: =IF(B2="Revenue",IF(E2>=0,"F","U"),IF(E2<=0,"F","U")) · H2: =IF(OR(ABS(E2)>=$B$10,ABS(F2)>=$B$11),"Explain","OK")
LineTypeBudget (C)Actual (D)Variance (E)Var % (F)F / U (G)Comment? (H)
RevenueRevenue482,000468,500−13,500−2.8%UExplain
COGSCost260,300251,200−9,100−3.5%FOK
PayrollCost98,000101,400+3,400+3.5%UOK
MarketingCost24,00031,200+7,200+30.0%UExplain
RentCost18,50018,50000.0%FOK
SoftwareCost6,2007,050+850+13.7%UExplain
TravelCost5,4004,100−1,300−24.1%FExplain

The four formulas, written for row 2 and copied down to row 8, are:

  • Variance in E2: =D2-C2
  • Variance % in F2: =E2/C2, formatted as a percentage with Ctrl + Shift + %. Always divide by the budget, because the question is "how far are we from the plan?". If a budget can be zero, use =IF(C2=0,"",E2/C2) so you do not get #DIV/0!.
  • F / U label in G2: =IF(B2="Revenue",IF(E2>=0,"F","U"),IF(E2<=0,"F","U")). The outer IF checks the type, the inner IF applies the right rule.
  • Comment flag in H2: =IF(OR(ABS(E2)>=$B$10,ABS(F2)>=$B$11),"Explain","OK"). ABS removes the sign, so a big favourable variance is flagged too. The dollar signs lock the references to the threshold cells when you copy the formula down.

Four lines need a comment. Revenue is only 2.8% short, but 13,500 dollars is above the USD 10,000 limit. Marketing is 7,200 dollars over, which is below the dollar limit but a full 30% above plan. Software is a small line, yet 850 dollars on a 6,200 budget is 13.7%. Travel came in 24% under budget, a favourable variance that still has to be explained: is the saving real, or has a trip simply moved into October? COGS, Payroll and Rent stay below both limits and are marked OK.

For the last question in the lab, the largest unfavourable cost line in dollars, you can read the table or let Excel find it: =MAXIFS(E2:E8,B2:B8,"Cost") returns 7,200, and =INDEX(A2:A8,MATCH(MAXIFS(E2:E8,B2:B8,"Cost"),E2:E8,0)) returns the name, Marketing. Revenue has a bigger shortfall, but it is not a cost line, which is exactly why the Type column matters.

September variance (actual − budget), USD-20k-15k-10k-5k05k10k15k20kRevenue-13.5k UCOGS-9.1k FPayroll+3.4k UMarketing+7.2k URent0Software+0.85k UTravel-1.3k F
The sign alone does not tell you whether a variance is good or bad. Negative revenue is unfavourable (peach), negative COGS is favourable (mint). Colour by the F/U label, not by the sign.

From a list of variances to a story your manager can repeat

A table of line variances answers "what moved". Your manager will usually be asked a simpler question by their own boss: "what happened to profit?" So the next step is to roll the lines up. Gross profit is revenue minus COGS, operating profit is gross profit minus the operating costs. Using the same actual − budget logic:

Roll-up of the September lines. Gross profit = Revenue − COGS; operating profit = Gross profit − Payroll − Marketing − Rent − Software − Travel
SubtotalBudgetActualVarianceF / U
Gross profit221,700217,300−4,400U
Gross margin46.0%46.4%+0.4 ptsF
Operating costs (5 lines)152,100162,250+10,150U
Operating profit69,60055,050−14,550U

Now the story is visible. Operating profit is 14,550 dollars below budget. Lower sales cost 13,500 of revenue, but most of that was offset by lower COGS, because fewer goods sold means fewer goods bought. The gross margin actually improved slightly, from 46.0% to 46.4%. The real damage is in operating costs, which ran 10,150 over plan, and about 70% of that is the marketing overspend. A waterfall chart (also called a bridge) shows exactly this walk from budget to actual profit.

Operating profit bridge, September (USD)020k40k60k80k69.6kBudget-13.5kRevenue+9.1kCOGS-3.4kPayroll-7.2kMarketing0Rent-0.85kSoftware+1.3kTravel55.05kActualTotalRaises profitLowers profit
Budget to actual operating profit. In a profit bridge every bar shows the effect on profit, so a cost overspend points down even though its variance is positive.

One more analyst trick: part of the favourable COGS variance is not a real saving. COGS was budgeted at 54.0% of revenue (260,300 ÷ 482,000). If that ratio had held on the actual revenue, COGS would have been about 253,000 (=D2*C3/C2). The actual COGS of 251,200 is therefore only about 1,800 better because of better buying or pricing; the other roughly 7,300 is simply lower volume. Splitting a variance into a volume part and a rate part like this is called flexing the budget, and it is the single most useful habit you can bring to a variance meeting. Lesson 3 of this course takes the same idea to revenue with a price and volume bridge.

How to build the variance report in Excel, step by step

  1. Paste or link the budget and actual columns next to each other with one row per P&L line. Keep the line names exactly as they appear in the ledger export, so next month's paste lands in the same place.
  2. Add a Type column and enter Revenue or Cost for each line. To prevent typos, select the column and use Data › Data Validation › List with the source Revenue,Cost.
  3. Put the materiality limits in their own labelled cells, for example 10000 in B10 and 10% in B11. Format them so they look like inputs (many teams use blue font for inputs and black for formulas).
  4. In E2 type =D2-C2. Select E2:E8 and press Ctrl + D to fill the formula down.
  5. In F2 type =E2/C2, fill down, and press Ctrl + Shift + % for percent format. Use Home › Increase Decimal to show one decimal place.
  6. In G2 type the F/U formula. Check one revenue line and one cost line by hand before you fill it down.
  7. In H2 start typing =IF(OR(ABS(E2)>=B10, then press F4 to turn B10 into $B$10. Do the same for B11, finish the formula and fill down.
  8. Highlight the flagged rows: select A2:H8, choose Home › Conditional Formatting › New Rule › Use a formula, enter =$H2="Explain" and pick a light fill. The dollar sign before H keeps every column looking at the flag.
  9. Give the variance column a signed number format so plus and minus are obvious: Ctrl + 1 › Number › Custom and type +#,##0;-#,##0;0.
  10. Press Ctrl + ` (the grave accent key) to show all formulas at once and scan the columns for one that breaks the pattern. Press it again to return to values.
Sort or filter by the flag column (Ctrl + Shift + L turns on filters) to get your commentary list in one click. Never sort the source data itself if other sheets link to it by position.

Writing commentary that a manager actually reads

The numbers tell your manager what; your comment tells them why and what happens next. A good variance comment is one sentence with three parts: the size, the driver, and whether it repeats. Compare these two comments on the marketing line:

  • Weak: "Marketing over budget due to higher spend." It repeats the number and explains nothing.
  • Strong: "Marketing +7.2k (30%): autumn campaign launched in September instead of October. Timing only, October budget will be underspent by a similar amount." Size, driver, and a clear statement that it reverses.

For the Halden example, a complete note might look like the list below. The reasons are invented for practice; in real life you get them by asking the budget owners, never by guessing.

  • Revenue −13.5k (−2.8%, U): for example, fewer store visits in the last two weeks of the month because of wet weather; online sales on plan. Expected to recover partly in October.
  • Marketing +7.2k (+30%, U): for example, campaign timing as above. Phasing difference, no change to the full-year budget.
  • Software +0.85k (+13.7%, U): for example, three extra licences for the new planning tool. Recurring from now on, so the forecast should be updated.
  • Travel −1.3k (−24%, F): for example, a supplier visit moved to October. Timing only.

Notice the words timing and recurring. Managers care far more about whether a variance will repeat than about its size in one month. A 7,200 timing difference that reverses next month is less worrying than an 850 overspend that will repeat twelve times a year. Get into the habit of labelling every comment as one or the other.

Excel for New Financial Analysts course: a P&L variance table with budget, actual, variance and favourable or unfavourable labels
The variance table is the first thing you build at every month-end, and the base for the rest of this course.

Common mistakes and how to spot them

  • Mixing up the sign convention. If half the report uses actual − budget and the other half budget − actual, favourable and unfavourable get swapped silently. Check: revenue below budget must show a negative variance and a U.
  • Dividing by the actual instead of the budget. =E2/D2 gives a different and misleading percentage. Marketing would show 23.1% instead of 30%. Check that every percentage formula points at the budget column.
  • Forgetting ABS in the threshold test. Without it, E2>=$B$10 only catches positive variances, and the 13,500 revenue shortfall would be marked OK. Test your flag with a large negative number.
  • Relative references to the threshold. If B10 is not locked with dollar signs, the copied formula in row 3 looks at B11, row 4 at B12, and so on. Click a lower cell and read the formula bar.
  • Calling a favourable variance "fine". A big underspend can mean a missed invoice, a delayed project or an accrual that was forgotten. Flag it and explain it like any other variance.
  • Hard-coding numbers into formulas. =IF(ABS(E2)>=10000,…) works today, but when the limit changes to 15,000 you have to find every copy. Keep assumptions in cells.
  • Commentary that only restates the number. If your comment could be written without talking to anyone, it is not an explanation yet.

Quick reference: functions and shortcuts used in this lesson

Function or shortcutWhat it doesExample from the lab
=D2-C2Variance, actual minus budgetMarketing: 31,200 − 24,000 = 7,200
=E2/C2Variance as % of budget7,200 ÷ 24,000 = 30%
IFReturns one value if a test is true, another if false=IF(B2="Revenue",…,…)
ORTRUE if at least one condition is trueOR(ABS(E2)>=$B$10,ABS(F2)>=$B$11)
ABSRemoves the sign of a numberABS(-13500) = 13,500
MAXIFSLargest value that meets a condition=MAXIFS(E2:E8,B2:B8,"Cost") = 7,200
INDEX + MATCHReturns the name next to a found valueFinds "Marketing"
F4Toggles relative and absolute referencesB10 → $B$10
Ctrl + DFills the top cell's formula down the selectionE2 → E2:E8
Ctrl + Shift + %Percent number format0.3 → 30%
Ctrl + `Shows formulas instead of resultsAudit column E to H
Ctrl + Shift + LTurns filters on or offShow only "Explain"

Real-world uses

  • Month-end management pack. The variance table with flags and comments is the first page of most monthly reporting packs, from small companies to listed groups.
  • Cost centre reviews. Budget holders such as the marketing or IT manager get the same table for their own lines and are asked to supply the comments you flagged.
  • Quarterly forecast updates. The comments marked "recurring" are exactly the items that change the forecast for the rest of the year, which is the topic of the next lesson.
  • Project and grant tracking. Non-profits and project teams compare spend against an approved budget line by line, often with a percentage threshold set by the funder.
  • Interview case studies. Many finance interviews hand you a small budget-versus-actual table and ask what you would tell the CFO. The method in this lesson is the answer.

What you will build in the rest of this course

This first lesson is free. The other nineteen lessons are part of the full course (USD 9.90, one-time) and follow Halden Outdoor through your first 90 days, each one a hands-on lab with the same live checking you used above.

Lesson 2: Budget vs Forecast: Rolling the Latest Estimate (6+6). After the June close you combine six months of actuals with a fresh forecast for the remaining six, and show the full-year gap to budget. This is the "latest estimate" your manager will ask for every quarter.

Lesson 3: Revenue Bridge: Volume and Price Effects in a Waterfall. You split the revenue change from FY25 to FY26 into a volume effect and a price effect and draw it as a waterfall chart, the same flexing idea you met above for COGS, done properly.

Lesson 4: Headcount Cost Model: Salaries, Start Months and On-Costs. Payroll is usually the biggest cost line. You turn a hiring plan into full-year people cost per role and department, with start months, benefits and employer taxes.

Lesson 5: Driver-Based Forecast: Sessions × Conversion × Order Value. Instead of guessing a revenue number, you forecast online sales from three drivers, so every assumption can be challenged and changed on its own.

Lesson 6: KPI Pack: Margins, Year-on-Year Growth and Run-Rate. You build the quarterly KPI page: gross and EBITDA margins, growth against the same quarter last year, and an annual run-rate.

Lesson 7: Trailing Twelve Months (TTM): A Moving SUM. You calculate rolling twelve-month revenue for every month with one formula, the cleanest way to see a trend through seasonal noise.

Lesson 8: Ratio Snapshot: Liquidity, Leverage and Working Capital Days. For a bank meeting you put current and quick ratio, debt to equity, DSO and inventory days on one page, and learn what each one tells a lender.

Lesson 9: Scenario Toggle: Base, Upside and Downside with CHOOSE. One cell switches the whole FY27 plan between three scenarios, a pattern you will reuse in almost every model you build.

Lesson 10: Sensitivity Table: EBITDA for Price and Volume Changes. A 5 × 5 grid shows EBITDA for every combination of price and volume change, coloured so the loss-making corners stand out.

Lesson 11: Reconciling Two Exports: XLOOKUP and the Difference Column. Sales and finance disagree about September billing. You match two system exports invoice by invoice and find exactly where they break.

Lesson 12: One-Page Management Dashboard: KPIs, Chart and Highlights. You finish the 90 days with a one-page dashboard for the leadership meeting: margins, revenue against budget, the best month, a chart and data bars.

Summary

A variance report is four formulas and one discipline. Calculate the variance as actual − budget, express it as a percentage of budget, let a Type column decide whether each line is favourable or unfavourable, and flag everything that crosses an agreed USD or percentage limit, in either direction. Then roll the lines up to profit, flex the cost lines for volume where it matters, and write one sentence per flagged line that names the driver and says whether it repeats. Scroll up and do the lab now: it takes about a quarter of an hour, every cell is checked as you type, and at the end you will have answered the month-end question for Halden Outdoor exactly the way a manager wants to hear it.

Frequently asked questions

How do you calculate a budget variance in Excel?

Subtract the budget from the actual, for example =D2-C2, and copy the formula down. For the percentage variance divide by the budget: =E2/C2, formatted as a percentage.

Is a positive variance favourable or unfavourable?

It depends on the line. With actual minus budget, a positive revenue variance is favourable, but a positive cost variance is unfavourable because you spent more than planned.

What is a materiality threshold in variance analysis?

It is the size above which a variance must be explained, for example USD 10,000 or 10% of budget. Each company sets its own limits, so keep them in input cells rather than inside formulas.

Why use ABS in a variance flag formula?

ABS removes the sign, so both large overspends and large underspends are flagged. Without it, a big negative variance such as a revenue shortfall would be marked OK.

Should variance percentage be divided by budget or actual?

Divide by the budget, because the question is how far the result is from the plan. Dividing by the actual gives a different, smaller percentage for overspends and confuses readers.

What should a good variance comment include?

The size of the variance, the business driver behind it and whether it is a one-off timing difference or will recur. Get the reason from the budget owner rather than guessing.

Keep going: Excel for New Financial Analysts: Your First 90 Days

This free lab is lesson 1 of 20. Unlock the other 19 hands-on labs, the final exam and your certificate for a one-time $9.90.

  1. Budget vs Forecast: Rolling the Latest Estimate (6+6)
  2. Revenue Bridge: Volume and Price Effects in a Waterfall
  3. Headcount Cost Model: Salaries, Start Months and On-Costs
  4. Driver-Based Forecast: Sessions × Conversion × Order Value
  5. KPI Pack: Margins, Year-on-Year Growth and Run-Rate
  6. Trailing Twelve Months (TTM): A Moving SUM
  7. Ratio Snapshot: Liquidity, Leverage and Working Capital Days
  8. Scenario Toggle: Base, Upside and Downside with CHOOSE
  9. Sensitivity Table: EBITDA for Price and Volume Changes
  10. Reconciling Two Exports: XLOOKUP and the Difference Column
  11. One-Page Management Dashboard: KPIs, Chart and Highlights
  12. From Trial Balance to P&L: Account Mapping with XLOOKUP and the Tie-Out Check
  13. Budget Phasing: Seasonal Monthly Budgets, YTD Variance and What Is Left to Spend
  14. Forecast Accuracy: Errors, MAPE and Bias by Product Line
  15. Allocating Shared Costs: Drivers, Department Contribution and Why the Driver Matters
  16. Cash Conversion: From EBITDA to Free Cash Flow with Working Capital and a Waterfall
  17. Capex Request: Payback, NPV and IRR for a New Store Fit-Out
  18. Group Reporting in USD: FX Translation, Reported vs Constant-Currency Growth
  19. Final Project: Month-End Board Pack – YTD vs Budget and Last Year, Margins & a Combo Chart

Buy course — $9.90 See the full course