SQL accounts-receivable ageing and a collections dashboard

Cascade Lab Supply sells pipettes, reagents and safety equipment to universities, hospitals and biotech start-ups in Washington, Oregon and British Columbia. Revenue is about US$22 million a year across 1,400 customers, billed in US and Canadian dollars. Receivables have grown to US$4.9 million and days sales outstanding (DSO) has crept from 46 to 61 days in a year. The bank that provides the revolving credit line has asked for a monthly ageing report it can trust.

Today, the credit team exports invoices from the ERP to Excel every Monday and builds the ageing by hand. It takes most of a day, it never agrees with the ledger control account, and partly paid invoices and unapplied credit notes are often counted twice. The two collectors spend their time on whoever shouts loudest rather than the largest risk.

The controller, Marcus Lee, wants you to: - design a small SQL database of customers, invoices, payments, payment allocations and credit notes, and fill it with a realistic synthetic dataset; - write SQL that produces an open-item ageing in standard buckets, in both currencies and translated to USD; - calculate DSO and other collection metrics by month; - reconcile the ageing to a ledger control total and explain every difference; - produce a ranked daily collections list and a simple dashboard.

Done looks like a repository of SQL scripts that run end to end on PostgreSQL or SQLite, a dashboard, and a one-page memo to Marcus. The ageing total must agree with the control account to the cent, and the collections list must put the riskiest US$1 million at the top.