Build a Budget vs Actual Variance Report in Excel with Python

The budget-versus-actual report is the centre of most month-end packs. Its layout is standard enough that reviewers expect it: report lines grouped into revenue and costs with subtotals, a net line, the month's budget, actual and variance, the same three for the year to date, and something that draws the eye to what went wrong. Building it by hand each month means copying figures, re-dragging formulas and fixing the subtotals someone broke. This page, part of Building Finance and Operations Reports, builds it from two tidy inputs with pandas and xlsxwriter, writing the source figures as values and the arithmetic as formulas a reviewer can trace.

The guide covers loading a ledger export, mapping accounts and tying totals back to the source. This page starts where that leaves off: monthly actuals and budget already summarised to report lines.

Prerequisites

pip install pandas openpyxl xlsxwriter

budget.xlsx has one row per month and report line with its section — Revenue or Costs — and the budget. actuals.xlsx has the actual per month and line, as produced by the ledger summary in the guide. The data covers July to September 2026; the financial year starts in July.

Step 1 — Aggregate the month and the year to date

Choose the reporting month and the start of the financial year, then aggregate both periods. The budget file drives the list of lines and their order, so a line with no actuals still appears:

import pandas as pd

month, year_start = "2026-09", "2026-07"
budget = pd.read_excel("budget.xlsx")
actual = pd.read_excel("actuals.xlsx")
data = budget.merge(actual, on=["Month", "Line"], how="left", validate="one_to_one").fillna({"Actual": 0})

def period(df, first, last):
    part = df[(df["Month"] >= first) & (df["Month"] <= last)]
    return part.groupby(["Section", "Line"], sort=False)[["Budget", "Actual"]].sum()

mtd = period(data, month, month)
ytd = period(data, year_start, month)
table = mtd.join(ytd, lsuffix=" month", rsuffix=" ytd").reset_index()
print(table.to_string(index=False))

Months are stored as YYYY-MM text, so comparing them as strings orders them correctly. sort=False keeps the lines in the order the budget lists them — the order the finance team chose — rather than alphabetically. validate="one_to_one" stops the run if an export repeats a line for a month.

Step 2 — Lay out sections, subtotals and the net line

A variance report is not a flat table: each section has its lines, then a subtotal, and the net line comes last. Build the layout as a list of rows, each tagged with its kind, so the writing step knows which rows hold values and which hold formulas:

layout = []
for section in ["Revenue", "Costs"]:
    for _, r in table[table["Section"] == section].iterrows():
        layout.append({"kind": "line", "section": section, "label": r["Line"],
                       "values": [r["Budget month"], r["Actual month"], r["Budget ytd"], r["Actual ytd"]]})
    layout.append({"kind": "subtotal", "section": section, "label": f"Total {section.lower()}"})
layout.append({"kind": "net", "section": None, "label": "Net profit"})
print([(row["kind"], row["label"]) for row in layout])

Keeping the structure in data, rather than in a chain of write calls with hard-coded row numbers, means adding a line to the budget file adds it to the report with no code change.

Step 3 — Write values and formulas

Now write the sheet. Budget and actual cells get values; subtotals get SUM formulas over their section; the net line subtracts total costs from total revenue; and every variance is a formula whose direction depends on the section, so positive always means favourable:

from xlsxwriter.utility import xl_rowcol_to_cell as cell

wb_path = f"budget_vs_actual_{month}.xlsx"
with pd.ExcelWriter(wb_path, engine="xlsxwriter") as writer:
    wb = writer.book
    ws = wb.add_worksheet("Variance")
    writer.sheets["Variance"] = ws
    money = wb.add_format({"num_format": "#,##0;[Red](#,##0);-"})
    bold_money = wb.add_format({"num_format": "#,##0;[Red](#,##0);-", "bold": True, "top": 1})
    pct = wb.add_format({"num_format": "0.0%;[Red](0.0%);-"})
    head = wb.add_format({"bold": True, "bottom": 1, "text_wrap": True, "align": "center"})
    ws.write("A1", "Budget vs actual — September 2026", wb.add_format({"bold": True, "font_size": 14}))
    headers = ["Line", "Month budget", "Month actual", "Variance", "Var %", "YTD budget", "YTD actual", "Variance", "Var %", "Comment"]
    ws.write_row(2, 0, headers, head)

    r, first_line, totals = 3, {}, {}
    for row in layout:
        fmt = money if row["kind"] == "line" else bold_money
        label_fmt = None if row["kind"] == "line" else wb.add_format({"bold": True})
        ws.write(r, 0, row["label"], label_fmt)
        for block, col in ((0, 1), (1, 5)):
            b, a = cell(r, col), cell(r, col + 1)
            if row["kind"] == "line":
                first_line.setdefault(row["section"], r)
                ws.write_number(r, col, row["values"][block * 2], fmt)
                ws.write_number(r, col + 1, row["values"][block * 2 + 1], fmt)
            elif row["kind"] == "subtotal":
                top = first_line[row["section"]]
                ws.write_formula(r, col, f"=SUM({cell(top, col)}:{cell(r - 1, col)})", fmt)
                ws.write_formula(r, col + 1, f"=SUM({cell(top, col + 1)}:{cell(r - 1, col + 1)})", fmt)
                totals[(row["section"], col)] = r
            else:
                rev, cost = totals[("Revenue", col)], totals[("Costs", col)]
                ws.write_formula(r, col, f"={cell(rev, col)}-{cell(cost, col)}", fmt)
                ws.write_formula(r, col + 1, f"={cell(rev, col + 1)}-{cell(cost, col + 1)}", fmt)
            favourable_up = row["section"] != "Costs"
            ws.write_formula(r, col + 2, f"={a}-{b}" if favourable_up else f"={b}-{a}", fmt)
            ws.write_formula(r, col + 3, f'=IF({b}=0,"",{cell(r, col + 2)}/ABS({b}))', pct)
        r += 1

    ws.set_column("A:A", 20)
    ws.set_column("B:I", 11)
    ws.set_column("J:J", 36)
    ws.freeze_panes(3, 1)
print("rows written:", r - 3)

The variance percentage divides by the absolute budget so its sign follows the variance, and IF(...=0,"",...) leaves it blank instead of showing #DIV/0! when a line has no budget. The net line counts as favourable-up, like revenue: more profit than budgeted is good. The number format's second section, [Red](#,##0), colours every negative figure red — and because negative variances are exactly the unfavourable ones, no conditional formatting is needed. The empty Comment column is for the reviewer's explanation of each variance.

Step 4 — Check the formulas give the right numbers

xlsxwriter does not calculate formulas: it stores 0 as each formula's cached result. Excel recalculates when the file is opened, so the reviewer sees the right numbers, but any program that reads cached results — pandas, openpyxl with data_only=True — sees zeros. Verify the arithmetic in pandas, independently of the formulas, so a mistake in a cell reference is caught before the file is sent:

check = table.copy()
sign = check["Section"].map({"Revenue": 1, "Costs": -1})
for p in ["month", "ytd"]:
    check[f"Var {p}"] = sign * (check[f"Actual {p}"] - check[f"Budget {p}"])
rev = check[check.Section == "Revenue"].sum(numeric_only=True)
cost = check[check.Section == "Costs"].sum(numeric_only=True)
print(check[["Line", "Var month", "Var ytd"]].to_string(index=False))
print("net profit month:", rev["Actual month"] - cost["Actual month"], "budget:", rev["Budget month"] - cost["Budget month"])
print("net profit ytd:  ", rev["Actual ytd"] - cost["Actual ytd"], "budget:", rev["Budget ytd"] - cost["Budget ytd"])

Product revenue is 14,000 ahead for the month, Travel 2,350 over, and net profit is 21,560 against a budget of 18,000 — a favourable 3,560. The same figures appear in the workbook once it is opened and recalculated. If a downstream process needs the calculated values stored in the file, load and save it with openpyxl — which drops the cached zeros — and let Excel or LibreOffice recalculate it, or write those cells as values instead.

Step 5 — Carry reviewer comments forward

Reviewers write explanations in the Comment column: "Travel — conference in Lisbon, budgeted in October". When next month's report is generated, the comments on continuing lines are useful context, and losing them each month is a common complaint. Read last month's file, if it exists, and pre-fill each comment prefixed with the month it came from:

from pathlib import Path
from openpyxl import load_workbook

def previous_comments(path):
    if not Path(path).exists():
        return {}
    ws = load_workbook(path, read_only=True)["Variance"]
    return {row[0]: row[9] for row in ws.iter_rows(min_row=4, values_only=True) if row[0] and row[9]}

print(previous_comments("budget_vs_actual_2026-08.xlsx") or "no previous report — comments start empty")

When the August file exists, write each returned comment into column J as "Aug: " + text, so the reviewer can keep, update or delete it. Matching on the line label means a renamed line starts fresh, which is the safe behaviour.

Common pitfalls

• Variance signs by line type. Use budget − actual for costs; positive must always mean favourable.
• Hard-coded row numbers. Build the layout from data so new lines need no code change.
• Inner joins dropping lines. Let the budget define the lines and fill missing actuals with zero.
• #DIV/0! on zero-budget lines. Guard the percentage formula with IF.
• Trusting unrecalculated formulas. Check the numbers in pandas or recalculate before sending.
• Rounding the data. Round in the number format; rounded values stop subtotals adding up.

Performance notes

A report with a few hundred lines writes in well under a second. The expensive part is usually the ledger summary upstream; cache the monthly actuals so the year-to-date columns do not re-read a year of transactions every month.

Conclusion

Aggregate the month and the year to date from monthly budget and actuals, letting the budget define the lines. Describe the layout — lines, subtotals and the net line — as data, then write source figures as values and subtotals, net and variances as formulas, with the variance direction chosen so positive is favourable. Use a red negative number format to highlight unfavourable variances, check the arithmetic independently in pandas, and carry reviewer comments forward from last month's file.

Frequently asked questions

How do I show month and year-to-date in one variance report? Filter the monthly data twice — once for the reporting month and once for every month from the start of the financial year up to it — aggregate both to the report lines, and place them side by side: budget, actual and variance for the month, then the same three for year to date.

Should variance be actual minus budget? For revenue, yes. For costs, use budget minus actual, so that a positive variance always means favourable. Readers can then scan the variance column without remembering which lines run the other way.

Why write the variance as a formula instead of a value? Reviewers click a variance to see how it was worked out, and sometimes type an adjusted actual to test a correction. A formula such as =D5-C5 shows the calculation and updates when they do. The budget and actual figures stay as values from the source.

How do I colour unfavourable variances red? Use a number format with a colour section, such as #,##0;[Red](#,##0);-, which Excel applies automatically to negative values, or a conditional format on the variance columns. Because positive means favourable, every negative variance is unfavourable.

Related

• Up one level: Building Finance and Operations Reports — the shared pipeline behind these reports.
• Build an Accounts Receivable Aging Report — the other month-end staple.
• Add Subtotals and Grand Totals to a pandas Pivot Table — subtotals computed in pandas.
• Write Formula to Excel Cell with openpyxl — formulas from Python.