The spreadsheet that ran 51 schools

In 2021 I opened the sheet Snack Pak 4 Kids San Antonio used to collect each month's orders and found a grid of typed-in numbers with nothing checking any of them. This was the second email this week; the first had apparently been tallied up incorrectly. Every school coordinator worked out an order by hand, typed it in, and hoped it was right.

My wife and I were two of those coordinators. Snack Pak sends food home for the weekend with students their teachers identify as needing it, and for the 2020-2021 and 2021-2022 school years we handled storage and weekly distribution at Hawthorne Elementary. I already knew what the hand math looked like from the school's side, because I had built a tracker so we wouldn't have to do it: students served, dietary restrictions, packs on hand, and what to order next.

What I hadn't seen was the other end. The program's two leaders took every school's typed numbers and turned them into warehouse pulls and delivery routes, and that took them hours every week.

Why the order math was harder than it looked

One school's order depended on four things: how many students were enrolled, how many had dietary restrictions, how many weeks the delivery covered, and which of those weeks were peanut butter weeks.

Then the units. Packs arrive in totes, and totes go out on pallets. Pallets & totes get delivered monthly, packs get delivered weekly. But totes of “regular” packs are different size than totes of “peanut butter” packs, and also different from the dietary restricted totes. Different quantities of packs per tote, and different capacity of totes per pallet made things complicated. And while the warehouse is trying to think all this through, coordinators are just thinking in “kid” units. Rounding up for orders, monthly reconciliations, on top of the logistics of getting the volunteers for distribution to teachers. There were so many places for the number to go wrong, and every conversion done by hand in a shared spreadsheet is a place for a number to drift.

This math was being done by volunteers for 4,000 children across more than 50 schools. Would you trust the total?

What I built

I offered the leaders a version of my tracker for the whole program. We met so I could get their requirements, I built it, we reviewed it together, and I revised it from their feedback.

Each delivery starts with an export from the program's own system for tracking schools, coordinators, and enrollment. For every school the workbook calculates three tote types from enrollment and allergy counts: regular packs, peanut butter packs, and allergy-safe packs. It rounds up to whole totes based on how many regular and peanut butter weeks the delivery covers. Packs per tote and totes per pallet are settings rather than numbers buried inside formulas, so when packaging changes, one cell changes. A coordinator can still override any school's count by hand, because the person at the school sometimes knows something the export doesn't.

The part I would point a skeptic to is the check. Every school is matched against the master list and against the route sheets, and flagged if it is missing or appears twice. A missing school means hungry kids for a month. A duplicate is a truck carrying a delivery nobody ordered. Both are cheap to catch in a spreadsheet and expensive to catch on a loading dock.

Routes came last. There are up to ten route sheets plus a warehouse pickup list. Pick a school on a route and its address, up to four contacts, its delivery instructions, and its tote counts fill in from the master list. Each route totals its load in full pallets plus leftover totes, and a summary sheet says it the way someone in a warehouse would: "6 pallets and 26 totes."

What it did, and what I can't tell you

The leaders told me it saved them about 10 hours a week. That is their estimate, not something I measured. Taken at face value, it is a quarter of a 40-hour week handed back to the two people running the program.

By February 2024 the workbook was running deliveries for 51 schools and 3,288 children across nine routes and a warehouse pickup list. Twenty-six of those children were on allergy-safe packs, which is under 1% (26 divided by 3,288 is about 0.8%). That small number is the reason the allergy logic had to live in the workbook instead of in someone's head. A category that rare is the one a tired person forgets.

Did it mean fewer shortages, or more kids reached? I think so, and I can't show you either one. Nobody counted shortages before or after, and I am not going to put a number on something I didn't measure. I do know that the number of obligatory “corrected” emails became exceedingly rare. Which made me more confident that things were running smoothly.

We moved to Vermont in August 2023. I kept maintaining the workbook remotely into 2024, adding improvements and repairing the occasional formula a user had typed over. That last part is the real cost of running a system in a spreadsheet. Anyone with edit access can break it, and eventually someone will, without meaning to.

Would I build it in a spreadsheet again?

For a volunteer program with no budget and no developer, yes. The people using it already knew Excel, and anyone curious about a number could click the cell and see where it came from. What it lacked was protection from its own users, and a backup for the one person who understood it. But more likely today I would take that spreadsheet and turn it into an app. And that’s something I’ve done multiple times already.

If you are running something like this on a spreadsheet, ask who fixes it after the person who built it moves away. For Snack Pak, for a while, the answer was me, from Vermont.