User story: budget vs actual variance

The story

Priya needs a one-page variance report for the CFO. Every department, every GL account, budget vs actual, with the worst variances at the top.

The script

import pandas as pd

budget = pd.read_excel("budget_2026.xlsx")    # cols: dept, account, month, budget
actual = pd.read_excel("actual_2026.xlsx")    # cols: dept, account, month, actual

merged = budget.merge(actual,
                      on=["dept", "account", "month"],
                      how="outer").fillna(0)

merged["variance"]     = merged["actual"] - merged["budget"]
merged["variance_pct"] = (merged["variance"] / merged["budget"].replace(0, pd.NA)) * 100

# Flag the worrying lines
def flag(row):
    if abs(row["variance"]) > 50_000:           return "MATERIAL"
    if abs(row["variance_pct"] or 0) > 20:      return "HIGH-PCT"
    return ""
merged["flag"] = merged.apply(flag, axis=1)

# Sort: flagged first, then by absolute variance
report = (merged.assign(abs_var = merged["variance"].abs())
                 .sort_values(["flag", "abs_var"], ascending=[True, False])
                 .drop(columns="abs_var"))

report.to_excel("variance_april.xlsx", index=False)

Make the output executive-friendly

from openpyxl import load_workbook
from openpyxl.styles import Font, PatternFill

wb = load_workbook("variance_april.xlsx")
ws = wb.active

# Headers: bold, green
for cell in ws[1]:
    cell.font = Font(bold=True, color="FFFFFF")
    cell.fill = PatternFill("solid", fgColor="217346")

# Highlight MATERIAL rows
red = PatternFill("solid", fgColor="FEE2E2")
for row in ws.iter_rows(min_row=2):
    if row[ws.max_column - 1].value == "MATERIAL":
        for cell in row:
            cell.fill = red

# Currency formats
for col in "DEFG":
    for cell in ws[col][1:]:
        cell.number_format = '"$"#,##0;[Red]("$"#,##0)'

# Freeze pane + widths
ws.freeze_panes = "A2"
for col, w in zip("ABCDEFGH", [10, 12, 10, 14, 14, 14, 10, 14]):
    ws.column_dimensions[col].width = w

wb.save("variance_april.xlsx")

Walkthrough for video