How to automate your monthly reports and management pack

Automate monthly management reporting by giving each tool the part it does well: Power Query pulls and cleans the data, Power Automate formats and sends it, Power BI keeps charts live, and AI drafts formulas and commentary. None is built around your business, so someone still stitches them together, checks the numbers and fixes things when sources change.

Read down the last column and the same four gaps keep coming back. None of these tools is custom. None of them knows your business logic. Each one turns into one person's knowledge. And most add another licence. The rest of this guide takes each route in turn, with the clicks, so you can decide which parts to automate yourself.

A monthly management report (often called the management pack, or the monthly MIS report) is the handful of pages the people running the business read to decide what to do next month. In most small and mid-sized firms it holds five things, and they don't all automate equally well:

Every monthly report is also two jobs wearing one deadline: getting the data in shape (downloading exports, knitting files together, fixing types and duplicates) and producing the output (the tables, charts and PDF that leadership expects). Work out which half eats your month-end, because the tools split the same way. For most teams it's the first half, and that's Power Query's job.

Automates: the actuals, the variances and the aged balances. Falls short: the commentary, and any KPI that lives outside the accounts.

Power Query is built into Excel and does the drudge work properly: it records your clean-up once, then reruns it on demand.

1. On the Data tab, choose Get Data and pick the source: From Folder if a new export lands each month, From Text/CSV for a single file, or a direct database connection.
2. Do the tidy-up once in the Power Query editor: remove blank rows, fix data types, split or merge columns, append the monthly files into one table. Every action is recorded as a step.
3. Close & Load the result to a table or PivotTable.
4. Next month, drop the new export in the folder and select Refresh All. Every step reruns against the new data, and the day of copy-paste becomes a click.

Where it strains: a source system renames a column and the query stops with an error, and logic that is genuinely conditional (different rules per customer, reconciling across systems) turns into fighting the tool. For consistent exports in a stable shape, though, this is the highest-value hour you can spend in Excel.

Automates: the data pull and the clean-up. Falls short: someone still has to open the file and refresh it, and fix it when a source changes.

If your firm runs on Microsoft 365, Power Automate can do the formatting and the sending without anyone opening the file. Here's the flow most firms want: on the 1st of the month, tidy the report and send it.

1. Save the workbook in OneDrive for Business or SharePoint. A flow can't reach a file on someone's laptop.
2. In Excel, on the Automate tab, choose Record Actions, do the formatting once (auto-fit columns, number formats, headers) and save the script.
3. In Power Automate, create a Scheduled cloud flow. Set the Recurrence to every 1 month, starting on the 1st at 7am, and set the time zone (it defaults to UTC).
4. Add the Excel Online (Business) action Run script. Use the file picker to choose the workbook, then choose your script.
5. Add Send an email (V2) from Office 365 Outlook with a link to the file, or Post message in a chat or channel to put it in Teams.
6. Select Test, run it manually once, and check the report it produced before you trust the schedule.

The step that isn't in that list is the refresh. Microsoft's own documentation says scripts run from Power Automate can't refresh most data: Refresh All only works there when Power BI is the source, and PivotTable refresh does nothing while still reporting success. So the flow works on data that is already in the workbook. Either someone refreshes Power Query on the desktop first, or the data has to reach the workbook another way.

Licensing: Office Scripts in Power Automate need a Microsoft 365 business or enterprise licence (Business Standard, Business Premium, E3 and E5 all include it). If you can't see the Automate tab in Excel, that's usually why.

Automates: the formatting, the schedule and the email or Teams post. Falls short: the refresh, and whoever built the flow is the only one who can fix it when it stops.

Power BI is the step up when the report should be a live view rather than a monthly file.

1. In Power BI Desktop (free), use Get data to connect the sources: your Excel workbook, accounts exports, a database or a SharePoint list.
2. Model the data: relate the tables, write the measures (margin, variance against budget), and build the report pages.
3. Publish to the Power BI service and set a scheduled refresh. Pro allows up to 8 a day; data held on your own server needs a gateway.
4. Subscribe people to the report so it arrives by email after the refresh.

The cost is per person. Power BI Pro is £10.80 per user a month and Premium Per User £18.50, both paid yearly and before VAT (Microsoft's UK price list, checked October 2026). Sharing a report and setting up subscriptions both need a paid licence, and on a small business setup everyone who views shared reports needs one too. Pro is included in Microsoft 365 E5.

The bigger cost is the person. Someone has to build the model, and change it every time the business changes how it measures things. Weighing that up? See when a bespoke dashboard beats another Power BI subscription, or how to build a business dashboard in Excel first.

Automates: the visuals, the refresh and the delivery. Falls short: a licence per viewer, and a data model one person builds and maintains.

The classic route still works: Developer tab, Record Macro, do the job once, stop recording, save the workbook as .xlsm and put the macro on a button. For local, click-heavy desktop jobs (page setup, printing to PDF, sending a sheet through Outlook) it remains the quickest answer. But recorded macros hard-code cell ranges and break silently when the layout shifts, and real VBA is a program, so the report now depends on the one person who can read it.

If the source data lives in a proper Excel table, modern array formulas can build the report sheet with no refresh step at all. It recalculates the moment new rows land:

=TAKE(SORT(FILTER(Orders, Orders[Month]=$B$1), 3, -1), 10)

FILTER, SORT, UNIQUE and TAKE replace the manual sort-and-copy that used to be half the job. The limits are human rather than technical: one paste over a spill range breaks the sheet, and by the time the formulas are nested three deep, you've rebuilt the one-person problem in a different notation.

Automates: repetitive desktop clicks and the report tables. Falls short: both are fragile, and both end up understood by one person.

AI assistants are now genuinely useful at month-end, as long as you know which part of the job they're doing. As of October 2026:

• Copilot in Excel writes and fixes formulas, summarises a table, and builds charts and PivotTables from a plain-English request. Its Agent Mode, generally available since January 2026, can build or edit a whole sheet step by step. The full in-app Copilot comes with a paid Microsoft 365 Copilot licence per user, or a business plan bundled with it; the free Copilot Chat does less inside Excel, and Microsoft has been changing that line, so check your plan.
• Claude can take a month's export and build a dashboard or a set of charts from it. The Claude for Excel add-in (on paid Claude plans) explains how a number was calculated, with links to the cells, and traces errors to their source. With Xero's connector, Claude can answer questions about your live profit, cash, invoices and customers; it's read-only, and needs a Claude account and an active Xero subscription.
• ChatGPT can analyse an uploaded spreadsheet and chart it, and its apps for SharePoint and Google Drive let it read files where they live. What's available depends on your ChatGPT plan.

We've written up how to set these up safely, which plan you need, and what to sort out first, in our free guide: connect AI to your business.

The honest limits. AI answers from what it can see, not from what your business means: it doesn't know that one customer's invoices are always paid late by agreement, or that a job isn't really profitable until the retention comes back. Ask the same question next month and the working can differ, so every number needs checking every month. Use business plans for business data, and only point an AI tool at files you trust. Anthropic's own documentation warns that a spreadsheet from outside can carry hidden instructions.

Automates: formulas, first drafts, one-off charts and quick answers. Falls short: the same report, the same way, every month, with judgement about what the numbers mean.

No-code connectors will move data into a sheet on a schedule, and for a throwaway report they're fine. For a report your business runs on, know what you're renting: a chain of steps that breaks quietly when a source changes, support that is a ticket queue, and another subscription that grows with use.

Automates: moving data between apps. Falls short: the logic is spread across steps nobody else can follow, and failures are quiet.

If your firm runs on Google rather than Microsoft, the same pattern applies with different names. A connector add-on pulls accounts or CRM data into Google Sheets. Looker Studio, which is free, turns the sheet into a dashboard and can email a PDF of it on a monthly schedule. Apps Script fills the gaps: a time-driven trigger with a month timer can rebuild a summary tab and email it, running under the account of whoever set it up.

Automates: the pull, the dashboard and the monthly email. Falls short: connector add-ons are another subscription, and a script tied to one person's account stops when they leave.

Your accounts system already produces most of the finance pages: P&L, balance sheet, aged debtors and creditors, and budget against actual. Xero now has its own analytics, built on Syft, which it bought in 2024, and reporting add-ons like Fathom turn the accounts into a formatted management pack with KPIs and commentary.

That's fine until the KPIs that matter live somewhere else. Margin by job, jobs won against quoted, hours against estimate: those sit in your job system, your quoting spreadsheet or your timesheets, and the accounts system can't see them.

Automates: the finance pages, straight from the ledger. Falls short: anything that isn't in the accounts.

There's a cost quieter and worse than the wasted day: the report that matters most lives in a file only one person really understands. The formulas are load-bearing, undocumented, and one wrong paste away from reporting the wrong number to your board. When that person is off, the report doesn't happen.

Every method above automates the work without fixing this. The knowledge just moves into query steps, a flow, a data model or a prompt, and stays with the same person. Automation changes who does the typing; it doesn't change who understands the report.

Each tool in this guide automates a slice. What none of them covers is the gap between the slices, and that gap is your business logic: how you price, which jobs matter, what counts as a problem worth flagging, which numbers you trust from which system. That's the part someone still does by hand at every month-end, and it's why the report still depends on them.

A fair test, either way:

• Keep stitching when the sources are stable, the logic is simple, and the output has to stay a spreadsheet. Power Query plus a flow will do.
• Stop stitching when the data comes from three or more systems, a silent error would be expensive, or what people actually want is the current numbers on demand, not a file once a month.

In the second case, a system designed around how you work covers what the tools can't: your pricing, your job stages and your definition of a problem, built in once and run the same way every month. You can click through a live sample dashboard to see the idea, and if you're in construction, here's reporting built around your projects.