Square-to-Xero Settlement Analysis
Phase 1–2 completed analysis of the Square-to-Xero settlement flow via Amaka middleware. Identifies three critical structural issues and one clean account. Confirms no “phantom” balances exist in the sense of erroneous entries, but accounting issues require correction.
Settlement Flow Architecture
Square processes card payments throughout the day. At end of business, Square batches transactions and initiates a single next-business-day payout to the linked bank account. The payout amount is net of processing fees (~1% in Australia). Amaka middleware syncs this to Xero via two cycles:
Daily Sales Sync (invoices):
- Breaks out revenue by category (SQ-200000 Square Sales, SQ-200001 Discounts, SQ-200003 Service Charges) with GST
- Receivable side splits by payment type: card payments → SQ-000000 Square Balance clearing account, cash payments → SQ-600000, bank transfers → SQ-600002, tips → SQ-800000, gift cards → SQ-800001
Settlement Sync (transfers + fees):
- When Square deposits net payout, Amaka creates bank transfer from Square Balance to mapped bank account AND bills for processing fees to SQ-300000
- Allows bank deposit to match clearing account transfer, not revenue directly
Account Structure (Correct State)
| Code | Name | Type | Purpose |
|---|---|---|---|
| SQ-000000 | Square Balance | Bank | Clearing account for card settlements. Should net to ~$0. |
| SQ-600000 | Square Cash Clearing | Asset | Cash POS takings. Cleared when staff deposit at bank. |
| SQ-600002 | Other Payment Clearing | Asset | Bank transfers/other tenders. Cleared when payment arrives. |
| SQ-300000 | Square Fees | Expense | Processing fees (~1% of card gross). Captured via settlement sync. |
| SQ-200000 | Square Sales | Revenue | Gross sales from Square POS (GST on Income). |
| SQ-200001 | Square Discounts | Revenue | Contra-revenue for discounts applied. |
| SQ-800000 | Square Tips | Liability | Tips collected on behalf of staff. Cleared when paid out. |
| SQ-800001 | Square Gift Card | Liability | Gift card liability. Cleared when redeemed. |
Square Fee Structure
Square charges flat 1% processing fee on card transactions in Australia. Each payout entry in Square API shows gross amount, fee amount, and net amount separately. TAX_ON_FEE entry (GST refund on fee) effectively reduces fee to ~0.9% net. Square deposits net amount (gross minus fee plus GST refund) to bank. Difference between gross revenue and bank deposit is processing fee—Amaka should record to SQ-300000.
Issues Identified
Issue 1: SQ-600000 (Square Cash Clearing) — ($396,364.25) credit balance
- Severity: HIGH
- Composition: ATM float correction (343,663.85—correct and should remain), Zeller EFTPOS miscoding (~$208,965 in FY25), structural drift from FY24 (no year-end clearing journal)
- Correction required: Create Zeller clearing account; reclassify 30 Zeller “Receive Money” entries; train bookkeeper to stop coding Zeller settlements to SQ-600000
Issue 2: SQ-600002 (Other Payment Clearing) — $27,968.32 debit balance
- Severity: HIGH
- Problem: Bank transfers recognised as revenue twice—once via Amaka invoice (SQ-200000) and once via bank receipt (account 200)
- Balance growth: $632 (FY23) → $2,677 (FY24) → $15,894 (FY25 audit) → $27,968 (now)
- Correction required: Post clearing journals per FY; reverse confirmed revenue double-counts; establish process to credit SQ-600002 when bank transfer arrives
Issue 3: Square Capital Loan Phantom Balances
- Severity: HIGH
- Accounts: SQ-600001 ($247,618.39 Cr) + SQ-800004 ($244,367.14 Dr); net residual $3,251.25
- Root cause: All 7 Square Capital loans fully repaid (Lumi refinancing 28 Mar 2024), but phantom balances from inconsistent recording across three bookkeepers
- Corrective journal ready to post
Square Balance Bank Reconciliation
The $4.67M difference on Square Balance (Statement: $4,705,037.50 vs Xero: $33,240.82) represents cumulative Amaka settlement transactions never reconciled against the Square Balance bank feed. Does not represent missing money—means internal clearing account reconciliation has never been performed.
Related Pages
- Square Cash Clearing Issue — detailed deep dive into SQ-600000 Zeller miscoding
- Other Payment Clearing Issue — revenue double-count via bank transfer route
- Square Capital Loans Issue — phantom balance resolution
- Amaka — middleware architecture and daily sync mechanics
- Bank Reconciliation — end-to-end reconciliation process