Cash Flow Statement Tie-Out
The statement of cash flows is the only primary statement that must reconcile to another one by
construction. Net income plus adjustments plus working capital movements, across all three
sections, has to equal the change in cash on the balance sheet. There is no judgment in that
requirement — it either foots or it does not.
Which is exactly why it is so often wrong. It is usually prepared last, under time pressure, by
plugging whichever line nobody will question. The plug hides inside "changes in other assets and
liabilities" or "other operating activities," and it survives review because reviewers check that
the bottom line agrees to cash rather than checking that each line agrees to the balance sheet.
So this skill derives each working capital movement from the balance sheet itself and compares
it to what the statement claims, rather than accepting the statement's own numbers.
The gate
No clean statement unless:
- Net change in cash equals the balance sheet movement. Operating + investing + financing
(+ FX effect) = closing cash − opening cash, per the balance sheet, to the cent.
- Every working capital line agrees to the balance sheet movement for that account, or carries
a named non-cash adjustment explaining the difference.
- The balance sheet balances in both periods. Assets = liabilities + equity. A cash flow
built on an unbalanced balance sheet is meaningless, so this is checked first.
- There is no unexplained residual. If a difference exists it appears as an explicit
"UNEXPLAINED — DO NOT PLUG" line rather than being absorbed into an "other" caption.
Standing rule on the plug
Every difference must be attributed to a named cause. The legitimate reasons a working capital
movement differs from the raw balance sheet change are finite and knowable:
- Non-cash additions and disposals — assets acquired under a lease, an asset received in a
non-monetary exchange, capitalised interest
- Acquisitions and disposals of businesses, which move balance sheet accounts without an
operating cash flow
- Foreign currency translation on balance sheet accounts of foreign operations, which belongs
in the FX effect line and not in working capital
- Reclassifications between captions, including current/non-current reclassification
- Non-cash write-offs — bad debt written off against the allowance, inventory written down
- Accrued but unpaid amounts moving between accrual accounts
- Stock compensation, deferred tax, and other non-cash charges in net income
If a difference cannot be placed in one of those buckets, it is not explained. Report it. The
script will not let it disappear into a caption.
Inputs
- Balance sheet, both periods — account level or at least caption level, with the cash and
cash-equivalent accounts identified.
- Income statement for the period.
- Known non-cash items — depreciation, amortisation, stock compensation, deferred tax,
gains and losses on disposal, impairment, bad debt expense.
- Investing and financing detail — capital expenditure, proceeds from disposals, debt drawn
and repaid, equity issued, dividends and distributions paid.
- The client's own cash flow statement, if one exists and you are verifying rather than
building. The comparison against your derived figures is the point of the exercise.
Step 1 — Prove the balance sheet, then derive
python3 scripts/cash_flow.py \
--balance-sheet bs.csv \
--income-statement is.csv \
--noncash noncash.csv \
--investing-financing invfin.csv \
--adjustments adjustments.csv \
--client "Latham Industrial Group" --period "FY2025" \
--out "Latham - FY2025 Cash Flow Tie-Out.xlsx"
Six tests:
- Test 1 — Balance sheet balances in both periods. Runs before anything else. An unbalanced
balance sheet makes every downstream figure meaningless.
- Test 2 — Cash movement identified. Opening and closing cash per the accounts flagged as
cash and equivalents, and the resulting change.
- Test 3 — Working capital derived from the balance sheet. Each operating asset and liability
account's movement computed and signed correctly. This is where the derivation replaces trust.
- Test 4 — The statement foots. Operating + investing + financing + FX = change in cash.
- Test 5 — Every adjustment named. Each difference between a derived movement and the amount
presented must be attributed to a cause in the list above.
- Test 6 — Comparison to the client's statement, where supplied, line by line.
Step 2 — Get the signs right
Sign errors are the second most common failure after plugging, and they are easy to get wrong
because the logic inverts between assets and liabilities:
| Account moves | Cash effect |
|---|
| Operating asset increases (AR, inventory, prepaid) | Outflow — cash was consumed |
| Operating asset decreases | Inflow |
| Operating liability increases (AP, accruals, deferred revenue) | Inflow — payment deferred |
| Operating liability decreases | Outflow |
The script applies this from each account's declared type, so a misclassified account produces a
sign error you can trace rather than a mystery. Check the account types before reading the
result — an account typed as a liability when it is a contra-asset will flip its contribution
and the statement will still foot, because the error is inside the section.
A doubled sign error is the nastiest case: two accounts wrong in opposite directions leave the
statement footing perfectly while both sections are misstated. Test 3's line-by-line derivation is
what surfaces it.
Step 3 — Read the classification questions
Section classification is judgment in a handful of recurring places, and the script flags them
rather than deciding:
- Interest paid — operating under most frameworks, but presentation varies and disclosure may
be required
- Dividends received — operating or investing depending on framework and policy
- Capitalised interest — investing, though the expense ran through operating
- Overdrafts — financing, or part of cash and equivalents, depending on the arrangement
- Bank debt with a revolving facility — gross versus net presentation of draws and repayments
- Restricted cash — whether it belongs in cash and equivalents at all
- Book overdrafts versus bank overdrafts — these are different and are treated differently
These are flagged as judgment items with the amounts involved, for the person signing.
Step 4 — Deliver
Workbook tabs:
- Cash Flow Statement — the derived statement in proper form, with the foot to the change in
cash shown, and the unexplained line reading
0.00. The signable page.
- Proof — the six tests with amounts and differences.
- Working Capital Derivation — every operating account: opening balance, closing balance,
raw movement, named adjustments, and the cash flow effect with its sign. The tab that catches
the plug.
- Balance Sheet Movement — every account, both periods, movement, and the section it feeds.
Nothing is allowed to feed no section.
- Comparison — where the client's statement was supplied, line by line against the derived
figures, with differences.
- Judgment Items — the classification questions above, with amounts, deliberately not
concluded.
- Monthly Analysis — where monthly balance sheets are supplied, the cash flow by month with
unusual movements flagged.
Then, in chat: whether it foots, the change in cash, the three sections, and any line where
the derived figure disagrees with what was presented. Lead with the disagreement — that is the
finding, not the total.
What to escalate
- A statement that foots only because of an "other" line. Report the size of that line
relative to the sections. A material unexplained "other" is a plug regardless of its label.
- Operating cash flow persistently below net income while revenue grows — the classic
earnings-quality signal. Report the relationship across periods; do not characterise it.
- Capital expenditure with no corresponding fixed asset movement, or vice versa.
- Debt movements that do not agree to the debt schedule or to interest expense at any
plausible rate.
- An account that feeds no section. Every balance sheet movement goes somewhere. An account
excluded from all three sections is the definition of a hidden plug, and the script refuses to
let it be silent.
- Restricted cash included in cash and equivalents without disclosure.
- A large FX effect in an entity with no foreign operations.
Security posture
Fully local: standard library plus openpyxl. No network calls, no uploads, no telemetry.
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/cash_flow.py
#!/usr/bin/env python3
"""
Indirect-method cash flow statement: derive it from the balance sheet and prove it.
Working capital movements are DERIVED from the balance sheet rather than taken from
the statement, because the usual failure is a plug hidden inside an "other" caption
that survives review when only the bottom line is checked.
Gates:
* the balance sheet must balance in both periods, checked before anything else
* operating + investing + financing + FX must equal the balance sheet cash movement
* every difference between a derived movement and the amount presented must be
attributed to a named cause
* every balance sheet account must feed a section - an account feeding nothing is
the definition of a hidden plug
Usage:
python3 cash_flow.py --balance-sheet bs.csv --income-statement is.csv \
--noncash noncash.csv --investing-financing invfin.csv \
--adjustments adjustments.csv \
--client "Latham Industrial Group" --period FY2025 \
--out "Latham - FY2025 Cash Flow Tie-Out.xlsx"
--balance-sheet CSV:
account, caption, type, opening, closing
type: cash | operating_asset | operating_liability | investing_asset |
debt | equity | noncash_contra
Anything else is rejected - an untyped account cannot be placed in a section.
--income-statement CSV: caption, amount (net_income row required)
--noncash CSV: caption, amount (depreciation, stock comp, etc.)
--investing-financing CSV: caption, section, amount section: investing | financing
--adjustments CSV: account, cause, amount, evidence
cause must be one of the recognised non-cash / reclass reasons.
"""
from __future__ import annotations
import argparse
import csv
import sys
from collections import defaultdict
from datetime import datetime
from decimal import Decimal, InvalidOperation
from pathlib import Path
try:
from openpyxl import Workbook
from openpyxl.styles import Alignment, Border, Font, PatternFill, Side
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)'
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")
SUB_FILL = PatternFill("solid", fgColor="D9E2F3")
TOP = Border(top=Side(style="thin"))
DBL = Border(top=Side(style="thin"), bottom=Side(style="double"))
TYPES = {"cash", "operating_asset", "operating_liability", "investing_asset",
"debt", "equity", "noncash_contra"}
# Sign applied to the balance sheet movement to get the cash effect.
# asset up -> cash down
# liability up -> cash up
OP_SIGN = {"operating_asset": Decimal(-1), "operating_liability": Decimal(1)}
# Contra-asset accounts (accumulated depreciation, the AR allowance) are deliberately
# NOT derived into working capital. Their movement IS the non-cash charge that is added
# back separately, so deriving them too would double-count it. Instead their movement is
# cross-checked against the non-cash adjustments supplied - see reconcile_sections().
CAUSES = {
"noncash_addition": "Non-cash addition (lease, non-monetary exchange, capitalised interest)",
"acquisition": "Acquisition or disposal of a business",
"fx_translation": "Foreign currency translation of a foreign operation",
"reclassification": "Reclassification between captions or current/non-current",
"noncash_writeoff": "Non-cash write-off (bad debt against allowance, inventory writedown)",
"accrual_transfer": "Accrued but unpaid amount moving between accrual accounts",
"noncash_charge": "Non-cash charge already in net income (stock comp, deferred tax)",
}
JUDGMENT = [
("Interest paid", "operating under most frameworks, but presentation varies and "
"disclosure may be required"),
("Dividends received", "operating or investing depending on framework and policy"),
("Capitalised interest", "investing, though the expense ran through operating"),
("Overdrafts", "financing, or part of cash and equivalents, depending on the "
"arrangement"),
("Revolving facility draws and repayments", "gross versus net presentation"),
("Restricted cash", "whether it belongs in cash and equivalents at all"),
("Book versus bank overdrafts", "these are different and are treated differently"),
]
def dec(raw):
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("$", "").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 d0(raw):
v = dec(raw)
return v if v is not None else ZERO
def clean(s) -> str:
return " ".join(str(s or "").split())
def rows_of(path: Path, label: str):
with path.open(newline="", encoding="utf-8-sig") as fh:
rows = list(csv.DictReader(fh))
if not rows:
sys.exit(f"No rows in {label} ({path})")
return rows
def load_bs(path: Path) -> list[dict]:
out = []
for i, r in enumerate(rows_of(path, "balance sheet"), start=1):
t = clean(r.get("type")).lower()
if t not in TYPES:
sys.exit(f"Balance sheet row {i} ({clean(r.get('account'))}): type "
f"'{t}' is not recognised. Every account must be typed as one of "
f"{sorted(TYPES)} - an untyped account cannot be placed in a "
f"section, and an account that feeds no section is a hidden plug.")
out.append({
"row": i,
"account": clean(r.get("account")) or f"ACC{i:04d}",
"caption": clean(r.get("caption")) or clean(r.get("account")),
"type": t,
"opening": d0(r.get("opening")),
"closing": d0(r.get("closing")),
})
return out
def load_pairs(path: Path | None, label: str, extra=()) -> list[dict]:
if not path or not path.exists():
return []
out = []
for r in rows_of(path, label):
d = {"caption": clean(r.get("caption")), "amount": d0(r.get("amount"))}
for e in extra:
d[e] = clean(r.get(e)).lower()
out.append(d)
return out
def load_adjustments(path: Path | None) -> dict[str, list[dict]]:
out: dict[str, list[dict]] = defaultdict(list)
if not path or not path.exists():
return out
for i, r in enumerate(rows_of(path, "adjustments"), start=1):
acct = clean(r.get("account"))
cause = clean(r.get("cause")).lower()
amt = dec(r.get("amount"))
if not acct or amt is None:
continue
if cause not in CAUSES:
sys.exit(f"Adjustment row {i} ({acct}): cause '{cause}' is not recognised. "
f"Valid causes are {sorted(CAUSES)}. If a difference cannot be "
f"placed in one of these, it is not explained - report it rather "
f"than inventing a caption.")
out[acct].append({"cause": cause, "amount": amt,
"evidence": clean(r.get("evidence"))})
return out
# ------------------------------------------------------------------- derivation
def derive(bs, adjustments) -> dict:
cash = [a for a in bs if a["type"] == "cash"]
open_cash = sum((a["opening"] for a in cash), ZERO)
close_cash = sum((a["closing"] for a in cash), ZERO)
wc, unattributed = [], []
for a in bs:
if a["type"] not in OP_SIGN:
continue
raw = a["closing"] - a["opening"]
adjs = adjustments.get(a["account"], [])
adj_total = sum((x["amount"] for x in adjs), ZERO)
cash_move = raw - adj_total
effect = cash_move * OP_SIGN[a["type"]]
wc.append({**a, "raw_movement": raw, "adjustments": adjs,
"adjustment_total": adj_total, "cash_movement": cash_move,
"cash_effect": effect,
"sign": "outflow if positive movement" if a["type"].endswith("asset")
else "inflow if positive movement"})
return {"cash_accounts": cash, "open_cash": open_cash, "close_cash": close_cash,
"change_in_cash": close_cash - open_cash, "working_capital": wc}
def bs_balance(bs, which: str) -> Decimal:
"""Assets less liabilities and equity. Should be zero."""
assets = sum((a[which] for a in bs
if a["type"] in ("cash", "operating_asset", "investing_asset")), ZERO)
contra = sum((a[which] for a in bs if a["type"] == "noncash_contra"), ZERO)
liabs = sum((a[which] for a in bs
if a["type"] in ("operating_liability", "debt")), ZERO)
equity = sum((a[which] for a in bs if a["type"] == "equity"), ZERO)
return (assets - contra) - (liabs + equity)
def build_statement(bs, inc, noncash, invfin, der) -> dict:
ni = next((x["amount"] for x in inc
if "net income" in x["caption"].lower()
or "net_income" in x["caption"].lower()), None)
if ni is None:
ni = sum((x["amount"] for x in inc), ZERO)
nc_total = sum((x["amount"] for x in noncash), ZERO)
wc_total = sum((x["cash_effect"] for x in der["working_capital"]), ZERO)
operating = ni + nc_total + wc_total
inv = [x for x in invfin if x.get("section") == "investing"]
fin = [x for x in invfin if x.get("section") == "financing"]
fx = [x for x in invfin if x.get("section") == "fx"]
investing = sum((x["amount"] for x in inv), ZERO)
financing = sum((x["amount"] for x in fin), ZERO)
fx_effect = sum((x["amount"] for x in fx), ZERO)
computed = operating + investing + financing + fx_effect
return {"net_income": ni, "noncash": noncash, "noncash_total": nc_total,
"wc_total": wc_total, "operating": operating,
"investing_items": inv, "investing": investing,
"financing_items": fin, "financing": financing,
"fx_items": fx, "fx_effect": fx_effect,
"computed_change": computed,
"actual_change": der["change_in_cash"],
"unexplained": computed - der["change_in_cash"]}
def reconcile_sections(bs, st, noncash, invfin) -> list[dict]:
"""Cross-check each non-operating balance sheet movement against what was supplied.
Every movement must be explained by something: a contra account by the non-cash
charge added back, PP&E by capital expenditure and disposals, debt by draws and
repayments, retained earnings by net income and distributions. An account whose
movement is explained by nothing is a hidden plug.
"""
def move(types):
return sum((a["closing"] - a["opening"] for a in bs if a["type"] in types), ZERO)
def cap_total(items, *words):
return sum((x["amount"] for x in items
if any(w in x["caption"].lower() for w in words)), ZERO)
checks = []
contra = move({"noncash_contra"})
nc_depr = cap_total(noncash, "depreciation", "amortisation", "amortization",
"bad debt", "impairment", "allowance")
checks.append({
"name": "Contra-asset movement vs non-cash charges added back",
"derived": contra, "supplied": nc_depr, "difference": contra - nc_depr,
"note": "Accumulated depreciation and the AR allowance move by the non-cash "
"charge, less any write-off or disposal. A difference means a disposal "
"or write-off was not supplied, or the add-back is wrong.",
})
inv_assets = move({"investing_asset"})
inv_supplied = -sum((x["amount"] for x in invfin
if x.get("section") == "investing"), ZERO)
checks.append({
"name": "Investing asset movement vs investing activities",
"derived": inv_assets, "supplied": inv_supplied,
"difference": inv_assets - inv_supplied,
"note": "Gross fixed asset movement should equal capital expenditure less the "
"cost of disposals. A difference means a disposal or a non-cash addition "
"was not supplied.",
})
debt = move({"debt"})
fin_debt = cap_total(invfin, "debt", "borrow", "loan", "note", "facility",
"repay")
checks.append({
"name": "Debt movement vs financing activities",
"derived": debt, "supplied": fin_debt, "difference": debt - fin_debt,
"note": "Debt balances move by draws less repayments. A difference means a "
"movement was not supplied, or debt was assumed or forgiven non-cash.",
})
equity = move({"equity"})
ni = st["net_income"]
fin_equity = cap_total(invfin, "dividend", "distribution", "equity", "stock",
"share", "buyback", "repurchase")
expected_equity = ni + fin_equity
checks.append({
"name": "Equity movement vs net income and equity financing",
"derived": equity, "supplied": expected_equity,
"difference": equity - expected_equity,
"note": "Equity moves by net income, plus equity issued or repurchased, less "
"dividends and distributions. A difference means a distribution was not "
"supplied, or something was posted directly to equity - which is worth "
"reading in full.",
})
return checks
# -------------------------------------------------------------------- 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 = 28
def widths(ws, w):
for c, v in w.items():
ws.column_dimensions[get_column_letter(c)].width = v
def sheet_statement(wb, st, der, meta, overall, esc):
ws = wb.active
ws.title = "Cash Flow Statement"
widths(ws, {1: 4, 2: 56, 3: 18, 4: 18, 5: 58})
r = 1
def line(label, v=None, t=None, *, bold=False, size=11, fill=None, border=None,
note="", indent=0):
nonlocal r
c = ws.cell(row=r, column=2, value=(" " * indent) + label)
c.font = Font(bold=bold, size=size)
c.alignment = Alignment(wrap_text=True, vertical="top")
if fill:
c.fill = fill
for col, val in ((3, v), (4, t)):
if val is None:
continue
cc = ws.cell(row=r, column=col, value=float(val))
cc.number_format = MONEY
cc.font = Font(bold=bold)
if border:
cc.border = border
if note:
n = ws.cell(row=r, column=5, value=note)
n.font = Font(italic=True, size=9)
n.alignment = Alignment(wrap_text=True, vertical="top")
r += 1
line("STATEMENT OF CASH FLOWS - INDIRECT METHOD", 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
v = ws.cell(row=r, column=2,
value="FOOTS - the statement reconciles to the balance sheet cash movement"
if overall else
"DOES NOT FOOT - see the unexplained line. Do not plug it.")
v.font = OK_FONT if overall else BAD_FONT
if not overall:
v.fill = BAD_FILL
r += 2
line("CASH FLOWS FROM OPERATING ACTIVITIES", bold=True, size=12, fill=SUB_FILL)
line("Net income", st["net_income"], indent=1)
if st["noncash"]:
line("Adjustments for non-cash items:", indent=1)
for x in st["noncash"]:
line(x["caption"], x["amount"], indent=2)
line("Changes in operating assets and liabilities:", indent=1)
for x in der["working_capital"]:
if x["cash_effect"] == ZERO:
continue
line(x["caption"], x["cash_effect"], indent=2)
line("Net cash from operating activities", None, st["operating"], bold=True,
border=TOP)
r += 1
line("CASH FLOWS FROM INVESTING ACTIVITIES", bold=True, size=12, fill=SUB_FILL)
for x in st["investing_items"]:
line(x["caption"], x["amount"], indent=1)
line("Net cash from investing activities", None, st["investing"], bold=True,
border=TOP)
r += 1
line("CASH FLOWS FROM FINANCING ACTIVITIES", bold=True, size=12, fill=SUB_FILL)
for x in st["financing_items"]:
line(x["caption"], x["amount"], indent=1)
line("Net cash from financing activities", None, st["financing"], bold=True,
border=TOP)
r += 1
if st["fx_items"] or st["fx_effect"] != ZERO:
for x in st["fx_items"]:
line(x["caption"], x["amount"], indent=1)
line("Effect of exchange rate changes on cash", None, st["fx_effect"], bold=True)
r += 1
line("NET CHANGE IN CASH", None, st["computed_change"], bold=True, border=TOP)
line("Cash at beginning of period", None, der["open_cash"])
line("Cash at end of period - computed", None,
der["open_cash"] + st["computed_change"], bold=True)
line("Cash at end of period - per balance sheet", None, der["close_cash"])
line("UNEXPLAINED - DO NOT PLUG", None, st["unexplained"], bold=True, border=DBL,
fill=None if st["unexplained"] == ZERO else BAD_FILL,
note="" if st["unexplained"] == ZERO else
"A difference here must be attributed to a named cause. Absorbing it into "
"an 'other' caption is how a plug survives review.")
c = ws.cell(row=r - 1, column=4)
c.font = OK_FONT if st["unexplained"] == ZERO else BAD_FONT
r += 1
if esc:
line("ESCALATE", bold=True, size=12, fill=BAD_FILL)
for e in esc:
line(" " + e)
r += 1
r += 1
line("Prepared by / date: __________________ ____________", bold=True)
line("Reviewed by / date: __________________ ____________", bold=True)
def sheet_proof(wb, tests):
ws = wb.create_sheet("Proof")
widths(ws, {1: 4, 2: 54, 3: 18, 4: 62})
r = 1
ws.cell(row=r, column=2, value="PROOF").font = Font(bold=True, size=14)
r += 2
for t in tests:
c = ws.cell(row=r, column=2, value=f"TEST {t['num']} - {t['name']}")
c.font, c.fill = Font(bold=True, size=12), SUB_FILL
r += 1
for label, val in t["lines"]:
ws.cell(row=r, column=2, value=" " + label)
cc = ws.cell(row=r, column=3,
value=float(val) if isinstance(val, Decimal) else val)
if isinstance(val, Decimal):
cc.number_format = MONEY
r += 1
ws.cell(row=r, column=2, value=" Result").font = Font(bold=True)
v = ws.cell(row=r, column=3, value="PASS" if t["passed"] else "FAIL")
v.font = OK_FONT if t["passed"] else BAD_FONT
if not t["passed"]:
v.fill = BAD_FILL
if t.get("note"):
n = ws.cell(row=r, column=4, value=t["note"])
n.font = Font(italic=True, size=9)
n.alignment = Alignment(wrap_text=True, vertical="top")
r += 2
def sheet_wc(wb, wc):
ws = wb.create_sheet("Working Capital Derivation")
heads = ["Account", "Caption", "Type", "Opening", "Closing", "Raw movement",
"Named adjustments", "Cash movement", "Sign applied", "Cash flow effect",
"Adjustment causes"]
ws.append(heads)
hdr(ws, len(heads))
for x in wc:
ws.append([x["account"], x["caption"], x["type"], float(x["opening"]),
float(x["closing"]), float(x["raw_movement"]),
float(x["adjustment_total"]), float(x["cash_movement"]),
"+1" if OP_SIGN[x["type"]] > 0 else "-1",
float(x["cash_effect"]),
"; ".join(f"{CAUSES[a['cause']]}: {a['amount']:,.2f}"
f"{' (' + a['evidence'] + ')' if a['evidence'] else ''}"
for a in x["adjustments"])])
last = ws.max_row
ws.append(["TOTAL", "", "", None, None, None, None, None, "",
f"=SUM(J2:J{last})", ""])
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for i in (3, 4, 5, 6, 7, 9):
row[i].number_format = MONEY
if isinstance(row[6].value, (int, float)) and row[6].value:
row[6].fill = WARN_FILL
for c in range(1, len(heads) + 1):
cell = ws.cell(row=ws.max_row, column=c)
cell.font, cell.border = Font(bold=True), TOP
r = ws.max_row + 2
for note in [
"Movements are DERIVED from the balance sheet, not taken from the statement.",
"Sign logic: operating asset up = outflow; operating liability up = inflow.",
"Check the account types before reading the result. Two accounts mis-typed in",
"opposite directions leave the statement footing perfectly while both sections",
"are misstated - this tab is what surfaces that.",
]:
ws.cell(row=r, column=1, value=note).font = Font(italic=True)
r += 1
ws.freeze_panes = "C2"
widths(ws, {1: 14, 2: 30, 3: 20, 4: 15, 5: 15, 6: 15, 7: 17, 8: 15, 9: 12,
10: 17, 11: 60})
def sheet_bs(wb, bs, section_checks):
ws = wb.create_sheet("Balance Sheet Movement")
heads = ["Account", "Caption", "Type", "Opening", "Closing", "Movement",
"Feeds"]
ws.append(heads)
hdr(ws, len(heads))
feeds = {"cash": "cash - the target of the proof",
"operating_asset": "operating - working capital",
"operating_liability": "operating - working capital",
"noncash_contra": "operating - non-cash add-back (NOT working capital)",
"investing_asset": "investing",
"debt": "financing", "equity": "financing / net income"}
for a in bs:
ws.append([a["account"], a["caption"], a["type"], float(a["opening"]),
float(a["closing"]), float(a["closing"] - a["opening"]),
feeds.get(a["type"], "?")])
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
for i in (3, 4, 5):
row[i].number_format = MONEY
if row[2].value == "noncash_contra":
row[6].fill = WARN_FILL
r = ws.max_row + 3
ws.cell(row=r, column=1, value="SECTION RECONCILIATIONS").font = Font(bold=True, size=12)
r += 1
for h, col in zip(["Check", "Per balance sheet", "Per detail supplied", "Difference",
"What a difference means"], range(1, 6)):
c = ws.cell(row=r, column=col, value=h)
c.fill, c.font = HDR_FILL, HDR_FONT
r += 1
for c in section_checks:
ws.cell(row=r, column=1, value=c["name"])
for col, v in ((2, c["derived"]), (3, c["supplied"]), (4, c["difference"])):
cc = ws.cell(row=r, column=col, value=float(v))
cc.number_format = MONEY
d = ws.cell(row=r, column=4)
if c["difference"] != ZERO:
d.font, d.fill = BAD_FONT, BAD_FILL
else:
d.font = OK_FONT
n = ws.cell(row=r, column=5, value=c["note"])
n.alignment = Alignment(wrap_text=True, vertical="top")
r += 1
r += 1
ws.cell(row=r, column=1,
value="Contra-asset accounts are NOT derived into working capital. Their "
"movement is the non-cash charge added back separately, so deriving "
"them as well would double-count it."
).font = Font(italic=True)
ws.freeze_panes = "C2"
widths(ws, {1: 42, 2: 20, 3: 22, 4: 16, 5: 70, 6: 16, 7: 44})
def sheet_compare(wb, st, client):
ws = wb.create_sheet("Comparison")
heads = ["Line", "Derived", "Per client statement", "Difference"]
ws.append(heads)
hdr(ws, len(heads))
if not client:
ws.cell(row=2, column=1,
value="No client statement supplied - nothing to compare. Where the "
"client has prepared a statement, the comparison against the "
"derived figures is the point of the exercise.")
widths(ws, {1: 90, 2: 18, 3: 20, 4: 16})
return
derived = {"operating": st["operating"], "investing": st["investing"],
"financing": st["financing"], "fx": st["fx_effect"],
"net change in cash": st["computed_change"]}
for k, v in derived.items():
cv = client.get(k)
ws.append([k, float(v), float(cv) if cv is not None else None,
float(v - cv) if cv is not None else 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
if isinstance(row[3].value, (int, float)) and row[3].value:
row[3].font, row[3].fill = BAD_FONT, BAD_FILL
widths(ws, {1: 30, 2: 18, 3: 22, 4: 16})
def sheet_judgment(wb, st):
ws = wb.create_sheet("Judgment Items")
heads = ["Classification question", "Why it is judgment", "Amount if identified",
"Determined by"]
ws.append(heads)
hdr(ws, len(heads))
caps = {x["caption"].lower(): x["amount"]
for x in st["noncash"] + st["investing_items"] + st["financing_items"]}
for name, why in JUDGMENT:
amt = next((v for k, v in caps.items() if name.split()[0].lower() in k), None)
ws.append([name, why, float(amt) if amt is not None else None, None])
for row in ws.iter_rows(min_row=2, max_row=ws.max_row):
row[2].number_format = MONEY
row[1].alignment = Alignment(wrap_text=True, vertical="top")
r = ws.max_row + 2
ws.cell(row=r, column=1,
value="Flagged, not concluded. Section classification is a policy and "
"framework question and belongs with the person signing."
).font = Font(italic=True)
widths(ws, {1: 40, 2: 62, 3: 20, 4: 22})
# ------------------------------------------------------------------------ main
def main() -> int:
ap = argparse.ArgumentParser(description=__doc__)
ap.add_argument("--balance-sheet", required=True)
ap.add_argument("--income-statement", required=True)
ap.add_argument("--out", required=True)
ap.add_argument("--noncash")
ap.add_argument("--investing-financing")
ap.add_argument("--adjustments")
ap.add_argument("--client-statement",
help="CSV: line, amount - the client's own statement, for comparison")
ap.add_argument("--client", default="")
ap.add_argument("--period", default="")
ap.add_argument("--force", action="store_true")
args = ap.parse_args()
bs = load_bs(Path(args.balance_sheet))
inc = load_pairs(Path(args.income_statement), "income statement")
noncash = load_pairs(Path(args.noncash) if args.noncash else None, "non-cash")
invfin = load_pairs(Path(args.investing_financing) if args.investing_financing
else None, "investing/financing", extra=("section",))
adjustments = load_adjustments(Path(args.adjustments) if args.adjustments else None)
client_st = {}
if args.client_statement and Path(args.client_statement).exists():
for r in rows_of(Path(args.client_statement), "client statement"):
client_st[clean(r.get("line")).lower()] = d0(r.get("amount"))
open_bal = bs_balance(bs, "opening")
close_bal = bs_balance(bs, "closing")
der = derive(bs, adjustments)
st = build_statement(bs, inc, noncash, invfin, der)
section_checks = reconcile_sections(bs, st, noncash, invfin)
unfed = [c for c in section_checks if c["difference"] != ZERO]
tests = []
tests.append({"num": 1, "name": "Balance sheet balances in both periods",
"lines": [("Opening: assets less liabilities and equity", open_bal),
("Closing: assets less liabilities and equity", close_bal)],
"passed": open_bal == ZERO and close_bal == ZERO,
"note": "" if open_bal == ZERO and close_bal == ZERO else
"A cash flow built on an unbalanced balance sheet is "
"meaningless. Fix this before reading anything else."})
tests.append({"num": 2, "name": "Cash movement identified from the balance sheet",
"lines": [("Cash accounts identified",
len(der["cash_accounts"])),
("Opening cash", der["open_cash"]),
("Closing cash", der["close_cash"]),
("Change in cash", der["change_in_cash"])],
"passed": bool(der["cash_accounts"]),
"note": "" if der["cash_accounts"] else
"No account was typed as 'cash'. Nothing can be proven "
"without identifying the target."})
unattributed = [x for x in der["working_capital"]
if x["adjustment_total"] != ZERO and
any(not a["evidence"] for a in x["adjustments"])]
tests.append({"num": 3, "name": "Working capital derived from the balance sheet",
"lines": [("Operating accounts derived",
len(der["working_capital"])),
("Total working capital effect", st["wc_total"]),
("Accounts with named adjustments",
len([x for x in der["working_capital"]
if x["adjustments"]])),
("Adjustments lacking evidence", len(unattributed))],
"passed": not unattributed,
"note": "" if not unattributed else
"An adjustment with a cause but no evidence reference is a "
"label, not an explanation."})
tests.append({"num": 4, "name": "The statement foots to the balance sheet",
"lines": [("Operating", st["operating"]),
("Investing", st["investing"]),
("Financing", st["financing"]),
("FX effect", st["fx_effect"]),
("Computed change in cash", st["computed_change"]),
("Change in cash per balance sheet", st["actual_change"]),
("Unexplained", st["unexplained"])],
"passed": st["unexplained"] == ZERO,
"note": "" if st["unexplained"] == ZERO else
"Attribute this to a named cause. Do not absorb it into an "
"'other' caption."})
tests.append({"num": 5,
"name": "Every non-operating movement is explained by what was supplied",
"lines": [(c["name"], c["difference"]) for c in section_checks],
"passed": not unfed,
"note": "" if not unfed else
"Each movement must be explained by something - a contra "
"account by the non-cash charge, PP&E by capex and disposals, "
"debt by draws and repayments, equity by net income and "
"distributions. An unexplained movement is a hidden plug."})
tests.append({"num": 6, "name": "Comparison to the client's statement",
"lines": ([("Client statement supplied", "yes")] +
[(k, v) for k, v in client_st.items()]) if client_st
else [("Client statement supplied", "no")],
"passed": True,
"note": "" if client_st else
"NOT PERFORMED - no client statement supplied. Where one "
"exists, comparing it to the derived figures is the point."})
esc = []
if st["unexplained"] != ZERO:
esc.append(f"The statement does not foot: {st['unexplained']:,.2f} unexplained. "
f"Attribute it to a named cause - a plug hidden in an 'other' caption "
f"survives review because reviewers check the bottom line rather than "
f"each line against the balance sheet.")
if open_bal != ZERO or close_bal != ZERO:
esc.append(f"The balance sheet does not balance (opening {open_bal:,.2f}, "
f"closing {close_bal:,.2f}). Everything downstream is meaningless "
f"until this is fixed.")
for u in unfed:
esc.append(f"{u['name']}: balance sheet movement {u['derived']:,.2f} against "
f"{u['supplied']:,.2f} supplied - difference {u['difference']:,.2f}. "
f"{u['note']}")
others = [x for x in st["noncash"] + st["investing_items"] + st["financing_items"]
if "other" in x["caption"].lower() or "misc" in x["caption"].lower()]
if others:
tot = sum((abs(x["amount"]) for x in others), ZERO)
sections = abs(st["operating"]) + abs(st["investing"]) + abs(st["financing"])
if sections and tot / sections > Decimal("0.05"):
esc.append(f"'Other' or miscellaneous captions total {tot:,.2f}, more than 5% "
f"of gross section activity. A material unexplained 'other' is a "
f"plug regardless of its label.")
if st["operating"] < st["net_income"] and st["net_income"] > ZERO:
esc.append(f"Operating cash flow ({st['operating']:,.2f}) is below net income "
f"({st['net_income']:,.2f}). Report the relationship across periods; "
f"it is an earnings-quality signal, not a conclusion.")
if st["fx_effect"] != ZERO and not any(
"foreign" in a["caption"].lower() or "translation" in a["caption"].lower()
for a in bs):
esc.append(f"An FX effect of {st['fx_effect']:,.2f} is presented but no balance "
f"sheet account suggests a foreign operation. Confirm the entity has "
f"foreign operations.")
overall = all(t["passed"] for t in tests)
meta = {
"Client": args.client or "(not stated)",
"Period": args.period or "(not stated)",
"Balance sheet accounts": len(bs),
"Cash accounts identified": len(der["cash_accounts"]),
"Operating accounts derived": len(der["working_capital"]),
"Prepared": datetime.now().strftime("%Y-%m-%d %H:%M"),
}
# ---- console
print("=" * 76)
print("CASH FLOW STATEMENT TIE-OUT")
print("=" * 76)
print(f"Client : {meta['Client']} Period: {meta['Period']}")
print()
print(f"Balance sheet balances: opening {open_bal:,.2f} closing {close_bal:,.2f}"
f" {'OK' if open_bal == ZERO and close_bal == ZERO else '*** DOES NOT BALANCE ***'}")
print()
print(f" Net income {st['net_income']:>16,.2f}")
print(f" Non-cash adjustments {st['noncash_total']:>16,.2f}")
print(f" Working capital (derived) {st['wc_total']:>16,.2f}")
print(f" = Operating {st['operating']:>16,.2f}")
print(f" Investing {st['investing']:>16,.2f}")
print(f" Financing {st['financing']:>16,.2f}")
print(f" FX effect {st['fx_effect']:>16,.2f}")
print(f" = NET CHANGE IN CASH {st['computed_change']:>16,.2f}")
print(f" Change per balance sheet {st['actual_change']:>16,.2f}")
print(f" UNEXPLAINED {st['unexplained']:>16,.2f} "
f"{'OK' if st['unexplained'] == ZERO else '*** MUST BE ZERO - DO NOT PLUG ***'}")
print()
for t in tests:
print(f"Test {t['num']}: {'PASS' if t['passed'] else '*** FAIL ***':<14} "
f"{t['name']}")
if esc:
print("\nESCALATE:")
for e in esc:
print(f" ! {e}")
if not overall and not args.force:
print("\n" + "=" * 76)
print("WORKBOOK NOT WRITTEN.")
print("Attribute every difference to a named cause. The statement of cash flows")
print("must reconcile to the balance sheet by construction - there is no judgment")
print("in that requirement.")
print("=" * 76)
return 1
wb = Workbook()
sheet_statement(wb, st, der, meta, overall, esc)
sheet_proof(wb, tests)
sheet_wc(wb, der["working_capital"])
sheet_bs(wb, bs, section_checks)
sheet_compare(wb, st, client_st)
sheet_judgment(wb, st)
wb.active = 0
out = Path(args.out)
if not overall:
out = out.with_name(out.stem + " [DOES NOT FOOT]" + out.suffix)
out.parent.mkdir(parents=True, exist_ok=True)
wb.save(str(out))
print(f"\n{'FOOTS' if overall else 'DOES NOT FOOT (forced)'}. Workbook: {out}")
return 0 if overall else 1
if __name__ == "__main__":
sys.exit(main())
