01
The dilemma
Grant-funded youth employment programs live or die on evidence. Every hour a participant works has to be recorded, priced, verified against a compliance file, paid, and then reported back to the funder. TYEP was doing all of that inside a single Excel workbook that had grown to more than sixty employer tabs.
What was breaking
- Sixty-plus tabs, one file. Each employer partner had its own sheet, with its own quirks in layout and formula. Nobody could see the program as a whole without manually merging them.
- Payroll calculated by hand. Hours multiplied by rate, tab by tab. Floating-point drift and copy-paste errors produced payment figures that did not reconcile against the funder's expectations.
- Compliance documents went missing. Work permits, eligibility forms and employer agreements were tracked in the same workbook, which meant a missing document surfaced at audit rather than at intake.
- No burn-rate visibility. Program managers could not answer the single question the funder cared about most: how much of the $835K has been spent, and how much is committed?
- Risk found too late. A participant who stopped showing up was noticed weeks later, by which point the placement and the funding attached to it were already lost.
The cost of standing still
A grant-funded program that cannot evidence its spend does not simply run inefficiently, it risks the next funding cycle. Every unverified document and every reconciliation error was an audit finding waiting to happen, against a $835,000 award and the program's credibility with its funder.
02
The idea
The obvious answer was a bespoke payroll application. We argued against it. TYEP did not need payroll software; they needed the program itself, participants, employers, placements, hours, documents and money, held in one relational structure with the funder's reporting requirements designed in from the start. A CRM platform already gave us the record model, the permissions, the audit trail and the reporting engine. What it did not give us was payroll logic, and that was the part worth building properly.
The principles we worked to
- Participant as the anchor record. Everything, employer, placement, payroll period, compliance document, intervention, hangs off the participant, because that is the unit the funder reports on.
- Calculation inside the platform, never in a formula bar. Native formula fields compute hours × rate at the record level, so the number is the same everywhere it appears.
- Compliance as a gate, not a folder. Missing documentation blocks progression rather than sitting unnoticed until an audit.
- The dashboard is the deliverable. If the executive director cannot see burn rate without asking anyone, the system has done its job.
What we ruled out, and why
| Option considered | Why we did not take it |
|---|---|
| A bespoke payroll application | Six-figure build, long timeline, and it would have solved the smallest part of the problem while leaving participant, employer and compliance data scattered. |
| A better-structured Excel workbook | Cheapest option, and the one they had already tried twice. Spreadsheets have no permissions, no audit trail and no way to stop two people editing the same row. |
| Off-the-shelf grant management software | Priced for large foundations, rigid around a workforce-development model, and none of it handled hourly payroll for minors. |
03
The build
The build ran as one relational system with five working parts.
Payroll data model
Custom Payroll Records and Payroll Periods modules sit beneath each participant. A period opens, hours are entered against an employer placement, and the record closes with a verified total. Native formula fields handle hours × rate, eliminating the manual arithmetic that produced the original errors.
Employer relationship layer
Each of the sixty-plus employers became a real record with its own placements, contacts, agreements and payment history, rather than a tab. Weekly employer summaries now generate and send automatically.
Compliance tracking
Document requirements are defined per participant type and tracked to completion. Pending verifications surface on the dashboard, which means an incomplete file is a visible task rather than a future audit finding.
AI anomaly detection with Zia
Rules watch for the patterns that predict a failed placement, a participant logging zero actual hours four weeks after start, hours falling off a cliff, an employer going quiet. Program managers get the signal while there is still time to intervene.
Executive analytics
A Zoho Analytics dashboard tracks grant spent, remaining balance, committed-but-unpaid, pending verifications and participant status. It is the view the funder asks for, available without anyone compiling it.
04
The impact
| Measure | Before | After |
|---|---|---|
| Program record | 60+ Excel tabs, one file | One relational system, 415+ participant records |
| Payroll calculation | Manual, per tab, prone to drift | Native formula fields, calculated once |
| Grant burn visibility | Reconstructed on request | Live on an executive dashboard |
| Compliance status | Discovered at audit | Surfaced as pending tasks in real time |
| Employer reporting | Manual Excel updates | Automated weekly summaries by email |
| At-risk participants | Identified retrospectively | Flagged by anomaly rules for early intervention |
Beyond the numbers
- Errors designed out. Manual data entry and hand-calculated payroll were removed from the process rather than checked more carefully.
- Audit readiness became a state, not a project. The evidence a funder asks for is assembled continuously instead of reconstructed under deadline.
- Managers intervene earlier. AI-driven flags move the team from reporting what went wrong to preventing it.
05
What comes next
The engagement continues. These are the next moves on the table.
- Participant outcome tracking beyond program exit, placement retention at 90 and 180 days.
- Employer scorecards ranking partners by completion and retention rate.
- A funder-facing portal so grant reporting becomes self-service.
Engagement delivered by the Ideentrix founding team. Technologies, scope and outcomes as recorded at project close, published with the client's consent.
