The reconciliation routine: keeping SPV data consistent without heroics

This guide describes manual finance controls in source ledgers and separate working papers. Clarity Ledger's read-only reporting connections do not complete bank reconciliations, post journals or perform intercompany eliminations. The KPI, forecasting and commentary processes below remain separate from its saved financial reports.

In property groups, "bad data" is rarely a single big mistake. It is usually small inconsistencies that compound across dozens of SPVs:

• one bank account not reconciled,
• one intercompany balance left hanging,
• one refurb invoice coded differently this month,
• one SPV closing late so the portfolio view is half-estimated,
• one KPI definition drifting between packs.

Then month-end arrives, and the finance team becomes the emergency response unit-pulling late nights to "make the numbers work."

There is a better way.

A scalable finance function does not rely on heroic effort. It relies on a repeatable reconciliation routine: a small set of checks, done consistently, that keeps SPV-level data clean and portfolio reporting trustworthy.

This post lays out a practical routine you can use across 10, 25, or 100+ SPVs-without turning reconciliation into a full-time project.

---

The goal of reconciliation in a multi-SPV world

Reconciliation is not "tick and bash." It is the discipline that ensures your reporting answers stay stable:

• Cash is real (tied to the bank, split by restrictions).
• NOI is comparable (consistent mapping and classification).
• Debt is accurate (balances, interest, and covenant inputs make sense).
• Portfolio roll-ups are trustworthy (SPVs included, cutoffs aligned, exceptions visible).

If you do reconciliation right, month-end becomes assembly-not reconstruction.

---

The "no heroics" principle

A reconciliation routine works when it follows three rules:

1) Small checks, done frequently

Most reconciliation problems are easiest to fix when they are fresh-not three weeks later.

2) Exception-based review

You do not need to deeply audit every SPV every month. You need to surface exceptions early and focus effort where risk is.

3) Single source of truth for roll-ups

If portfolio reporting is still a manual spreadsheet roll-up, reconciliation will always feel like chaos. The routine needs a consistent consolidation layer and mapping logic so the same checks work every month.

---

The minimum viable reconciliation stack

Think of reconciliation as a stack with five layers. You do not need perfection in every layer on day one-but you do need consistency.

Layer 1: Cash and bank reality

What you reconcile

• Bank balances per SPV (per bank account) to the ledger
• Outstanding items: deposits in transit, uncleared payments, bank fees

Why it matters
Cash is the fastest way for a portfolio to "look fine" while actually being at risk.

Common mistakes

• "Close enough" bank recs (or not done for all accounts)
• Reporting total cash without splitting restrictions (DSRA, escrow, tenant deposits)
• Forgetting second/third bank accounts (capex accounts, rent accounts, reserves)

Best-practice routine

• Reconcile every bank account, not just the main operating account

• Split cash every month into:

  • unrestricted
  • restricted
  • tenant deposits/other

• Add a simple control: "No SPV is 'green' for close until bank rec is complete."

---

Layer 2: AR, collections, and arrears integrity

What you reconcile

• Rent billed/earned vs receipts vs arrears movement
• Arrears aging (0-30, 31-60, 61-90, 90+)

Why it matters
This is where NOI turns into a cash issue.

Common mistakes

• Occupancy looks stable, but cash receipts drift (and nobody notices until late)
• Arrears reported as a single number (no aging = no insight)
• Timing mismatches between property manager reports and finance cutoffs

Best-practice routine

• Monthly "triangle check" per asset/SPV:

  • opening arrears
  • billed
  • cash received
  • closing arrears

• Track collections rate and arrears aging as default KPIs

• Require commentary when:

  • collections drops below a threshold, or
  • 90+ arrears grows for 2 consecutive periods, or
  • tenant concentration drives most of the risk

---

Layer 3: AP, accruals, and cutoffs

What you reconcile

• AP subledger / invoice listings vs control accounts
• Accruals for predictable costs (utilities, services, professional fees)
• Prepayments where relevant (insurance, annual contracts)

Why it matters
Cutoff discipline prevents the "NOI yo-yo" where performance swings due to timing rather than reality.

Common mistakes

• Treating accruals as optional ("we will catch it next month")
• Recurring costs posted late across different SPVs -> portfolio volatility
• Insurance or annual invoices creating misleading month-to-month noise

Best-practice routine

• Standard cutoff rules with materiality thresholds:

  • what must be accrued,
  • what can roll forward,
  • and what needs explicit disclosure if missing

• A short list of "always-accrue" categories (per asset class)

• A simple "late invoice tracker" so surprises shrink over time

---

Layer 4: Intercompany and structure consistency

What you reconcile

• Due to/from balances between SPVs
• Intercompany loans, management fees, recharges
• Distribution flows and transfer constraints (trapped cash)

Why it matters
Intercompany is the #1 reason portfolio consolidations do not tie out cleanly.

Common mistakes

• Only one side of an intercompany entry posted (one SPV books it, the other does not)
• Recharges coded into operating expenses -> NOI distortion
• Cash assumed movable when it is restricted by covenants/structure

Best-practice routine

• Maintain an intercompany register:

  • counterparty SPV, balance, ageing, reason, expected settlement date

• Use a "two-sided rule":

  • No intercompany item is green until both SPVs reflect it consistently

• Consider isolating intercompany lines in reporting until eliminations are reliable (better to be transparent than pretend)

---

Layer 5: Mapping and KPI definition integrity

What you reconcile

• SPV trial balances mapped into standard reporting lines
• KPI definitions: NOI, capex vs opex, one-offs, occupancy, net debt, LTV basis

Why it matters
Even perfectly reconciled SPVs can produce misleading portfolio reporting if classifications differ.

Common mistakes

• Refurb invoices sometimes in OpEx, sometimes in CapEx
• "Other" buckets grow and become a dumping ground
• Mapping changes silently -> trends break and nobody trusts variances
• NOI definition drifts between internal and investor packs without a bridge

Best-practice routine

• Standard chart + mapping layer so SPVs roll up consistently

• Mapping change control:

  • who changed what, when, why

• "Other bucket" control:

  • track it as % of total costs and aim to reduce it over time

• KPI dictionary pinned to the close pack:

  • one page, explicit definitions, stable month-to-month

---

A separate check: source-ledger reports versus saved portfolio reports

Completing a bank reconciliation and checking a reporting import answer different questions. The first checks the ledger against supporting evidence; the second checks that you are reviewing the right saved report, period and classifications. Keep both checks in your close routine.

For a portfolio using Clarity Ledger:

1. Identify the company and report. Confirm that the connection is eligible: Xero and QuickBooks Online support GBP, EUR or USD base/home currencies; FreeAgent support is for UK GBP limited companies. For QuickBooks Online, compare the imported accrual report with the same dates, basis and home currency in the provider.
2. Check the saved-data date. After a ledger correction, request a new sync and wait for it to succeed before relying on the revised figures. Opening a report does not import subsequent ledger changes. Record the report period and latest successful sync alongside your review.
3. Compare Source first, then Standard. Check the Source report against the same company and period in its provider. Then review account classifications and mapping gaps in Standard reports. The SPV account-mapping guide explains the mapping review; changing a reporting mapping does not post a ledger adjustment.
4. Explain differences before combining totals. Check dates, accounting basis, account coverage and mapping choices. For EUR or USD companies presented in GBP, review exchange-rate coverage as a separate step. A translated GBP total should not be compared directly with an untranslated native-currency amount.

Keep bank-reconciliation evidence, journals and intercompany decisions in the source ledger or your working papers. Clarity Ledger provides saved P&L, balance sheet and trial balance reports; it does not replace those close controls. Use the portfolio consistency checklist to carry the same dates, definitions and currency choices across companies.

---

The reconciliation cadence that scales

A "no heroics" routine is not something you do once a month. It is a cadence.

Weekly (30-60 minutes total, once established)

Focus: liquidity and drift

• Refresh bank balances (spot missing feeds or anomalies)
• Review collections rate and arrears aging
• Update capex commitments due in the next 2-4 weeks
• Flag any SPV projected to breach a minimum cash buffer

Output: a short "exceptions list" with owners and due dates.

---

Pre-close (days -5 to 0)

Focus: reduce month-end surprises

• Confirm bank feeds and rec readiness (all accounts)
• Capture known late invoices and planned accruals
• Update capex commitments and timing
• Check mapping changes made during the month

Output: a "known issues going into close" log.

---

Month-end (days 1 to 7)

Focus: lock the building blocks

• Bank recs complete per SPV
• AR/AP controls tie out
• Intercompany balances matched
• Trial balance mapped into standard reporting
• Portfolio consolidation checks pass (more on this below)

Output: SPVs sign-off waves (green/amber/red) plus a consolidated portfolio view.

---

Portfolio-level reconciliation checks that prevent embarrassment

Once SPVs are closed, do these portfolio checks before publishing packs:

1) Completeness check

• Which SPVs are included?
• Which are estimated?
• Which are missing?

A simple "close coverage %" metric builds trust immediately.

2) Cash tie-out

• Portfolio cash should equal the sum of reconciled bank balances (by SPV and account)
• Split unrestricted vs restricted in the same view

3) Intercompany sanity check

• Intercompany net position should be near zero after eliminations (if you eliminate)
• If not eliminating yet, intercompany should be clearly isolated-not buried in OpEx

4) Variance outlier check

Use thresholds to avoid line-by-line review:

• Flag any category moving >GBP X or >Y%
• Flag any SPV with unusual changes in "Other" or unmapped lines

5) Definition consistency check

• NOI definition unchanged?
• LTV valuation date clearly labeled?
• Capex vs opex rules applied consistently?

---

The single tool that makes this routine stick: a reconciliation status matrix

At scale, you need visibility. A simple matrix works:

Columns (checks):

• Bank rec complete
• AR/collections checked
• AP/accruals checked
• Intercompany matched
• Mapping complete / "Other" within tolerance
• Ready for consolidation

Rows:

• One per SPV

Statuses:

• Green: complete
• Amber: complete with known exception (documented)
• Red: incomplete / not publishable

This turns reconciliation from "tribal knowledge" into a controlled workflow.

---

What changes when you move from spreadsheets to a portfolio layer

If your roll-up process is spreadsheet-driven, reconciliation will always be partly manual. If you build a portfolio layer-multi-entity consolidation plus standardised mappings-then:

• reconciliation checks can be run repeatedly,
• drill-down from dashboard -> SPV -> mapped line -> account becomes possible,
• exceptions can be flagged automatically (missing SPVs, stale data, outliers),
• and month-end stops being a data assembly marathon.

That is when "no heroics" becomes realistic.

---

Common failure modes (and simple fixes)

Failure mode: "We reconcile cash, but portfolio cash still feels wrong."
Fix: reconcile all bank accounts and split restricted cash explicitly.

Failure mode: "Intercompany makes consolidation impossible."
Fix: treat intercompany as its own workflow with a register and two-sided posting rule.

Failure mode: "NOI trends are noisy and hard to explain."
Fix: standardise capex vs opex and one-off tagging through definitions + mappings.

Failure mode: "We only discover issues at month-end."
Fix: weekly exception cadence + pre-close issue log.

Failure mode: "The dashboard is beautiful but untrusted."
Fix: publish completeness, definitions, and tie-outs on the dashboard itself.

---

Closing thought: consistency beats intensity

Finance teams should not have to "save month-end" every month.

A reconciliation routine-cash, AR/AP, intercompany, mappings, and a handful of portfolio tie-outs-keeps SPV data consistent without heroics. And once that foundation is in place, everything improves:

• close becomes faster,
• reporting becomes more trusted,
• forecasting and scenario testing become more reliable,
• and AI commentary becomes safer because it is grounded in consistent inputs and definitions.