Build a budget vs actual variance report for an April–March year

The shape finance sends, and the shape you need

Budgets are usually prepared as a sheet with one row per line item and twelve month columns, April to March. Actuals come from the ledger as a long list: line, month, amount. You cannot compare them until both are long, one row per line per month. In pandas that is a melt; in Power Query it is Unpivot Other Columns, covered step by step in unpivot April–March month columns in Power Query.

The month number is the other piece. Calendar month numbers put January before April, which breaks every "up to month N" comparison. Use a FY month number where April is 1 and March is 12, as built in add Indian financial year and quarter columns.

Year-to-date variance in pandas

The budget, actuals and line items below are synthetic, for FY 2026-27 with August as the last closed month.

import pandas as pd

MONTHS = ["Apr", "May", "Jun", "Jul", "Aug", "Sep", "Oct", "Nov", "Dec", "Jan", "Feb", "Mar"]

# Synthetic FY 2026-27 budget, as finance usually sends it: one row per line, 12 month columns.
budget_wide = pd.DataFrame({
    "line": ["Revenue", "Salaries", "Rent", "Marketing", "Travel"],
    "type": ["income", "cost", "cost", "cost", "cost"],
    **{m: v for m, v in zip(MONTHS, zip(
        *[[1800000, 1850000, 1900000, 2000000, 2100000, 2200000, 2600000, 2400000, 2300000, 2200000, 2200000, 2500000],
          [950000] * 12,
          [180000] * 12,
          [120000, 120000, 150000, 150000, 200000, 250000, 400000, 300000, 150000, 150000, 150000, 200000],
          [40000] * 12]))},
})
budget = budget_wide.melt(id_vars=["line", "type"], var_name="month", value_name="budget")
budget["fy_month"] = budget["month"].map({m: i + 1 for i, m in enumerate(MONTHS)})

# Synthetic actuals from the ledger, April to August booked so far.
actual = pd.DataFrame({
    "line": ["Revenue"] * 5 + ["Salaries"] * 5 + ["Rent"] * 5 + ["Marketing"] * 5 + ["Travel"] * 4,
    "fy_month": [1, 2, 3, 4, 5] * 4 + [1, 2, 4, 5],
    "actual": [1720000, 1905000, 1860000, 2080000, 1990000,
               948000, 951000, 962000, 975000, 980000,
               180000, 180000, 180000, 189000, 189000,
               95000, 132000, 171000, 148000, 262000,
               36000, 51000, 22000, 61000],
})

CLOSED_THROUGH = 5   # August is the last closed month

df = budget.merge(actual, on=["line", "fy_month"], how="left", validate="one_to_one")
ytd = (df[df["fy_month"] <= CLOSED_THROUGH]
       .groupby(["line", "type"], sort=False)[["budget", "actual"]].sum(min_count=1))
ytd["actual"] = ytd["actual"].fillna(0)

# Variance as actual - budget, then a label that means the same thing for every line.
ytd["variance"] = ytd["actual"] - ytd["budget"]
good = ((ytd.index.get_level_values("type") == "income") & (ytd["variance"] >= 0)) | \
       ((ytd.index.get_level_values("type") == "cost") & (ytd["variance"] <= 0))
ytd["verdict"] = ["favourable" if g else "adverse" for g in good]
ytd["variance_%"] = (100 * ytd["variance"] / ytd["budget"]).round(1)
ytd["material"] = (ytd["variance"].abs() >= 50000) & (ytd["variance_%"].abs() >= 5)
print(ytd.to_string())

full_year = budget.groupby("line", sort=False)["budget"].sum()
print("\nFY budget used so far (%):", (100 * ytd.droplevel("type")["actual"] / full_year).round(1).to_dict())
assert int(ytd["budget"].sum()) == int(budget.loc[budget["fy_month"] <= 5, "budget"].sum())

The output reads naturally once the labels are there. Revenue is ₹95,000 below budget for April to August, which is adverse but only 1%. Marketing is ₹68,000 over budget, 9.2%, the only line that crosses both materiality thresholds, so it is the one that needs a comment from its owner. Travel is 15% under budget and favourable, but look at the data before praising anyone: there is no June travel entry at all.

A missing month is not a zero

Travel's apparent saving comes partly from June having no actuals. That might mean nobody travelled, or it might mean June's travel bills have not been booked yet. The script keeps the difference visible by summing with min_count=1; a report that fills every missing month with zero before comparing would present unbooked costs as savings. Add a check that lists line-months with no actual for every closed month, and send it to accounts before the report goes out.

Why "favourable" needs the line type

A variance of +₹66,000 means different things on different lines. On revenue it is good; on salaries it is not. The type column in the budget carries that meaning so the label is computed rather than guessed:

Keep the sign of the number as actual minus budget on every line, and let the label carry the judgement. Colour-coding the number itself red or green, without a label, is how a board pack ends up with a green salary overrun.

Excel formulas for the same report

Put the last closed FY month number in a named cell ClosedMonth (5 for August). With the budget in a table Budget whose month columns run Apr to Mar, and actuals in a table Act with columns Line, FYMonth and Amount:

YTD budget   =SUMPRODUCT((Budget[Line]=A2)
                 *(COLUMN(Budget[[Apr]:[Mar]])-COLUMN(Budget[[Apr]:[Apr]])+1<=ClosedMonth)
                 *Budget[[Apr]:[Mar]])
YTD actual   =SUMIFS(Act[Amount], Act[Line], A2, Act[FYMonth], "<="&ClosedMonth)
Variance     =C2-B2
Verdict      =IF(OR(AND(Type="income",D2>=0),AND(Type="cost",D2<=0)),"favourable","adverse")
Variance %   =IFERROR(D2/B2,"")
Material     =AND(ABS(D2)>=50000,ABS(F2)>=5%)

Replace Type with the cell holding the line's type. The SUMPRODUCT counts month columns from April and keeps only those up to the closed month, so changing one cell rolls the whole report forward.

Useful additions without clutter

Full-year context. The last line of the script shows how much of each annual budget has been used after five of twelve months. Straight-line would be 41.7%; Salaries and Rent are at about 42%, which is expected for flat monthly costs. Marketing at 34.5% looks low, but its budget is back-loaded into October and November, so the percentage alone would mislead. Show it, and let the monthly phasing explain it.

Owners. Add a column naming who owns each line and who writes the commentary for material variances. Commentary without an owner is rarely written.

Control total. The final assert checks that the YTD budget in the report equals the budget file's total for months 1 to 5. If a line was dropped by the merge, it fails.

What not to do

Do not compare YTD actuals with the full-year budget and report 60% "under budget" in August. Do not reforecast inside the variance report; if management wants a revised full-year view, build a separate forecast column so the original budget remains the baseline. And do not re-phase the budget quietly after the year starts, because the variance history then stops meaning anything.

Variance reporting against an Indian financial year is one of the recurring MIS tasks we build in Excel and Power BI during our Data Analyst course, with the same emphasis on controls as on formulas.