How many teachers will next summer actually need?

For a school or college planning next year's staffing: put in the enrollment count for each past term, one row per term, and get back a forecast for the next three terms with the teachers each one implies. The sample is fifteen terms across five years, and the summer forecast of 296 is the one that decides how many teachers you do not hire.

School Intermediate Forecasting free

Install free How forecasting works here

After you install, this is the model to open.

How Many Students Next Term?

In your spreadsheet, click the Sortia icon in the strip of icons down the right-hand edge. No strip? Click the arrow at the bottom-right to open it. You can also use Extensions, then Sortia, then Open Sortia.
Click Start from a template and put that name in the search box.
Pick the card with that name and click Load this template. It arrives on a new tab with real numbers already in it.

The answer

Next autumn: 543 students, 95% bounds 532 to 554
Next summer: 296 students, 55% of the autumn intake
Teachers summer needs: 10.3 against 18.8 in autumn, at 28.9 students a teacher
Fit error: 1.4% mean absolute miss across fifteen terms

Fifteen terms across five academic years, in order. Click Run at a season length of 3 and a horizon of 3. The forecast comes back 543.2 for next autumn with 95% bounds of 532.1 to 554.3, then 484.2 for spring, then 296.2 for summer, and the mean absolute percentage error over the fitted period is 1.4%, which is unusually tight and is what a stable institutional series looks like.

Season length is the setting that matters here and it is not 12. Holt-Winters counts readings, not months, so a cycle of three terms is a season length of 3, and typing 12 into that box would ask the tool to find a pattern repeating every four years in data that covers five. The same reasoning gives you 4 for quarterly readings, 7 for daily readings with a weekly rhythm, and 52 for weekly readings with an annual one.

Read the summer forecast rather than the autumn one, because it is the number that costs money. Summer runs at 296 against autumn's 543, which is 55% of the autumn intake, and the two staffing lines beside the data turn that into 18.8 teachers against 10.3 at the historical 28.9 students per teacher. A provider that staffs summer off the annual average carries about five teachers it has nothing to give, every year, and the annual budget still reconciles.

The bounds hardly widen with distance, 532 to 554 next term against 285 to 307 three terms out, which is the fitted smoothing carrying almost none of each shock forward rather than the far term being as sure as the near one, so treat the near forecast as a plan and the far one as a shape. Then rerun with the season length set to 0, which switches the seasonal term off: every future term becomes about 400, the fitted error goes from 1.4% to 28.3%, and the bounds three terms out run from 56 to 756.

That comparison is the honest way to show a finance committee that the seasonality is in the data rather than in the argument. What the model cannot see is a change in funding rules or a new provider down the road, because it is an extrapolation of five years of your own behavior and nothing else. To use your own data, paste one column of period enrollments in time order under the Enrollments heading and set the season length to the number of periods in one full cycle.

The model

It arrives on a tab called Template: How Many Students Next Term, carrying these columns:

Academic year
Term
Enrollments (count)
Teachers employed (count)
=AVERAGE(C2:C16)/AVERAGE(D2:D16)

with the model computed beside the data:

Autumn teachers implied (count)	18.8
Summer teachers implied (count)	10.25

Once it is in your sheet

The model arrives with real numbers in it and runs as it stands, so you can press the button first and understand it second.
Change the numbers to yours. The sheet marks which cells are inputs and which hold formulas, and most labels carry a note explaining the row.
Press the run button at the bottom of the panel. It is labeled for the tool you are in, and the result lands on its own tab, with a written reading of it beside the figures.

Never used Google Sheets? Start here goes the whole way, in seven steps, and assumes nothing.

Next question

Every model like this one, and the method behind them: Forecasting in Google Sheets.

Install free All 379 templates