Session 10 lab: Which spending needs review?¶

# 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("Budget keys unique:", not budget.duplicated(KEY).any())
print("Any negative budget:", (budget["budget"] < 0).any(), "| zero budgets:", (budget["budget"] == 0).sum())
print("Missing amounts:", actuals["amount"].isna().sum(), "| missing budgets:", budget["budget"].isna().sum())
actuals.groupby("posting_type")["amount"].agg(postings="count", total="sum", smallest="min", largest="max")

posting_id unique: True
Budget keys unique: True
Any negative budget: False | zero budgets: 3
Missing amounts: 0 | missing budgets: 0

# 1. Run the cell. How many actual cells are there, compared with posting rows?
# 2. Check: is the total of `actual` exactly equal to the total of the posting amounts?
# 3. Change "sum" to "max" for actual and run again. Why would that be wrong? Change it back.
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))
print(f"Sum of postings:     {actuals['amount'].sum():,.2f}")
print(f"Sum of actual cells: {actual_cells['actual'].sum():,.2f}")
actual_cells.head()

Posting rows: 1378 -> actual cells: 431
Sum of postings:     6,241,801.60
Sum of actual cells: 6,241,801.60

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["postings"] = var["postings"].fillna(0).astype(int)
var["actual"] = var["actual"].fillna(0)            # budget-only: no postings, so actual = 0
var["budget_status"] = np.select([var["budget"].isna(), var["budget"] == 0],
                                 ["unknown budget", "approved zero budget"], default="positive budget")

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"

pd.crosstab(var["match"], var["budget_status"])

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"  rows {len(table)} vs keys {len(all_keys)}")
    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, "my variance table")

Reconciliation: my variance table
  rows 438 vs keys 438
  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

# 1. Run the cell. Which month has the largest variance? Is it favourable or unfavourable?
# 2. Change department to "Sales", then "Technology". Which months stand out?
# 3. Why is variance_pct recomputed from the monthly totals rather than averaged from the cells?
department = "All"

scope = var if department == "All" else var[var["department"] == department]
comparable = scope[scope["budget_status"] != "unknown budget"]
monthly = comparable.groupby("month")[["actual", "budget"]].sum()
monthly["variance"] = monthly["actual"] - monthly["budget"]
monthly["variance_pct"] = monthly["variance"] / monthly["budget"]
monthly["unknown_budget_actual"] = scope[scope["budget_status"] == "unknown budget"].groupby("month")["actual"].sum()
monthly["unknown_budget_actual"] = monthly["unknown_budget_actual"].fillna(0)

total = monthly[["actual", "budget", "unknown_budget_actual"]].sum()
print(f"{department}: comparable actual {total['actual']:,.2f} | budget {total['budget']:,.2f} | "
      f"variance {total['actual'] - total['budget']:+,.2f} ({(total['actual'] - total['budget']) / total['budget']:+.1%}) "
      f"| unknown-budget actual {total['unknown_budget_actual']:,.2f}")
monthly.assign(variance_pct=monthly["variance_pct"].map("{:+.1%}".format))

All: comparable actual 6,208,423.43 | budget 6,133,000.00 | variance +75,423.43 (+1.2%) | unknown-budget actual 33,378.17

# 1. Run the cell. Compare the two lists. Which cells appear in both?
# 2. Change rank_by to "variance_pct". Which small cells rise to the top? Are they worth a meeting?
# 3. Change n to 15. Does the % list become dominated by one department or account?
rank_by = "variance"
n = 10

show = ["month", "department", "account", "postings", "actual", "budget", "variance", "variance_pct", "direction"]
known = var.dropna(subset=["variance_pct"])
top = known.sort_values(rank_by, key=abs, ascending=False).head(n)
top[show].assign(variance_pct=top["variance_pct"].map("{:+.0%}".format))

# 1. Run the cell. Which account has spending but no budget? In which months?
# 2. Change status to "approved zero budget". Which zero-budget cell has spending, and why is there no %?
# 3. Change status to "budget only" (it filters on the match column). What questions would you ask about these cells?
status = "unknown budget"

if status == "budget only":
    cells = var[var["match"] == "budget only"]
else:
    cells = var[var["budget_status"] == status]
print(f"{status}: {len(cells)} cells | actual {cells['actual'].sum():,.2f} | "
      f"budget known for {cells['budget'].notna().sum()} of {len(cells)} cells, total {cells['budget'].sum():,.2f}")
cells[["month", "department", "account", "postings", "actual", "budget", "variance", "direction"]]

unknown budget: 6 cells | actual 33,378.17 | budget known for 0 of 6 cells, total 0.00

# 1. Run the cell. In which month is the variance most unfavourable, and in which most favourable?
# 2. Change account to "Professional services" and department to "People". Which month has a negative posting?
# 3. Set months to ["2025-09-01", "2025-10-01", "2025-11-01"] for People professional services.
department, account = "Sales", "Travel"
months = ["2025-05-01", "2025-06-01"]

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

postings = actuals[(actuals["department"] == department) & (actuals["account"] == account)
                   & actuals["month"].isin(pd.to_datetime(months))]
postings[["posting_id", "posting_date", "month", "amount", "posting_type", "reference_posting_id", "description"]]

same_content = [c for c in actuals.columns if c != "posting_id"]
dupes = actuals[actuals.duplicated(same_content, keep=False)]
print("Postings that have an identical twin:", len(dupes))
display(dupes[["posting_id", "posting_date", "department", "account", "amount", "description"]])

reversals = actuals[actuals["posting_type"] == "reversal"]
print("Reversals and the posting each one cancels:")
reversals.merge(actuals, left_on="reference_posting_id", right_on="posting_id", suffixes=("", "_reversed"))[
    ["posting_id", "month", "amount", "posting_id_reversed", "month_reversed", "amount_reversed"]]

Postings that have an identical twin: 2

Reversals and the posting each one cancels:

# 1. Run the cell. Which departments are over budget for the year? Which are under?
# 2. Change group to "account". Which account drives most of the overspend?
# 3. Add a sentence-style title that states the main message instead of just describing the chart.
group = "department"

annual = var[var["budget_status"] != "unknown budget"].groupby(group)[["actual", "budget"]].sum()
annual["variance"] = annual["actual"] - annual["budget"]
annual["variance_pct"] = annual["variance"] / annual["budget"]
annual = annual.sort_values("variance")

fig, ax = plt.subplots(figsize=(9, 4.5))
colours = [UNFAVOURABLE if v > 0 else FAVOURABLE for v in annual["variance"]]
ax.barh(annual.index, annual["variance"], color=colours)
ax.axvline(0, color="black", linewidth=0.8)
for y, (v, p) in enumerate(zip(annual["variance"], annual["variance_pct"])):
    ax.text(v, y, f" {v:+,.0f} ({p:+.1%}) ", va="center", ha="left" if v > 0 else "right", fontsize=8)
ax.set_title(f"Annual expense variance by {group}, 2025 (comparable budget cells)")
ax.set_xlabel("Variance = actual − budget (currency units); right of zero = over budget")
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)"], fontsize=8, loc="lower right")
ax.margins(x=0.3)
plt.show()

unknown_actual = var.loc[var["budget_status"] == "unknown budget", "actual"].sum()
print(f"Not in the chart: {unknown_actual:,.2f} of spending with no budget (reported separately).")

Not in the chart: 33,378.17 of spending with no budget (reported separately).

# 1. Run the cell. How many lines are flagged? Which are flagged by amount, which by %?
# 2. Change AMOUNT_LIMIT to 10000 and PCT_LIMIT to 0.15. How does the list change? Who should choose these limits?
# 3. Change line_to_plot to one of the flagged lines. Is the variance spread across the year or concentrated in a few months?
AMOUNT_LIMIT = 20000
PCT_LIMIT = 0.15
MIN_AMOUNT_FOR_PCT = 1000
line_to_plot = ("Technology", "Software subscriptions")

view = var[var["budget_status"] != "unknown budget"].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)
big_amount = view["variance"].abs() >= AMOUNT_LIMIT
big_pct = (view["variance_pct"].abs() >= PCT_LIMIT) & (view["variance"].abs() >= MIN_AMOUNT_FOR_PCT)
view["flag"] = np.select([big_amount & big_pct, big_amount, big_pct], ["amount and %", "amount", "%"], default="")
flagged = view[view["flag"] != ""].sort_values("variance", key=abs, ascending=False)
display(flagged.assign(variance_pct=flagged["variance_pct"].map("{:+.1%}".format)))

one = var[(var["department"] == line_to_plot[0]) & (var["account"] == line_to_plot[1])].set_index("month")
ax = one[["actual", "budget"]].plot(marker="o", style=["-", "--"])
ax.set_title(f"{line_to_plot[0]} | {line_to_plot[1]}: actual and budget by month")
ax.set_xlabel("Month")
ax.set_ylabel("Currency units")
plt.show()

comparable = var[var["budget_status"] != "unknown budget"]
from_totals = (comparable["actual"].sum() - comparable["budget"].sum()) / comparable["budget"].sum()
mean_of_cells = comparable["variance_pct"].mean()
by_dept = comparable.groupby("department")[["actual", "budget"]].sum()
mean_of_depts = ((by_dept["actual"] - by_dept["budget"]) / by_dept["budget"]).mean()

print(f"From comparable totals (correct): {from_totals:+.2%}")
print(f"Mean of department %:             {mean_of_depts:+.2%}")
print(f"Mean of every cell's %:           {mean_of_cells:+.2%}")

From comparable totals (correct): +1.23%
Mean of department %:             +0.67%
Mean of every cell's %:           -0.21%

# Colleague's code (before)
colleague_1 = actuals.merge(budget, on=KEY, how="left")
print(f"Colleague's budget total: {colleague_1['budget'].sum():,.2f}")
print(f"Colleague's actual total: {colleague_1['amount'].sum():,.2f}")
print(f"Unique budget cells:      {budget['budget'].sum():,.2f}")

Colleague's budget total: 14,771,900.00
Colleague's actual total: 6,241,801.60
Unique budget cells:      6,133,000.00

# Your fix (after). Replace the line below with code that builds one row per
# (month, department, account) with columns "actual" and "budget", then reconcile it.
fixed_1 = colleague_1.rename(columns={"amount": "actual"})   # <- still the colleague's grain: replace this line
reconcile(fixed_1, "bug 1 after your fix")

Reconciliation: bug 1 after your fix
  rows 1378 vs keys 438
  actual 6,241,801.60 vs postings 6,241,801.60
  budget 14,771,900.00 vs budget cells 6,133,000.00
  FAIL  One row per (month, department, account)
  FAIL  Every key from either source is present
  PASS  Actual total = signed posting total
  FAIL  Budget total = total of unique budget cells

# Colleague's code (before)
colleague_2 = (actuals.groupby(KEY, as_index=False)["amount"].sum()
               .rename(columns={"amount": "actual"})
               .merge(budget, on=KEY))                      # default how="inner"
reconcile(colleague_2, "bug 2 before")
print("Accounts in postings but not in the colleague's table:",
      sorted(set(actuals["account"]) - set(colleague_2["account"])))

Reconciliation: bug 2 before
  rows 425 vs keys 438
  actual 6,208,423.43 vs postings 6,241,801.60
  budget 6,126,500.00 vs budget cells 6,133,000.00
  PASS  One row per (month, department, account)
  FAIL  Every key from either source is present
  FAIL  Actual total = signed posting total
  FAIL  Budget total = total of unique budget cells
Accounts in postings but not in the colleague's table: ['Cloud hosting']

# Your fix (after). Change the merge so no cell is lost, and label where each row came from.
# Remember: a missing actual can become 0, a missing budget must stay unknown.
fixed_2 = colleague_2.copy()                                # <- replace with your corrected merge
reconcile(fixed_2, "bug 2 after your fix")

Reconciliation: bug 2 after your fix
  rows 425 vs keys 438
  actual 6,208,423.43 vs postings 6,241,801.60
  budget 6,126,500.00 vs budget cells 6,133,000.00
  PASS  One row per (month, department, account)
  FAIL  Every key from either source is present
  FAIL  Actual total = signed posting total
  FAIL  Budget total = total of unique budget cells

# Colleague's code (before)
dept = var[var["budget_status"] != "unknown budget"].groupby("department")[["actual", "budget"]].sum()
dept["variance"] = dept["actual"] - dept["budget"]
dept = dept.sort_values("variance")

fig, ax = plt.subplots(figsize=(8, 4))
ax.barh(dept.index, dept["variance"],
        color=[FAVOURABLE if v > 0 else UNFAVOURABLE for v in dept["variance"]])
ax.axvline(0, color="black", linewidth=0.8)
ax.set_title("Variance by department")
ax.set_xlabel("Variance")
plt.show()

negatives = actuals[actuals["amount"] < 0].groupby(["department", "account"]).size()
dupe_lines = dupes.groupby(["department", "account"]).size()
lines = var.groupby(["department", "account"]).agg(
    actual=("actual", "sum"),
    known_budget=("budget", lambda s: s.sum(min_count=1)),     # stays blank when no month has a budget
    months_unknown_budget=("budget_status", lambda s: (s == "unknown budget").sum()),
    months_no_postings=("postings", lambda s: (s == 0).sum()),
    months_over_budget=("variance", lambda s: (s > 0).sum()),
)
lines["variance_known"] = var.groupby(["department", "account"])["variance"].sum(min_count=1)
lines["variance_pct"] = np.where(lines["known_budget"] > 0, lines["variance_known"] / lines["known_budget"], np.nan)
lines["credits_or_reversals"] = negatives.reindex(lines.index).fillna(0).astype(int)
lines["duplicate_postings"] = dupe_lines.reindex(lines.index).fillna(0).astype(int)

lines.sort_values("variance_known", key=abs, ascending=False, na_position="first").head(15)