Month-End Close Engine — BS + IS Reconciliation

A production-grade Python suite for automating month-end close with internal controls. Reconcile every balance-sheet account to its support, analyze income-statement variances, and prove the financial statements articulate.

Features

• BS Account Reconciliation — tie every material BS account to supporting schedules, bank statements, or subledgers
• IS Flux Analysis — period-over-period and budget-to-actual variance analysis with materiality thresholds
• 9 SOX-Style Controls — automated checks + evidenced reviews; the marquee control proves BS and IS articulate
• Maker-Checker Segregation — independent reviewer recomputes truth from raw data and catches preparer errors
• Deterministic Test Data — synthetic data with planted, discoverable breaks — no answer key
• Portable & ASCII-Safe — runs on any computer; no hardcoded paths; Windows cp1252-safe console output

Quick Start

Install

pip install openpyxl  # optional: for .xlsx output (CSV fallback included)

Run

# Generate synthetic close data with planted breaks
python regen_close_data.py

# Run the close controls — THE headline is X-1 (articulation)
python close_controls.py

# Reconcile balance-sheet accounts
python bs_recon.py

# Analyze income-statement variances
python is_flux.py

# Independent review (maker-checker)
python close_review.py

# Run all tests
python -m pytest -q test_close.py

Output

• BS_Recon_Report.xlsx — balance-sheet account reconciliations and exceptions
• IS_Flux_Report.xlsx — income-statement variance analysis
• Close_Review.xlsx — independent reviewer findings
• Console — control register with PASS/FAIL/REVIEW per control

The Core Idea

A close tool is only useful if it discovers problems, flags them for human review, and proves it tied out. A tool that silently plugs a variance is worse than no tool.

This suite follows five non-negotiable principles:

1. Raw inputs only — no answer key; the tool computes every figure from the data.
2. Discover, don't be told — breaks are found by querying for patterns, not hardcoded.
3. Flag VERIFY — variances are marked for confirmation; the tool never auto-clears.
4. Prove the work — control checks computed from the data; every pass is a real boolean.
5. Maker-checker — two scripts with separate code paths; the reviewer recomputes truth.

Internal Controls

Control	Type	What It Does	The Headline
BS-1	AUTOMATED	Trial balance balances (A = L + E)	Foundational sanity check
BS-2	AUTOMATED	All material accounts have support	No forgotten accounts
BS-3	AUTOMATED	GL-to-support tie per account	Each account reconciled
BS-4	AUTOMATED	Roll-forwards foot (Opening + Activity = Closing)	Schedules are correct
BS-5	EVIDENCED	Stale reconciling items (>60 days) flagged	Old items reviewed
BS-6	AUTOMATED	Accounts carry normal-balance sign	No sign anomalies
IS-1	EVIDENCED	Material variances (>$5K or >10%) flagged	Requires explanation
IS-2	AUTOMATED	Revenue/Expense sign sanity	No wrong-way postings
X-1	AUTOMATED	NI (IS) → RE movement (BS) articulation	THE CONTROL — statements must tie

X-1 is the marquee control. If Net Income from the Income Statement does not equal the change in Retained Earnings on the Balance Sheet, the statements do not articulate and the close cannot be signed. This control is automated and cannot be overridden.

See CONTROLS.md for the full control narrative, assertion map, and how to read each one.

The Data

Inputs (Synthetic CSVs)

Generated by regen_close_data.py:

• TB_current.csv — trial balance as of period-end (current period)
• TB_prior.csv — trial balance as of prior period (drives variance analysis)
• Budget.csv — budget by account
• Bank_Statement.csv — bank statement for cash reconciliation + stale items
• AR_Aging.csv — customer-level AR detail (ties the AR control account)
• Prepaid_Schedule.csv — prepaids roll-forward
• FA_Register.csv — fixed-asset register with accumulated depreciation roll-forward
• Accrual_Schedule.csv — accrual roll-forward
• Flux_Explanations.csv — (optional) written variance explanations; IS-1 skips accounts explained here

The clean books are built from balanced journal entries, so the trial balance is internally consistent by construction. Each break is then applied as a specific, realistic error — and each control isolates exactly one:

1. BS-1 — $1,234.56: a one-sided (unbalanced) debit posted to Debt. The BS-1 imbalance equals this plug exactly.
2. BS-4: FA-002's opening accumulated depreciation is misstated in the register, so its roll-forward doesn't foot — while the register total still ties to the GL, proving BS-4 and BS-3 catch different errors.
3. BS-5: an outstanding check dated April 15 (63 days before the as-of date, past the 60-day staleness threshold).
4. BS-6: a balanced but misposted entry (DR Allowance / CR AP) leaves the credit-normal allowance with a $3,500 debit balance. BS-1 is unaffected because the entry balances — only the sign control fires.
5. IS-1: COGS actual $60,000 vs budget $48,000 — 25% over and above the $5K threshold, with no written explanation on file.
6. X-1 — $7,500 (THE headline): a balanced top-side entry posted directly against Retained Earnings, bypassing the income statement. NI is $25,000 but RE only moves $17,500. BS-1 stays at $1,234.56 — the two controls decompose two different errors.

Expected results from python close_controls.py on the planted data:

BS-1: FAIL   - imbalance 1,234.56 (the unbalanced entry, isolated exactly)
BS-2: PASS   - every material account has a recon method
BS-3: PASS   - all 6 supported accounts tie to their schedules
BS-4: FAIL   - FA-002 roll-forward does not foot
BS-5: REVIEW - stale outstanding check (63 days)
BS-6: FAIL   - allowance carries a 3,500.00 debit balance
IS-1: REVIEW - COGS variance 12,000.00 (25.0%) requires explanation
IS-2: PASS   - no revenue/expense sign anomalies
X-1:  FAIL   - NI 25,000.00 vs RE movement 17,500.00; difference 7,500.00

SUMMARY: 3 PASS, 4 FAIL, 2 REVIEW out of 9 controls

Run python regen_close_data.py --clean for break-free books: every AUTOMATED control passes and every EVIDENCED population is empty — proof the verdicts are computed from the data, not hardcoded to look alarming or green.

Outputs

• .xlsx workbooks with tabs for each section, plus a Control_Checks tab
• CSV fallback if openpyxl is not installed
• Console register showing PASS/FAIL/REVIEW for each control
• Exceptions lists for each control

Scripts

regen_close_data.py

Generate deterministic synthetic close data. Clean books come from balanced journal entries; planted breaks are layered on as specific realistic errors. --clean generates break-free books, --outdir DIR writes elsewhere.

Run: python regen_close_data.py [--clean] [--outdir DIR]

bs_recon.py

Balance-sheet reconciliation preparer. Ties Cash, Prepaids, AR, FA cost, accumulated depreciation, and Accruals to their schedules; ages reconciling items; runs ControlChecks; writes the working papers and Close_Summary.csv — the labeled package the independent reviewer grades. Output: BS_Recon_Report.xlsx + Close_Summary.csv.

Run: python bs_recon.py

is_flux.py

Income-statement flux analysis preparer. Computes period-over-period and budget-to-actual variance with materiality thresholds. Output: IS_Flux_Report.xlsx.

Run: python is_flux.py

close_controls.py

The controls register — 9 automated/evidenced controls covering BS, IS, and cross-statement articulation. Output: console register.

Run: python close_controls.py

Import-safe: from close_controls import run_controls; result = run_controls() returns a dict; no side effects on import.

close_review.py

Maker-checker / independent reviewer. Recomputes every summary figure from the raw CSVs — it imports no preparer code (a test enforces this) — then reads the prepared Close_Summary.csv by label and diagnoses each mismatch: sign flip, double count, hidden articulation gap, or off-by-amount. It also renders its own articulation verdict regardless of what the package claims. Output: Close_Review.xlsx.

Run: python close_review.py (run bs_recon.py first)

test_close.py

Test suite covering data generation, control register, BS/IS recon, and schema validation.

Run: python -m pytest -q test_close.py

Reusable Library

This suite uses acct_lib.py, a portable accounting-automation library that provides:

• Path resolution: here() — resolve files relative to the running script (not the shell's CWD)
• Parsing: num() (handles currency, thousands, negatives), parse_date(), days_between()
• CSV loading: load_csv() (utf-8-sig for Excel BOM)
• Ledger math: net_impact(), account_balance()
• Auto-discovery: discover_unreferenced() — find GL lines with no source reference
• Control checks: ControlChecks() class for passing boolean assertions
• ASCII-safe output: aprint() — Windows cp1252-safe console
• Report writing: Report() class with .xlsx + CSV fallback

Run python acct_lib.py directly to self-test the library on any machine.

Portability

This suite runs on any machine:

• No hardcoded paths — all files resolved with here(...)
• Standard library to run — openpyxl is optional
• ASCII-safe console — works on Windows cp1252 terminals
• UTF-8-sig input — Excel BOM handled automatically
• Tolerant parsing — handles common ERP export formats and column-name synonyms

Testing

# Generate fresh planted data (CI does this before every test run)
python regen_close_data.py

# Run controls (expect 3 PASS, 4 FAIL, 2 REVIEW - the planted breaks)
python close_controls.py

# Full suite: planted breaks detected with exact figures, clean data passes
# everything, reviewer catches a doctored package with correct diagnoses
python -m pytest -q test_close.py

The tests assert the specific planted figures (BS-1 imbalance exactly 1,234.56; X-1 gap exactly 7,500.00), that clean data fires nothing, and that the reviewer diagnoses a deliberately doctored package (sign flip, double count, hidden articulation gap). CI runs on Ubuntu and Windows.

Architecture

Preparer scripts build the close package:

• regen_close_data.py — synthetic inputs
• bs_recon.py — reconcile accounts
• is_flux.py — analyze variances

Control script audits the close:

• close_controls.py — run 9 controls

Checker script verifies independently:

• close_review.py — segregation of duties

Test suite validates:

• test_close.py — pytest

Topics

month-end-close, balance-sheet-reconciliation, income-statement, flux-analysis, financial-statements, trial-balance, retained-earnings, articulation, account-reconciliation, sox-compliance, internal-controls, gl-reconciliation, roll-forward, variance-analysis, accounting-automation, python, icfr, controllership, audit, financial-controls

Next Steps

For production use:

1. Replace synthetic data with real GL exports + subledger files.
2. Map your COA to the control account list.
3. Add cycle-specific supports (AR aging, AP subledger, etc.).
4. Extend controls with user approvals and audit trails.
5. Integrate with your GL system (API, scheduled export, etc.).

---

Built with the accounting-python-tools skill — a blueprint for trustworthy close automation that an auditor would trust.