Predictive Calculations

Hello all,

Who is up to a challenge, that I couldn’t sort out :( ?

Scenario:

We are a secondary school and, like any other school, by the end of the year our students:

- Roll up a year (Year 7 become 8, 8 become 9 and so on) <- Spoiler: This is the challenge

- The Year 12 leaves the school

- New enrolments join on the following year

Our data model allow to easily predict new enrolments (showed on attached Matrix)

Desired Outcome:

I want to create a prediction matrix that tells me how many students I will have in 2018, 19, 20 and so on, by getting the (“Number of Current Students” – “Year 12 Students” + “Number of Enrolments”)

For 2018 it would be 886 – 137 + 192 = 941 (Sliced by Year Group, like shown in the Matrix)

Challenge:

Although it is quite simple to have this result for the following year (2018), it isn’t so for the subsequent ones, because in 2019 the students leaving will be our current Year 11 and so on.

How can I sort this issue???

Your assistance is very much appreciated and I would like to thank everyone for your time in advance.