Context
Operational context
A billing team repeatedly reviewed large spreadsheets, calculated charges and prepared customer-facing outputs by hand. The workflow consumed most of a working day and made consistency dependent on manual checks.
Challenge
The problem
Manual review, calculation and formatting took more than eight hours per billing cycle and introduced avoidable errors and inconsistencies. Staff had limited time left for financial review and exception handling.
Constraints
- The workflow needed to accept the organisation’s existing Excel inputs.
- Missing and inconsistent rows had to be identified before bills were produced.
- Outputs needed to remain usable in both Excel and printable PDF formats.
Architecture
System design
A Python processing pipeline reads workbook inputs with Pandas and OpenPyXL, validates required fields and business rules, computes totals and taxes, records exceptions, and generates structured Excel and PDF billing outputs. The stages are separated so invalid rows can be reviewed before publication.
Solution
What was built
I developed a repeatable billing workflow that parses source workbooks, validates entries, applies calculations and tax rules, and produces distribution-ready bills and monthly summaries. Exception reporting keeps incomplete records visible instead of silently producing unreliable output.
Engineering judgement
Decisions and trade-offs
Retain Excel as the input boundary
Context: The billing team already depended on established workbooks
Decision: Automate around the existing input format with Pandas and OpenPyXL
Trade-off: Spreadsheet schema changes must be detected and managed
Validate before generation
Context: Incomplete rows could create incorrect bills
Decision: Separate validation and exception reporting from final document output
Trade-off: Exceptions still require a controlled human review
Generate familiar outputs
Context: Users needed files they could review and distribute immediately
Decision: Produce formatted Excel and PDF documents
Trade-off: Multiple output formats require consistent templates
Contribution
My role
- Mapped the billing rules and workbook structure.
- Built the Python processing workflow with Pandas and OpenPyXL.
- Implemented validation and exception reporting for inconsistent data.
- Automated monthly summaries and formatted bill generation.
- Optimised processing for large workbooks.
Product demonstration
See the system in use
Results
Operational impact
- Removed repetitive calculation and formatting work from each billing cycle.
- Made missing or inconsistent records visible before documents were issued.
- Produced consistent Excel and PDF outputs for review and distribution.
- Freed administrative time for analysis and exception handling.
Reflection
Lessons learned
- Spreadsheet automation should validate the input contract explicitly because workbook formats evolve.
- Exception reports are more valuable than silently coercing questionable data.
- Keeping familiar output formats can accelerate adoption while the underlying process changes.