What does actual vs last year mean?

It compares this period result against the same period a year earlier, which controls for seasonality. It answers whether the business grew. Actual against budget answers a different question: whether it did what it said it would.

Should I compare to budget or to last year?

Both, in the same table, in fixed columns. Budget measures the promise, last year measures the growth. A month can beat budget and be down on last year, and that pair of facts is usually the finding.

How do I compare year to date against an annual budget?

Phase the budget into months first, then sum only the months that have closed. Comparing eight months of actual against a twelve month plan makes every line look like a 33 percent miss.

What order should the columns go in?

Actual, prior year, then the prior year variance; then budget and the budget variance; then forecast. Group each reference point with its own variance so no reader has to count across the table to find which pair belongs together.

← All Kits · Excel Kit

Actual vs Last Year vs Budget: Three Comparisons in One Table

Michael Nocito · Updated August 2026 · Every number on this page was worked before it was published

One actual number, three reference points. Compared to last year it is growth. Compared to budget it is delivery. Compared to forecast it is whether anyone can predict anything. The same 88,250 can be a good month and a bad month at the same time, and the table has to let both be visible.

What you do: fix the column order, group each reference with its own variance, and phase the annual plan into months before anything is summed. Below is the layout and the one arithmetic mistake that makes a report wrong without making it look wrong.

The short version. Budget answers "did we do what we said". Last year answers "are we growing". Put both on the page and let the reader see when they disagree.

The layout

Region	Actual	Last year	vs LY	vs LY %	Budget	vs Bud	vs Bud %
North	131,400	118,000	+13,400	+11.4%	120,000	+11,400	+9.5%
South	88,250	81,000	+7,250	+9.0%	95,000	-6,750	-7.1%
East	139,100	146,500	-7,400	-5.1%	140,000	-900	-0.6%
West	74,800	52,300	+22,500	+43.0%	60,000	+14,800	+24.7%

South is the row that earns the table. It grew 9.0 percent on last year and missed its plan by 7.1 percent. Both are true. A report that carries only the budget column says South had a bad month. A report that carries only the prior year column says South had a good one. The two-reference table says the honest thing: South grew, and somebody planned for more growth than that.

East is the same shape in reverse. Practically on plan, and down 5.1 percent on last year. If your review only ever looks at budget variance, East is the row you will keep not discussing, for as long as the plan keeps being set at last year minus a bit.

Keep each variance next to its own reference. Actual, prior year, prior year variance, then budget, budget variance. When all the variances are bunched at the right of the table, readers pair them with the wrong column, and they do it silently.

The phasing mistake

This is the one that produces confidently wrong reports. The plan is annual, 400,000. Eight months have closed and the actual to date is 268,000. Someone writes 268,000 / 400,000 and reports 67 percent of plan, which reads as a serious miss.

It is a miss against nothing. Eight of twelve months is 66.7 percent of the year, so 268,000 against a straight-line phasing of 266,667 is a hair ahead.

Comparison	Working	Result	True?
YTD against annual plan	268,000 / 400,000	67% of plan	Meaningless
YTD against straight-line 8 months	268,000 / 266,667	+0.5%	Only if the business is flat
YTD against phased 8 months	268,000 / phased plan	The real answer	Yes

Straight line is itself an assumption, and a bad one for anything seasonal. A retailer with a December makes eight months look wonderful on a straight-line phasing every single year. Phase the plan the way the business actually earns, store the phased months as their own row in the model, and sum those.

Getting the prior year column in

Two ways, and the choice matters more than it looks.

Approach	Formula	When it is right
Lookup from a prior year sheet	`=XLOOKUP(A2,LY!A:A,LY!C:C,"not in LY")`	Two separate files that will not be reloaded
One long table, filtered	`=SUMIFS(Amount,Region,A2,Year,$C$1-1)`	Anything that gets refreshed. Prefer this.

The fourth argument of XLOOKUP is not optional here. A region that exists this year and not last year must say so, not return #N/A that gets swept into a zero, which would make a brand new region look like a 100 percent gain. The full comparison of the two lookup functions is in VLOOKUP against XLOOKUP.

Which comparison belongs in which report

Audience	Lead with	Because
Board or owners	Last year	They are asking whether the business is growing
Budget holders	Budget	They committed to a number and own the gap
Operations weekly	Forecast	Only the latest view can still be acted on
Anyone new to the numbers	Both, with a sentence	The disagreement is the finding

How to apply this to your own work

Open your monthly pack and count the reference points. If there is only one, add the other and see how many rows change their story.
Check any year to date percentage in the pack. Find out whether the denominator is the annual plan or the phased plan for the closed months.
If the plan is straight lined, plot last year by month. If that line is not flat, the phasing is fiction and the early-year rows are flattering.
Move every variance column so it sits beside the reference it belongs to, and put the reference name in the header, not just "Var %".
Add a not-in-last-year label rather than a zero for anything new. New lines and dead lines are the two rows that break growth percentages.

The one habit to keep

Say the comparison out loud with its reference attached: "up nine percent on last year, seven percent behind plan". A percentage with no reference is not a fact, it is half of one, and the missing half is where the argument in the meeting comes from.

In your last pack, was there a row that beat budget and shrank on last year? Did anyone notice?

Every number here was worked before it was published. 268,000 against a straight-line eight-twelfths of 400,000 is 266,667, a difference of 1,333 or 0.5 percent.

The number is not in dispute. Which number it should sit next to is.

Excel for Analysts is 378 pages that read every formula and dialog one at a time, so the workbook stops being a place where numbers appear and becomes one you can check.

Excel for Analysts, $19 →

Build the table rather than reading about it.

The Excel Kit covers the lookups and the layout, and month over month growth handles the time arithmetic. For the percentage rules, see the variance percentage traps.

Open the Excel Kit →