How many teachers do we need? The official Governmental model

How do we know how many teachers are required to keep the UK’s schools in good working order? It’s an interesting question, with obvious implications for Governmental education policy with regards to teacher compensation, incentives, training places and so on.

The “official” requirements are calculated via the Government’s “Teacher Supply Model”, which, happily, in the name of transparency you can get a copy of here.

But rather than have to read through the 61 page user guide and two big fat Excel files, below are some basic notes on what factors go into its calculations. Most of this is summarised or reproduced from their manual (it is hefty, but I appreciate their openness in preparing it!).

Firstly we should define what exactly the model tries to calculate, and then predict.

Target variable

One of key outputs is to define the number of teacher training places required, so it works from a top down approach of “how many teachers do we want overall” to get to that figure.

The “how many teachers do we need to enter the profession each year?” is the focus of this post.

It’s referred to as “model part 1” in the official documentation. Model part 2 works from this to get to an actual number of NQT training places this requires, needed because there are other routes to increase teacher numbers outside of the typical NQT route.

Anyway, being such a model, part 1 necessarily involves a mix of data and assumptions.

It’s set up in a way that you can tweak the assumptions to show for instance the implications if greater or fewer teachers quit than expected. Where figures from this model are mentioned in future posts, from the default “central” scenario unless noted otherwise.

The authors are (very) keen to highlight that any assumptions made in the model do not equate or suggest knowledge of future Government policy! But rather it’s what these domain experts predict is likely to happen.

Model scope:

  • Only applies to England
  • Only concerns itself with qualified teachers
  • Includes state funded schools; primary (+ ones with nurseries attached), secondary, academies and free schools and key stage 5 (aka 6th-form) teaching within secondary schools.
  • Does not include special schools, referral units, independent schools, early years schools or standalone further/6th form colleges

Input variables used:

(*) wastage is the slightly unpleasant sounding term to mean teachers leaving for reasons other than death or retirement.

Assumptions implied:


The active stock of teachers in November 2014 (when the census is conducted) will not change significantly by the end of the 2014-15 academic year.

Teachers are categorised into what subject they actually teach, not what they were employed to teach. For example, if they are officially a science teacher, but spend 25% of the week teaching maths, then that’s 0.75 of a science teacher and 0.25 of a maths teacher.

Hours spent teaching PSHE are excluded.

Long term, the rate of change of key stage 5 pupil numbers will match the rate of change as the national 16-19 year old population. Short term, the same increases in post-16 participation from the past 3 years will continue.


The proportion of teachers who will leave as wastage going forward (per age group, per gender) is calculated from a weighted average of the wastage rates in the past 4 years worth of data.

This data is also broken down into groups of subjects (but not individual subjects).

The groups are as follows:

  1. Group 1: EBacc Science and Mathematics subjects – including Biology, Chemistry, Computing, Mathematics, and Physics.
  2. Group 2: EBacc non-Science and Mathematics subjects – including Classics, English, Geography, History, and Modern Foreign Languages.
  3. Group 3: All other subjects – including drama, music, PE, and RE among others.

The below table shows the assumed wastage rates based on subject/age. In general, group 1 subject teachers are more likely to leave than group 2, and then group 3 are the least likely to leave.

Projected wastage rates also factor in economic variables via the “econmetric wasteage model”, e.g. looking at historical relationships between teacher wastage and economic growth, unemployment etc,

Retirement / deaths

Uses weighted historical retirement and deaths in service rates from the past 4 years of data. Rates are calculated by age group by gender .

Model assumes retirement/deaths in service rates are the same in all subjects and over all future time periods (but it does take into account that some subjects tend to have older/different gender teachers than others and takes into account the projected changes in teacher demographics)

Method to estimate future teacher stocks needed

Start by projecting how the pupil teacher ratio will change going forward as pupil numbers change.

This is not as simple as “if the number of pupils doubles, so should the number of teachers”. They show via data that historically when pupil population increases, some of the extra was dealt with by increasing the pupil teacher ratio, as well as getting new teachers.

Therefore, model assumes for every 1% increase in pupil population from now, the pupil teacher ratio will increase by only 0.5 percentage points (primary) or 0.6 percentage points (secondary). It is however capped at a level that relates to previous historic maximums.

Here’s their “historical” chart on the subject:


Knowing future pupil numbers and future pupil-to-teacher ratios allows calculations of FTE teachers needed.

The model assumes that the ratio of unqualified teachers to qualified teachers will remain constant at today’s rates (by phase and subject). Demand met by unqualified teachers is therefore removed from this model.

FTE teacher requirements are converted into actual physical headcount, via multiplying by the current FTE rate for teachers (with implicit assumption that that ratio remains constant going forward).

Then to calculate teacher need by subject:

Calculate FTE rate based on current needs; e.g. if 10% of teaching time in secondary schools is spent teaching English (irrespective of how many English teachers there are) then 10% of the workforce needs to be English teachers.

Different subjects are more or less popular as pupil options at the distinct Key Stages, and the proportion of pupils in each Key Stage also changes.

The model therefore estimates the quantity of teaching time needed per pupil per subject at KS3, 4, 5 and scales upwards.

Then, add adjustment for anticipated education policies:

If any changes of educational policy might be expected to adjust the need for teachers by more than 100 FTE then it should be added to the model. Right now there are 7 such policies in the Secondary teacher section.

The policies they address are as follows:

  • Hold 2016-17 ITT places for all Ebacc subjects at 2015/16 levels or above (to support “Ebacc for all” policy).
  • Assume increases in Ebacc subject takeup.
  • Remove option to just take Core Science (Core Science is to be replaced by Combined Science, meaning that 10% of KS4 students will need double the science teaching time).
  • Add extra maths teaching time for a new core mathematics policy.
  • Assume continuing increases of uptakes for Maths and Further Maths A-levels due to enhanced further mathematics support progress.
  • Impact of new Maths GCSE will require a greater amount of Mathematics teaching per pupil at KS3 & 4.
  • Impact of new English GCSE will require a greater amount of English teaching per pupil at KS4.

Estimating new teachers needed to enter stock each year

Once one knows the above figures, it’s a simple calculation:

Need for entrant teachers in year x = Teacher need in year x  - Stock of teachers at the end of previous year + No. of teachers expected to leave in year x

This is calculated per subject per phase using variables on the left of the below diagram, iterating for years beyond 2016/17 with the process on the right of the diagram.


One key assumption is made here when it comes to modelling future years (2017-18 onwards).

The model assumes that if we determined we needed [x] new teachers by the end of year [y] then indeed that many will have been successfully acquired and added to the active stock. That will then be the starting stock for year [x+1].

There’s no consideration of events that would lead to fewer teachers being active in future years that the model says are required, e.g. if recruitment efforts fail.

That’s the end of part 1 of the model, which calculates the total and new teachers required.

Possibly more interesting will be to see the actual numbers behind the above calculations, which give indications of trends in KPIs affecting teacher requirements. More on that soon.

Showing the formula behind a Tableau quick table calculation

Tableau gives super-fast access to many common analytical operations via its “quick table calculation” feature: running total, percent difference, moving average and so on.

Just rightclick the measure pill, select “Quick Table Calculation”, make your choice and often that’s all that’s needed.


But what if you want to customise them in ways beyond what a simple “edit table calculation” can do, or understand how to reuse their functions in your own, more complex, formulae?

Behind the scenes actually all “quick table calculations” can be written out manually as a Tableau formula. You can work out how to do that by studying Tableau’s table calculation functions. But if you’re feeling lazy or lost, perhaps you’d rather just see how Tableau created its quick table calculations behind the scenes directly.

To do this, you can use the “edit in shelf” feature present since Tableau version 9.

Once your measure is set up as a Quick Table Calculation (which then will show a triangle to the right of its name on the shelf), you can right click it and choose “Edit in Shelf”.

This will convert it (temporarily) from a “quick table calculation” to its underlying formula which you can then copy for reuse, or edit.

The code behind my quick-table-calc “% difference in sales” is for instance:

(ZN(SUM([Sales])) - LOOKUP(ZN(SUM([Sales])), -1)) / ABS(LOOKUP(ZN(SUM([Sales])), -1))

You could then take that snippet and reuse it in your own calculations.

If you know the basics of the table calculation functions, you can also adjust the parameters directly in the shelf.

For instance, in the above example, perhaps you want the percentage difference in sales from 12 datapoints ago, not just since the last period. If so, just replace all the -1s in the above with -12, so the lookup looks 12 places to the left of the current value.

Setting up the automatic “upload to Tableau Online / Server” feature in Alteryx 10.1

CaptureAlteryx released their new version 10.1 this week – license holders/demo seekers can pop along to the download page to install it.

Amongst the new features it promised was:

Automatically update Tableau Server and Tableau Online with a new Tableau macro

The concept is simple: process/model/advanced-analyse your data in Alteryx and then it will push up the results as a Tableau data source which all your Tableau Server or Tableau Online users can use in their Tableau workbooks.  If you set it up nicely, you could therefore have it auto-update a bunch of complicated datasources with one  click of a button.

As the community of Tableau users gets ever more integrated with the community of Alteryx users, this is a pretty exciting development. In fairness, the geniuses at the Information Lab had already enabled this via a couple of nice macros they were kind enough to distribute – but I guess to see this made a feature of the core software itself at least shows that what they did was highly desired!

After quickly installing 10.1 though, I couldn’t find the tool immediately. It turns out you need to download and install it. It’s very easy to do, but below are some notes on the process to follow, and how to configure the tool if you use Tableau Online, rather than Tableau Server.

Steps to install the “Publish to Tableau Server” tool

Firstly, make sure you have installed Alteryx 10.1. Get it here.

Then go download the “Publish to Tableau Server” tool from the Alteryx Analytics Gallery.

Double-click to run it once you have downloaded it and it will open in Alteryx.

Press the toolbar button for “Run as an anCapturealytic app”. This is not the usual “play” triangle, but rather the one that looks like a wand next to it.

From the resulting wizard, choose “Install”

Close and restart Alteryx – and now you should have the sparkly new Publish to Tableau Server tool available.

Configuring the tool to upload to Tableau Online

(which is really pretty much the same to a Tableau Server, but there’s a couple of extra bits to bear in mind for those using the cloud online version of Tableau.)

As with any other Alteryx tool, pop it into your workflow such that the data you want uploading to Tableau Online/Server is going into its input. The output it passes on will be the same as whatever input you gave it, so you can continue processing the data in Alteryx after the upload has happened.


Click on the Publish tool and you will see you have to configure 2 tabs: Connection and Output.

They’re quite self explanatory, but note, for Tableau Online:

When configuring with a server URL, do not just enter Instead you have to include the subdomain before the “online” bit. To find out what that is just log onto your Tableau Online site in your web browser and note the first part of the address. The one I tested on for instance is called:

The “Use default site” option will not work for Tableau Online. This is presumably because there is not really a default site when a single server is being used by a large number of different customers! Manually type in the name of your Tableau online site, which you can find in the URL of your web browser again when you log into Tableau Online after the “/site/” bit. It’s probably the name of your company or something similar (if you’re working in a company).

You can then use the “Output” tab of the configuration to specify the project, data source and so on to save to. Then just hit the normal “run” button, wait a couple of minutes until Alteryx tells you it’s finished, and you should find you have a nice new datasource in your Tableau Online site.