From manual spreadsheets to a smart, auto-populated dashboard
Proposal for Studio 1 — New York6,500+ rows manually maintained — someone has to look up each Square payment and type the amount into the correct cell every day
No instant search — finding a customer means Ctrl+F through thousands of rows, hoping the spelling matches
Errors go undetected — if a Square payment gets assigned to the wrong location or category, nobody knows until reconciliation
Finished accounts clutter the view — customers who've paid in full are still taking up rows alongside active debtors
No real-time visibility — to know who's missed 3+ payments, someone has to manually scan month columns
Unmatched payments invisible — if a Square payment doesn't match any customer in the ledger, it's lost
| Feature | Current (Manual Sheet) | Option A (Sheet + Dashboard) | Option B (Clean + Dashboard) |
|---|---|---|---|
| Daily payment entry | Manual lookup + type | Automatic from Square | Automatic from Square |
| Search by Reference ID | Ctrl+F (slow) | Instant search bar | Instant search bar |
| See only active accounts | All 6,500 rows mixed | Filter: Active only | Default view (archived hidden) |
| Spot delinquent accounts | Manual column scanning | Auto-flagged (3+ misses) | Auto-flagged (3+ misses) |
| Unmatched payments | Invisible | Flagged immediately | Flagged immediately |
| Payment errors | Found months later | Flagged same day | Flagged same day |
| Customer payment timeline | Scroll right across columns | Click to see visual timeline | Click to see visual timeline |
| Make adjustments | Find cell, type value | Button: Write-off / Refund | Button: Write-off / Refund |
| Google Sheet still usable | Yes (it's the only thing) | Yes (it's the data store) | Yes, but dashboard is primary |
| Completed accounts | Still showing | Filterable out | Auto-archived |
| New customers | Manual row creation | Flagged for manual add | Auto-added from daily sales |
| Name | Reference | Sale | Balance | Last Payment | Misses | Status |
|---|---|---|---|---|---|---|
| Leotiltia Vargas | A1540166 | $4,000 | $1,400 | Jan 2026 | 0 | Active |
| Brandon Campbell | P1703433 | $4,000 | $4,000 | Nov 2025 | 4 | Delinquent |
| Xavier Powell | B1563449 | $3,500 | $0 | Mar 2026 | 0 | Paid Off |
| Mikell Nesbitt | B1638551 | $4,500 | $4,500 | Aug 2025 | 7 | Monterey |
Click any customer in the table to see their full payment history:
Sale: $4,000 | Deposit: $450 | Balance: $1,400 | Monthly: $100
Once the debt ledger is auto-populated, these reports become available instantly — no manual data gathering needed:
How much is owed in 0-30, 31-60, 61-90, and 90+ day buckets. Instantly see how much debt is "fresh" vs "stale".
Based on active accounts and their monthly payment amounts, project expected collections for the next 3 months.
Customers with 3+ consecutive missed payments — flagged automatically. Includes Monterey eligibility (5+ misses, 120+ days).
Payments on wrong Square locations, unmatched payments with no customer record, duplicate entries. Catch mistakes the same day.
Total write-offs by period (monthly/quarterly/yearly). Track how much debt is being written off and trending direction.
Which collectors are recovering the most? Payment volume, success rates, and average recovery per collector — pulled from the same data.
Customers within $200 of paying off their balance. Quick wins for the collections team — one more payment and they're done.
Monthly success rate over time — are more payments succeeding or failing? Overlay with team changes, pricing changes, or seasonal patterns.
How many new customers entered the collections system this month vs how many existing accounts were collected on?
Based on historical success rates per miss number, predict how much of the outstanding $2.4M is realistically recoverable.
To build this right, we need to understand what Mike actually uses the debt ledger for: