Collections & Accounts Receivable Management Business Logic

The collections and accounts receivable system manages customer payments and outstanding balances within the Stratpoint Timesheet Application. It provides automated collection workflows, aging analysis, and comprehensive payment tracking from billing through final settlement or write-off.

How Collections Management Works

For Finance Teams: 1. Monitor accounts receivable aging and payment due dates 2. Execute systematic 6-stage collection process with escalation 3. Track interest calculations and late payment fees 4. Process partial and full settlements from customers 5. Generate aging reports and collection analytics

For Project Managers: 1. Track milestone payment status and collection activities 2. Coordinate with finance on customer payment issues 3. Provide project context for collection efforts 4. Monitor impact of collection status on project cash flow

For Management: 1. Review accounts receivable aging and collection performance 2. Analyze payment patterns and customer risk profiles 3. Make decisions on write-offs and settlement negotiations 4. Monitor cash flow impact of outstanding receivables

System Features: - Automated 6-stage collection workflow with escalation - Comprehensive aging analysis with multiple time buckets - Interest calculation and late fee management - Partial and full settlement processing - Integration with invoicing and financial systems - Detailed collection reporting and analytics

Collection Process Workflow

6-Stage Collection System

stateDiagram-v2
    [*] --> Stage1: Payment overdue (1+ days)
    Stage1 --> Stage2: No response (31 days)
    Stage2 --> Stage3: Still unpaid (61 days)
    Stage3 --> Stage4: Escalation needed (75 days)
    Stage4 --> Stage5: Legal involvement (91 days)
    Stage5 --> PartialSettlement: Partial payment
    Stage5 --> FullSettlement: Full payment
    Stage5 --> WriteOff: Uncollectable
    PartialSettlement --> FullSettlement: Final payment
    FullSettlement --> [*]: Account closed
    WriteOff --> [*]: Account written off

Collection Stages and Responsibilities

Stage 1: Courtesy Email (1+ days overdue) - Responsibility: Accounting Associate - Action: Friendly payment reminder via email - Tone: Professional courtesy notice - Escalation: Automatic after no response

Stage 2: Formal Follow-up (31+ days overdue) - Responsibility: Accounting Manager - Action: Formal written notice requesting payment - Tone: More serious, requesting immediate attention - Documentation: Written follow-up with payment terms

Stage 3: First Notice (61+ days overdue) - Responsibility: Finance Head - Action: Official first notice of overdue account - Tone: Formal demand for payment with consequences - Legal: Warning of potential legal action

Stage 4: Second Notice (75+ days overdue) - Responsibility: Finance Head and Business Unit Head - Action: Escalated notice involving business leadership - Tone: Final administrative warning - Coordination: Joint finance and business unit action

Stage 5: Final Notice (91+ days overdue) - Responsibility: Legal Department - Action: Legal notice with collection threats - Tone: Formal legal demand - Preparation: Ready for legal collection action

Stage 6: Resolution Options - 6a-Partially Settled: Partial payment received, balance outstanding - 6b-Fully Settled: Complete payment received, account closed - 6c-For Write-off: Deemed uncollectable, approved for write-off

Automatic Escalation Rules

• Timing: System automatically suggests next collection stage based on days overdue
• Thresholds: Fixed day intervals (1, 31, 61, 75, 91 days)
• Override: Manual progression allowed for special circumstances
• Documentation: All escalations require notes and justification

Accounts Receivable Aging

Aging Categories and Buckets

Unbilled Receivables: - For COCA Issuance/Approval: 0-30, 31-60, 61-90, 91-180, 181-365, 365+ days - Payment Approved But Not Billed: Same aging buckets - Pending Client Approval: Awaiting customer sign-off

Billed Receivables: - Current (Not Due): Within payment terms - 1-30 Days Past Due: Recently overdue - 31-60 Days Past Due: Requiring follow-up - 61-75 Days Past Due: Formal notice stage - 76-90 Days Past Due: Escalated attention - 91-365 Days Past Due: Legal consideration - 365+ Days Past Due: Write-off candidates

Aging Analysis Features

• Multi-dimensional Grouping: By client, project, business unit, line of business
• Currency Handling: Foreign currency and PHP equivalent amounts
• Interest Tracking: Separate tracking of principal and interest amounts
• Historical Trends: Period-over-period aging comparison

Payment Milestone Management

Milestone Lifecycle

flowchart TD
    A[Milestone Created] --> B[Payment Approved]
    B --> C[Billed to Client]
    C --> D[Payment Due Date]
    D --> E{Payment Received?}
    E -->|Yes| F[Fully Settled]
    E -->|Partial| G[Partially Settled]
    E -->|No| H[Collection Process]
    H --> I[Interest Accrual]
    I --> J[Escalation]
    G --> K[Remaining Balance Collection]
    K --> F
    J --> L[Write-off Consideration]

Milestone Information Tracking

• Payment Terms: Net 30, Net 60, or custom terms
• Due Dates: Calculated based on billing date and terms
• Invoice Details: Invoice numbers and billing amounts
• Currency: Multi-currency support with exchange rates
• Collection Status: Current stage in collection process
• Interest Amounts: Late payment charges and penalties

Payment Processing

• Full Payment: Complete settlement of milestone amount
• Partial Payment: Partial settlement with remaining balance
• Early Payment: Payment before due date (potential discounts)
• Late Payment: Payment after due date (with potential interest)

Interest and Late Fees

Interest Calculation

• Manual Entry: Interest amounts entered through collection notices
• Currency Support: Interest tracked in original currency and PHP
• Historical Tracking: Complete history of interest charges
• Compound Interest: Ability to calculate interest on interest

Late Fee Management

• Fee Structure: Configurable late payment fees
• Automatic Calculation: System-supported interest calculations
• Manual Override: Finance team can adjust interest amounts
• Currency Conversion: Multi-currency interest management

Interest Reporting

• Interest Aging: Aging analysis includes interest components
• Total Exposure: Principal plus accrued interest tracking
• Collection Impact: Interest affects collection priority and strategy

Settlement Processing

Settlement Types

Full Settlement: - Process: Complete payment of outstanding balance - Status Change: Milestone marked as "06b-Fully Settled" - Documentation: Payment confirmation and receipt processing - Account Closure: Complete closure of collection activity

Partial Settlement: - Process: Partial payment against outstanding balance - Status Change: Milestone marked as "06a-Partially Settled" - Remaining Balance: Continued collection on unpaid portion - Payment Allocation: Allocation between principal and interest

Write-off Process: - Approval: Management approval for uncollectable amounts - Status Change: Milestone marked as "06c-For Write-off" - Documentation: Business justification and approval chain - Financial Impact: Proper accounting treatment of loss

Settlement Documentation

• Payment Confirmation: Bank confirmations and receipt documents
• Settlement Agreements: Negotiated payment terms and discounts
• File Management: Document storage and retrieval system
• Audit Trail: Complete history of settlement negotiations

Collection Reporting and Analytics

Key Performance Indicators

• Days Sales Outstanding (DSO): Average collection period
• Aging Distribution: Percentage in each aging bucket
• Collection Efficiency: Success rate by collection stage
• Write-off Percentage: Proportion of uncollectable receivables

Standard Reports

AR Aging Report: - Grouping Options: Client, project, business unit, line of business - Time Periods: Multiple aging bucket configurations - Currency Options: Original currency and PHP equivalent - Export Formats: Excel, CSV, PDF formats

Collection Activity Report: - Stage Progression: Movement through collection stages - Response Rates: Customer response to collection efforts - Settlement Analysis: Payment patterns and negotiation outcomes - Interest Analysis: Interest charges and collection impact

Performance Analytics: - Collection Team Performance: Individual and team metrics - Client Payment Patterns: Customer payment behavior analysis - Business Unit Analysis: Departmental collection performance - Trend Analysis: Historical collection performance trends

Dashboard Features

flowchart TD
    A[AR Aging Distribution] --> B[Current: 45%]
    A --> C[1-30 Days: 25%]
    A --> D[31-60 Days: 15%]
    A --> E[61-90 Days: 10%]
    A --> F[90+ Days: 5%]

    style A fill:#1f2937,stroke:#374151,color:#fff
    style B fill:#10b981,stroke:#059669,color:#fff
    style C fill:#06b6d4,stroke:#0891b2,color:#fff
    style D fill:#f59e0b,stroke:#d97706,color:#fff
    style E fill:#fb7185,stroke:#f43f5e,color:#fff
    style F fill:#ef4444,stroke:#dc2626,color:#fff

Integration Points

Financial System Integration

• NetSuite Connection: Synchronization with ERP system
• Invoice Management: Integration with billing systems
• Payment Processing: Connection to payment platforms
• General Ledger: Automatic journal entry generation

Project Management Integration

• Milestone Tracking: Project milestone payment status
• Cash Flow Impact: Project profitability and cash flow analysis
• Resource Planning: Payment status affects resource allocation
• Client Relationship: Collection status influences project decisions

Communication Integration

• Email Automation: Automated collection notice distribution
• Document Management: File storage and retrieval
• Notification System: Alerts for collection milestones
• Reporting Distribution: Automated report generation and distribution

---

Technical Implementation Details

For developers and system administrators

Core API Endpoints

Collection Notice Management:

// Collection notice operations
POST /api/v2/collection-notices
{
  "paymentMilestoneId": 123,
  "collectionNoticeStatusId": 2,
  "noticeDate": "2024-06-15",
  "noticeDueDate": "2024-06-30",
  "interestAmount": "2500.00",
  "notes": "Formal follow-up notice sent to client"
}

// Bulk status updates
PUT /api/v2/collection-notices/bulk-update
{
  "milestoneIds": [123, 124, 125],
  "newStatusId": 3,
  "notes": "Escalating to first notice stage"
}

Aging and Reporting:

// AR aging report
POST /api/v2/reports/ar-aging
{
  "asOfDate": "2024-06-30",
  "groupBy": "client",
  "includeCurrency": "both",
  "ageingBuckets": "standard"
}

// Collection performance analytics
GET /api/v2/analytics/collection-performance
{
  "startDate": "2024-01-01",
  "endDate": "2024-06-30",
  "groupBy": "stage"
}

Database Schema

Collection Notice Model:

// Core collection tracking fields
'paymentMilestoneId' => 'Payment milestone reference (required)',
'collectionNoticeStatusId' => 'Collection stage (required)',
'changedByUserId' => 'User who created notice (required)',
'noticeDate' => 'Date notice sent (required)',
'noticeDueDate' => 'Response due date (nullable)',
'interestAmount' => 'Interest charged (nullable)',
'interestAmountInPhp' => 'Interest in PHP (calculated)',
'notes' => 'Collection notes and details (nullable)',
'fileLink' => 'Supporting documents (nullable)',
'created_at' => 'Creation timestamp',
'updated_at' => 'Last update timestamp'

Payment Milestone Integration:

// Collection-related fields in payment_milestone
'latestCollectionNoticeStatusId' => 'Current collection stage',
'latestCollectionNoticeDate' => 'Last collection activity date',
'interestAmount' => 'Total accrued interest',
'interestAmountInPhp' => 'Interest in local currency',
'paymentDueDate' => 'Original payment due date',
'paymentTermInfo' => 'Payment terms description'

Collection Notice Status Model:

// Collection stage definitions
'sequence' => 'Stage order (1-6)',
'name' => 'Stage name (required)',
'description' => 'Stage description',
'responsibleRole' => 'Who handles this stage',
'daysTrigger' => 'Days overdue to trigger',
'isResolution' => 'Final resolution stage flag',
'isActive' => 'Stage availability flag'

Collection Workflow Engine

Automatic Escalation Logic:

// Collection status progression rules
public static function getNextCollectionStatus($currentStatus, $daysOverdue)
{
    $escalationRules = [
        1 => ['daysDue' => 1, 'nextStatus' => 2],   // Courtesy to Formal
        2 => ['daysDue' => 31, 'nextStatus' => 3],  // Formal to First Notice
        3 => ['daysDue' => 61, 'nextStatus' => 4],  // First to Second Notice
        4 => ['daysDue' => 75, 'nextStatus' => 5],  // Second to Final Notice
        5 => ['daysDue' => 91, 'nextStatus' => 6],  // Final to Resolution
    ];

    $rule = $escalationRules[$currentStatus] ?? null;

    if ($rule && $daysOverdue >= $rule['daysDue']) {
        return $rule['nextStatus'];
    }

    return $currentStatus;
}

Aging Calculation Algorithm:

// AR aging bucket calculation
public function calculateAgingBuckets($asOfDate)
{
    $buckets = [
        'current' => 0,
        '1_30' => 0,
        '31_60' => 0,
        '61_75' => 0,
        '76_90' => 0,
        '91_365' => 0,
        'over_365' => 0
    ];

    $milestones = PaymentMilestone::where('paymentDueDate', '<=', $asOfDate)
        ->where('status', '!=', 'Paid')
        ->get();

    foreach ($milestones as $milestone) {
        $daysOverdue = $asOfDate->diffInDays($milestone->paymentDueDate);

        if ($daysOverdue <= 0) {
            $buckets['current'] += $milestone->amount;
        } elseif ($daysOverdue <= 30) {
            $buckets['1_30'] += $milestone->amount;
        } elseif ($daysOverdue <= 60) {
            $buckets['31_60'] += $milestone->amount;
        } elseif ($daysOverdue <= 75) {
            $buckets['61_75'] += $milestone->amount;
        } elseif ($daysOverdue <= 90) {
            $buckets['76_90'] += $milestone->amount;
        } elseif ($daysOverdue <= 365) {
            $buckets['91_365'] += $milestone->amount;
        } else {
            $buckets['over_365'] += $milestone->amount;
        }
    }

    return $buckets;
}

Interest Management System

Interest Calculation Framework:

// Interest calculation and tracking
public function calculateInterest($principal, $annualRate, $daysOverdue)
{
    $dailyRate = $annualRate / 365;
    $interestAmount = $principal * $dailyRate * $daysOverdue;

    return round($interestAmount, 2);
}

// Currency conversion for interest
public function convertInterestToPHP($foreignAmount, $currencyId, $conversionDate)
{
    $exchangeRate = $this->getExchangeRate($currencyId, $conversionDate);
    return $foreignAmount * $exchangeRate;
}

Permission and Security

Access Control: - Permission 208: View collection notices and aging reports - Permission 209: Create and modify collection notices - Permission 210: Approve write-offs and settlements - Permission 211: Access sensitive financial information

Security Features:

// Collection notice access validation
public function authorize($user, $action, $collectionNotice = null)
{
    switch ($action) {
        case 'view':
            return hasPermission($user, 208);
        case 'create':
        case 'update':
            return hasPermission($user, 209);
        case 'writeoff':
            return hasPermission($user, 210);
        default:
            return false;
    }
}

Performance Optimization

Query Optimization:

-- Optimized aging report query
CREATE INDEX idx_payment_milestone_aging
ON payment_milestone (paymentDueDate, status, amount);

CREATE INDEX idx_collection_notices_status
ON collection_notices (paymentMilestoneId, collectionNoticeStatusId, noticeDate);

-- Composite index for collection workflow
CREATE INDEX idx_collection_workflow
ON payment_milestone (latestCollectionNoticeStatusId, latestCollectionNoticeDate);

Caching Strategy: - Aging reports cached for 6 hours - Collection status calculations cached daily - Dashboard metrics cached for 1 hour - Exchange rates cached for 24 hours

Integration Architecture

NetSuite Synchronization:

// NetSuite AR integration
public function syncToNetSuite($collectionNotice)
{
    $payload = [
        'customerId' => $collectionNotice->milestone->project->client->netsuiteId,
        'invoiceId' => $collectionNotice->milestone->invoiceNumber,
        'collectionStatus' => $collectionNotice->status->name,
        'noticeDate' => $collectionNotice->noticeDate,
        'interestAmount' => $collectionNotice->interestAmount
    ];

    return $this->netsuiteService->updateARRecord($payload);
}

Notification System:

// Automated collection notice emails
public function sendCollectionNotice($collectionNotice)
{
    $template = $this->getEmailTemplate($collectionNotice->status);

    Mail::send($template, [
        'client' => $collectionNotice->milestone->project->client,
        'amount' => $collectionNotice->milestone->amount,
        'dueDate' => $collectionNotice->milestone->paymentDueDate,
        'interest' => $collectionNotice->interestAmount
    ], function ($message) use ($collectionNotice) {
        $message->to($collectionNotice->milestone->project->client->email)
                ->subject('Payment Notice - ' . $collectionNotice->status->name);
    });
}

This comprehensive collections and accounts receivable management system provides end-to-end management of customer payments with sophisticated workflow automation, detailed aging analysis, and comprehensive reporting capabilities essential for effective cash flow management in professional services organizations.