Turning a cost-allocation spreadsheet into a system of record.

The situation

Every month, a commercial property manager takes the utility bills for each building — electricity, water, heating, cleaning, waste, security — and divides them across the tenants. For years that division lived in a spreadsheet that exactly one person could safely operate. The spreadsheet was never wrong in a way anyone could see, which is the most dangerous kind of right.

The rules are genuinely hard. Some costs split by leased square metres. Some follow sub-meters, whose readings must be subtracted from a parent meter before the remainder is shared. Tenants have contract terms — this one doesn't pay for heating, that one pays a fixed service fee, another pays a percentage. Buildings have their own quirks and standing allowances. And when a tenant moves out on the 20th and another moves in on the 21st, the month splits into periods, and every shared cost has to be re-pooled against whoever was actually present in each period.

The output is not a report. It's sales invoices in the accounting system. Every cent has to reconcile.

The approach

We built the monthly close as a pipeline. Supplier invoices arrive from the e-invoicing operator per building and accounting month. Each invoice line lands in a calculator grid where the allocation runs — and where the accountants can see every intermediate number and override it when reality demands. When the month balances, per-tenant invoices are exported into the accounting ERP, and the invoice numbers it assigns are captured back.

The tenant rules we deliberately did not abstract into clever code. They're data — rows an administrator can edit, with a small vocabulary of verbs — because the rules change whenever a contract does, and a rule change should not require a deployment.

// A tenant's contract terms, as data — not code.
{ account: 'heating',   action: 'doesnt_pay' }
{ account: 'cleaning',  action: 'fixed',      amount: 120.00 }
{ account: 'security',  action: 'percentage', value:  12.5 }

Trust was earned by parity. The system has a rendering mode that reproduces the original spreadsheet's exact layout, column by column, so the accountants could run both side by side and verify the new numbers against the old ones, month after month, to the cent — before anything replaced anything.

The result

The monthly close that used to depend on one person is now a process anyone on the accounting team can run: import, review, export. The intermediate numbers are visible instead of buried in cell formulas, and the audit trail runs from the supplier's invoice line to the tenant's.

When the portfolio changes — a new tenant, a new sub-meter, a building with its own way of doing things — it's handled as configuration and review, not spreadsheet surgery. We still maintain the system, and the rules keep evolving with the buildings.