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.
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.
| Line | Type | Budget (C) | Actual (D) | Variance (E) | Var % (F) | F / U (G) | Comment? (H) |
|---|---|---|---|---|---|---|---|
| Revenue | Revenue | 482,000 | 468,500 | −13,500 | −2.8% | U | Explain |
| COGS | Cost | 260,300 | 251,200 | −9,100 | −3.5% | F | OK |
| Payroll | Cost | 98,000 | 101,400 | +3,400 | +3.5% | U | OK |
| Marketing | Cost | 24,000 | 31,200 | +7,200 | +30.0% | U | Explain |
| Rent | Cost | 18,500 | 18,500 | 0 | 0.0% | F | OK |
| Software | Cost | 6,200 | 7,050 | +850 | +13.7% | U | Explain |
| Travel | Cost | 5,400 | 4,100 | −1,300 | −24.1% | F | Explain |
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.
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:
| Subtotal | Budget | Actual | Variance | F / U |
|---|---|---|---|---|
| Gross profit | 221,700 | 217,300 | −4,400 | U |
| Gross margin | 46.0% | 46.4% | +0.4 pts | F |
| Operating costs (5 lines) | 152,100 | 162,250 | +10,150 | U |
| Operating profit | 69,600 | 55,050 | −14,550 | U |
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.
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
- 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.
- 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. - 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).
- In E2 type
=D2-C2. Select E2:E8 and press Ctrl + D to fill the formula down. - In F2 type
=E2/C2, fill down, and press Ctrl + Shift + % for percent format. Use Home › Increase Decimal to show one decimal place. - In G2 type the F/U formula. Check one revenue line and one cost line by hand before you fill it down.
- 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. - 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. - Give the variance column a signed number format so plus and minus are obvious: Ctrl + 1 › Number › Custom and type
+#,##0;-#,##0;0. - 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.
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.

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/D2gives 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$10only 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 shortcut | What it does | Example from the lab |
|---|---|---|
=D2-C2 | Variance, actual minus budget | Marketing: 31,200 − 24,000 = 7,200 |
=E2/C2 | Variance as % of budget | 7,200 ÷ 24,000 = 30% |
IF | Returns one value if a test is true, another if false | =IF(B2="Revenue",…,…) |
OR | TRUE if at least one condition is true | OR(ABS(E2)>=$B$10,ABS(F2)>=$B$11) |
ABS | Removes the sign of a number | ABS(-13500) = 13,500 |
MAXIFS | Largest value that meets a condition | =MAXIFS(E2:E8,B2:B8,"Cost") = 7,200 |
INDEX + MATCH | Returns the name next to a found value | Finds "Marketing" |
| F4 | Toggles relative and absolute references | B10 → $B$10 |
| Ctrl + D | Fills the top cell's formula down the selection | E2 → E2:E8 |
| Ctrl + Shift + % | Percent number format | 0.3 → 30% |
| Ctrl + ` | Shows formulas instead of results | Audit column E to H |
| Ctrl + Shift + L | Turns filters on or off | Show 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.
- Budget vs Forecast: Rolling the Latest Estimate (6+6)
- Revenue Bridge: Volume and Price Effects in a Waterfall
- Headcount Cost Model: Salaries, Start Months and On-Costs
- Driver-Based Forecast: Sessions × Conversion × Order Value
- KPI Pack: Margins, Year-on-Year Growth and Run-Rate
- Trailing Twelve Months (TTM): A Moving SUM
- Ratio Snapshot: Liquidity, Leverage and Working Capital Days
- Scenario Toggle: Base, Upside and Downside with CHOOSE
- Sensitivity Table: EBITDA for Price and Volume Changes
- Reconciling Two Exports: XLOOKUP and the Difference Column
- One-Page Management Dashboard: KPIs, Chart and Highlights
- From Trial Balance to P&L: Account Mapping with XLOOKUP and the Tie-Out Check
- Budget Phasing: Seasonal Monthly Budgets, YTD Variance and What Is Left to Spend
- Forecast Accuracy: Errors, MAPE and Bias by Product Line
- Allocating Shared Costs: Drivers, Department Contribution and Why the Driver Matters
- Cash Conversion: From EBITDA to Free Cash Flow with Working Capital and a Waterfall
- Capex Request: Payback, NPV and IRR for a New Store Fit-Out
- Group Reporting in USD: FX Translation, Reported vs Constant-Currency Growth
- Final Project: Month-End Board Pack – YTD vs Budget and Last Year, Margins & a Combo Chart