Year-over-Year Variance Analysis
The purpose is not to list what changed. It is to reach the point where every material
movement has a named cause, and every material non-movement has been challenged.
That second half is what separates this from a spreadsheet subtraction. Most reviewers scan
for big changes and stop. The errors that survive review are usually the opposite pattern: a
figure that is identical to last year because nobody updated it — a depreciation amount, an
allocation percentage, a state apportionment factor, an accrual that was rolled forward
because it was easier than recomputing it. A zero variance is a finding until you have
confirmed it should be zero.
What counts as an explanation
An explanation is causal and quantified. Direction is not an explanation.
- ✗ "Revenue increased." — restates the variance
- ✗ "Higher volume." — no magnitude, no source, not testable
- ✗ "Timing." — the single most common non-explanation in practice; it hides errors
- ✓ "Revenue up $1.24M: $900K from the Riverside contract beginning March 2025, $340K from
the April price increase, offset by $(60)K from the Delta account lost in Q3." Ties to
$1.24M and can be tested against the customer detail.
If the components don't sum to the variance, the explanation is incomplete and the remainder
stays visible as unexplained. Do not let the last 15% of a variance disappear into "other."
Inputs
- Current year — return, trial balance, or financials
- Prior year, as filed — not the software's current state of the prior-year file, which
may have been amended, adjusted, or corrupted since. If the prior year was amended, use
the amended figures and say so.
- Optional context — budget, entity events during the year (acquisitions, dispositions,
new locations, contract wins and losses, headcount changes, method changes), and the
client's own explanation of the year. Ask for this. Without it you will generate questions
the client answers in five minutes, which is fine but slower than asking up front.
Confirm both years are on a comparable basis before comparing: same entity, same accounting
method, same period length, same chart of accounts. A short period, a method change, or an
account restructuring makes raw variances meaningless — normalize first and disclose the
normalization, or the entire schedule misleads.
Step 1 — Map the lines
Align current-year lines to prior-year lines. Three outcomes, and the last two matter most:
- Matched — same line both years. Compute the variance.
- New this year — a line with a current-year amount and no prior-year line. Every one
needs a cause. New lines are high-signal: a new revenue stream, a new state, a new
expense category, a new form. Also the classic tell for a misposting into a freshly
created account.
- Disappeared — a prior-year line with no current-year amount. Higher signal still.
Either the activity genuinely stopped (name the event) or something was dropped. Dropped
carryforwards, dropped state filings, and dropped schedules all appear here and nowhere
else on the return.
Where the chart of accounts was restructured, map old to new explicitly and show the mapping.
Do not silently absorb a restructuring into the variances.
Step 2 — Run the comparison
python3 scripts/yoy_compare.py \
--current cy.csv --prior py.csv \
--explanations explanations.csv \
--dollar-threshold 25000 --percent-threshold 10 \
--absolute-floor 5000 \
--client "Acme Holdings LLC" --cy-label "TY2025" --py-label "TY2024" \
--out "Acme Holdings - TY2025 vs TY2024 Variance.xlsx"
Input CSVs: line_id, statement, caption, amount, and optional form, line_ref,
group, activity_changed (yes/no/blank — see the unchanged test).
Materiality is dual, and both tests must be satisfied to be immaterial. A variance is
material if it exceeds the dollar threshold or the percent threshold, subject to an
absolute floor below which percent is ignored. This matters: a 400% swing on a $200 account
is noise, while a 3% move on an $8M revenue line is not. A single test alone produces either
an unusable list or a false sense of coverage. Set thresholds by engagement judgment and
record them — they are part of the workpaper.
Regardless of threshold, these are always flagged for explanation:
- Sign flips — income to loss, asset to liability, debit to credit balance. Direction
changes are qualitatively material at any magnitude.
- New and disappeared lines — per Step 1.
- Zero-variance lines where activity changed — the unchanged test. Mark
activity_changed = yes on any line whose underlying facts moved (revenue grew, assets
were bought or sold, headcount changed, the entity entered a new state). If the amount
didn't move and the activity did, the script flags it. Depreciation identical to last year
after a year of capital additions is not a coincidence.
- Round-number amounts on lines that should be computed — a depreciation figure of
exactly $45,000 or an accrual of exactly $100,000 suggests a plug, not a calculation.
- Lines equal to prior year to the penny on any account with ongoing activity.
Step 3 — Relationship tests
Individual line variances miss errors that only show up as broken relationships. Run these
and explain any that break:
- Revenue vs. cost of sales — gross margin percentage by year; a margin that moves several
points without a stated cause is either a classification error or a real business change
worth naming
- Revenue vs. payroll, and revenue vs. headcount — revenue up sharply with flat payroll means
one of them is wrong or something genuinely changed that should be documented
- Revenue vs. receivables, and receivables vs. days sales outstanding
- Purchases vs. payables, and inventory vs. cost of sales
- Fixed-asset additions vs. depreciation expense — the most reliable detector of a stale
depreciation schedule
- Debt balances vs. interest expense — implied rate by year; a sharp move means missing debt,
missing interest, or capitalized interest
- Officer compensation vs. distributions (S corps) — a shift from wages to distributions is
the most examined S corp pattern and should be documented deliberately, not discovered
- Book income vs. taxable income — the total book-to-tax difference by year, with each
component's movement explained
- Effective tax rate by year, reconciled — the top-down check that catches what the
bottom-up line review missed. If the ETR moved and no line variance explains it, keep
looking
Step 4 — Require explanations
The script writes unexplained.csv listing every flagged variance with no explanation. Fill
in cause, components (which must sum to the variance), evidence, and owner, then
re-run with --explanations. The script reports the explanation coverage rate — the
percentage of flagged dollars explained — and refuses to mark the analysis complete below
100% of flagged items. Partial coverage is reported honestly rather than smoothed over.
Where a variance is explained by a client-provided reason you have not corroborated, mark
evidence = client representation. That is a legitimate but weaker basis and the workpaper
should distinguish it from an explanation tied to a document. Reviewers need to know which
is which.
Step 5 — Deliver
Workbook tabs:
- Summary — thresholds used, lines compared, count and dollar value of material
variances, explanation coverage rate, top ten variances by absolute dollar, and the
always-flag exceptions. Read-in-30-seconds page.
- Variance Schedule — every line: caption, form/line reference, prior year, current
year, dollar variance, percent variance, materiality verdict, flag reason, cause,
components, evidence, owner. Sorted by absolute variance descending.
- Always-Flag Exceptions — sign flips, new lines, disappeared lines, unchanged-but-
activity-changed, and round-number suspects, each with its explanation status.
- Relationship Tests — the ratios above, both years, with the movement and its
explanation.
- Unexplained — what remains. An empty tab is the goal; a missing tab is not acceptable.
Then, in chat: the three or four variances that actually matter, in plain language, with
their causes — followed by what remains unexplained and who owns it. Lead with the
unexplained items if there are any. A partner reading this wants to know what to ask the
client, not to re-read the schedule.
When variances suggest something worse
Some patterns are worth escalating rather than explaining:
- Revenue up with cash flow flat or down
- Margin improving while the business reports no operational change
- Expenses that disappear in a year when the related activity continued
- Round-dollar variances that exactly offset each other across two accounts
- A prior-year figure that does not agree to the prior-year return as filed — this means
someone changed a closed year, and it needs to be reconciled before any variance is
meaningful
- Repeated year-over-year "timing" explanations for the same line, which is the signature of
a systemic error rolling forward rather than a genuine timing difference
Raise these as observations with the facts you have. Do not characterize intent.
Security posture
Fully local. No client financial or tax data leaves the machine. Inputs read-only.
Dependencies
pip install openpyxl
Built by Adopt AI — free to use and modify.
Appendix — bundled files
If you installed the .skill package these files are already in place and you can ignore this appendix. If you copied the skill as text, create the files below at the paths shown, alongside your SKILL.md. The skill will not run without them.
scripts/yoy_compare.py
#!/usr/bin/env python3
"""
Year-over-year variance analysis with dual materiality and an explanation gate.
Flags a variance as requiring explanation when it breaches the dollar OR the
percent threshold (percent ignored below an absolute floor), plus five
always-flag conditions that are qualitatively material at any magnitude:
sign flip · new line · disappeared line ·
zero variance where the underlying activity changed · round-number plug
Reports explanation coverage and will not call the analysis complete until
every flagged item has a cause whose components sum to the variance.
Usage:
python3 yoy_compare.py --current cy.csv --prior py.csv \
--dollar-threshold 25000 --percent-threshold 10 --absolute-floor 5000 \
--client "Acme Holdings LLC" --cy-label TY2025 --py-label TY2024 \
--out "Acme - TY2025 vs TY2024 Variance.xlsx"
Input CSVs (current and prior, same shape):
line_id stable key used to match years required
caption human-readable line name required
amount signed required
statement e.g. Income Statement / Balance Sheet / Return optional
form e.g. "1065 pg1" optional
line_ref e.g. "Ln 1c" optional
group subtotal grouping optional
activity_changed yes|no|blank (current-year file only) optional
--explanations CSV:
line_id, cause, components, evidence, owner, notes
components : semicolon-separated signed amounts that must sum to the
variance, e.g. "900000; 340000; -60000"
evidence : document reference, or the literal "client representation"
"""
from __future__ import annotations
import argparse
import csv
import re
import sys
from decimal import Decimal, InvalidOperation
from pathlib import Path
try:
from openpyxl import Workbook
from openpyxl.styles import Alignment, Font, PatternFill
from openpyxl.utils import get_column_letter
except ImportError:
sys.exit("openpyxl is required. pip install openpyxl")
ZERO = Decimal("0.00")
MONEY = '#,##0.00;[Red](#,##0.00)'
PCT = '0.0"%"'
HDR_FILL = PatternFill("solid", fgColor="1F3864")
HDR_FONT = Font(bold=True, color="FFFFFF")
OK_FONT = Font(bold=True, color="006100")
BAD_FONT = Font(bold=True, color="9C0006")
BAD_FILL = PatternFill("solid", fgColor="FFC7CE")
WARN_FILL = PatternFill("solid", fgColor="FFEB9C")
INFO_FILL = PatternFill("solid", fgColor="DDEBF7")
# Amounts that are suspiciously round on a line that should be computed.
# Roundness has to scale with magnitude or the test is pure noise: $5,360,000 being
# a multiple of 10,000 means nothing, but $100,000 being a multiple of 100,000 does.
# So the modulus must be a meaningful fraction (>=5%) of the amount itself.
ROUND_MODULI = (Decimal("1000000"), Decimal("500000"), Decimal("100000"),
Decimal("50000"), Decimal("25000"), Decimal("10000"),
Decimal("5000"), Decimal("1000"))
ROUND_MIN = Decimal("1000")
ROUND_SIGNIFICANCE = Decimal("20") # modulus >= |amount| / 20
def dec(raw) -> Decimal | None:
if raw is None:
return None
s = str(raw).strip()
if s in ("", "-", "--", "n/a", "N/A", "None"):
return None
neg = s.startswith("(") and s.endswith(")")
s = s.strip("()").replace(",", "").replace("$", "").replace("%", "").strip()
if s.endswith("-"):
neg, s = True, s[:-1]
try:
v = Decimal(s)
except InvalidOperation:
raise ValueError(f"Unparseable amount: {raw!r}")
return -v if neg else v
def is_round(v: Decimal) -> str:
a = abs(v)
if a < ROUND_MIN:
return ""
need = a / ROUND_SIGNIFICANCE
for m in ROUND_MODULI:
if m >= need and a >= m and a % m == ZERO:
return f"exact multiple of {m:,.0f}"
return ""
def load(path: Path, label: str) -> dict[str, dict]:
with path.open(newline="", encoding="utf-8-sig") as fh:
rows = list(csv.DictReader(fh))
if not rows:
sys.exit(f"No rows in {path}")
out: dict[str, dict] = {}
for i, r in enumerate(rows, start=1):
lid = " ".join(str(r.get("line_id", "") or "").split())
cap = " ".join(str(r.get("caption", "") or "").split())
if not lid:
lid = cap
if not lid:
raise ValueError(f"{label} row {i}: needs a line_id or a caption.")
amt = dec(r.get("amount"))
if amt is None:
raise ValueError(f"{label} row {i} ({lid}): amount is required.")
if lid in out:
raise ValueError(
f"{label}: duplicate line_id {lid!r}. Line IDs must be unique - "
f"aggregate the duplicates or give them distinct IDs."
)
act = str(r.get("activity_changed", "") or "").strip().lower()
out[lid] = {
"line_id": lid, "caption": cap or lid, "amount": amt,
"statement": " ".join(str(r.get("statement", "") or "").split()),
"form": " ".join(str(r.get("form", "") or "").split()),
"line_ref": " ".join(str(r.get("line_ref", "") or "").split()),
"group": " ".join(str(r.get("group", "") or "").split()),
"activity_changed": act in ("yes", "y", "true", "1"),
}
return out
def load_explanations(path: Path | None) -> dict[str, dict]:
if not path or not path.exists():
return {}
out = {}
with path.open(newline="", encoding="utf-8-sig") as fh:
for r in csv.DictReader(fh):
lid = " ".join(str(r.get("line_id", "") or "").split())
if not lid:
continue
comps = []
raw = str(r.get("components", "") or "")
for part in re.split(r"[;|]", raw):
part = part.strip()
if part:
v = dec(part)
if v is not None:
comps.append(v)
out[lid] = {
"cause": " ".join(str(r.get("cause", "") or "").split()),
"components_raw": raw.strip(),
"components": comps,
"evidence": " ".join(str(r.get("evidence", "") or "").split()),
"owner": " ".join(str(r.get("owner", "") or "").split()),
"notes": " ".join(str(r.get("notes", "") or "").split()),
}
return out
def compare(cy: dict, py: dict, dollar: Decimal, pct: Decimal,
floor: Decimal) -> list[dict]:
rows = []
for lid in sorted(set(cy) | set(py)):
c, p = cy.get(lid), py.get(lid)
cy_amt = c["amount"] if c else None
py_amt = p["amount"] if p else None
src = c or p
var = (cy_amt or ZERO) - (py_amt or ZERO)
pct_var = None
if py_amt not in (None, ZERO):
pct_var = (var / abs(py_amt)) * Decimal(100)
flags: list[str] = []
if c and not p:
flags.append("NEW line this year")
if p and not c:
flags.append("DISAPPEARED - present last year, absent this year")
if cy_amt is not None and py_amt is not None:
if (cy_amt > ZERO and py_amt < ZERO) or (cy_amt < ZERO and py_amt > ZERO):
flags.append("SIGN FLIP - direction changed")
if var == ZERO and c and c["activity_changed"]:
flags.append("UNCHANGED but activity changed - stale rolled-forward figure?")
if var == ZERO and cy_amt != ZERO and c and not c["activity_changed"]:
flags.append("identical to prior year to the penny - confirm still correct")
if cy_amt is not None:
rnd = is_round(cy_amt)
if rnd:
flags.append(f"round number ({rnd}) - computed or plugged?")
# dual materiality
mat_d = abs(var) >= dollar
mat_p = (pct_var is not None
and abs(pct_var) >= pct
and max(abs(cy_amt or ZERO), abs(py_amt or ZERO)) >= floor)
material = mat_d or mat_p
why = []
if mat_d:
why.append(f"|variance| >= {dollar:,.0f}")
if mat_p:
why.append(f"|%| >= {pct}% above {floor:,.0f} floor")
needs = material or bool(flags)
rows.append({
"line_id": lid, "caption": src["caption"],
"statement": src["statement"], "group": src["group"],
"form": src["form"], "line_ref": src["line_ref"],
"py": py_amt, "cy": cy_amt, "variance": var, "pct": pct_var,
"material": material, "materiality_basis": "; ".join(why),
"flags": flags, "needs_explanation": needs,
})
rows.sort(key=lambda r: abs(r["variance"]), reverse=True)
return rows
def attach(rows: list[dict], expl: dict) -> tuple[list[dict], list[str]]:
problems: list[str] = []
for r in rows:
e = expl.get(r["line_id"])
r["cause"] = e["cause"] if e else ""
r["components_raw"] = e["components_raw"] if e else ""
r["evidence"] = e["evidence"] if e else ""
r["owner"] = e["owner"] if e else ""
r["notes"] = e["notes"] if e else ""
r["explained"] = bool(e and e["cause"])
r["components_tie"] = ""
if not r["explained"]:
continue
if e["components"]:
total = sum(e["components"], ZERO)
diff = total - r["variance"]
if diff == ZERO:
r["components_tie"] = "ties"
else:
r["components_tie"] = f"OFF BY {diff:,.2f}"
problems.append(
f"{r['line_id']} ({r['caption']}): components sum to {total:,.2f} "
f"but the variance is {r['variance']:,.2f} - {diff:,.2f} of the "
f"movement is unexplained. Do not absorb the remainder into 'other'."
)
elif r["material"]:
r["components_tie"] = "no components given"
problems.append(
f"{r['line_id']} ({r['caption']}): material variance "
f"{r['variance']:,.2f} has a cause but no quantified components. An "
f"explanation that cannot be tested is a restatement of the variance."
)
if not r["evidence"]:
problems.append(
f"{r['line_id']} ({r['caption']}): explained with no evidence "
f"reference. Cite a document, or state 'client representation' so the "
f"weaker basis is visible to a reviewer."
)
for lid in sorted(set(expl) - {r["line_id"] for r in rows}):
problems.append(
f"Explanation supplied for line_id {lid!r}, which appears in neither year. "
f"Likely a typo."
)
return rows, problems
# ------------------------------------------------------------------ workbook
def hdr(ws, n, row=1):
for c in range(1, n + 1):
cell = ws.cell(row=row, column=c)
cell.fill, cell.font = HDR_FILL, HDR_FONT
cell.alignment = Alignment(vertical="center", wrap_text=True)
ws.row_dimensions[row].height = 30
def widths(ws, w):
for c, v in w.items():
ws.column_dimensions[get_column_letter(c)].width = v
SCHED_COLS = [
("caption", "Caption", 42), ("statement", "Statement", 18),
("form", "Form", 12), ("line_ref", "Line", 10),
("py", "Prior year", 16), ("cy", "Current year", 16),
("variance", "Variance $", 16), ("pct", "Variance %", 12),
("material", "Material", 10), ("flagstr", "Flag reason", 46),
("cause", "Cause (causal + quantified)", 56),
("components_raw", "Components", 30),
("components_tie", "Tie", 14),
("evidence", "Evidence", 26), ("owner", "Owner", 14),
("line_id", "Line ID", 16),
]
def sheet_schedule(wb, rows, title="Variance Schedule", subset=None):
ws = wb.create_sheet(title)
ws.append([lbl for _, lbl, _ in SCHED_COLS])
hdr(ws, len(SCHED_COLS))
data = subset if subset is not None else rows
for r in data:
r["flagstr"] = "; ".join(r["flags"])
out = []
for key, _, _ in SCHED_COLS:
v = r.get(key)
if key in ("py", "cy", "variance"):
out.append(float(v) if v is not None else None)
elif key == "pct":
out.append(float(v) if v is not None else None)
elif key == "material":
out.append("YES" if v else "")
else:
out.append(v or None)
ws.append(out)
idx = {k: i for i, (k, _, _) in enumerate(SCHED_COLS)}
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for k in ("py", "cy", "variance"):
row[idx[k]].number_format = MONEY
row[idx["pct"]].number_format = PCT
if row[idx["material"]].value == "YES":
row[idx["material"]].font = BAD_FONT
if row[idx["flagstr"]].value:
row[idx["flagstr"]].fill = WARN_FILL
tie = row[idx["components_tie"]].value
if tie and tie != "ties":
row[idx["components_tie"]].fill = BAD_FILL
row[idx["components_tie"]].font = BAD_FONT
elif tie == "ties":
row[idx["components_tie"]].font = OK_FONT
if (row[idx["material"]].value == "YES" or row[idx["flagstr"]].value) \
and not row[idx["cause"]].value:
row[idx["cause"]].fill = BAD_FILL
row[idx["cause"]].value = "*** UNEXPLAINED ***"
if str(row[idx["evidence"]].value or "").lower() == "client representation":
row[idx["evidence"]].fill = INFO_FILL
if ws.max_row == 1:
ws.cell(row=2, column=1, value="None.")
ws.freeze_panes = "A2"
ws.auto_filter.ref = f"A1:{get_column_letter(len(SCHED_COLS))}{max(ws.max_row, 2)}"
widths(ws, {i: w for i, (_, _, w) in enumerate(SCHED_COLS, start=1)})
def sheet_summary(wb, rows, meta, stats, problems):
ws = wb.active
ws.title = "Summary"
widths(ws, {1: 4, 2: 50, 3: 20, 4: 20, 5: 62})
r = 1
def line(txt, *, bold=False, size=11, fill=None, col=2):
nonlocal r
c = ws.cell(row=r, column=col, value=txt)
c.font = Font(bold=bold, size=size)
c.alignment = Alignment(wrap_text=True, vertical="top")
if fill:
c.fill = fill
r += 1
line("YEAR-OVER-YEAR VARIANCE ANALYSIS", bold=True, size=14)
r += 1
for k, v in meta.items():
ws.cell(row=r, column=2, value=k).font = Font(bold=True)
ws.cell(row=r, column=3, value=v)
r += 1
r += 1
complete = stats["needs"] > 0 and stats["explained"] == stats["needs"] and not problems
if stats["needs"] == 0:
line("No variance breached materiality and no always-flag condition arose.",
bold=True, fill=INFO_FILL)
elif complete:
line("ANALYSIS COMPLETE - every flagged variance has a quantified, "
"evidenced cause.", bold=True, fill=None)
ws.cell(row=r - 1, column=2).font = OK_FONT
else:
line(f"ANALYSIS INCOMPLETE - {stats['needs'] - stats['explained']} flagged "
f"item(s) unexplained"
+ (f", {len(problems)} explanation problem(s)" if problems else "")
+ ". See Unexplained tab.", bold=True, fill=BAD_FILL)
r += 1
line("COVERAGE", bold=True, size=12)
for label, val in [
("Lines compared", stats["total"]),
("Requiring explanation", stats["needs"]),
("Explained", stats["explained"]),
("Explanation rate (count)", f"{stats['rate_count']:.0f}%"),
("Flagged variance $ (absolute)", None),
("Explained variance $ (absolute)", None),
("Explanation rate (dollars)", f"{stats['rate_dollars']:.0f}%"),
]:
ws.cell(row=r, column=2, value=" " + label)
if label == "Flagged variance $ (absolute)":
c = ws.cell(row=r, column=3, value=float(stats["flagged_dollars"]))
c.number_format = MONEY
elif label == "Explained variance $ (absolute)":
c = ws.cell(row=r, column=3, value=float(stats["explained_dollars"]))
c.number_format = MONEY
else:
ws.cell(row=r, column=3, value=val)
r += 1
r += 1
top = [x for x in rows if x["variance"] != ZERO][:10]
if top:
line("TOP 10 VARIANCES BY ABSOLUTE DOLLAR", bold=True, size=12)
for h, col in zip(["Caption", "Prior", "Current", "Variance", "Cause"],
(2, 3, 4, 5, 6)):
hc = ws.cell(row=r, column=col, value=h)
hc.fill, hc.font = HDR_FILL, HDR_FONT
r += 1
for x in top:
ws.cell(row=r, column=2, value=x["caption"])
for col, key in ((3, "py"), (4, "cy"), (5, "variance")):
v = x[key]
c = ws.cell(row=r, column=col, value=float(v) if v is not None else None)
c.number_format = MONEY
cc = ws.cell(row=r, column=6, value=x["cause"] or "*** UNEXPLAINED ***")
if not x["cause"]:
cc.fill, cc.font = BAD_FILL, BAD_FONT
r += 1
r += 1
line("RELATIONSHIP TESTS TO PERFORM (see Relationship Tests tab)", bold=True, size=12)
for t in [
"Gross margin % by year",
"Revenue vs. payroll and headcount",
"Revenue vs. receivables and DSO",
"Fixed-asset additions vs. depreciation expense (stale-schedule detector)",
"Debt balances vs. interest expense (implied rate)",
"Officer compensation vs. distributions (S corps)",
"Book income vs. taxable income, by component",
"Effective tax rate, reconciled (top-down check)",
]:
line(" - " + t)
r += 1
line("An explanation is causal and quantified. 'Timing' and 'higher volume' are "
"not explanations - they restate the variance.", bold=True)
def sheet_flags(wb, rows):
subset = [r for r in rows if r["flags"]]
sheet_schedule(wb, rows, "Always-Flag Exceptions", subset)
def sheet_unexplained(wb, rows, problems):
subset = [r for r in rows if r["needs_explanation"] and not r["explained"]]
sheet_schedule(wb, rows, "Unexplained", subset)
ws = wb["Unexplained"]
if problems:
r = ws.max_row + 3
c = ws.cell(row=r, column=1, value="EXPLANATION PROBLEMS")
c.font = Font(bold=True, size=12)
for p in problems:
r += 1
cell = ws.cell(row=r, column=1, value=p)
cell.font = BAD_FONT
cell.alignment = Alignment(wrap_text=True, vertical="top")
def sheet_relationships(wb, cy_label, py_label):
ws = wb.create_sheet("Relationship Tests")
heads = ["Test", py_label, cy_label, "Movement", "Explanation", "Evidence"]
ws.append(heads)
hdr(ws, len(heads))
for t in [
"Gross margin %",
"Payroll as % of revenue",
"Revenue per employee",
"DSO (days sales outstanding)",
"Inventory turns",
"Fixed-asset additions",
"Depreciation expense",
"Depreciation as % of gross fixed assets",
"Debt balance (period end)",
"Interest expense",
"Implied interest rate %",
"Officer compensation",
"Distributions to owners",
"Book income",
"Taxable income",
"Total book-to-tax difference",
"Effective tax rate %",
]:
ws.append([t, None, None, None, None, None])
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for i in (1, 2, 3):
row[i].number_format = MONEY
ws.freeze_panes = "B2"
widths(ws, {1: 42, 2: 18, 3: 18, 4: 16, 5: 62, 6: 26})
# ------------------------------------------------------------------ main
def main() -> int:
ap = argparse.ArgumentParser(description=__doc__)
ap.add_argument("--current", required=True)
ap.add_argument("--prior", required=True)
ap.add_argument("--explanations")
ap.add_argument("--dollar-threshold", default="25000")
ap.add_argument("--percent-threshold", default="10")
ap.add_argument("--absolute-floor", default="5000",
help="below this magnitude, percent variance is ignored as noise")
ap.add_argument("--client", default="")
ap.add_argument("--cy-label", default="Current year")
ap.add_argument("--py-label", default="Prior year")
ap.add_argument("--basis-note", default="",
help="normalization applied, e.g. 'PY restated for amended return'")
ap.add_argument("--out", required=True)
ap.add_argument("--unexplained-out", default="unexplained.csv")
args = ap.parse_args()
dollar = dec(args.dollar_threshold) or ZERO
pct = dec(args.percent_threshold) or ZERO
floor = dec(args.absolute_floor) or ZERO
cy = load(Path(args.current), "current")
py = load(Path(args.prior), "prior")
rows = compare(cy, py, dollar, pct, floor)
expl = load_explanations(Path(args.explanations) if args.explanations else None)
rows, problems = attach(rows, expl)
needs = [r for r in rows if r["needs_explanation"]]
explained = [r for r in needs if r["explained"]]
flagged_dollars = sum((abs(r["variance"]) for r in needs), ZERO)
explained_dollars = sum((abs(r["variance"]) for r in explained), ZERO)
stats = {
"total": len(rows), "needs": len(needs), "explained": len(explained),
"rate_count": (len(explained) / len(needs) * 100) if needs else 100.0,
"flagged_dollars": flagged_dollars,
"explained_dollars": explained_dollars,
"rate_dollars": (float(explained_dollars / flagged_dollars) * 100)
if flagged_dollars else 100.0,
}
# worklist for the preparer
unexp = [r for r in needs if not r["explained"]]
if unexp:
with Path(args.unexplained_out).open("w", newline="", encoding="utf-8") as fh:
w = csv.writer(fh)
w.writerow(["line_id", "caption", "prior", "current", "variance", "pct",
"flag_reason", "cause", "components", "evidence", "owner", "notes"])
for r in unexp:
w.writerow([
r["line_id"], r["caption"],
f"{r['py']:.2f}" if r["py"] is not None else "",
f"{r['cy']:.2f}" if r["cy"] is not None else "",
f"{r['variance']:.2f}",
f"{r['pct']:.1f}" if r["pct"] is not None else "",
"; ".join(r["flags"]) or r["materiality_basis"],
"", "", "", "", "",
])
meta = {
"Client": args.client or "(not stated)",
"Current year": args.cy_label,
"Prior year": args.py_label,
"Dollar threshold": float(dollar),
"Percent threshold": f"{pct}%",
"Absolute floor (percent test ignored below)": float(floor),
"Comparability / normalization": args.basis_note or "(none stated - confirm same "
"entity, method, period length, "
"and chart of accounts)",
}
wb = Workbook()
sheet_summary(wb, rows, meta, stats, problems)
sheet_schedule(wb, rows)
sheet_flags(wb, rows)
sheet_relationships(wb, args.cy_label, args.py_label)
sheet_unexplained(wb, rows, problems)
wb.active = 0
complete = (stats["needs"] == 0) or (stats["explained"] == stats["needs"] and not problems)
out = Path(args.out)
if not complete:
out = out.with_name(out.stem + " [INCOMPLETE]" + out.suffix)
out.parent.mkdir(parents=True, exist_ok=True)
wb.save(str(out))
# ---- console
print("=" * 72)
print(f"YEAR-OVER-YEAR VARIANCE: {args.py_label} -> {args.cy_label}")
print("=" * 72)
print(f"Materiality: |var| >= {dollar:,.0f} OR |%| >= {pct}% "
f"(percent ignored below {floor:,.0f})")
print(f"Lines compared : {stats['total']}")
print(f"Requiring explanation : {stats['needs']}")
print(f"Explained : {stats['explained']} "
f"({stats['rate_count']:.0f}% of items, {stats['rate_dollars']:.0f}% of dollars)")
flags = [r for r in rows if r["flags"]]
if flags:
print(f"\nAlways-flag exceptions ({len(flags)}):")
for r in flags[:20]:
print(f" ! {r['caption'][:44]:<44} {r['variance']:>14,.2f} "
f"{'; '.join(r['flags'])[:60]}")
if len(flags) > 20:
print(f" ... and {len(flags) - 20} more")
if unexp:
print(f"\nUNEXPLAINED ({len(unexp)}):")
for r in unexp[:20]:
print(f" ? {r['caption'][:44]:<44} {r['variance']:>14,.2f}")
if len(unexp) > 20:
print(f" ... and {len(unexp) - 20} more")
print(f"\nWorklist written to {args.unexplained_out}. Fill in cause, "
f"components, evidence, owner and re-run with --explanations.")
if problems:
print(f"\nEXPLANATION PROBLEMS ({len(problems)}):")
for p in problems[:20]:
print(f" ! {p}")
print(f"\nStatus : {'COMPLETE' if complete else 'INCOMPLETE'}")
print(f"Workbook: {out}")
return 0 if complete else 1
if __name__ == "__main__":
sys.exit(main())
