How to automate your weekly report: from manual Excel to a report that builds itself

Friday afternoon. The controller downloads an export from the ERP, then from accounting, the online shop and the CRM, pastes them into Excel, links them with VLOOKUP, fixes whatever does not match and writes three paragraphs for management. Four to six hours every week — and when they are off sick, there is no report. If you want to automate your weekly report, the good news is that most of that work can go. The less good news: the hardest part is not technical.

Reporting automation has four steps, in this order: write down how the report is calculated, pull data from the sources into one place automatically, let the report build itself, and only then add AI for the commentary, alerts and questions about the data. You can usually manage the first three without AI. The fourth is where AI genuinely saves management time, not just the controller’s.

Before you automate your weekly report: where the time goes

Before automating anything, break down a single week. For a typical management report it looks roughly like this:

The table shows what matters: the biggest chunk of time goes on mechanical work that needs no AI at all. AI comes in with the commentary — and with something the table does not show: management’s follow-up questions, which today wait until next week.

Step 1: write down how the report is calculated

It sounds trivial, but this is where most projects stall longest. Every spreadsheet that has been maintained by hand for years hides rules: credit notes are deducted from sales, but only from a certain date; the northern branch also includes online orders for its region; customer X counts as wholesale even though the ERP lists them as retail.

These rules often live only in one person’s head. Until you write them down, the automated report will produce different numbers from the manual one and nobody will trust it. The method is simple: sit down with whoever builds the report, go through every column and ask “where does this come from and what happens to it?”. The result is a page or two of rules. That document alone is worth having — the report stops depending on one person.

Step 2: data from the sources, automatically, in one place

Instead of manual downloads, the data is pulled every night from the sources into one database (a data warehouse). The options, from best to worst:

• API — the ERP, online shop or CRM offers an interface. The most reliable and the cheapest to maintain.
• Direct database access — read-only, often the route with older ERP systems.
• Scheduled export — the system drops a file into a folder every night and it is processed from there. Not elegant, but it works.

This is also where codes get reconciled: the same customer has a different number in the ERP and the CRM, a product has one code in the online shop and another in the warehouse. Mapping tables handle that — and this is the stage where you first see how much inconsistent data the company really has.

Step 3: the report builds itself

Once the data is in one place and the rules are written down, the report itself is the easiest part. Two common routes:

• A BI tool — Power BI, Looker Studio or Metabase. The dashboard refreshes itself and management can filter by branch and period.
• A generated Excel file or PDF — if management is used to a particular table, there is no need to retrain them. The file is created at seven on Monday morning and arrives by email.

For the first few weeks, run the automated report alongside the manual one. Do not switch off the manual report until the figures match to the euro.

Step 4: where AI helps with reporting automation

This is where the most is promised today and where the most care is needed. AI has four honest jobs in reporting:

1. Commentary on the figures. From the finished results it writes what changed against last week and the plan, where the biggest variances are and what to look at. The controller reviews and edits the commentary instead of writing it from scratch.
2. Anomaly alerts. A branch’s sales drop by a third, credit notes spike, a customer stops ordering — the alert arrives straight away, not on Friday.
3. Unstructured sources. Sales notes in the CRM, customer emails or PDF statements do not fit in a table. AI can pull out what belongs with the numbers (“three complaints about the same delivery”).
4. Plain-language questions. The managing director asks in Teams “why did the margin drop in the east?” and gets an answer from the same data as the report. Technically this means connecting the model to the database, often via MCP and with read-only access.

One rule does not bend: the database calculates the numbers, not the language model. The model receives finished results and comments on them. Every number it uses in the commentary is automatically checked against the source. A model that does its own sums will get one wrong sooner or later — and in a management report, one invented figure is worse than no commentary at all.

When a BI tool is enough and when you need AI

If you are in the first two rows, you do not need AI — do not let anyone sell it to you. A well-configured export and Power BI handle most of the reporting in an ordinary company.

Typical obstacles

• Inconsistent data. Automation does not fix inconsistencies, it exposes them — often out loud for the first time. Expect part of the project to be tidying up codes.
• No API. An older ERP has no interface. There are ways round it (database access, scheduled exports), but they take more work and break more easily.
• “The one person who knows how it is calculated.” If they have no time for step 1, the project stalls. Give them a few hours for it — it is the best-invested time in the whole project.
• A report nobody reads. Writing down the rules sometimes reveals that half the columns are of no use to anyone. Do not automate them — drop them.

When it is not worth it

• The report is produced once a month in an hour. Automation will not pay back.
• The company is switching ERP in six months. Wait — you would build the connection twice.
• The report changes every month depending on what management is interested in. Settle what you want to track first.

What it costs

Indicatively, excluding VAT, for a small or medium company: analysis of reports and sources €900–1,800, an automated report from 2–3 sources €3,000–6,000, AI commentary, alerts and questions on the data another €4,000–10,000, operations from €290 a month. We explain why the prices vary in how much an AI solution costs.

With a single weekly report that takes five hours, you save around 20 hours a month — which pays back a pure BI solution sooner than an AI layer. AI commentary and questions on the data make economic sense once there are more reports (a weekly one for management, monthly ones per branch, sales reports per rep) that together take around ten hours a week. We describe a model scenario for such a company on the reporting automation page.

Summary

• Rules first, technology second. Without a written-down calculation, nobody will accept the automated report.
• Data into one place automatically — via API, database or scheduled export.
• A BI tool or a generated Excel file builds the report. No AI needed for that.
• Add AI for the commentary, anomalies, unstructured sources and questions — and never let it calculate the numbers.

What could you automate in your company? If you can say how many hours a week someone spends putting reports together, you have a number to start from. We are happy to work it through with you — and if Power BI is enough, we will tell you.