Automated Accounts Receivable & Invoice Collection System

The Problem

Many businesses can create and send invoices, but the collection process afterward still depends on manual follow-up.

Staff may need to repeatedly check due dates, confirm payments, send reminders, remember which customers were already contacted, and identify accounts that need escalation. This creates opportunities for missed follow-ups, duplicate reminders, inconsistent collection activity, and accidental contact after payment.

The goal of this project was to turn that process into a controlled Accounts Receivable workflow using Microsoft 365.

Track invoices from source data through reminders, escalation, and payment closure without relying on manual follow-up.

Power Automate — Single-Flow Orchestration

The solution is built around one Power Automate cloud flow:

AR - Invoice Source Integration

Rather than splitting synchronization, collection decisions, reminders, escalation, and reliability into separate flows, the implementation extends one flow across the complete AR lifecycle.

One Power Automate flow handles invoice synchronization, collection evaluation, customer reminders, escalation, payment closure, and targeted failure handling.

Microsoft Lists — AR Operations Layer

Excel remains the invoice and payment source, while Microsoft Lists acts as the operational Accounts Receivable database used by the flow.

The core AR records contain invoice identity, customer details, original amount, payment status, amount paid, outstanding balance, due date, and payment date.

The Accounts Receivable List stores the synchronized invoice and payment state used by the collection process.

The same List also maintains operational controls such as OnHold, Disputed, DoNotContact, reminder-completion flags, EscalationSent, and ProcessingError.

Operational fields track collection exceptions, reminder history, escalation state, and processing-error quarantine without allowing normal source synchronization to overwrite those controls.

Invoice Synchronization

The source workbook uses a structured Excel table where one row represents one invoice.

The Excel table provides invoice, customer, due-date, payment-status, and balance information to the Power Automate flow.

When the flow runs, each source row is matched against Microsoft Lists using ExternalInvoiceID.

An existing match is updated. A missing invoice creates a new AR record.

The upsert logic reuses existing AR records and creates only missing invoices, preventing duplicate records during tested repeat runs.

The implementation currently performs a full-table synchronization because the portfolio dataset is small and no demonstrated performance requirement justified more complex incremental change tracking.

Collection Automation

After synchronization, the flow retrieves invoices that remain eligible for automatic collection.

The eligibility gate excludes fully paid invoices, DoNotContact = Yes, and invoices quarantined with ProcessingError = Yes. Disputed and OnHold records remain available to the decision engine so they can enter manual-review or paused states rather than being treated as closed.

The decision engine evaluates the invoice relative to its due date:

The decision section applies Disputed and On Hold protections before determining the current pre-due, due-date, overdue, or escalation stage.

Duplicate-Safe Reminders

Each customer reminder stage has its own completion state. The flow checks whether the current stage has already been completed before sending another message.

Reminder completion flags prevent the same stage from being sent repeatedly when the flow runs again.

Testing also exposed a Second Overdue fall-through defect where an already-completed 14-day reminder could incorrectly fall backward into the earlier 7-day stage. The logic was corrected by separating stage detection from duplicate prevention so the invoice remains in its current valid stage.

Customer reminders are sent using Office 365 Outlook with the invoice number, due date, currency, and current outstanding balance.

The received email verifies the actual customer-facing output rather than relying only on a successful Power Automate run.

Payment & Escalation Controls

Partially paid invoices remain active in the collection process and reminders use the remaining OutstandingBalance.

Fully paid invoices synchronize with OutstandingBalance = 0 and a confirmed PaymentDate, then disappear from the eligible collection set. Historical reminder information remains preserved.

A fully paid invoice closes the automatic collection lifecycle while retaining its existing AR history.

Invoices that reach 30 or more days overdue stop receiving additional customer reminders and move into internal escalation.

EscalationSent prevents repeated internal notifications.

The escalation message provides the invoice, customer, balance, due date, days overdue, and escalation reason for manual review.

Reliability & Failure Handling

The flow distinguishes between a notification failure and a more dangerous failure where the notification succeeds but its completion state cannot be saved.

Without protection, that second case could cause the same message to be delivered again on a later run.

The solution uses ProcessingError as an invoice-specific quarantine flag.

If notification delivery succeeds but the following completion update fails or times out, the affected invoice is marked with ProcessingError = Yes, excluded from future collection processing, and an internal alert is sent for reconciliation.

The post-notification failure path quarantines the affected invoice before another automatic reminder or escalation can occur.

The internal error alert identifies the affected invoice and failed completion stage so the record can be corrected before automation resumes.

This pattern was tested across Pre-Due, Due-Date, First Overdue, Second Overdue, and internal escalation completion actions. Healthy invoices continue processing while only the affected invoice is quarantined.

Solution & Outcome

The completed portfolio MVP connects:

Excel source → Microsoft Lists → collection decisions → customer reminders → internal escalation → payment closure → failure quarantine

The implementation was validated using controlled invoice records, Power Automate Run History, Microsoft Lists state, and actual received Outlook messages.

The project validated:

• Existing invoices are updated instead of duplicated during tested repeat runs.
• Paid and Do Not Contact invoices are excluded from normal collection.
• Disputed invoices move to manual review.
• On Hold invoices pause automatic collection.
• Reminder stages follow the approved due-date policy.
• Completed reminder stages are not repeatedly sent.
• Partially paid invoices remain active using the remaining balance.
• Fully paid invoices stop future collection.
• 30+ day overdue invoices trigger duplicate-safe internal escalation.
• Post-notification persistence failures quarantine only the affected invoice.
• Healthy invoices continue processing while quarantined invoices remain paused.

Business Outcome

The system converts a spreadsheet-based invoice process into a structured collection workflow.

Repeatable tasks such as checking invoice status, calculating collection stages, sending reminders, preventing duplicate contact, closing paid invoices, and escalating serious overdue accounts are handled by the flow, while exceptions remain available for manual review.

Validation Scope

This is a portfolio MVP validated with controlled invoice records and test mailboxes.

It does not claim measured improvements in cash flow, Days Sales Outstanding, revenue, or collection rate because those outcomes have not been measured in a live business deployment.

The current implementation also uses a manual flow trigger and personal Microsoft 365 resources. Production scheduling, shared ownership, shared mailbox architecture, formal governance, and production-scale reporting remain outside the current personal-project scope.

Technology Stack

Workflow Diagram