How to automate weekly management reporting

Begin with the decisions made in the meeting

List the questions people actually ask each week: which orders are late, which quotes need action, where stock is below policy, what cash is due, or which jobs have no owner. For each question, name the decision it supports. If nobody changes a priority, assigns work, or investigates an exception because of a measure, it may not belong in the first report.

• Write each question in plain language.
• Name the person who acts on the answer.
• Set the reporting cut-off time and timezone.

Define every measure before building a chart

Write a short definition for each number, including source, calculation, filters, owner, and update frequency. Terms such as active customer, late order, open quote, and gross margin often mean different things across teams. Use a small test set with known answers and have the business owner approve it. A dashboard cannot settle an unresolved definition; it can only repeat one definition consistently.

Fix the grain and keys

State what one row represents before combining files. It might be one order line, one job, one invoice, or one daily stock balance. Then identify the stable keys used to join related data. Joining an order-level total to order lines can multiply revenue, while joining customer names can split or merge records after spelling changes. Reconcile row counts, totals, and unmatched keys after every merge.

Create a repeatable input path

Prefer a direct read-only connection when the source and access controls support it. If exports are the reliable option, standardise the file name, folder, columns, and delivery time. Power Query can combine files with the same schema from a controlled folder. Keep raw data separate from transformations and report tables, and fail visibly when a required column disappears rather than silently returning blanks.

• Archive or version the raw input used for each reporting period.
• Validate required columns, types, and duplicate keys.
• Record the latest successful source timestamp in the report.

Use a model people can reason about

For Power BI, a simple star-shaped model usually keeps event data such as orders or stock movements separate from descriptive tables such as customer, product, site, and date. Keep each fact table at a consistent grain and use relationships based on stable keys. This makes filters and measures easier to test than one giant table assembled through repeated lookups.

Show exceptions before decoration

The first page should make action obvious: overdue items, missing owners, unusual changes, failed source loads, and the records behind each total. Use clear labels and include the reporting period. Colour should support a written state, not carry meaning alone. A manager should be able to move from a summary number to the affected records without asking the report builder to explain the chart.

Make refresh an owned operation

Document where credentials live, whether a gateway is required, when refresh runs, how long it normally takes, and who receives failure notifications. Microsoft recommends checking gateway selection and version when troubleshooting common refresh problems. Whatever the tool, the report should display its latest successful refresh time and should not present stale data as current after an input or connection fails.

• Send refresh failures to a monitored address or named owner.
• Set an escalation time before the management meeting.
• Document how to run the refresh manually and verify its result.

Deliver the alert and the drill-down view

Use a short weekly message to state what changed, what is outside tolerance, and where a decision is needed. Link to the live report for detail instead of attaching another uncontrolled copy. Keep the message factual and generated from tested measures. The dashboard provides context and record-level detail; the alert brings the few urgent items to attention.

Test the full reporting cycle

Rebuild at least one previous week and compare its key totals with the accepted report. Test late files, empty files, duplicate rows, changed headers, missing owners, and a failed refresh. Have a manager use the report in a real meeting before retiring the manual pack. Handover should include measure definitions, data lineage, access, schedules, failure steps, and a named owner for changes.