Anonymized real-world work Multi-location retail

COGS & Cost Reconciliation System

A centralized sales-and-cost reconciliation workflow that finds missing or incorrect costs, matches SKUs, flags exceptions, and notifies the person responsible for correcting them.

Situation

Accurate cost of goods sold depends on every sale being matched to a correct cost. For this multi-location retailer, sales records and cost records didn't always line up.

Problem

Sales data contained products with missing or inconsistent cost records. The causes included:

  • SKU normalization issues: the same product formatted differently in different records.
  • Leading-zero mismatches, where one record stored 0004512 and another stored 4512.
  • Discounts and other exceptions that complicated reconciliation.

These gaps made incomplete cost data hard to identify and reporting discrepancies hard to investigate.

What we built

A centralized sales-and-cost reconciliation workflow and database that:

  • Normalizes and matches SKUs, so equivalent products are recognized as the same item.
  • Identifies sales lines with missing or incorrect costs.
  • Flags exceptions for review.
  • Emails the person responsible, so they can review and correct the cost.

The system detects the problem automatically, but does not invent a financial value. A responsible person reviews and corrects the cost.

How the cost reconciliation works Sales lines go through SKU normalization and are matched to cost records. Matched lines go to cost reporting. Lines with missing or incorrect costs are flagged as exceptions and the responsible person is emailed. That person corrects the cost, and the corrected cost flows to cost reporting. Sales lines Normalize SKUs Match to cost records matched Cost reporting missing / incorrect Flag exception Email the responsible Person corrects cost corrected
How the cost reconciliation works Sales lines go through SKU normalization and are matched to cost records. Matched lines go to cost reporting. Lines with missing or incorrect costs are flagged and the responsible person is emailed. That person corrects the cost, which then flows to cost reporting. Sales lines Normalize SKUs Match to cost records matched missing / incorrect Flag exception Email the responsible person Person corrects cost corrected Cost reporting
  • Automated step (blue outline)
  • Handled by a person (solid)
  • Data or system (dark outline)
The system detects cost problems automatically. A responsible person makes the correction.

How it works: an illustrative example

Synthetic data for illustration only. These are not client records.

Sales SKU Cost record SKU Result
0004512 4512 Matched after normalization
0007781 none found Flagged: missing cost
3310-B 3310B Matched after normalization
5520 5520 Flagged: cost inconsistent with other records

Each flagged line is sent to the person responsible for costs, who reviews it and enters the correct value.

Security considerations

  • SKU matching is tested against known edge cases such as leading zeros, formatting differences, discounts, and unmatched products.
  • Missing or incorrect cost records are flagged for review.
  • The system sends notifications to the person responsible for correcting cost issues.
  • Cost corrections remain a human decision rather than being guessed or automatically invented by the system.

Outcome

  • Better ability to identify incomplete cost data.
  • Reporting discrepancies that are easier to investigate, because exceptions are visible instead of buried in totals.

Possible next steps

  • Track exception status over time, such as open, corrected, or unresolved.
  • Add escalation or reminders for cost issues that remain unresolved.
  • Catch missing costs earlier in the product lifecycle, before they affect reporting.

Services: Data & Reporting Automation Systems & Integrations

Do your reports rely on data you can't fully reconcile?

Request a consultation