How to automate reports, and let AI draft the monthly summary

How to automate reports, in short: trace every number in the report you send today back to its source, connect those sources to one model that refreshes on a schedule, and rebuild the layout once. Then add data checks that hold the send when something looks wrong. AI comes last: it drafts the written summary from figures that were already calculated, and a person reads and approves it before anyone else sees it.

Start with the report you already send

Most guides on automated reporting start with tools. I'd start with last month's pack, the actual file that went to the directors, because it already tells you what your readers expect, from which KPIs appear to the order they come in and what each is compared with. Automating a report that no reader asked for is an easy way to spend a month and change nothing.

Open it next to a blank spreadsheet and write down three things for every figure in it.

• Where it comes from, whether that's the accounting package, the CRM, a warehouse export or, very often, a spreadsheet someone keeps by hand.
• How it is calculated. Is gross margin before or after freight? Is revenue booked on invoice date or delivery date? Write the definition as one sentence.
• Who touches it before it goes in. A manual adjustment is either a rule you can write down or a judgement call that stays with a person.

This inventory is tedious, and it's also where most of the value sits. When a report turns out to be hard to automate, the cause is usually not the tooling. It's two people defining the same KPI differently without knowing it, or a number that only one colleague knows how to produce. Better to find that now than in month three.

How to automate reports in five steps

1. Trace each number to its source and write down its definition (the inventory above). Anything you can't trace gets flagged now.
2. Connect the sources to one model on a schedule. That can be Power Query in Excel, a Power BI semantic model, or a small database fed by a scheduled job. What matters is that each KPI is calculated once, in one place.
3. Rebuild the layout once, copying the pack your readers already know. A new layout slows adoption; the old layout with correct numbers arriving on time doesn't.
4. Add data checks before delivery: row counts, missing days, duplicates and a reconciliation to the ledger. If a check fails, the report is held and its owner gets a message.
5. Schedule delivery and run it next to the manual version for one full cycle. Compare every headline figure, and only retire the manual process when the two agree or you know exactly why they don't.

The AI summary comes after step five, once the numbers can be trusted.

Step five is the one people want to skip, and I understand why, because by then the new version looks finished. But a month side by side is what earns the trust of the person who used to build the pack by hand. If they don't trust it, they'll keep their own spreadsheet going in the background, and the company ends up with two versions of the truth instead of one.

Excel, Power BI or a script: where to automate Power BI reports and the rest

The right tool depends on where the report is read and how many sources feed it.

If the pack lives in Excel and comes from two or three exports, Power Query does most of the work: point it at a folder, define the cleaning steps once and refresh. It's the cheapest route, and the Excel automation I build usually starts there. The limit is that someone still has to open the file and press refresh, unless Power Automate or a scheduled script does it for them.

If you want to automate Power BI reports, much of the plumbing already exists. A few details from Microsoft's own documentation shape the design.

• Scheduled refresh runs up to 8 times a day on Power BI Pro and up to 48 times on Premium Per User or a capacity, according to Microsoft Learn on scheduled refresh. There is no monthly option, so a month-end run needs Power Automate or a pipeline.
• Power BI switches a refresh schedule off after four consecutive failures, and pauses scheduled refresh on a model that no one has opened for two months. A pack that is only read once a month can come close to that second rule, although a subscription on the report stops the pause.
• Email subscriptions send a snapshot, or in workspaces on Premium capacity or Premium Per User, the full report as a PDF or PowerPoint attachment of up to 20 pages, per Microsoft's documentation on subscriptions. Subscribing other people needs a Pro or PPU licence, and external recipients only work on a paid capacity.

Licensing is where Power BI delivery plans most often go wrong, so check it before you design the last step. If the readers sit outside your organisation, or you'd rather not pay per user, a small scheduled job that reads the same data and sends an email or a PDF is often simpler. For the wider trade-off, my comparison of Excel and Power BI for financial reporting goes into more detail.

The data checks that make report automation safe

An automated report that sends wrong numbers is worse than a manual one, because a person copying figures at least looks at them on the way. So the checks are where I spend the most time in any report automation, and they're simple ones.

• Did every source refresh today? Compare the latest timestamp per source with the one you expect.
• Is the row count in a normal range compared with recent periods? A sales table with half its usual rows usually means a failed export, not a bad month.
• Are there gaps in the dates, or duplicates on a field that should be unique, such as the invoice number or order ID?
• Does revenue in the model reconcile to the ledger total for the period, within a tolerance you set?
• Did any KPI move further than it plausibly could in a month? A margin that jumps fifteen points is either the story of the year or a mapping error, and someone should look before it goes out.

When a check fails, the pack is held and the owner gets a message naming the check and the source. Holding instead of sending with a warning is what people end up trusting. Most teams that tried automation once and gave up were burned by the opposite: a run that failed without telling anyone, and wrong numbers that reached the board.

Letting AI draft the summary without inventing numbers

The written commentary is usually the last thing done before the deadline, and it's where a language model helps most. Given this month's figures and last month's, a model writes a decent first draft about what moved: which category drove revenue, whether margin fell because of pricing or because the mix shifted toward cheaper products, which KPI crossed its target.

The rule I build on is that the model never calculates anything. The data model computes revenue, margin and the change per category; the AI receives those finished numbers and writes sentences about them. Before anything is sent, a check compares every number in the draft with the data, and a sentence that doesn't match is flagged and held back.

You can see the result in this example monthly pack for a made-up retailer on sample data. On that page the summary is generated in your browser from the sample numbers rather than by a live model, but the layout, the checks and the tone match a real build.

On privacy, send the model aggregated figures only, never customer-level rows. I use EU regions where the provider offers them and API terms that exclude training on your data where that option exists.

Automated financial reporting at month end

Automated financial reporting has a timing problem that sales and marketing reports don't have: the numbers aren't final until the books close, and the close date moves from month to month. A schedule that fires on the third working day will sometimes run on an unfinished ledger, which is exactly the situation the checks above are meant to catch, but it's better not to create it in the first place.

The cleaner trigger is the close itself. When the controller marks the period as closed (a status in the accounting system, a flag in a control table, even a checkbox in a SharePoint list), the pack rebuilds and the draft lands in their inbox. They spend the morning reviewing a draft instead of rebuilding charts, and the directors get the pack on the day the numbers are final.

Two decisions belong at the start. First, which adjustments happen after close, such as accruals or reclassifications, and whether the pack should wait for them. Second, who signs off. For a board pack I'd keep an approval step between the draft and the send even when every check passes.

What goes wrong after launch

Automated reports rarely fail in the first week. They fail in month four, when something upstream changes and no one connects the two events. These are the usual causes.

• A password or access token expires and one source stops refreshing.
• Someone renames a column in an export, or adds a product category the mapping doesn't know.
• A KPI definition changes in a meeting but not in the model.
• The person who approved the commentary leaves, and no one takes over the step.

The checks catch the first two. The last two are organisational, which is why every build I hand over comes with a plain document listing each source, each KPI definition and how to change the recipients. If the definition of gross margin changes, it should change in one place and be written down there. If your report already exists in Power BI and has become slow as it grew, that's worth fixing before you automate around it; this guide on fixing a slow Power BI report covers the usual causes.

How to tell the automation is working

Measure it like any other process. Track the hours spent on the pack before and after (an honest estimate is fine), the number of days between close and the pack reaching its readers, how often a check holds a send, and how many corrections the reviewer makes to the AI draft. If corrections stay high after a few cycles, the inputs or the instructions to the model need work. If the checks never fire at all, are they too loose?

Ask the readers too. A pack that arrives earlier and says what it always said is a good outcome, even if the readers never notice that it changed.

If you'd like this built around your own pack, my automated reporting service starts from the report you send today: send last month's version and I'll reply with a fixed quote. For the numbers that can't wait a month, KPI tracking and alerts sends a message as soon as one crosses a line you set.

Frequently asked questions