In the fall of 2008, I moved into a new role with Denver Public Schools (DPS). My job was to manage all of the school financial supports and processes. Budget development was right around the corner and, for the first time, I would be managing the process to support 150 schools in building their site-level budgets.
The process for DPS, like many other districts, is Excel-based and involves creating individual forms for each school, emailing these forms to principals, coaching them through form completion and aggregating all the spreadsheets back into a consolidated format to become part of the district-wide budget; a process we often refer to as the Spreadsheet Shuffle.
I led my team through a series of major revisions to the Excel templates in order to make the forms more user-friendly and transparent for our school leaders to complete their site-level plans. We had high hopes of a smooth process, but here’s how it went down:
1 Week Before Forms Distribution
With just a few days remaining until budget forms were to be distributed, we were completing our final testing to make sure that all the tabs in the workbook— each containing hundreds of complex Excel formulas— were working correctly. We were also testing the macros used to turn one master spreadsheet into 150 individual forms. What we discovered was that, while generating a full set of forms should have been a relatively quick task, we were actually looking at about a 3-hour run time. From there on out, any template changes requiring a new set of forms to be run would take a significant amount of time.
It was also around this time that my lead analyst, who had the primary responsibility in form development, went into labor 5 weeks early, largely due to stress.
1 Day Before Form Distribution
We were moving along smoothly. With 24 hours to go, forms were functioning perfectly and the team was calm and ready for action. I was called into the Superintendent’s office that morning. It was decided that the formula used to set school allocations was going to change “just slightly”. This “slight change” was easy enough to update in the master spreadsheet, but resulted in the need for modification of the form template, more testing to make sure we had it right, and hours of waiting to re-run forms, but by 10:00pm that night we’d done it. We were ready for form distribution.
Day of Form Distribution
My team and I arrived early the next morning, albeit a little groggy from the late night prior, to make final checks. At 9:00am we were ready to hit send to distribute 150 forms exemplifying all our hard work to school principals. The moment of truth was upon us. SEND – “Outlook Error – File Size too Large”.
Back to the drawing board to find a way to make the forms less complex and shrink the file size. Five hours later we were again ready to send. This time it worked! Budget forms were now in the hands of all our school leaders. Success! Well, almost …
9:00pm Night of Forms Distribution
I was out to dinner celebrating a birthday and a job well done when my cell phone rang. It was the CFO, my boss. He informed me that a calculation error in the forms had resulted in the misallocation of $2 million dollars of funding to school budgets. Dinner was cut short, and I rushed home to try to figure out what had gone wrong and try to recall all the emails in Outlook (that always works, right?). I isolated and resolved the error and early the next morning had a fresh (and accurate) set of forms to re-distribute. I drafted an apologetic email to principals explaining that their budget was incorrect and they did not have all the funds available that had been previously reported to them. I kindly asked them to discard the forms they’d received previously and use the new version in their inbox.
After Form Distribution
With accurate forms now in hand, budget development was underway and principals worked through completion of their site-level budgets. The process went largely as planned, but wasn’t without hiccup. Many principals worked through their budget on the incorrect form and had to repeat the process at the lower funding level, some of the complex formulas we’d used to build the forms didn’t work in earlier versions of Excel that were commonly installed on district computers resulting in a sea of #NA errors across the budget forms. Each time we came across one of these issues, forms had to be emailed back and forth between finance and the school to correct the problem exacerbating the version control issues.
Reconciling the Spreadsheets
Once all the school budgets were completed, we began the process of aggregating the budget data from each spreadsheet and completing an extensive reconciliation to ensure account codes were accurate, funding rules had been followed and required purchases had been accounted for. The validation process took 2 weeks longer than estimated, but we ultimately had accurate and balanced budgets for all 150 schools.
A Better Way
While we met our goal in the end, I knew there had to be a better way to build budgets. There had to be a process that was far more efficient for me and my team, but also easier to follow for our school leaders. There had to be a way to shift the focus of budget development away from accounting rules and Excel formulas and toward the strategic alignment of resources to further student achievement. The process did continue to improve year after year, but without a tool for participants to collaborate during the process and a seamless way to make updates year to year, the focus on student success during the process could never be fully realized.
About the Author
Kate Kotaska is a Senior District Partner for Allovue, Inc. Before joining Allovue, Kate spent the majority of her career in Denver Public Schools shaping the district’s resource allocation model and building a backbone of financial support known as the Financial Partner Network. As DPS’ Executive Director of Budget and Finance, Kate was responsible for streamlining the district’s budget process to maximize stakeholder engagement and transparency. Kate earned her undergraduate degree from Pace University in Pleasantville, NY.