Playbook: Multi-Channel E-Commerce Accounting & Gateway Reconciliation

Project template walkthrough

This page walks through the built-in Invoice & Bank Statement Reconciliation project template (invoice-reconciliation) — choose it under New Project. File names, figures and run times are an illustrative example, not a benchmark; your results depend on your files and the model you select.

Domain: Financial Accounting & Audit
Target Roles: Chief Financial Officer, VP of Finance, Controller, Chief Accountant
Example run time: 7 Minutes

---

1. The Financial Challenge

D2C brands and omnichannel retailers selling across Amazon Seller Central, Shopify Plus, Stripe, and PayPal process thousands of transactions per month.

• Amazon and Shopify payout batches lump gross sales, FBA fulfillment fees, refund holdbacks, platform commission tiers, and merchant chargebacks into single net bank deposits.
• Manually matching 4,800 monthly orders against commercial operating bank statements (e.g. JPMorgan Chase, Mercury, or Silicon Valley Bank) consumes 4–5 business days of an accounting team's time.
• Subtle calculation discrepancies, unremitted reserves, or silent payment processor interchange fee tier misclassifications quietly erode 2–5% of operating profit margins.

---

2. Step-by-Step Walkthrough: Multi-Channel Reconciliation ($348,000 Monthly Volume)

Step 1: Ingest Raw Files into Sources

Add merchant payout logs and commercial bank statements to the project's Sources (files you add are kept in sources/uploads/):

• Chase_Operating_Statement_Aug2026.xlsx: 640 bank credit entries from company operating account.
• Amazon_US_Settlement_Summary.csv: 2,650 orders covering gross merchandise value, FBA weight-handling fees, refund reserves, and net disbursements.
• Shopify_Plus_Payouts_Aug2026.xlsx: 1,420 orders with platform processing fees, tax withholdings, and gift card deductions.
• Stripe_Gross_Balance_Transactions.csv: 730 direct checkout credit card transactions.

Step 2: Run the Executive Accounting Prompt

Execute a deterministic cross-reconciliation between our Chase operating statement and multi-channel merchant settlement reports:

1. Ingest all 4 files into the local analytical engine using `accounting_load_file`.
2. Verify schema integrity across Transaction ID, Payout Batch ID, Settlement Date, Gross Amount, Processing Fees, and Net Remittance.
3. Cross-reconcile bank credit entries against net disbursement batches from Amazon, Shopify Payments, and Stripe.
4. Flag every financial exception:
   - Orders marked 'Completed' with no corresponding bank settlement.
   - Gateway fee deductions exceeding contracted enterprise rates (e.g. Shopify processing fee > 2.4% + $0.30).
   - Unreleased rolling reserves or uncollected marketplace funds.
5. Export full audit deliverables into `reports/Reconciliation_Report.xlsx` with an executive Exception Summary PDF.

Step 3: What AMBA Executes Autonomously

1. Database Ingestion: Parses 4,800 total order records into the local SQLite analytics database (workings/.analytics.db).
2. Deterministic SQL Cross-Joins (accounting_sql_query):
   • Matches $344,550 in verified net disbursements against Chase deposit records using SQL joins rather than model arithmetic.
   • Pairs each payout with at most one bank row via accounting_reconcile_sheets (exact ID, normalized ID, ID found in the bank description, amount + date window); amounts such as 1.234.000 ₫ or 1,234,000 are read exactly, unreadable ones are reported.
   • Matched pairs and exceptions are exported unchanged from the engine's result tables; a "Matched" row the engine did not pair fails the run instead of reaching the report.
3. Exception Discovery:
   • Unremitted Amazon Reserve: Uncovers $2,630 in FBA reimbursements withheld under a temporary inventory reconciliation hold.
   • Interchange Fee Misclassification: Flags 184 transactions on Shopify billed at standard 2.9% + 30¢ instead of contracted Enterprise volume tier 2.4% + 30¢, discovering $820 in gateway fee overcharges.
   • Zero Arithmetic Errors: Every subtotal verified down to the exact cent.

Step 4: Deliverables in reports/

• 📊 reports/Reconciliation_Report.xlsx: A comprehensive 4-tab workbook containing:
  • Executive Summary Dashboard: Total Processed ($348,000), Total Reconciled ($344,550), Total Discrepancy to Recover ($3,450).
  • Matched Transactions: Full audit trail with bank ACH reference numbers.
  • Exception Register: Itemized breakdown of the withheld Amazon reserve and the 184 Shopify fee overcharges.
• 📄 reports/Reconciliation_Exception_Summary.pdf: Board-ready executive audit memo with formal dispute claim drafts ready to submit to vendor merchant support.

Total Time: 7 minutes. Business recovers $3,450 in unremitted funds and overcharged fees.