Automating Multi-Source Reconciliation for Maplewood University

The Client

Maplewood University manages tuition and student funding through three independent systems:

• Government Loan Payouts (federal and provincial aid programs)
• Bank Transactions
• Student Portal Fee Collections

Each month, the finance team manually compared spreadsheets from all three sources to verify receipts, deposits, and accounting entries.

With over 4,000 active students and multiple fee components (tuition, uniforms, lab fees, etc.), the process was time-consuming, error-prone, and depended heavily on manual cross-checking.

Identifiers often varied across systems “John Doe” in loan data vs. “John” in the student portal causing endless back-and-forth.

Before automation: 3 analysts spent 8–10 days monthly on reconciliation.

The Challenge

When Maplewood’s Finance Director asked,

Can we automate reconciliation so our team isn’t stuck matching line by line?

The answer was yes but not with a plug-and-play tool.

The challenges were deeper than format mismatches:

Data Challenges

• Inconsistent naming conventions across systems
• Partial and installment-based payments
• Multiple fee heads per student

System Constraints

• Government data disbursement delays (making strict date matching unreliable)
• QuickBooks Online API’s inability to mark transactions as “reconciled”

Operational Impact

• Delayed monthly close
• Increased error risk
• Limited audit visibility

The Solutions

Over five to six months, our team conducted a detailed discovery exercise mapping every data source, reconciliation rule, and edge case.

It became clear that Maplewood required custom logic and architecture, not an off-the-shelf reconciliation tool.

Key Highlights

• The process needed a 4-way reconciliation (Government → Student Portal → Bank → QuickBooks Online).
• Existing reconciliation tools couldn’t handle hybrid (precise + fuzzy) matching logic.
• Consistency and auditability demanded a rule-driven engine with transparent logic.

How we built A Rule-Based Matching Engine?

Step 1. Government → Student Portal Matching

Pair each government payout with the correct student record using:

• Given Name + Surname + Last 3 digits of SIN
• Amount logic (ignoring strict date equality to account for delays)

Result: ~90% auto-match accuracy in pilot runs.

Step 2. Government → QuickBooks Payments

Match using Contract ID and period totals, removing dependency on student name.
Totals are verified per funding cycle.

Result: Reliable link between funding receipts and accounting entries.

Step 3️. Bank Cross-Check

Bank data validates actual deposits and handles installment payments.

Step 4️. Smart Bucketing

Transactions are categorized into:

• Matched – Auto-verified
• Partial – Review suggested
• Unmatched – Clear reason displayed (e.g., name mismatch, missing SIN)

Step 5️. Lock, Consolidate, and Report

Accountants can:

• Lock a reconciliation period
• View consolidated reports across all sources
• Create a Bank Deposit in QBO directly from verified items

Step 6️. Handling QBO’s API Limitation

Since QuickBooks Online cannot mark transactions as reconciled via API,
a custom safe extension was added to reflect matched items in QBO’s reconciliation view.

Results

Key Outcomes:

• 85% of transactions auto-matched on first pass
• Consistent revenue recognition across all fee heads
• Locked, versioned reports with complete audit trails
• Seamless QuickBooks deposit creation from verified data

Why Not “Just Use a Tool”?

Generic reconciliation tools assume clean identifiers and simple data structures.
Maplewood’s environment involved:

• Multiple government disbursement programs
• Variable fee heads
• SIN-based identity quirks
• QBO API posting logic

This required custom, transparent matching rules rather than black-box automation.

It’s the difference between almost matching and being right.