The Dashboard That Agreed With Nobody
A retail finance team built a daily sales dashboard pulling from three sources: the POS system (transaction-level, near real-time), the ERP (posted invoices, T+1 batch), and a data warehouse fed by both. The CFO notices the dashboard shows $2.14M for Tuesday, the ERP close shows $2.08M, and the POS extract sums to $2.19M. Refunds, gift-card redemptions, and inter-store transfers are handled differently in each system. The warehouse ETL runs at 02:00 and the POS feed has a known 4-hour lag, so late-evening transactions from the prior day sometimes land in the next day's batch. The team wants to publish the dashboard to store managers as a performance scorecard.
1. Explain the three reconciliation gaps and identify which figure is appropriate for which purpose.
Read the worked answer
- POS $2.19M vs ERP $2.08M (~$110K): POS captures gross tenders including gift-card redemptions and before refunds; ERP posts net revenue after refunds and excludes gift-card redemptions (liability settlement, not revenue). The gap is largely definitional, not error.
- Dashboard $2.14M vs POS $2.19M: The 4-hour lag plus the 02:00 ETL means late Tuesday transactions are split across batches - a cutoff/timing gap, not a data-quality failure.
- Dashboard vs ERP: Mix of both effects.
Use: ERP for financial reporting and revenue recognition (it is the book of record, net of refunds). POS for operational/store-level activity and traffic. Dashboard only for directional monitoring, clearly labeled as unclosed and subject to restatement.
2. The team proposes publishing the dashboard to store managers as a scorecard. What controls or design changes are required before that is defensible?
Read the worked answer
- Define the metric explicitly - e.g., 'net sales after refunds, excluding gift-card redemptions' - and reconcile it to ERP monthly so managers see a stable, closeable number.
- Fix the cutoff - align the POS batch window to a defined day boundary (e.g., midnight local) and reprocess late arrivals, or publish on a T+1 closed basis rather than intraday.
- Separate timing from performance - a manager should never be penalized for a transaction that fell into the next batch. Show a 'restated prior day' line.
- Data lineage and ownership - document source-to-report mapping, assign a data steward, and log ETL failures.
- Reconciliation tolerance - set an acceptable variance threshold (e.g., <0.5%) with an exception report; investigate breaches.
Without these, managers will optimize to a number that disagrees with their P&L, destroying trust in both.