by Chris Dietrich

Anyone who works closely with my chief financial officer, Chuck McCullagh, knows that he loves spreadsheets. In the summer of 2011, during my first week as the new director of financial aid at Williston Northampton School (MA), I remember sitting in a meeting for over two hours, combing through what seemed an endless pile of data and spreadsheets. The information tracked everything from enrollment numbers, tuition dollars, and financial aid history to capital expenditures on the school’s facilities. To be honest, much of what Chuck and I covered during that first month escaped me, as far as the task I had at hand.

At that time, Williston Northampton had a higher enrollment than we do today, but our financial aid as a percentage of our gross tuition revenue (aka, our discount rate) was disproportionately and unsustainably high when compared to our peer schools. Unlike some of my colleagues at other schools, my first task as director of financial aid was not to find more students to whom we should provide financial assistance, but rather to create a systematic way to bring in mission-appropriate students using a leaner financial aid budget.

After several weeks of what felt like a daunting task, I recall an “aha!” moment during one of our meetings. One of the spreadsheets Chuck had created parsed out enrollment and financial aid numbers for each grade, broken down by day and boarding. The one-page report showed the current year on one side, then moved those numbers forward to the next year, with estimates filled in for new students and potential new financial aid allocation. These sections were linked visually with an arrow. Examining the information, we were able to settle on a budgetary target for overall financial aid for the upcoming year. I had already been doing some work on my own, analyzing our recent past history with enrollment, financial aid allocation, and average net tuition dollars per student per grade, trying to decipher patterns or trends that I could address to bring down our overall discount rate. I distinctly remember looking at Chuck’s enrollment chart during one of our meetings and thinking “I’m not sure how, but I’m positive this can help me.” Ultimately, we called this report the “Arrow Chart.”

In my work at other schools, particularly those with larger enrollments and larger financial aid budgets, the approach used was one where a general financial aid budget was allocated to each individual class. Now, using the Arrow Chart, I knew I had a good starting point to implement a similar system for the admission process at Williston, but with much greater detail. Chuck and I still had a lot of work to do to convince the head of school and the board of trustees that a slightly lower enrollment and lower financial aid allocations ultimately meant more net tuition revenue. After analyzing a considerable amount of current and historical data, particularly looking at the net revenue patterns at Williston over the previous 10 years, we provided convincing evidence that a lower enrollment level, with a moderated financial aid budget, was the correct path for our school. The board of trustees approved a strategic enrollment adjustment that brought our enrollment targets and financial aid budgets to sustainable levels. That approach became an important part of the school’s 2014 strategic plan.

While we were excited to have the support of our board of trustees, I knew that a big part of this plan’s success was dependent on how I would implement it with our team of admission officers. Since that first year we used the Arrow Chart, the admission office has focused on both the enrollment targets and the financial aid budgets, broken down by class and derived from the work that Chuck and I do each fall. The Arrow Chart is updated for the upcoming year and guides the selection of students to whom we will offer acceptance, as well as financial aid. Chuck and I get together during the second or third week of the school year, after he has completed his preliminary work of creating a budget for the next year. We update the enrollment target numbers and financial aid budgets for each class, for day and boarding student categories. These then become the enrollment and financial aid goals the admission office must achieve to meet our budgetary targets.

I take the dollar amounts allocated to each subgroup (9th grade day students, 9th grade boarders, 10th grade boarders, etc.), and incorporate yield projections based on recent history (four-year average on yield for accepted students, with and without financial aid in each subgroup). This helps us to arrive at the number of acceptances and the total financial aid to extend to families as we target each of these specific subgroups for March 10th decisions. With the Arrow Chart as a guide, the entire admission office meets and makes final decisions about who is to be accepted and the financial aid dollars associated with those acceptances. The admission team, in many instances, has to make the hard decision to move an applicant to either the financial aid wait list or a regular wait list. The applicant review meetings are long and sometimes difficult. However, our data-supported process has allowed us to be much more deliberate about the admission and financial aid decisions we make. It has also helped us to evenly distribute financial aid more strategically, with appropriate enrollment levels and financial aid per classes, and has provided guidance in achieving our annual enrollment targets. The use of the system has also allowed us, year to year, to consider enrollment and financial aid shifts from grade to grade, and has guided decisions to redistribute financial aid dollars to other classes if we feel it is appropriate.

One of the most beneficial byproducts of our system is that it has given the admission team a more transparent understanding of how tuition dollars are utilized to support Williston’s overall budget, while also providing business office colleagues with more insight into the difficult process the admission office goes through to select new students and allocate financial aid. The admission officers see the level of analysis in the Arrow Chart, and I believe they have developed a better appreciation of the challenges that Chuck, as our CFO, has to go through to achieve a balanced budget each year. Because the admission office and business office work so well together, we have even taken the step of having Debbie Lennon, the assistant controller in the business office, join our financial aid committee during the winter to help us to derive financial aid awards and deliberate on award decisions.

The efforts Chuck and I make, coupled with the work of the financial aid committee, provide tremendous coordination and cooperation between our business and admission offices in the annual attempt to achieve our enrollment and tuition revenue goals. I cannot imagine leading my admission team through this arduous process without the collaboration that exists between our offices.