Sales Data Reconciliation: Matching 3 Sources by Hand

UrbanMart's sales data arrives from 3 independent sources — online store, POS system, and distributor invoices — and they rarely agree. The finance team needs a single reconciled sales figure for the quarterly report. The analyst must design a rule-based matching algorithm and manually reconcile 12 records across the 3 sources before closing the books.

# Aplly.xyz Case Study Submission

## Title
Sales Data Reconciliation: Matching 3 Sources by Hand

## Type
Data Analytics

## Difficulty
Advanced

## Estimated Time
75 minutes

## Overview
UrbanMart's sales data arrives from 3 independent sources — online store, POS system, and distributor invoices — and they rarely agree. The finance team needs a single reconciled sales figure for the quarterly report. The analyst must design a rule-based matching algorithm and manually reconcile 12 records across the 3 sources before closing the books.

## Case Details

Function Focus: Multi-source data reconciliation, record-matching rule design, discrepancy detection, duplicate identification, data consolidation algorithm

Scenario:
UrbanMart's quarterly sales report is due in 3 days. The problem: sales data comes from 3 systems that don't talk to each other. The online store records customer orders, the POS system records in-store transactions, and the distributor system records wholesale invoices. A single sale may appear in multiple systems with different reference numbers, slightly different amounts (due to taxes, discounts, or rounding), or even different dates. The finance analyst must design a reconciliation algorithm — defining what constitutes a match, a partial match, a mismatch, and an orphan record — then manually trace through 12 records from the 3 systems to produce a consolidated view. No reconciliation software exists yet; this is all done on paper.

Reconciliation Rules (Draft):
- A match is declared when 2+ records have the same date and the same customer/entity and amounts within ₹100 tolerance
- For online–POS matches: match on (Date, Customer Name, Amount ± ₹100)
- For distributor matches: match on (Date, Distributor Name, Amount ± ₹500)
- If a record matches no other record, it is an "orphan" requiring investigation
- If 2 records from the same source have identical data, flag as possible duplicates
- Priority: prefer exact amount matches within same source, then cross-source matches

Tasks:
1. Before tracing, read the draft rules and identify at least 2 scenarios they don't handle (e.g., what if a single transaction is split across 2 invoices? what if the date differs by 1 day due to end-of-day processing?)
2. Using the draft rules, trace all 12 records across the 3 sources — produce a matching table that shows which records match each other, which are orphans, and which are possible duplicates
3. For each cross-source match, check whether the amounts are within tolerance — flag any that exceed the tolerance and require investigation
4. Identify the most common type of discrepancy across the 3 sources (e.g., online amounts consistently higher than POS, or distributor records missing dates) — compute the frequency and hypothesize why
5. Only after submitting your manual reconciliation, compare your matching against a tool-based approach (spreadsheet or AI) and report any records where your manual match differed from the computed one — then propose a revised set of reconciliation rules

Expected Output:
A reconciliation packet containing: (a) critique of the draft rules with at least 2 missing scenarios, (b) a full matching table linking all 12 records across the 3 sources, (c) a discrepancy log listing records outside tolerance, (d) frequency analysis of the most common discrepancy type with a hypothesized cause, (e) revised reconciliation rules, and (f) a round-trip discrepancy note.

Evaluation Criteria:
Completeness of the matching table (every record accounted for), correct application of tolerance rules, accurate identification of duplicates and orphans, meaningful frequency analysis of discrepancy patterns, and specific, implementable rule revisions.

## Data Sources

Source 1 — Online Store Orders:
| Record ID | Date | Customer | Items | Amount (₹) | Status |
|---|---|---|---|---|---|
| ON-01 | 15-Sep | Ravi Kumar | Phone case, charger | 1,450 | Delivered |
| ON-02 | 16-Sep | Priya Sharma | Running shoes | 3,200 | Delivered |
| ON-03 | 17-Sep | Amit Singh | Notebook set × 2 | 840 | Delivered |
| ON-04 | 18-Sep | Meera Patel | Desk lamp | 2,100 | Shipped |

Source 2 — POS System:
| Record ID | Date | Customer | Payment Method | Amount (₹) |
|---|---|---|---|---|
| POS-01 | 15-Sep | Ravi Kumar | Credit Card | 1,450 |
| POS-02 | 16-Sep | Priya Sharma | Debit Card | 3,250 |
| POS-03 | 16-Sep | Walk-in Customer | Cash | 1,200 |
| POS-04 | 18-Sep | Meera Patel | UPI | 2,100 |
| POS-05 | 19-Sep | Ananya Reddy | Credit Card | 3,200 |

Source 3 — Distributor Invoices:
| Record ID | Date | Distributor | Invoice Ref | Amount (₹) | Paid |
|---|---|---|---|---|---|
| DIST-01 | 15-Sep | Ravi Kumar (Retail) | INV-2026-0901 | 1,400 | Yes |
| DIST-02 | 17-Sep | Priya Sharma Stores | INV-2026-0902 | 3,200 | Yes |
| DIST-03 | 20-Sep | City Distributors | INV-2026-0905 | 8,500 | No |
| DIST-04 | 16-Sep | No name | INV-2026-0903 | 1,200 | Yes |

Context:
- Online amounts include GST and delivery charges
- POS amounts include GST but not delivery
- Distributor amounts are wholesale (ex-GST, ex-delivery)
- Reporting deadline: 3 days
- Last quarter, the 3 sources differed by ₹42,000 and finance had to estimate

## Solution Frameworks
Multi-source reconciliation, record linkage, rule-based matching, tolerance-based discrepancy detection, data quality auditing

## Solver Guidance & Tutorials
Link to: "Manual Data Reconciliation Across Multiple Sources" tutorial

## What You'll Learn
- Designing record-matching rules for multi-source data
- Handling tolerance-based amount comparison
- Detecting duplicates, orphans, and systematic discrepancies
- Building a consolidated view from conflicting sources

## Tags
data reconciliation, ETL, sales data, data quality, multi-source matching

## Registration Links
- Register as Solver
- Register as Evaluator

Source 1 — Online Store Orders:
| Record ID | Date | Customer | Items | Amount (₹) | Status |
|---|---|---|---|---|---|
| ON-01 | 15-Sep | Ravi Kumar | Phone case, charger | 1,450 | Delivered |
| ON-02 | 16-Sep | Priya Sharma | Running shoes | 3,200 | Delivered |
| ON-03 | 17-Sep | Amit Singh | Notebook set × 2 | 840 | Delivered |
| ON-04 | 18-Sep | Meera Patel | Desk lamp | 2,100 | Shipped |

Source 2 — POS System:
| Record ID | Date | Customer | Payment Method | Amount (₹) |
|---|---|---|---|---|
| POS-01 | 15-Sep | Ravi Kumar | Credit Card | 1,450 |
| POS-02 | 16-Sep | Priya Sharma | Debit Card | 3,250 |
| POS-03 | 16-Sep | Walk-in Customer | Cash | 1,200 |
| POS-04 | 18-Sep | Meera Patel | UPI | 2,100 |
| POS-05 | 19-Sep | Ananya Reddy | Credit Card | 3,200 |

Source 3 — Distributor Invoices:
| Record ID | Date | Distributor | Invoice Ref | Amount (₹) | Paid |
|---|---|---|---|---|---|
| DIST-01 | 15-Sep | Ravi Kumar (Retail) | INV-2026-0901 | 1,400 | Yes |
| DIST-02 | 17-Sep | Priya Sharma Stores | INV-2026-0902 | 3,200 | Yes |
| DIST-03 | 20-Sep | City Distributors | INV-2026-0905 | 8,500 | No |
| DIST-04 | 16-Sep | No name | INV-2026-0903 | 1,200 | Yes |

Context:
- Online amounts include GST and delivery charges
- POS amounts include GST but not delivery
- Distributor amounts are wholesale (ex-GST, ex-delivery)
- Reporting deadline: 3 days
- Last quarter, the 3 sources differed by ₹42,000 and finance had to estimate

Multi-source reconciliation, record linkage, rule-based matching, tolerance-based discrepancy detection, data quality auditing