Case Study: Replacing a Nonprofit's Payroll Allocation Spreadsheet

Short version: A grant-funded social-services nonprofit split every paycheck across about two dozen funding sources — federal, state, and local grants — in an Excel workbook that needed manual adjustments to tie out. We replaced it with a payroll allocation tool that won't close a pay period until the numbers tie out on their own. There's no AI in it, on purpose. It's live in beta.

The before: a workbook that needed manual adjustments

Payroll at a grant-funded nonprofit has two halves. One is paying people, which the payroll provider handles. The other is answering the question every funder eventually asks: which grant paid for that time? Most of the staff work across several programs, so each person's pay is split by percentage across the grants that fund them — wages, employer taxes, workers' comp, and health insurance alike.

All of that lived in one large Excel workbook, updated twice a month from the payroll provider's register. It had grown the way spreadsheets grow, with the same rule written in several places — a formula on one tab, a different formula on another — that had drifted apart. Only a few of the funding sources had a grant report tab at all.

And the totals didn't always tie on their own. The journal entry has to match net pay to the penny, because net pay is what actually left the bank account. When it didn't, the workbook needed a hand-typed adjustment so the bottom line came out right — what accountants call a plug.

Plugs aren't carelessness. They're what a spreadsheet produces when you split money by percentages. Divide one paycheck across four grants and you get fractions of a cent; round each share on its own and the shares no longer add back up to the paycheck. Repeat that for every employee and every pay period, and someone ends up typing in a number to make the difference go away. The total comes out right, but nobody can say which grant those pennies belong to.

None of that is a criticism of the people running it. It's what happens to any spreadsheet that does an accounting system's job for years. If you're weighing the same move, here's how to replace a spreadsheet with a web app without losing your data.

What we built

A web app the payroll team signs into. HR keeps people, hours, and grant splits current. Accounting imports the payroll and closes the period. The executive director can see everything and change nothing. Each pay period runs through the same steps:

• Import the register, in whatever form it arrives. CSV, Excel, a PDF, or a scanned PDF. A scanned register is just pictures of pages, so scanned PDFs go through OCR (optical character recognition) on the tool's own server and must pass the same tie-out. The register never goes to an outside service.
• The tie-out gate. Every figure read from the register has to add up to the register's own printed totals: the employee count, every column, every deduction code, and gross minus taxes minus deductions equals net on every row. The tolerance is zero. Off by a cent, and the import is rejected with every reason listed. OCR misreads digits silently — a 3 becomes an 8 and nothing looks wrong — but if the misread changes any total, the tie-out catches it.
• No guessing about people. Employees are matched by payroll ID, not by name or row position. A near-miss on a name is a suggestion for a person to confirm, never filled in automatically.
• The split. Each employee's wages, employer taxes, workers' comp, and health insurance are divided across their funding sources. Every amount is stored in whole cents, and the split uses a method that guarantees the pieces add back up to the whole. There's no leftover penny, so there's nothing to plug.
• Sanity checks before close. Arithmetic can balance and still be wrong, so the tool also checks whether the figures make sense as payroll — a negative paycheck, a deduction that suddenly jumps, taxes far from what the tool calculates. The serious ones block the close until someone fixes the figure or writes down why it's right, and that reason is kept.
• A journal entry that must balance. The output is an accounting journal entry coded to the organization's QuickBooks accounts and classes. It has to balance to the penny against net pay with no hand-typed adjustments, and the tool refuses to produce one that doesn't. There's no plug, and no place to put one. Once Accounting posts the period, it's locked against edits.
• Per-grant cost reports. For any funding source, not just the few that had a tab: wages, employer taxes, benefits, payroll fees, and workers' comp charged to that grant, person by person. Grant reports and the journal entry come from the same calculation, so they can't disagree. Everything prints or downloads as Excel, PDF, or CSV.

Behind all of it are about 390 automated tests. The ones I care about most take a clean register, change a single digit, and confirm the import is refused. A check that has only ever been seen passing could be a check that always says yes. The proof is watching it fail when it should.

Why there's no AI in it

Most of what we build has AI in it, and for paperwork it follows one pattern: AI reads the paperwork and drafts; a person approves; the software does the math. The house rule is "the model transcribes the numbers; the server does the arithmetic."

This job is almost all arithmetic. Splitting a paycheck across grants has one right answer, and an auditor needs to see exactly how you got it. That calls for deterministic code: the same inputs give the same outputs every time, and every step can be traced. A language model that's right most of the time can draft an email. Payroll that's right most of the time is wrong.

The one step where AI could plausibly help is reading a scanned register. We used conventional OCR instead and put the tie-out gate behind it, so a misread digit that changes any total stops the import instead of reaching a grant report. There's a second reason, too. The register carries every employee's wages, garnishments, and health deductions, and the client's standing rule is that it doesn't go to a hosted AI service. If they ever want that to change, it's their decision to make — not something we slip in during an update.

Knowing when to leave AI out is part of using it well. Our practical guide to AI for small business covers both sides.

Where it stands

The tool is live in beta. I don't have a before-and-after number to give you — we haven't measured one, and I'm not going to invent a percentage. What changed is structural: the journal entry balances without anyone typing in a correction, and when a pay period doesn't tie, the tool says where and why.

Is something like this worth it for your nonprofit? If you have a handful of staff on one or two funding sources, probably not. A careful spreadsheet, or the class tracking in your accounting software, may be all you need. It makes sense when staff are split across many grants, funders audit what you bill them, and someone is typing plugs to make the month close. More on what we build for grant-funded organizations is on our nonprofits page and in the Umbrella HQ case study.

Frequently asked questions