Reimagining month end from first principles
Month-end reporting for a private investment group depended on a dozen accounting systems and one large spreadsheet. We replaced it with a system that refreshes itself every night and shows where every number comes from.
- Services
- Advisory · Data platform build
- Portfolio
- Nine figures across managed funds, direct property, a rural operation and cash
- Structure
- More than a dozen entities, trusts and companies, plus a charitable foundation
Three sources that did not reconcile
Month-end reporting for the directors was assembled by the CFO by hand. Accounting data came from a dozen separate accounting-system organisations. A management-reporting add-on consolidated them. One large spreadsheet held everything the accounting systems could not: asset classification, property valuations and the investment policy targets.
The three sources did not reconcile, and there was no way to see where they disagreed. The spreadsheet was the only place classification and unlisted valuations lived, so it was also the single point of failure. It was undocumented.
Figures went stale without anyone noticing. The workbook's performance columns had been calculated once and showed the same numbers for months. The reporting answered what do we own
but not how are we doing
. And a charitable fund that must by law be reported separately, and never combined with family assets, was kept separate only by people remembering to.
Put each rule in one place
We started with a short consulting engagement and a working session with the CFO, then built.
The principle was simple: anything the spreadsheet did by habit, the system should do by design. The carry-forward valuation rule is implemented once, in the database, so no report can apply it differently from another. The charitable fund's separation is enforced by a database constraint, and the application has no view that combines it with family assets. If the combined figure cannot be requested, it cannot be produced by mistake.
A system that refreshes itself and shows its sources
Every accounting organisation is connected by API. The domain is modelled as entities, funds, holdings and valuations. Allocation is measured against the investment policy, with drift tracked across month ends. Every figure shows where it came from.
- Sync
- Incremental. The nightly refresh fetches only what changed since the last run.
- Valuation
- The carry-forward rule is implemented once, in the database, and applied the same way everywhere.
- Separation
- The charitable fund is isolated by a database constraint. There is no combined view.
- Allocation
- Holdings are measured against the investment policy targets, with drift tracked over time.
- Provenance
- Each amount shows its source (ledger, spreadsheet or PDF valuation), when it was struck and how old it is.
Month end is now automatic
A nightly job refreshes every organisation in well under a minute, using a fraction of a percent of the available API allowance. A full reload would take about forty times the calls and half an hour.
The system also surfaced three problems the spreadsheet had hidden.
A negative seven-figure position that the workbook said to ignore, but which the reporting was counting.
An apparent concentration of more than half the portfolio under an unknown counterparty. It was a classification error.
A receivable carried unchanged for a year. The ledger showed it had been settled and reinvested.
The client now has a system rather than a report. It is deployed, access-controlled, refreshes itself, and is theirs.
Have a problem shaped like this one?
Fixed-scope discovery to start.
Strata: Max van den Oetelaar — Unsplash