Third Party Billing KPI Processor
Browser-based automation that transforms 5 PeopleSoft GL exports into a formatted KPI Excel workbook with preserved formulas, integrated validation, and real-time processing logs. Reduced monthly processing from ~40 minutes to under 2 minutes.
The Problem
Business Context
Third Party Billing team responsible for preparing monthly KPI reports by Calendar Day 8. The process involved downloading 5 source files from PeopleSoft GL, manually copying data into 6 worksheets, applying filters, extending formulas, and verifying outputs. Each cycle took ~40 minutes for experienced users, longer for others.
The Manual Process
- Formula errors: Dragging formulas to wrong row counts broke calculations
- Missed filters: Forgetting SHELL exclusion or MB filter corrupted outputs
- Training burden: New staff needed hours of walkthrough to learn process
- Deadline pressure: Calendar Day 8 deadline with no room for rework
The Solution
Built a browser-based application that automates the entire KPI generation process. Users point to their folder containing the 5 source files, click "Process KPIs", and receive a fully formatted Excel workbook with all formulas preserved and an integrated validation report.
Source File Mapping
| Source File | Target Worksheet | Filter Applied |
|---|---|---|
| WX_AR_DEPOSIT_DETAIL.xls | Payment Application Data | — |
| WX_AP_AR_REBATE_VCHRS.xls | Rebate Payment Data | MB invoices only |
| WX_AR_MERCHANT_AGING_REC.xls | % Unpaid | Excludes SHELL |
| PB Calendar.xlsx | Invoice Distribution + Journal Entries | — |
| Previous Month KPI Template | Template for formulas/formatting | — |
Technical Architecture
Zero Installation
Browser-based with SheetJS library. No server, no setup, works offline after load.
Template Row Architecture
Preserves formulas by extending from row 2 pattern, updating cell references dynamically.
Intelligent File Recognition
Auto-identifies files by prefix patterns with manual override for edge cases.
Integrated Validation
Compares output against sources: row counts, filter results, formula integrity.
Processing Pipeline
Processing Steps
Results
| Metric | Before | After | Improvement |
|---|---|---|---|
| Processing Time | ~40 minutes | <2 minutes | 95% reduction |
| Manual Steps | 12+ actions | 3 clicks | 75% fewer |
| Error Rate | 5-10% rework | <1% | 90%+ reduction |
| Formula Errors | Common | Eliminated | 100% |
| Training Time | Hours | Minutes | ~80% reduction |
Stakeholder Feedback
"I used to dread KPI day. Now it takes two minutes and I know it's right."— Finance Analyst