# Deep Reconciliation Analysis - Final Report

**Event:** Mohamed Abdo NYE 2025
**Analysis Date:** 6 January 2026
**Methodology:** Multi-method data analytics reconciliation

---

## EXECUTIVE SUMMARY

### Matching Results (All Methods Combined)

| Method | Matches | Amount | Description |
|--------|---------|--------|-------------|
| **Exact Match (±30 min)** | 33 | £70,250 | Same amount + time proximity |
| **Pair Match (2 payments)** | 1 | £4,000 | Order #718: 2x £2,000 = £4,000 |
| **Fuzzy Match (±5%)** | 2 | £2,550 | Orders #725, #759 |
| **TOTAL MATCHED** | **36** | **£76,800** | ~40% of POS value |

### Unreconciled Summary

| Category | Orders/Payments | Amount | Status |
|----------|-----------------|--------|--------|
| **DB-ONLY amounts** | 9 unique amounts | £87,350 | No Revolut at these values |
| **REV-ONLY amounts** | 15 unique amounts | £32,450 | No DB orders at these values |
| **Gap** | - | £51,039 | DB higher than Revolut |

---

## METHODOLOGY APPLIED

### Data Analytics Techniques Used

Based on [payment reconciliation best practices](https://www.vaia.com/en-us/explanations/computer-science/fintech/payment-reconciliation/):

1. **Exact Match** - Amount + time window (baseline)
2. **Sequential/Ordinal Match** - Nth order = Nth payment per day
3. **Amount Tolerance (Fuzzy)** - Allow ±5% or ±£100 variance
4. **Ticket Decomposition** - N tickets → N payments
5. **Name Matching (Levenshtein)** - Customer ↔ cardholder
6. **Combination Analysis** - Multiple payments → single order
7. **Closest Amount** - Find nearest Revolut per POS

### Key Constraint

**Air-gapped system**: POS orders were manually entered in DB, then payment taken separately on Revolut Reader. No automatic linking.

---

## METHOD-BY-METHOD RESULTS

### Method 1: Exact Match (Amount + 30 min)

**Result: 33 matches = £70,250**

These are HIGH CONFIDENCE matches where:
- DB order amount = Revolut payment amount (exactly)
- Time difference ≤ 30 minutes

### Method 2: Sequential Matching

**Result: ~0 additional matches**

If staff processed payments in strict order, the Nth order on each day should match the Nth payment. Testing showed:

| Date | POS Orders | Rev Payments | Sequential Matches |
|------|------------|--------------|-------------------|
| Dec 28 | 3 | 6 | 0 |
| Dec 29 | 15 | 23 | 0 |
| Dec 30 | 25 | 30 | 1 |
| Dec 31 | 0 | 4 | 0 |

**Conclusion**: Staff did NOT process payments in strict order. Likely multi-threaded (multiple staff, walk-ins).

### Method 3: Amount Tolerance (±5%)

**Result: 2 fuzzy matches**

| Order | DB Amount | Revolut | Diff | % |
|-------|-----------|---------|------|---|
| #725 | £1,450 | £1,400 | £50 | 3.4% |
| #759 | £1,100 | £1,000 | £100 | 9.1% |

**Insight**: Very few near-matches. The mismatches are NOT typos or rounding - they are LARGE differences.

### Method 4: Ticket Decomposition

**Result: 2 full matches + 2 partials**

Looking for N Revolut payments that sum to DB order total:

| Order | Amount | Tickets | Found Payments | Result |
|-------|--------|---------|----------------|--------|
| #698 | £2,000 | 2x £1,000 | 2x £1,000 | ✓ FULL |
| #731 | £2,000 | 2x £1,000 | 2x £1,000 | ✓ FULL |
| #668 | £4,000 | 4x £1,000 | 1x £1,000 | PARTIAL |
| #739 | £2,000 | 2x £1,000 | 1x £1,000 | PARTIAL |

**Insight**: Some evidence of per-ticket charging for £1,000 tickets.

### Method 5: Name Matching

**Result: 0 matches**

The orphaned Revolut payments have **NO cardholder names** in the exported data (all null). Cannot perform name matching.

### Method 6: Combination Analysis

**Result: 1 definitive match found**

**Order #718** (£4,000, Yasser al saleh):
- DB order total: £4,000 (4 tickets @ £1,000)
- Nearby Revolut: £2,000 at 15:54:52 + £2,000 at 15:55:09
- Time span: **17 seconds**
- **★ PAIR MATCH: £2,000 + £2,000 = £4,000**

This is strong evidence that staff charged 2x £2,000 instead of 1x £4,000.

---

## AMOUNT ANALYSIS (FACT-BASED)

### DB-ONLY Amounts (No Revolut exists at these values)

| Amount | Count | Total | Ticket Composition |
|--------|-------|-------|-------------------|
| £7,800 | 1 | £7,800 | 4x VVIP @ £1,950 |
| £6,650 | 1 | £6,650 | 3x VIP + 2x BROWN |
| £5,550 | 2 | £11,100 | 3x VIP @ £1,850 |
| £4,350 | 4 | £17,400 | 3x BROWN @ £1,450 |
| £4,000 | 2 | £8,000 | 4x @ £1,000 |
| £3,700 | 6 | £22,200 | 2x VIP @ £1,850 |
| £1,850 | 5 | £9,250 | 1x VIP @ £1,850 |
| £1,100 | 4 | £4,400 | 2x BROWN @ £550 |
| £550 | 1 | £550 | 1x BROWN @ £550 |
| **TOTAL** | **26** | **£87,350** | |

**KEY FACT**: These 9 unique amounts have ZERO Revolut payments at those exact values.

### REV-ONLY Amounts (No DB order exists at these values)

| Amount | Count | Total | Analysis |
|--------|-------|-------|----------|
| £4,200 | 1 | £4,200 | Non-standard |
| £2,950 | 1 | £2,950 | Close to £3,000 |
| £2,100 | 3 | £6,300 | Could be 3x £700 |
| £1,700 | 1 | £1,700 | Non-standard |
| £1,400 | 2 | £2,800 | Close to £1,450 (BROWN) |
| £1,200 | 1 | £1,200 | Could be 2x £600 |
| £950 | 1 | £950 | Non-standard |
| £700 | 5 | £3,500 | Round number (manual) |
| £690 | 1 | £690 | Close to £700 |
| £600 | 2 | £1,200 | Non-standard |
| £500 | 11 | £5,500 | Round number (manual) |
| £450 | 1 | £450 | Non-standard |
| £400 | 1 | £400 | Non-standard |
| £310 | 1 | £310 | Non-standard |
| £300 | 1 | £300 | Non-standard |
| **TOTAL** | **33** | **£32,450** | |

**KEY FACT**: None of these amounts match standard ticket prices (£550, £1,000, £1,100, £1,450, £1,850, £1,950). They appear to be manually typed custom amounts.

---

## HYPOTHESIS EVALUATION

### Hypothesis A: Second Terminal Used

**If TRUE**: £87,350 in DB-only orders went through another card machine

**Evidence FOR**:
- Large unique amounts (£7,800, £6,650, £5,550) not in Revolut AT ALL
- These are complete order totals, not partial amounts
- No combination of Revolut payments sums to these amounts

**Evidence AGAINST**:
- Client stated only one terminal was used

**Verdict**: Cannot rule out without terminal logs

### Hypothesis B: Staff Entered Wrong Amounts

**If TRUE**: Staff typed different amounts on Revolut Reader

**Evidence FOR**:
- Order #725 (£1,450) has nearby Revolut £1,400 (£50 diff)
- Order #718 (£4,000) has 2x £2,000 payments (staff split it)

**Evidence AGAINST**:
- Average amount difference is £1,888 - too large for typos
- Most differences are 20-80%, not small rounding errors

**Verdict**: Some evidence, but doesn't explain large gaps

### Hypothesis C: Orders Never Actually Charged

**If TRUE**: Orders marked "paid" but payment never taken

**Evidence FOR**:
- 26 orders with amounts that have ZERO Revolut matches
- System requires manual marking as "paid"

**Evidence AGAINST**:
- Staff would need to see terminal "accepted" message
- Tickets were issued to customers

**Verdict**: Possible for some orders, would require customer verification

### Hypothesis D: Custom Amount Door Sales (REV-ONLY)

**If TRUE**: Staff used Revolut Reader without creating DB orders

**Evidence FOR**:
- £32,450 in Revolut payments with no matching DB order
- Amounts like £500, £700, £2,100 suggest manual entry
- Revolut description shows "Custom product" or "Custom amount"

**Evidence AGAINST**:
- Process required order creation before payment

**Verdict**: STRONG - Likely explains REV-ONLY amounts

---

## STATISTICAL SUMMARY

```
TOTAL POS CARD ORDERS:     76 orders = £189,550
TOTAL ORPHANED REVOLUT:    96 payments = £138,511
                           -----------------------
GAP (DB higher):           £51,039

RECONCILIATION:
  Matched (all methods):    36 pairs = £76,800 (40.5%)

  DB UNMATCHED:            40 orders = £112,750
    - DB-ONLY amounts:      26 orders = £87,350 (77.5%)
    - DB excess at shared:  14 orders = £25,400 (22.5%)

  REV UNMATCHED:           60 payments = £61,711
    - REV-ONLY amounts:     33 payments = £32,450 (52.6%)
    - REV excess at shared: 27 payments = £29,261 (47.4%)
```

---

## CONCLUSIONS (FACT-BASED)

### What We KNOW FOR CERTAIN

1. **33 exact matches confirmed** - Same amount + ≤30 min time
2. **1 combination match confirmed** - Order #718: 2x £2,000 = £4,000
3. **£87,350 in DB orders have NO Revolut at that amount** - Unexplained
4. **£32,450 in Revolut has NO DB order at that amount** - Likely untracked door sales
5. **Staff did NOT process in strict sequential order** - Multi-threaded operations
6. **Amount differences are LARGE (avg £1,888)** - Not typos/rounding

### What REQUIRES INVESTIGATION

1. **26 DB-ONLY orders (£87,350)**: Were these charged on a different terminal? Were they never charged?
2. **33 REV-ONLY payments (£32,450)**: Confirm these were door sales without order creation
3. **Order #718 pattern**: Are there other split-payment cases we missed?

### Recommended Actions

1. **MBS Terminal Report**: Cross-reference with terminal batch report to identify secondary terminal usage
2. **Customer Spot-Check**: Contact 3-5 customers from DB-ONLY orders to verify payment method used
3. **Staff Debrief**: Interview door staff about payment procedures, especially for large orders

---

## FILES GENERATED

| File | Purpose |
|------|---------|
| `DEEP_RECONCILIATION_FINAL_2026-01-06.md` | This analysis |
| `POS_DISCREPANCY_ANALYSIS_2026-01-06.md` | Initial discrepancy report |
| `SESSION_SUMMARY_2026-01-05.md` | Previous session summary |
| `pos_orders_detailed.json` (server) | All POS orders with tickets |
| `orphaned_with_cardholders.json` (server) | Orphaned Revolut payments |

---

*Analysis performed using: exact matching, sequential matching, fuzzy matching, ticket decomposition, combination analysis, and name matching (where available).*

*Sources: [Payment Reconciliation Methodology](https://www.vaia.com/en-us/explanations/computer-science/fintech/payment-reconciliation/), [Fuzzy Matching Best Practices](https://takeitpersonelly.com/2025/08/04/tips-for-solving-payment-discrepancies-with-fuzzy-name-matching/), [ML for Financial Reconciliation](https://medium.com/@lawrenceklamecki/using-machine-learning-to-solve-data-reconciliation-challenges-in-financial-services-b2f1a2dbb954)*