Receivables Intelligence System
Turning tangled spreadsheets into clean, decision-ready data. A reconciliation system that connects siloed finance data sources into a single, tweakable source of truth.
Context
- Our enterprise system exports opaque billing data with limited visibility into actual receivables.
- Our payment processor tracks inflows, but with zero correlation to internal identifiers.
- There was no mechanism for dynamic division-level breakdowns.
- Finance teams had no real-time view. Leadership was flying blind.
- Monthly reporting was a black box — manual, slow, and impossible to trust at scale.
Problem
Before this tool, the process didn't exist. Manual reconciliation was infeasible, error-prone, and fundamentally not scalable. What would've taken 10+ hours of guesswork and Excel wrangling now takes 5 minutes — with more accuracy, less fragility, and full flexibility.
- No unified view of financial data across systems
- Inability to track receivables by division or management unit
- Significant manual effort required for basic financial reporting
- Decision-making hampered by lack of timely, accurate data
- No way to model scenarios or forecast effectively
Intervention
I built an Excel-based processor that bridges data from our enterprise backend and payment processor to generate a unified, real-time snapshot of our receivables pipeline — down to the division level.
- Designed a reconciliation engine using Excel + a Node.js processor
- The tool ingests:
- Raw reports from the enterprise system
- Payment activity logs from our processor
- Normalizes, parses, and joins records using flexible mapping rules
- Applies accounting logic to produce four critical views:
- Pending receivables
- Collected receivables
- Net receivables
- Revenue by division
- Outputs to a calculator sheet with live-tweakable configs for scenario modeling
Architecture Flow
Data Flow Process
- Extract raw data from enterprise system (billing/invoicing)
- Extract transaction logs from payment processor
- Normalize both datasets through Node.js processor
- Apply identifier mapping to correlate records across systems
- Join datasets on normalized identifiers
- Apply division classification rules
- Generate reconciled view with intelligent lookups
- Output to dynamic Excel dashboard with scenario controls
A unified pipeline that transforms disconnected financial data into actionable intelligence.
Results
- Cut reporting time from 10+ hours to 5 minutes
- Created a real-time view of previously invisible receivables
- Enabled per-division analysis and forecasting
- Fully self-service — no dev or ops required for use
- Internalized finance logic through reverse-engineered accounting structure
Impact
The system transformed financial visibility from a monthly struggle to a daily capability. We gained the ability to reconcile and model receivables in real-time.
Reflection
This one taught me how to build infrastructure around ambiguity. Not every system starts with clean inputs — some start with problems no one thinks can be solved.
I learned to create structure from noise. To make spreadsheets talk. To turn finance into something functional.