Refining Your Data

By Net Assets posted 09-19-2016 06:23 PM


Financial Management |

Schools are using reports and models that move school leaders away from minutiae and toward mission-motivated planning and action. “We tell a story.”

Article By Donna Davis

From the Sept/Oct 2016 Net Assets

It’s fall, the perfect time to give the varsity baseball field a little TLC in the form of reseeding and fertilizer. As the business officer, you want to make sure that the field maintenance company the school chooses is charging a fair price for materials and labor. For Alan Comrie, controller for Kingswood Oxford School, the relevant information is a computer button-click away — down to the square footage and in high-resolution, full-color photos no less.

Comrie uses an Excel spreadsheet layered not just with numbers and statistics, but also with photos, schematics and floorplans of every inch of the West Hartford, Connecticut, campus. His method of presenting data reflects his belief that data doesn't have to be boring, as in lines and lines of tiny numbers that cause staff, board and donor eyes to glaze over. Instead, he and other business officers are finding creative ways to harness data in interactive, real-time and forward-looking presentations that resonate with different audiences and allow school leaders to plot strategies and take actions on everything from tuition to facilities repairs.

“The whole idea is to expand our world,” says Comrie, who also serves as director of financial aid, technology adviser and strategic adviser to the head of school at the 508-student day school for grades 6 – 12. The fact that business officers routinely collect and analyze data is not tantamount to using it as effectively as they could. “We have huge assets that are underutilized in our schools. Let’s talk internally to people in our school as a group to look at data in ways that we haven’t looked at it before.”

Clarity over Complexity

At Kingswood Oxford School, Comrie set out to flip what he saw as a preference for complexity over clarity. “My thought was to develop a model that was so easy anyone could use it.” As a result, his Property Management Model contains incredible detail — for instance, how much it will cost to replace a precise section of sidewalk — but is assembled so that the “complexity stays under the hood.”

He developed the model using “hourglass methodology, with the top being the reporting [of data] and the bottom the user interface. Both have to be robust.” The complicated pieces — formulas, logic and threading of concepts of logic — reside in the middle, invisible to the end user.

To illustrate the school’s facilities and facilitate use, Comrie created Excel spreadsheets that feature not just numbers, but also buttons, hyperlinks and photos. A user can bring up the aerial photo of the campus and click on any of these features to zero in on information about a particular building, athletic field or grounds feature, down to the location of a boiler’s shutoff location, complete with a close-up photo.

“The model breaks down the silos and makes our offices talk,” Comrie says. “We have much better data for financial, student body and marketplace forecasting, and we are positioned to make decisions in a timely manner.”

"The model breaks down the silos and makes our offices talk. We have much better data…and we are positioned to make decisions in a timely manner."

Alan Comrie
Kingswood Oxford School

In addition, the facilities model has made the school more efficient at handling its deferred maintenance needs, among other matters. Knowing the exact dimensions of a field means the school can check a vendor quote for fertilizer against the amount of square footage. Kingswood Oxford has also integrated the model into its safety and security plan. “We know all the shutoffs, all the points of entry and egress, and the exact dimensions and layout of every room,” Comrie says. If an incident occurs, “we can hand that [information] over to the authorities.”

In addition to the facilities model, Kingswood Oxford administrators and trustees have several other ways to access information — and can dig as deeply as they like into the data. This access has several possible potential outcomes. For instance:

  • A quarterly financial package and a capital campaign report sheet allow trustees to talk strategy rather than numbers. (See the online version of this article for a downloadable handout from that package.)
  • An “All-Student Data Analysis” includes a live table of contents on a variety of topics, from towns where students live to their travel time to school. The data come from the admissions office and general ledger; Comrie arranged the model using just two formulas — count-ifs and sum-ifs.

“We tell a story,” Comrie says. “Instead of just showing data in tables and columns, I’m all about pictures and stories.”

Real-Time Admissions Dashboard

St. John’s-Ravenscourt School also uses Excel to give administrators an up-to-the-minute view of its admissions data. In Winnipeg, Canada, the 800-student K – 12 day and boarding school feeds data from its online enrollment contract system into the dashboard’s spreadsheets. “It has eliminated hours spent on reconciling numbers and compiling results,” says Jillian Lamothe, assistant head of finance and operations. The dashboard has also enabled administrators to respond quickly to issues such as possible under-enrollment in a grade, families who are lagging on signing enrollment contracts or a potential imbalance between the number of boarders and available housing.

Ryan Johanneson, information systems manager, and Paul Prieur, director of admissions and marketing, worked with Lamothe to design the system. The dashboard is a constant feature on their desktops. “It’s a little addictive,” Lamothe says. “We joke that we are auto-refreshing it by the moment.”

For admissions, a quick glance tells Prieur how many students have accepted enrollment offers, individual class sizes and overall enrollment versus projections for next year, among other data points. “For marketing, I’m looking at those grades where we don’t necessarily have the number of applicants, and then we adjust our marketing message accordingly,” he says.

Johanneson, who did the programming for the dashboard, advises schools to talk with key stakeholders to learn what types of information they want to see and to know the range — and limits — of their online enrollment system and data-gathering. Although the trio continues to fine-tune the model based on requests from users, the school year dictates when a particular dashboard’s time is done. “The fact that it is real-time is another important aspect because at one point we have to lock it and start a new year,” he adds. For St. John’s-Ravenscourt that point occurs in September.

Data-Driven Diversity

Sometimes a school requires a more detailed presentation of data for a particular issue. At St. John’s Prep, Cindy Fanikos, assistant head of school for finance/CFO, and Headmaster Edward Hardiman knew that six new trustees would be starting their board terms during the 2015 – 16 school year. At the same time, a major challenge around accessibility and affordability faced the school, which has 1,450 boys in grades 6 – 12.

For the June board retreat on that topic, Fanikos and Hardiman decided to create a whitepaper that would provide new and veteran board members with critical background on tuition increases and assistance over the past 15-plus years. “If we were to come to a conclusion on how we approach tuition increases for the future, we first had to understand how it had been done in the past,” Fanikos says.

Fanikos pulled together the historic data on tuition increases, both gross and net, as well as tuition assistance information, including percentage of students receiving assistance, average awards, top 10 towns receiving aid, percentage of need met and average disclosed income of families. These data points helped drive the discussion at that board retreat. Moreover, former trustees contributed institutional knowledge, such as providing context for certain spikes in tuition and dips in enrollment, and explaining a protracted attempt to keep tuition increases between 3 and 4 percent, Fanikos says.

From these discussions came a decision to “dive into different income brackets” to look for certain trends or barbell effects, “and to discuss whether we’re missing some families who think they can’t afford our school,” she says. The data also are allowing school leaders to hone in on issues unique to St. John’s and the area, including what defines a middle-income family in the Boston area.

There’s more data in St. John’s future. Fanikos is developing an “accessibility dashboard” that will help track important trends such as the number of applicants from lower-income cities and how many students leave the school for financial reasons. “In this way,” Fanikos says, “we hope to provide a snapshot as to our success in providing accessibility to our school.”

Board Strategy, not Minutiae

No matter how much data a school presents to its constituencies, there’s always more data. The trick is knowing how much to present and how to present it, especially when working with trustees, says Robert Sedivy, a retired CFO and now independent school consultant.

Simplicity and clarity take top priority, he says. “Trustees are sometimes overwhelmed by data if it is not presented well.”

He recalls a consulting project where one trustee was upset about a line item showing the library at 21 percent over its budget. Delving into the numbers, Sedivy found that the 21 percent represented $400. Moreover, that money was used to replace outdated reference books.

“The $400 decision was made for all the right reasons,” Sedivy says. “Why did it become a board issue?” The problem, he contends, was line-item budgeting at the board level. Too much detail can lead a board to stray into non-strategic discussions. Instead, he advocates a simpler, quarterly format for reporting budget results. Budget discussions are fairly simple, he believes. What’s harder is “strategic ideas like increasing diversity.” Too much detail — like a $400 library overage — can derail those conversations.

A simple presentation of budget data allows more time for those talks. “I can do a one-page presentation with historical comparisons and give board members a super-clear snapshot.”

Sedivy suggests grouping budget items by program centers — lower, middle and upper school; admission and development offices. “What does it cost to run the development office? What is the all-in cost of facilities, salaries, benefits, contract workers, grounds care? Add them all up and then you know the cost of your instructional program and can say 73 percent is spent on instruction and the rest is debt service and overhead.”

For a longer view of financial data, Sedivy provides trustees with a 10-year trend report that outlines financial data in a few key areas including enrollment, total operating cost, plant reserves and endowment market value. Schools can alter the report to reflect particular problems or strategic goals.

The presentation is simple and effective, Sedivy says. “By pointing to those numbers, the CFO can say here is where we have met or exceeded our goal. The admissions director can comment on enrollment changes over time; the development officer can talk about plans to raise more endowment funds; the facilities director can talk about debt service.

“These sections are insights into every strategic plan of the school,” he adds. “This is the administration’s report card on how the school is doing, relative to the board’s expectations. Just as the teacher grades his or her students, the board can grade the school.”

Donna Davis is a freelance writer in Boulder, Colorado. A contributor to Net Assets since 2008, she specializes in education-related topics.