Reconcile conflicting metric totals between two reports

Diagnose discrepancies between two data sources to identify the root cause of differing metric totals.

You getDiscrepancy Snapshot, root Cause Analysis and resolution Protocol

Prompt

Prompt
Act as a senior data analyst and financial systems auditor. Reconcile the discrepancy between the two pasted report summaries.

Rules:
- Identify likely structural causes for variance (such as timezone cutoffs, timezone offsets, gross vs. net calculations, status filters, or cancellation exclusions).
- Break down the variance into testable hypotheses ranked from most likely to least likely.
- Avoid making assumptions; highlight what definitions or parameters must be confirmed in the underlying data systems.
- Note that this guidance provides analytical frameworks only, not formal audit advice.

Output format:
- Discrepancy Snapshot (Total Difference, Percentage Gap)
- Root Cause Analysis (Numbered list of hypotheses with validation tests)
- Resolution Protocol (Checklist to align definitions across teams)

Here are the two reports and their notes:

Example output

Example for: CRM booked sales report showing $1,240,000 versus ERP revenue showing $1,115,000 for October

Discrepancy Snapshot
  • CRM Total: $1,240,000
  • ERP Total: $1,115,000
  • Total Difference: $125,000 (10.08% variance)
Root Cause Analysis
  1. Timing and Recognition Cutoff (Most Likely): The CRM marks revenue upon deal signature, while the ERP marks revenue upon invoice issuance or contract activation. Multi-year deals signed on October 30-31 may not have generated October invoices.

    • Validation Test: Filter the CRM export for deals marked 'Closed-Won' between October 27 and October 31, and cross-reference them against the November ERP ledger.
  2. Refunds and Credit Memos: The CRM tracks gross bookings, whereas the ERP reflects revenue net of refunds, churn adjustments, or service credits.

    • Validation Test: Pull the ERP credit memo log for October to see if netting accounts for part of the $125,000 gap.
  3. Tax and Currency Treatment: The CRM may be recording quotes in multi-currency at static monthly rates, while the ERP uses daily spot exchange rates.

    • Validation Test: Compare foreign currency denominated contracts across both ledgers.
Resolution Protocol
  • [ ] Confirm whether CRM metrics represent Bookings or recognized Revenue.
  • [ ] Align timezone cutoffs (confirm if CRM uses UTC while ERP uses Eastern Time).
  • [ ] Update dashboard documentation with standardized metric definitions approved by Finance and Sales Ops.
Written by AI from this prompt. Your results will vary.

Paste the figures, date ranges, and metric definitions from both systems directly below the prompt in ChatGPT, Claude, or Gemini. Follow up by providing any known query filters or database table schemas to get a targeted SQL comparison script.

Make it repeatable

Keep using this prompt without pasting it in every time.

Keep exploring

More in Data Analysis