Process Automation
Monthly P&L consolidation – from 3 days to 30 minutes
Problem
Finance team manually consolidating P&L from 6 entities every month, taking 3 full working days with high error risk.
Solution
Python pipeline (pandas + openpyxl) that pulls data from source files, validates, consolidates, and generates the final reporting pack.
Result
Close process reduced from 3 days to 30 minutes. Zero manual errors in 6 months of operation.
The situation
A finance team at a multi-entity company was closing their books using a process built entirely on manual copy-paste. Six entities, six Excel files, one senior analyst spending three full days every month consolidating the data.
The risk was real: a wrong cell reference, a missed row, or an FX rate typed in the wrong cell could invalidate the entire report. The team knew it, but there was no budget for an ERP upgrade and no internal IT resources available.
What I built
A Python pipeline using pandas and openpyxl that:
- Reads source files from a shared folder (one Excel file per entity)
- Validates structure on load — catches missing columns, unexpected formats, or blank sheets before processing begins
- Applies entity-specific currency conversions using a centrally-maintained rate table
- Maps each entity’s chart of accounts to the consolidated reporting structure
- Outputs a finished, formatted Excel pack — headers, totals, and tab structure matching the existing management report template
The script runs in under 2 minutes on a standard laptop. No server, no cloud, no dependencies beyond a Python installation.
What made it work
The critical design decision was validation first. The script refuses to run if source files don’t match the expected format — which forced the team to standardise their monthly templates. That standardisation alone reduced downstream errors before the automation even touched the data.
Handover was designed for non-developers: a single .bat file to launch the script, a README with troubleshooting steps, and a log file that records every run with timestamps and entity-level row counts.
Key takeaways
- Most finance automation problems are data-quality problems in disguise
- A script that rejects bad input is safer than one that silently processes it
- The best automation is the one the team actually uses — keep the interface as close to what they already know as possible