How to automate the reports you send every month

A monthly report gets automated in this order: first you decide what decision each figure drives and remove everything that drives none; then you find where each surviving number comes from; then you write the exact definition of each indicator; and only then do you build the calculation in one place and schedule the send for the 1st. Skipping the first three steps is what turns an automation into an automated report nobody believes.

That's the failure I see most. Someone asks to "automate the report" and it turns out the report runs to twenty-eight pages of which three get looked at, and that "monthly sales" means one thing to the board and another to accounts. Automating that as it stands is industrialising a problem.

Why start by removing rather than by programming?

Because every table you leave in is maintenance work forever. An automated report isn't free: each chart depends on a source that can change format, on a definition someone can dispute, and on a calculation that needs revisiting when the business changes.

The exercise takes an hour. Take the last report you sent, sit next to each table and answer: if this figure doubled, what would we do differently? If the answer is "nothing, it's just good to know", out it goes. In the reports I've reviewed, between half and two thirds of the content doesn't survive that question.

What's left is short and quick to automate. And more importantly, somebody reads it.

How do you automate it, step by step?

1. Write down what decision each figure drives. Next to every table and chart. Anything driving no decision goes out. This usually cuts the report in half.
2. Find the real source of each number. ERP table, CSV someone exports, sheet filled in by hand. What comes out of a person's head has to become readable data first.
3. Fix each indicator's definition in writing. "Monthly sales": with or without VAT? By invoice date or payment date? With returns? Without that line, the report gives a different number and nobody trusts it.
4. Build the calculation in one place. One query or script that reads the sources and produces the final table. Two places calculating the same thing end up disagreeing.
5. Schedule the generation and the send. On the 1st, PDF generated and sent to whoever needs it. And the same content on a dashboard anyone can check any day.
6. Add failure alerts and a quarterly review. If a source fails or a figure jumps, warn before sending. Every three months, ask what people actually look at.

Step three is the one that causes arguments, which is why it gets skipped. It's worth having them: it almost always turns out two departments have spent years measuring the same thing differently.

What tools do you need?

Fewer than it seems. It depends on where the data comes from:

Notice the choice isn't about brands. It's about whether you have read access to your own data, which is the question worth asking your ERP vendor before promising anything.

PDF or live dashboard?

Both, with the same calculation behind them. The dashboard is for looking any day and drilling in when something stands out. The PDF earns its place because it's what gets opened on a phone, printed for a meeting and filed as the month's snapshot that nobody can recalculate afterwards.

The mistake is having them on different sources. If the dashboard reads the database and the PDF is built by hand from a spreadsheet, within three months they disagree and confidence in both is gone. One calculation, two ways of showing it.

How much time does it really save?

The sum is done with a clock, not a feeling. A monthly report of normal complexity, built by hand, takes someone between one and three days: export, reconcile, paste into the template, review, fix what doesn't add up. Call it sixteen hours. Over a year that's nearly two hundred hours, a long month of one person's work spent copying figures.

Automated, the month comes down to reviewing the draft and writing the commentary: one or two hours. The first version usually costs one or two weeks of work when the data is accessible, and considerably more when it has to be gathered first. Even so, the payback is counted in months, not years.

There's a saving that doesn't show in that sum and it's the one people notice most: the report stops arriving on the 12th. It arrives on the 1st, and decisions get made on a month people still remember.

When is it NOT worth automating?

• When nobody reads it. Before automating, stop sending it for a month and see who asks. If nobody asks, you've just saved yourself the whole project.
• When it changes shape every month. If each meeting wants a different cut, what you need is a dashboard where everyone filters, not a fixed report.
• When the value is in the commentary. If what matters is the reading given by the person who signs it, automate the tables and charts and leave the analysis out. No automation does that part well.
• When the data isn't trustworthy. Automating over dirty data spreads errors faster and in better typography. Clean the source first.

What mistakes do people usually make?

• Copying the old report as it is. You automate the surplus too, and with it the maintenance of the surplus.
• Not fixing the definitions. In month one someone will say "this figure doesn't match", and with no written definition the argument never closes.
• Sending without checking. If a source fails, the report goes out full of zeros and gets sent anyway. A pre-send alert costs an afternoon and saves you emailing a blank report to a board.
• Forgetting the history. Keep every generated report exactly as it went out. When someone asks in a year why March showed a different figure, that copy is the answer.
• Letting it grow unreviewed. Automated reports put on weight: every request adds a table and none get removed. A ten-minute quarterly review keeps it in check.

Frequently asked questions

How long does it take to automate a monthly report?

If the data already lives in a database or an accessible ERP, one to two weeks for the first report, scheduled send included. If you have to start by gathering data from loose spreadsheets and agreeing what each indicator means, that part usually takes longer than the programming.

Is a PDF report or a live dashboard better?

Both, with the same data source behind them. The dashboard is for checking any day and drilling into detail; the PDF is what people open on their phone, take into a meeting and keep filed as the month's snapshot. What matters is that both come out of the same calculation, not two different spreadsheets.

What if the data lives in several different tools?

That's the normal case: invoicing in one place, hours in another, sales in the CRM. It's solved with a prior step that brings each source into a common store, nightly or weekly, and the report is calculated there. It's more work than reading a single database, but you do it once and it then serves any future report.

When is automating a report not worth it?

When nobody reads it, when it changes shape every month because each meeting asks for something different, or when the real value is the commentary written by the person who signs it. In that last case, automate the tables and charts and leave the analysis to the person: that's the part no automation does well.