Delinquent Account Analytics & Automation Framework
Information Design & Process Improvement
Tools: Excel Power Query, PowerShell, SharePoint Online, Microsoft 365
Overview
To support collections operations within the WSU Bursar's Office, I designed and developed an end-to-end reporting and automation framework that transformed raw PeopleSoft exports into actionable work queues, placement history tracking, and management reporting.
The solution combined Power Query data transformation, PowerShell automation, and SharePoint integration to improve data consistency, reduce manual work, and streamline collections workflows.
The Challenge
Student account collections data originated from multiple reports containing duplicate records, inconsistent identifiers, historical placements, and multiple charge categories. Staff were spending significant time preparing and validating data before meaningful work could begin.
My Solution
I developed a multi-stage framework that:
Consolidated and cleaned raw PeopleSoft exports
Automated data transformation using Excel Power Query
Built logic for placement tracking and write-off eligibility
Generated collections and write-off work queues
Automated SharePoint updates and historical tracking
Distributed assignments automatically
Produced reporting exports for operational review
Technical Highlights
Power Query
Automated file imports
Data cleansing and normalization
Student record deduplication
Category-level balance analysis
Placement history analytics
PowerShell
Modular automation architecture
SharePoint integration
Queue generation
Historical tracking
Automated assignment workflows
Reporting exports
Skills Demonstrated
Information Design
Process Improvement
Workflow Automation
Excel Power Query
PowerShell Development
SharePoint Administration
Data Analysis
Systems Thinking
Outcome
Created a scalable workflow that transformed multiple manual processes into an automated system supporting collections management, historical tracking, reporting, and workload distribution.
Designed and implemented an end-to-end collections management workflow that transformed raw PeopleSoft exports into automated SharePoint work queues, historical records, reporting, and staff assignments using Power Query, PowerShell, and Microsoft 365.
