Utility Enrollment & Billing Optimization Project
This project simulates the end-to-end operational workflow of a regulated energy provider, inspired by the Operations Analyst role at Summitt Energy. It focuses on enrollment handling, billing setup, reconciliation logic, and operational reporting—all critical components of a high-volume utility environment.
Key Deliverables
• Visio Enrollment Workflow
Mapped the full contract-to-billing lifecycle, including contract intake, utility validation, rate setup, and CRM-utility alignment checkpoints.
• Data Schema & SOP
Defined required data fields (e.g., customer ID, rate code, meter ID) and established standard operating procedures for intake validation and enrollment confirmation.
| Field Name | Description | Data Type | Required |
|---|---|---|---|
| Customer_ID | Internal customer identifier | VARCHAR(12) | Yes |
| Utility_Number | Utility account reference | VARCHAR(10) | Yes |
| Rate_Code | Pricing plan code | VARCHAR(6) | Yes |
| Start_Date | Service start date | DATE | Yes |
| Postal_Code | Customer’s postal region | VARCHAR(7) | Yes |
SOP Summary: Enrollment Lifecycle
- Validate incoming contract data (utility number, postal code, rate)
- Match to internal rate setup and pricing matrix
- Structure payload for utility submission
- Submit via EDI batch process
- Monitor utility response and log errors for triage
• Reconciliation Matrix & Resolution Tree
Designed an Excel-based matrix to compare CRM status with utility transaction records. Built a resolution decision tree for handling errors such as rejected enrollments, invalid meter IDs, and pricing mismatches.
Reconciliation Matrix
-
Issue 1:The utility response lacked a valid rate code for the contract. Impact: Enrollment cannot proceed until a recognized rate is provided. Action: Retry submission with corrected product setup, or escalate to Pricing team.
-
Issue 2: Utility confirmed enrollment with a different effective start date. Impact: Billing may be misaligned or rejected if dates conflict. Action: Review original contract terms and coordinate with Sales Ops to align CRM record.
Resolution Tree – Swimlane Enrollment Error Handling
This resolution flowchart outlines how different types of enrollment errors are classified and resolved based on both their source and the responsible department. By combining a tree structure with a swimlane layout, it visualizes the handoff between teams when an enrollment issue arises from either the CRM system or a utility response.
📂 Error Classification
The resolution begins from an identified Enrollment Error, then splits into two main paths:
Utility Response Errors
-
Utility Account Number Mismatch
→ The Sales team confirms the correct account number with the customer
→ Resubmission is handled by Sales -
Start Date Not Accepted
→ The Sales team reviews the contract start date with the customer
→ Once updated, Sales resubmits to the utility -
Rate Plan Data Not Recognized
→ Operations escalates the issue to the Pricing team
→ Once corrected, Operations resubmits to the utility
CRM Validation Errors (Pre-submission)
-
Format Error
→ Operations runs format sanitization scripts to correct field issues
→ After cleansing, the enrollment is submitted to the utility -
Missing Field
→ Sales is notified to follow up with the customer and complete the record
→ After update, Sales submits the completed data
Why This Structure Matters
- Clarifies team ownership for each error type, reducing handoff delays
- Enables faster triage by visually grouping error paths by source
- Supports standard operating procedures for CRM validation and utility response reconciliation
- Reflects real-world workflow divisions seen in transactional energy operations like at Summitt Energy
• Power BI Dashboard
Developed a prototype dashboard to track enrollment success rates, error types, and billing accuracy metrics—supporting trend monitoring and cross-functional transparency.
Impact
- Demonstrated readiness for regulated operations through SOP design, error handling logic, and reconciliation strategy.
- Provided tangible visuals and frameworks applicable to utility operations in Canada and the U.S.
- Reinforced capabilities in Excel, Visio, and Power BI for high-volume data environments.