How I eliminated every manual step in monthly finance report preparation — from raw data pull to formatted management pack delivery — using Power Automate, Excel, and SharePoint.
Every month, finance reporting at Malvern involved the same sequence of manual steps. Download reports from the accounting system. Clean and reformat in Excel. Combine data across multiple entities. Apply standard formatting. Save to the correct SharePoint folder. Notify the relevant people. Send the management pack to the CFO.
Each step was individually straightforward. Together, they consumed 2–3 hours every single month — hours spent on process, not analysis. And because it was manual, it was prone to version errors: wrong file saved, old template used, data from a different export date.
Manual reporting routines don't just cost time — they create risk. One misplaced file or outdated Excel reference can send incorrect data to a CFO who trusts it implicitly. The automation wasn't just about speed. It was about reliability.
The process was also emotionally draining — not intellectually demanding, just relentless. Month-end is already the most pressured week in finance. Spending the first two hours of it on repetitive formatting was a poor use of any analyst's time.
I designed and built the entire automation independently. I mapped the existing process, identified every manual touchpoint, designed the flow, built and tested it, and handed it over as a production system. The CFO had no visibility into how it worked — they just saw the management pack appear in their inbox, correctly formatted, on schedule.
I also built the exception-handling layer — so if any step failed (e.g., source file not found, SharePoint path changed), the flow would notify me rather than silently delivering incomplete data.
Mapped the full existing process step by step. Set the flow to trigger automatically on the first working day after month-end close — using a scheduled trigger with a condition checking that the source data file had been updated. This prevents the flow from running against stale data.
The flow pulls the raw accounting export from SharePoint (where the accounting system deposits it), reads it using Excel Online actions, and extracts the relevant tables for each entity — ELT, Pathways, Juniors, and Group. Built dynamic file referencing so the flow always picks the latest file regardless of naming convention changes.
Built a master Excel template with Power Query connections pre-configured. The flow populates the source data, triggers a Power Query refresh via Excel Online, and the template automatically calculates variances, applies conditional formatting, and builds the summary tables. No formulas that can break — the logic lives in the query, not the cells.
Built a consolidation step that pulls entity-level outputs from each report and combines them into the group summary view — handling intercompany eliminations via a lookup table maintained in SharePoint. Any changes to intercompany entries propagate automatically on the next run.
The final step saves the completed management pack to a version-controlled SharePoint folder (with date stamp) and sends a customised email to the CFO and relevant stakeholders — including a summary of what moved at group level pulled directly from the output. The email is generated dynamically, not a template with blanks.
Every major action has a failure branch. If any step fails, the flow logs the error to a SharePoint list, sends me an alert with the exact failure point and error message, and stops — rather than continuing with incomplete data. Built a simple monitoring dashboard in Power BI connected to that log list.
The management pack now arrives in the CFO's inbox automatically. The remaining 20% of manual steps involve CFO-level judgement calls — narrative sections that require human context — which is exactly where finance time should be spent.
The error-handling layer has caught two real issues since launch: once when the accounting system file naming changed mid-year, and once when a SharePoint folder was reorganised. Both times the alert fired immediately rather than delivering bad data silently.