The finance team at a large school district had lost the developer who understood the allotment system to retirement. They needed to calculate staff-position and spend allocations for nearly 200 schools, and had only a shared spreadsheet and a Perl script thousands of lines long. They couldn't operate the process independently, and the person who could safely change the code was gone.
I was asked to work out what should replace it. I worked with the finance team and its director to develop the approach, then designed and built an application they could operate themselves. It supported roughly ten years of allocation cycles.
Working out what needed to change
I met with the full finance team initially, then several times with its director to understand their process and what was making the work difficult.
The calculations represented years of accumulated knowledge. Some school-specific exceptions, though, were still held in people's memories. The script produced flat files, and working from a shared spreadsheet introduced errors. Even changing an input required help from a developer.
There was useful work to preserve here. I kept the calculation algorithms that still served their purpose and rebuilt the workflow around them. Finance's underlying allocation process stayed largely intact; the changes gave staff a better way to carry it out.
That choice also gave me an established set of results against which to check the replacement.
Building around finance's work
The new system was a database-backed web application. Finance staff could enter and manage their inputs directly, without having someone modify the script for them.
I brought the school-specific exceptions into the common calculation engine so they would be applied consistently. Staff could compare allocations across years, and results could be reconciled with Oracle E-Business Suite. Schools had permission-controlled access to their own allocation information.
These decisions came from the work finance needed to do. Historical comparisons helped them review numbers. Reconciliation connected the application to the district's financial system. School-level access gave each school a way to see its information.
I owned the solution and its implementation, working with finance leadership to keep it aligned with their needs.
Checking the numbers
Before deployment, I compared the new application's outputs with the old system's flat files, school by school and year by year.
I held deployment until the results matched. The existing calculations gave us a way to verify that the new application preserved the behavior finance depended on while changing how staff operated it.
What happened after delivery
The application went on to support approximately a decade of allocation cycles.
The finance director and several team members independently contacted me to describe the difference it made in their daily work. They reported substantial time savings and the elimination of errors caused by the shared spreadsheet. We didn't measure a percentage reduction in time or an error rate, so I can't attach those figures to the result.
What I can say is that finance could run the process directly. The allocation rules, including school-specific exceptions, were captured in the application. Staff had historical comparisons and financial reconciliation available in the same workflow, and schools could view their own numbers.
The system remained useful through roughly ten years of the work it was built to support.