Seven steps between your accounting and your first metric

Everyone who does this knows it is work. Almost no one has ever written it down. That is a shame, because in the list sits the answer to why almost no subscription business keeps this up monthly, and why the file that comes out rarely matches last month's.

This is the route, step by step, with where it goes wrong at each step.

1. Export

You pull invoices or ledger movements over the period out of Exact Online, as a file or via a standard report.

Where it goes wrong. The first choice is immediately the most important and is rarely made deliberately: do you export invoices or subscription lines? Invoices contain everything, including one-off work you have to strip out again later. Subscription lines are cleaner but miss what was invoiced outside subscription management, and at many businesses that is precisely the largest part.

Whatever you choose: choose the same thing every month. Switching means your series is no longer comparable.

2. Clean up

Merge duplicate customers, tie credit notes to the right month, strip out internal and test invoices.

Where it goes wrong. Credit notes. A credit note in May for an invoice from March belongs to March, not May. Book it in May and you get an artificial dip in May while March was too high all along. Do this by hand and it goes right for the first few months and then no longer.

Duplicate customers are the second stumbling block. The same customer under two numbers counts as two customers, which drops your average revenue per customer without anything having happened. And if one of them cancels, you record churn that is not there.

3. Classify

Determine per line what is recurring and what is one-off: implementation, extra work, invoiced hours, hardware, training.

Where it goes wrong. This is the step that causes the most pollution, because one-off work and subscription often sit on the same invoice and sometimes on the same line. An implementation invoice of €4,000 counted as recurring revenue puts your MRR €4,000 too high that month and produces a churn of €4,000 the next month that never took place.

The structural fix is not in your spreadsheet but in Exact: a separate item code or ledger account for subscription revenue. One hour to set up, earned back every month afterwards.

4. Normalise

Convert annual, quarterly and monthly contracts back to one comparable monthly value, prorating on a mid-term start or stop.

Where it goes wrong. Not in the arithmetic, dividing by twelve is within everyone's reach. It goes wrong with contracts that change halfway, with customers switching from annual to monthly, and with the question of whether you prorate on a daily basis or only from the next full month. Both are defensible; using them interchangeably is not. The full method is covered in this article.

5. Compare

Lay this month next to last month and determine per customer what has happened: new, existing, expanded, contracted, left.

Where it goes wrong. This is the step that is the most work and gets the least attention. You need last month's customer lines, and those are not in your export, they are in last month's file. From here on your reporting depends on a chain of files, where one missing month makes the gap permanent.

You cannot reconstruct March if February's file is gone. Not because the data is missing, but because the snapshot of who was a customer in February is kept nowhere.

6. Calculate

Derive the build-up of your recurring revenue, plus churn, retention and average revenue per customer.

Where it goes wrong. Edge cases, and there are more of them than you think. A customer who leaves and comes back two months later: new or existing? A customer who contracts and expands in the same month: two items or the net? A group with four operating companies: one customer or four? A price indexation of 3% across the whole portfolio: is that expansion?

Every answer is defensible. No answer is defensible if it differs from month to month, and that is what happens the moment someone else takes over the file.

7. Reconcile and distribute

Reconcile against the ledger revenue, explain deviations, refresh charts, send the file round.

Where it goes wrong. At the reconciliation, and that usually takes the longest. Your MRR does not reconcile with your revenue, and it should not. An annual contract invoiced in January sits twelve months in your MRR and once in your revenue. A customer who cancels in March at the end of the term leaves your MRR in March and your revenue in December.

Anyone who does not know that difference spends half a day hunting an error that is not there. Anyone who does know it has to explain it again every month to whoever receives the report.

What it really costs

Seven steps, every month, by hand. At two hundred subscriptions that is a morning, at five hundred a day.

But the turnaround time is not the objection. The objection is what those hours crowd out. Someone in your business, usually someone you cannot spare, spends a fixed part of every month reconstructing something the accounting already knows but does not show. That time does not go to customers, not to pricing, not to winning back the customers about to leave. It goes to reporting on exactly those things.

And the file is out of date the moment it is finished. An overview ready on the twelfth describes last month. All that work does not remove the blind spot. It shifts it by a month.

What you can do about it, even without software

Three things you can do today that make the rest easier:

• Separate subscription revenue in Exact. A dedicated item code or ledger account largely removes step 3.
• Fix your definition on one page. The edge cases from step 6, with the answer per case. That is the only document that stops you having two numbers a year from now that are both called "the MRR".
• Keep the customer lines separately each month. One line per customer per month, with the normalised amount. That is the snapshot from step 5, and without that series your history cannot be reconstructed later.

Where this comes from

These seven steps are part four of the whitepaper The blind spot in Exact Online, where they sit in a larger story: why your accounting records every movement but shows none of them, and which four questions tell you within five minutes whether your business has that problem. And if you are looking for the routes around it, they are laid side by side here.