Session 10 lecture: Finance: budget accountability¶

# Finance data setup (synthetic). Run this cell first.
# Downloads the course's synthetic finance data from GitHub and checks it is the exact version this course uses.
import hashlib
import io
import os
import urllib.request

import pandas as pd

FINANCE_URL = "https://raw.githubusercontent.com/Aayush181509/AnalyticsLecture/main/data/synthetic/finance-v1/"
FINANCE_SHA256 = {
    "finance_actuals.csv": "3aeb2773990f86c21a1536b8854d7290794315e8bc872420c2e5df77151ac7fd",
    "finance_budget.csv": "238dbec8e7e80a61068c12c48280ff4f9de91198879ee295366774f530efad60",
}
FINANCE_DIR = os.environ.get("COURSE_FINANCE_DIR", "")


def load_finance(name):
    """Load one finance-v1 file: 'finance_actuals.csv' or 'finance_budget.csv'."""
    if FINANCE_DIR:
        with open(os.path.join(FINANCE_DIR, name), "rb") as f:
            data = f.read()
    else:
        data = urllib.request.urlopen(FINANCE_URL + name).read()
    if hashlib.sha256(data).hexdigest() != FINANCE_SHA256[name]:
        print("WARNING: this file differs from the course version. Ask your facilitator.")
    return pd.read_csv(io.BytesIO(data), parse_dates=["month"], keep_default_na=False,
                       na_values={"amount": [""], "budget": [""]})


print("Finance setup complete.")

Finance setup complete.

import matplotlib.pyplot as plt
import numpy as np

pd.set_option("display.float_format", "{:,.2f}".format)
plt.rcParams.update({
    "figure.figsize": (8, 4),
    "axes.spines.top": False,
    "axes.spines.right": False,
    "axes.grid": True,
    "axes.axisbelow": True,
    "grid.color": "#e6e5e1",
    "axes.prop_cycle": plt.cycler(color=["#2a78d6", "#eb6834", "#1baf7a"]),
    "axes.titleweight": "bold",
    "axes.titlelocation": "left",
})

# Colour-blind-safe pair used for every variance chart in this session.
# Blue = favourable (under budget), orange = unfavourable (over budget). Always add a legend too.
FAVOURABLE, UNFAVOURABLE = "#2a78d6", "#eb6834"

actuals = load_finance("finance_actuals.csv")
budget = load_finance("finance_budget.csv")
print("Postings:", len(actuals), "| budget cells:", len(budget))

Postings: 1378 | budget cells: 432

KEY = ["month", "department_id", "department", "account_id", "account"]

print("posting_id unique:", actuals["posting_id"].is_unique)
print("Most budget rows for one (month, department, account):", budget.groupby(KEY).size().max())
print("Names per department code:", actuals.groupby("department_id")["department"].nunique().max(),
      "| names per account code:", actuals.groupby("account_id")["account"].nunique().max())
print("Months:", actuals["month"].min().date(), "to", actuals["month"].max().date())
actuals.head()

posting_id unique: True
Most budget rows for one (month, department, account): 1
Names per department code: 1 | names per account code: 1
Months: 2025-01-01 to 2025-12-01

actuals.groupby("posting_type")["amount"].agg(postings="count", total="sum", smallest="min", largest="max")

actuals.loc[actuals["amount"] < 0,
            ["posting_id", "posting_date", "department", "account", "amount", "posting_type",
             "reference_posting_id", "description"]]

is_cell = lambda df: (df["month"] == "2025-05-01") & (df["department"] == "Sales") & (df["account"] == "Travel")
one_budget = budget[is_cell(budget)]
its_postings = actuals[is_cell(actuals)]

joined = its_postings.merge(one_budget, on=KEY)
print("Budget cells:", len(one_budget), "| postings:", len(its_postings), "| rows after the join:", len(joined))
print(f"True May budget:            {one_budget['budget'].sum():,.2f}")
print(f"'Budget' after the join:    {joined['budget'].sum():,.2f}")
joined[["posting_id", "amount", "budget"]]

Budget cells: 1 | postings: 6 | rows after the join: 6
True May budget:            12,000.00
'Budget' after the join:    72,000.00

naive = actuals.merge(budget, on=KEY)                                   # postings joined straight to budgets
matched_cells = budget.merge(actuals[KEY].drop_duplicates(), on=KEY)    # the same cells, each counted once

print(f"Budget after the naive join:            {naive['budget'].sum():,.2f}")
print(f"Budget of the same cells, counted once: {matched_cells['budget'].sum():,.2f}")
print(f"Inflation factor: {naive['budget'].sum() / matched_cells['budget'].sum():.2f}x")

Budget after the naive join:            14,771,900.00
Budget of the same cells, counted once: 6,126,500.00
Inflation factor: 2.41x

actual_cells = actuals.groupby(KEY, as_index=False).agg(actual=("amount", "sum"),
                                                        postings=("posting_id", "count"))
print("Posting rows:", len(actuals), "-> actual cells:", len(actual_cells))
actual_cells.head()

Posting rows: 1378 -> actual cells: 431

var = actual_cells.merge(budget, on=KEY, how="outer", indicator=True, validate="one_to_one")
var["match"] = var["_merge"].astype(str).map({"both": "actual and budget",
                                              "left_only": "actual only",
                                              "right_only": "budget only"})
var = var.drop(columns="_merge")
var["match"].value_counts()

match
actual and budget    425
budget only            7
actual only            6
Name: count, dtype: int64

var["postings"] = var["postings"].fillna(0).astype(int)
var["actual"] = var["actual"].fillna(0)            # budget-only cells: no postings, actual = 0

var["budget_status"] = np.select(
    [var["budget"].isna(), var["budget"] == 0],
    ["unknown budget", "approved zero budget"],
    default="positive budget",
)
var.loc[(var["match"] != "actual and budget") | (var["budget_status"] != "positive budget"),
        KEY[0:1] + ["department", "account", "postings", "actual", "budget", "match", "budget_status"]]

var["variance"] = var["actual"] - var["budget"]                     # NaN when the budget is unknown
var["variance_pct"] = np.where(var["budget"] > 0, var["variance"] / var["budget"], np.nan)
var["direction"] = np.select(
    [var["budget"].isna(), var["variance"] > 0, var["variance"] < 0],
    ["not assessed (budget unknown)", "unfavourable", "favourable"],
    default="on budget",
)
var.loc[(var["budget"] == 0) & (var["actual"] > 0), "direction"] = "unbudgeted spend against zero"

var["direction"].value_counts()

direction
unfavourable                     232
favourable                       197
not assessed (budget unknown)      6
on budget                          2
unbudgeted spend against zero      1
Name: count, dtype: int64

may = var.loc[is_cell(var), ["month", "department", "account", "actual", "budget", "variance", "variance_pct", "direction"]]
may.assign(variance_pct=may["variance_pct"].map("{:+.0%}".format))

def reconcile(table, label):
    """Compare a variance table with the two source tables and print PASS or FAIL for each check."""
    all_keys = pd.concat([actuals[KEY], budget[KEY]]).drop_duplicates()
    checks = [
        ("One row per (month, department, account)", not table.duplicated(KEY).any()),
        ("Every key from either source is present", len(table) == len(all_keys)),
        ("Actual total = signed posting total", abs(table["actual"].sum() - actuals["amount"].sum()) < 0.005),
        ("Budget total = total of unique budget cells", abs(table["budget"].sum() - budget["budget"].sum()) < 0.005),
    ]
    print(f"Reconciliation: {label}")
    print(f"  actual {table['actual'].sum():,.2f} vs postings {actuals['amount'].sum():,.2f}")
    print(f"  budget {table['budget'].sum():,.2f} vs budget cells {budget['budget'].sum():,.2f}")
    for name, ok in checks:
        print(f"  {'PASS' if ok else 'FAIL'}  {name}")


reconcile(var, "aggregate first, then outer join")

Reconciliation: aggregate first, then outer join
  actual 6,241,801.60 vs postings 6,241,801.60
  budget 6,133,000.00 vs budget cells 6,133,000.00
  PASS  One row per (month, department, account)
  PASS  Every key from either source is present
  PASS  Actual total = signed posting total
  PASS  Budget total = total of unique budget cells

AMOUNT_LIMIT = 5000
PCT_LIMIT = 0.25
MIN_AMOUNT_FOR_PCT = 1000

big_amount = var["variance"].abs() >= AMOUNT_LIMIT
big_pct = (var["variance_pct"].abs() >= PCT_LIMIT) & (var["variance"].abs() >= MIN_AMOUNT_FOR_PCT)
var["review_flag"] = np.select([big_amount & big_pct, big_amount, big_pct],
                               ["amount and %", "amount", "%"], default="")

flagged = var[var["review_flag"] != ""]
print("Cells flagged:", len(flagged), "of", len(var))

known = var.dropna(subset=["variance_pct"])
fig, ax = plt.subplots(figsize=(8, 5))
ax.scatter(known["variance_pct"] * 100, known["variance"], s=14, color="#b8b6b0", label="Not flagged")
# Purple, green and black here mark review flags, not favourability (blue and orange are kept for that).
for flag, colour, marker in [("amount", "#7a5195", "o"), ("%", "#1baf7a", "s"), ("amount and %", "black", "D")]:
    part = known[known["review_flag"] == flag]
    ax.scatter(part["variance_pct"] * 100, part["variance"], s=36, color=colour, marker=marker,
               label=f"Flagged: {flag}")
ax.axhline(0, color="black", linewidth=0.8)
ax.axvline(0, color="black", linewidth=0.8)
ax.set_title("Monthly cells: variance in currency units against variance %")
ax.set_xlabel("Variance % of budget (right = over budget)")
ax.set_ylabel("Variance (currency units)")
ax.legend(fontsize=8)
plt.show()

Cells flagged: 17 of 438

ranked = flagged.sort_values("variance", key=abs, ascending=False)[
    ["month", "department", "account", "actual", "budget", "variance", "variance_pct", "review_flag"]]
ranked.assign(variance_pct=ranked["variance_pct"].map("{:+.0%}".format))

comparable = var[var["budget_status"] != "unknown budget"]      # cells with a known budget (including zero)
unknown = var[var["budget_status"] == "unknown budget"]

total_actual = comparable["actual"].sum()
total_budget = comparable["budget"].sum()
monthly = comparable.groupby("month")[["actual", "budget"]].sum()

print(f"Comparable scope: actual {total_actual:,.2f} | budget {total_budget:,.2f} | "
      f"variance {total_actual - total_budget:+,.2f}")
print(f"  Variance % from totals (correct):      {(total_actual - total_budget) / total_budget:+.1%}")
print(f"  Average of the 12 monthly %:           {((monthly['actual'] - monthly['budget']) / monthly['budget']).mean():+.1%}")
print(f"  Average of every cell's %:             {comparable['variance_pct'].mean():+.1%}")
print(f"Reported separately, actuals with unknown budget: {unknown['actual'].sum():,.2f} "
      f"({', '.join(sorted(unknown['account'].unique()))}, {len(unknown)} cells)")

Comparable scope: actual 6,208,423.43 | budget 6,133,000.00 | variance +75,423.43
  Variance % from totals (correct):      +1.2%
  Average of the 12 monthly %:           +1.2%
  Average of every cell's %:             -0.2%
Reported separately, actuals with unknown budget: 33,378.17 (Cloud hosting, 6 cells)

sales_travel = var[(var["department"] == "Sales") & (var["account"] == "Travel")].set_index("month")
display(sales_travel[["postings", "actual", "budget", "variance", "direction"]])

may_june = sales_travel.loc["2025-05-01":"2025-06-01", ["actual", "budget", "variance"]].sum()
print(f"May + June together: actual {may_june['actual']:,.2f} | budget {may_june['budget']:,.2f} | "
      f"variance {may_june['variance']:+,.2f}")

May + June together: actual 26,660.79 | budget 21,200.00 | variance +5,460.79

same_content = [c for c in actuals.columns if c != "posting_id"]
duplicates = actuals[actuals.duplicated(same_content, keep=False)]
reversal = actuals[actuals["posting_type"] == "reversal"]
pd.concat([duplicates, reversal])[["posting_id", "posting_date", "month", "department", "account",
                                   "amount", "posting_type", "reference_posting_id", "description"]]

view = comparable.groupby(["department", "account"])[["actual", "budget"]].sum()
view["variance"] = view["actual"] - view["budget"]
view["variance_pct"] = np.where(view["budget"] > 0, view["variance"] / view["budget"], np.nan)
view = view.sort_values("variance", key=abs, ascending=False)

print(f"Department x account lines: {len(view)} | actual {view['actual'].sum():,.2f} "
      f"| budget {view['budget'].sum():,.2f}  (should equal the comparable totals in Part 9)")
view.head(10).assign(variance_pct=view["variance_pct"].head(10).map("{:+.1%}".format))

Department x account lines: 36 | actual 6,208,423.43 | budget 6,133,000.00  (should equal the comparable totals in Part 9)

top = view.head(12).iloc[::-1]                   # largest at the top of the chart
labels = [f"{d} | {a}" for d, a in top.index]
colours = [UNFAVOURABLE if v > 0 else FAVOURABLE for v in top["variance"]]

fig, ax = plt.subplots(figsize=(9, 5.5))
ax.barh(labels, top["variance"], color=colours)
ax.axvline(0, color="black", linewidth=0.8)
for y, (v, p) in enumerate(zip(top["variance"], top["variance_pct"])):
    ax.text(v, y, f" {v:+,.0f} ({p:+.0%}) " , va="center", ha="left" if v > 0 else "right", fontsize=8)
ax.set_title("Largest annual expense variances, 2025 (comparable budget cells)")
ax.set_xlabel("Variance = actual − budget (currency units)")
ax.legend(handles=[plt.Rectangle((0, 0), 1, 1, color=UNFAVOURABLE),
                   plt.Rectangle((0, 0), 1, 1, color=FAVOURABLE)],
          labels=["Over budget (unfavourable)", "Under budget (favourable)"], loc="lower right", fontsize=8)
ax.margins(x=0.25)
plt.show()

unknown_total = unknown["actual"].sum()
top_line = view.index[0]
print(
    f"In 2025, comparable expenses were {total_actual:,.0f} against a budget of {total_budget:,.0f}, "
    f"{(total_actual - total_budget) / total_budget:+.1%} overall. "
    f"The largest line variance is {top_line[0]} {top_line[1].lower()} at {view['variance'].iloc[0]:+,.0f} "
    f"({view['variance_pct'].iloc[0]:+.1%}). "
    f"A further {unknown_total:,.0f} of {', '.join(unknown['account'].unique()).lower()} spending had no budget "
    f"and is reported separately."
)

In 2025, comparable expenses were 6,208,423 against a budget of 6,133,000, +1.2% overall. The largest line variance is Operations salaries and wages at +40,096 (+2.7%). A further 33,378 of cloud hosting spending had no budget and is reported separately.