Submeter billing without the spreadsheet

Manual submeter billing fails in predictable ways: a missed read becomes an estimate, an estimate becomes a dispute, and the dispute costs more staff time than the recovery was worth. The spreadsheet is rarely the root cause, but it is where every weakness in the process becomes permanent — the formula nobody can explain, the tenant rate that was updated in one tab and not the other, the month someone was on holiday.

This is a walkthrough of what an automated billing process actually consists of, and where the manual version tends to break.

Why the spreadsheet survives longer than it should

It survives because it works, right up until it doesn't. One property, six tenants, a monthly walk with a clipboard and an hour in Excel is genuinely manageable. The failure comes with scale and with time: a second building, a staff change, a lease with an escalator, a tenant who finally asks how the common-area share was calculated.

At that point the spreadsheet's real problem shows up. It records results, not method. Anyone auditing it a year later can see the number but cannot reconstruct how it was produced, which means every challenge becomes an argument about memory rather than data.

The automation checklist

A defensible billing process needs four things working together.

• Automated reads with gap detection, so estimates are rare and clearly flagged as estimates when they happen.
• Rate configuration that mirrors the lease, including tiered rates, escalators, minimums and any admin fee the lease permits.
• Common-area allocation rules documented in the system, not in someone's head — square footage, occupancy, submetered deduction, or whichever method the lease specifies.
• An audit trail from invoice line to raw interval data, so any figure can be opened down to the measurement that produced it.

Miss any of the four and the process still runs, but it stops being defensible.

Reads and gaps

Automated collection removes the walk, but the real gain is gap detection. A meter that stops reporting on the 14th is invisible in a manual process until month-end, when someone estimates around it and moves on. An automated system flags the gap the day it happens, while there is still time to fix the device and bill on real data.

When an estimate is genuinely unavoidable, it should be labelled as one on the statement, with the method stated. Tenants tolerate estimates far better than they tolerate discovering an estimate they were not told about.

Rates that match the lease

Most billing disputes are not measurement disputes. The meter is usually right; the rate applied to it is wrong, out of date, or subtly different from what the lease says. Rate logic belongs in the system as configuration — effective-dated, so a mid-year change does not retroactively rewrite earlier invoices — rather than as a hard-coded multiplier in a workbook.

Effective dating matters more than it sounds. Without it, correcting a rate quietly changes the history, and you lose the ability to reproduce an invoice you already sent.

Allocations, written down

Common-area recovery is where most of the ambiguity lives. Whatever the method, it should be visible: the basis, the total pool, the tenant's share and the resulting amount, all on the statement. A tenant who can see the arithmetic rarely contests it. A tenant who receives a single line labelled "CAM electricity" almost always does.

The audit trail

The test is simple: pick any line on any invoice from any month and click through to the interval data behind it. If that path exists, the billing process survives lease audits, ownership changes and tenant challenges. If it does not, every one of those events becomes a research project.

Transparency shortens disputes

When tenants can see their own usage curve, most questions answer themselves before they become emails. A tenant portal is not a courtesy feature; it is a deflection mechanism. The tenant who sees their consumption spike on the same three nights they ran extended production does not send the "this bill looks wrong" message at all.

Transparency also changes behaviour, which is the second-order benefit. Tenants with visibility into their own usage reduce it, typically in the range that makes the whole program easier to justify to ownership — not because anyone asked them to, but because the cost finally has their name on it.

What good looks like operationally

A mature process at month-end looks like this:

1. Reads land automatically; exceptions are already resolved because they were flagged mid-cycle.
2. Rates and allocations apply from configuration, unchanged since the lease was set up.
3. Draft statements generate for review, with any estimated or unusual line highlighted for a human to check.
4. A person approves — the review is the human step, not the arithmetic.
5. Statements distribute, and the tenant portal shows the same data the invoice was built from.

The staff time moves from data entry to exception handling, which is the only part that ever needed judgement.

Getting there from a spreadsheet

Do not attempt to convert everything at once. Take one property, configure its leases faithfully, and run the automated process in parallel with the spreadsheet for one or two cycles. Differences will appear, and they are informative — they almost always reveal something the spreadsheet was doing that nobody remembered, which is precisely the knowledge worth capturing before it leaves the building with the person who holds it.

Once the parallel run reconciles, retire the workbook and move to the next property. The goal is not a faster spreadsheet. It is a billing process that can explain itself without you.