Forecasting Simplified

Financial Forecasting Simplified

(it shouldn't be hard)

The trajectories of school budgets can take years to change

Two Tweaks to Improve Financial Oversight

Tweak #1: Build a trusted and simplified financial forecasting model

Boards usually only require financial summaries to support budget decisions. Similarly, financial forecasting models usually don't require financial details from separate departments, and instead can be built using the aggregated budget categories that your business office uses to summarize financial data for your board (more details can be added as needed of course)
Simpler models make error-checking, and vetting easier
Simpler models make training new trustees easier
Graphing the results can make forecasts more intuitive, and make trends easier to see.
To avoid errors, follow best-practices when building, using and updating models

Tweak #2: Train Everyone
Intuitive financial models coupled with a one hour interactive training session for new trustees can empower the entire board to look at issues through a risk management lens, and:

give trustees the tools they need to help to keep your school out of trouble in the near and more distant future
avoid non-productive boardroom debates on issues that are financially infeasible
show how risky taking on debt can be, and how critical financial reserves are, by forecasting plausible negative scenarios (e.g. what if the next recession is similar to 2008's? Can our debt payments be met? How long will our reserves last?)

Can your school handle the next big recession?

Forecast to help with setting goals for un-restricted reserves

help ensure that capital campaigns address the most urgent needs of the future
promote generative discussions by allowing for live "what if?" scenarios to be forecasted in meetings (or allow multiple scenarios to be easily produced and emailed to trustees before meetings), empowering trustees to strategize with risk/reward in mind

Forecasting scenarios by changing one budget component at a time can quickly give non-experts an intuitive feel for what drives your financials. Plus its more fun, and stickier than reading documents

These two tweaks may seem like extra work - well, they do require some upfront work - but once these practices are established at your school you will have way less work since you'll avoid financial crises, avoid dysfunctional or misinformed boards directing heads to do foolish things, have less worry about the future keeping you awake at night, etc. In the long run, it's smart and lazy.

Best-Practices are Critical
Spreadsheet programs were invented for these types of financial models, and they are usually what schools choose to use since:

Spreadsheet skills are widespread
Spreadsheets are easy to update, modify, graph, etc.

But, even with simple models it's easy to make errors that can go unnoticed for years. Therefore, it's critical that models that are guiding your school's financial decisions are built and updated using best-practices, and that changes are vetted by someone else.

Preserve Your Institutional Wisdom
To avoid future errors, re-training, and loss of trust in the models or process, don't re-invent the wheel when volunteers turn over, or staff leave. Instead, "evolve" your models and financial policies in the finance committee, and have them "live" in your business office. The head of school should actively support this process by making sure new board chairs and treasurers understand that they should use and evolve your proven models and policies, and that their improvements will be preserved as well, allowing them to make a positive impact in perpetuity.

Need Help?
Implementing such changes can be challenging since school cultures often resist change. Outside expertise can help with showing the long-term benefits, and with getting buy-in for change. For example:

Some business office managers may think they lack the skills or time to be responsible for annually updating financial models. If they can enter an equation into Excel, then they will be able to do this easily, quickly, and errorlessly with a model built with best-practices and an hour or two of fun, collaborative training. And they will quickly start loving and relying on the insights it gives them.
Some highly-experienced new treasurers may prefer to do things their way, and create and use their own models. However, without a commitment to financial oversight continuity, there's no guarantee that the next round of volunteers won't do the same thing and ignore the new treasurers' valuable models and insights. Instead, if treasurers focus their talents on evolving the school's models and policies, then their positive impact can endure long after they step down.

If you need help establishing this at your school, including the creation of financial models and policies customized for your school, this is one of the consulting services I offer. I would be happy to have an introductory call with you to understand where your are, where you'd like to be, and if and how I can help.

Back to forecasting......
Below steps thru some of the major components of school financial forecasts, starting with some financial data of NAIS day schools and finishing with a simplified interactive forecasting model (along with a downloadable Excel version).

1. Major costs of NAIS day schools (2002-2016)

Medians of (major costs/tuition) of NAIS day schools

The vertical rectangles plot the 25%-75% range of costs/student of NAIS day schools (the IQR). The center line is the median.

2. Major sources of income per student of NAIS day schools (2002-2016)

Medians of (incomes/student) of NAIS day schools

The vertical rectangles plot the 25%-75% range of incomes/student of NAIS day schools (the IQR). The center line is the median.

3. Major budget levers that need to be balanced

Whenever cost increases are considered, always ask: "How are we going to pay for it in a sustainable way?"

4. Impact of changing major budget levers over time

Tuition price growth and wage pool growth have the most impact over time, but initially they have LESS impact - don't let this fool you into directing your school down an unsustainable path

5. Future Campus Expenses?
These simplified models do not include a category for future campus maintenance and improvements, which can be substantial. If you don't have estimates of these future costs, then consider hiring a firm to do an assessment of likely campus improvement and maintenance costs over the next decade so that your school can be prepared to handle these future expenses. These estimates should also give you more accurate forecasts based on cash-accounting (rather than accrual-accounting which uses depreciation - see last section).

6. Avoid the icebergs - interactive forecasting model
Below is a simplified 10 yr forecast of a school with 500 students paying $20,000/yr in tuition. All salaries, benefits and taxes are lumped into the "wage pool", and other, less significant budget components are ignored to focus on the big picture.

As can be seen in the middle graph of Net Income, the school is currently losing about $100,000/yr, and is projected to lose more. Can you cut the losses by changing the major levers of the budget? Adjust the levers with the slider bars on the right so that the bottom graph of Cumulative Net Income is $0 for all 10 years.

Excel-Based Models
Excel-based models that include all of your school's departments can be built efficiently using the output format of your business office's accounting software, at the level of detailed needed to support the board's and finance committee's decisions. This makes annual updating of the model easy and error-free since the new year's data is simply copied and pasted into the model.

Also, since the future is uncertain, consider including some uncertainty in your model. For example, you could plot 3 net income forecasts on the same plot - 1. likely, 2. pessimistic, 3. optimistic, by varying parameters like future enrollment.

As noted above, Excel models need to be built with care since mistakes are easy to make, can be hard to find, and can lead to budget decisions that can harm schools in the future (trust me!).

Recommendations:

someone other than the model's creator should double check the model for errors
formulas should be protected, and uniform across years
avoid modifying parameters like growth rates by modifying a formula in a cell. Instead, put parameters in their own cells, and have the protected formula reference them, as done in the simplified Excel model below.

An Excel version of above simplified forecast can be downloaded here:

simplified_10_yr_forecast.xlsx
File Size:	21 kb
File Type:	xlsx

Download File

Excel Model with a few VBA Controls Added

Capitalized Expenses and Depreciation 101
As noted above, cash-based accounting that doesn't involve depreciation is usually more accurate for financial forecasting models, but requires estimates of future expenses for facilities, etc. If you don't have estimates of these future campus costs, then consider hiring a firm to do an assessment of likely campus improvement and maintenance costs over the next decade so that your school can be prepared to handle these future expenses.

The alternative to cash-based accounting is accrual accounting, where the cost of items like a new roof aren't accounted for as an expense, but are instead "depreciated". Depreciation in the accounting sense can be confusing for trustees who don't have financial backgrounds, but the core concept is fairly simple: depreciation spreads the cost of long-lived items (vehicles, buildings, etc.) across the years of their useful lives.

Here are a few more details, along with the associated accounting jargon

When long-lived items are purchased they are capitalized
The useful lifespan of capitalized items is estimated by their depreciation schedules, which are assigned under GAAP.
Straight-line is the simplest method of depreciation, and assumes the same percentage of the asset is "used up", or "spent" during each year of the depreciation schedule.

Example: the purchase of a $2,000 computer with a 5 year, straight-line depreciation schedule

Upon purchase, cash is debited by $2,000, which will be reflected on the balance sheet and the cash flow statement.
However, this $2,000 outlay is not treated as a cost on the income statement, and it will not affect net income.
Instead, the annual depreciation expense incurred will be $400/year ($2,000/5 years), which will lower net income by $400 for each of the next 5 years.
The assumed value of the computer over time, or book value, starts at $2,000 and declines by the $400 depreciation expense each year. The computer's book value is represented on the assets side of the balance sheet.

The below table summarizes the impact of purchasing this $2,000 computer at the beginning of 2018. Notice that when the computer's book value declines to $0 in 2022, the accumulated depreciation rises to the computer's original $2,000 purchase price.

Wade Vagle, CFA, CAIA
Get in touch at Wade@SchoolsThatLast.com