Excel Data Cleaning and Reconciliation, Waghong Sales Data (Accounting Student Friendly)

Project Desciption

About the project
I have a messy Excel workbook of café sales for Q4 2024 (Oct–Dec). It combines receipts, a payment summary, a daily summary and an item summary on one sheet. I need it cleaned, validated and reconciled. The work is hands-on, using the kind of data checks an accountant does.

What you’ll do

• Split the combined sheet into 4 clean tables (Receipts, Payments, Daily, Items)
• Remove duplicates, blank rows, repeated headers and subtotal rows
• Standardise text (payment type, receipt type, dining option) and fix spacing and case
• Convert text-formatted numbers and mixed date formats into real numbers and dates
• Handle missing cost of goods and flag the affected rows
• Find and fix outliers (suspected typing errors)
• Add check columns (Net = Gross − Discounts, Gross profit = Net − COGS)
• Reconcile receipts against the payment and daily summaries and list any differences
• Build a monthly summary (Oct / Nov / Dec): receipts, net sales, gross profit, refunds

What I’ll provide

• The workbook with a task list (16 tasks, with hints)
• Note: December is real POS data. Oct, Nov and Dec 31 are synthetic test data. This is a practice and quality-check job, not a live audit.

Deliverables

1. Cleaned workbook (one tab per clean table, plus an Issues Log tab)
2. Issues Log listing each problem found, how many rows, and how you fixed it
3. Reconciliation sheet showing differences and your explanation
4. Monthly summary (PivotTable or formulas)

Requirements

• Bachelor’s degree accounting student or recent graduate
• Solid Excel: TRIM, VALUE, SUBSTITUTE, IF, COUNTIF, SUMIFS, XLOOKUP/INDEX-MATCH, PivotTables
• Understanding of gross sales, net sales, COGS, gross profit and refunds
• Attention to detail, and the habit of documenting assumptions

Nice to have

• Power Query experience
• Bookkeeping or POS data experience

Rules

• Use formulas where possible. Don’t hardcode results.
• Keep the original data tab untouched.
• State every assumption (for example, how you treated unknown COGS and ambiguous dd/mm dates).