Planning & Forecasting Tool

[Blake 7]

Dear Community

Please find attached a Planning and Forecasting Tool for “students” and “Funding”

The adopted model has three distinct matrices:

1. PLAN - you assign expected headcount (C10:AL10) and Duration (AM10)

2. ACTUAL - nothing is entered here - actual figures are driven off section 3

3. HEADCOUNT - i) starters: actual starters are inserted each month (C57:?57 [? changes each month]) and future starters are sourced from PLAN
ii) leavers: actual leavers are added in each month but assigned based on respective enrolment dates (e.g. 4 leave in Dec - 2 re: Aug, 1 re: Sep & 1 re: Oct)

I would like to be able to add two more features to this spreadsheet but cannot think of a an intelligent or efficient way of doing so.

I would like to add.....

1) A balancing payment feature – This is whereby a student leaves the course before the expected end date (Cell AM10) however all monies which were due to be paid as per instalment plan are paid in one go on the month he/she leaves
2) Funding change option (cells J, L and M 3) – Sometimes the amount of funding a course receives changes from one academic yr to the next, this may happen or may not happen. If it does happen it happens in August.

Any ideas / input / comments or suggestions are welcome.

I can provide more details if required

BTW DO is the man behind the clever formula.

Thanks
Darren

[DonkeyOte]

Slightly wary of getting involved in another one but hey

I think the below is probably the area to focus on first given it impacts all areas (as I see it at any rate)

Funding change option (cells J, L and M 3) – Sometimes the amount of funding a course receives changes from one academic yr to the next, this may happen or may not happen. If it does happen it happens in August.

I think the pertinent questions here are:

a) are you looking then to store three separate "total" payables - one per year of forecast
(with associated calculated values for first month, last month etc...)

b) is the rate being applied determined by the "revenue" month or the year in which the student(s) started ?

Perhaps easiest to elaborate on b) with an example:

Revenue Month Aug-11 (Column O) represents beginning of Year 2.
All starters with exception of those in Aug* represent "Year 1" enrolments with their revenue rolling into Year 2
The values generated in O12:O22 are to be based on which rates - Year 1 or Year 2 ?

* based on 12 month duration value in AM10

Until you've finalised the complexity of the "rates" and established the best means for setting this up I don't think there's any great value pursuing the remainder of the requirements.
It is my belief that the structure of any "rate table" will dictate how everything else is to be approached (to varying degrees).

[Blake 7]

Hi Don - Hows it going? As I stated in my msg, thanks for picking up the gauntlet with this again!

Here are the answers to your questions.......

a) are you looking then to store three separate "total" payables - one per year of forecast
(with associated calculated values for first month, last month etc...)

Yes.
At the start of every academic year (August), the funding rates change.
They change immediately on 01 August and affect ALL students, new students and continuing students.
For Example
If student A is on a 12 month course starting in Sep 09 his final payment, (the achievement payment cell J3) payable in Aug 10 would be at the new rate.
If student B is on a 12 month course starting in Oct 09 his final (subsequent month payment Cell M3) and (achievement payment Cell J3) would both be at the new rate.

b) is the rate being applied determined by the "revenue" month or the year in which the student(s) started ?

The academic year.

Perhaps easiest to elaborate on b) with an example:

Revenue Month Aug-11 (Column O) represents beginning of Year 2.
All starters with exception of those in Aug* represent "Year 1" enrolments with their revenue rolling into Year 2
The values generated in O12:O22 are to be based on which rates - Year 1 or Year 2 ?

Year 2

* based on 12 month duration value in AM10

Hope this helps.

looking forward to seeing what you come up with this time.

All the best
Darren

Edit involved grammer! tis late and ive had a black sheep ale!

[DonkeyOte]

Just as a heads up... I am looking at this...

Handling the rate changes per academic Year in the Plan is not overly difficult (ie is complete it my working example)

The complexity arises when handling the lump sum payments for leavers given this is in effect a (potentially) blended calculation.
Each cell within the three year matrix represents an entirely different final amount per head (with exception of those for which no value will exist at all - ie invalid intersects)

The "final" amount for any given Revenue Month is itself determined by

a) how many months remain between then and the end of the "contract"
remaining months based on overall duration and instalments made up to and excluding current revenue month

b) to which "category" each of those remaining months (1 to n) fall into - they will not all be the same obviously
categories being: first, intermediate, last [though first should not really exist]

c) to which "academic year" each those remaining months fall into
for a 12 month duration a large number of data points will have to blend rates as the remaining months cross 2 academic years

the combination of b) & c) will allow you to determine the appropriate rate for a given month - this process must be repeated for 1 to n months where n will vary in every cell

there are a few ways of approaching this - I'm mulling them over in my head and shall report back once I have a working sample.

[DonkeyOte]

OK, I think the attached does what you want or is at least a step in the right direction ?

In this version you will note:

a) two new matrices (INSTALMENTS and RED. FEES)

b) a few rows added above PLAN matrix (basically allowing a simple return of Academic Year)

I have assumed that each of your tables occupying rows 2:6 represent the rates for that given academic year.

I am also (importantly) assuming that the rates will always be completed for each year - even if this means the prior years values are simply copied to future years where no "new" rate presently exists.

The new matrices actually simplify things (I hope) - specifically the INSTALMENT matrix

This matrix shows the rate applicable per head for each combination of Revenue Month & Starter Month and incorporates the Academic Year should the rates change from one to the next.

The values in this matrix are now the source for ALL the other matrices in one form or another - PLAN for ex. is simply these values * headcount.

The RED. FEES matrix represents amounts payable by any leaver at that given point in time.
These values are calculated by simply summing the appropriate cells from the INSTALMENT matrix.

Finally ACTUAL matrix is also simplified given this is now simply:

(INSTALMENT value * active headcount) + (LEAVERS value * RED. FEES value)


If we look at the sample and ACTUAL matrix we can (as a basic proof) see that the total revenue for any given year amounts to the sum of INSTALMENTS for same period * original headcount.

Using the example of Sep starters we can see how in Nov 10 the revenue spikes in line with leavers (front loading the revenue).
If you change F53 from 1 to 2 (ie increase leavers for that period) you will see the spike in ACTUAL becomes more pronounced.

As I say - hopefully this is along the right lines...

I trust you can also see the value in the INSTALMENT matrix - in hindsight we should have added that beforehand as it reduces the number of repetitive calculations being undertaken in the model overall
(not to mention simplifying the formulae)


Reloaded attachment 19:50 UK having reduced file bloat... formulae etc unchanged

[Blake 7]

Good Morning Don, hope all well with you.

First of all thanks so much for your help and support with this, your input has been most valuable.

I have now had the time to read and your post and play with the attachment.

I have two follow ups and they (funnily enough) are a kin with your final comment to me, see below.

1) You stated:

"It might prove to be the case that leavers should have final amount determined solely at rates of the academic year in which they depart.

This should be the case.

In fact (in retrospect) from a work flow perspective that would make sense though would be a little more complex to formulate".

You were spot on in your visionII

My second follow up.

In the previous version, the headcount matrix allowed me to cater for students who left the course and where no further payments were necessary. Can we get that option back or have I missed a trick?

Some student complete and achieve the course early hense the balancing payment, and some students withdraw from the course as they dont like it and from the month they withdraw thier funding stops.

Final Q, what the the Red stand for? in RED FEES

Does this make sense Don and is this achieveable?
Thanks so much Don
Darren

[DonkeyOte]

1) You stated:

"It might prove to be the case that leavers should have final amount determined solely at rates of the academic year in which they depart.

This should be the case.

OK this is relatively easy to set up if we add a second instalment matrix.

The first matrix calculates instalments based on the academic year in which the instalment occurs

The second matrix calculates instalments based on the "revenue" year in which the instalment occurs (ie based on starter date)

The first matrix populates PLAN & ACTUAL for current headcount

The second matrix populates RED. FEES matrix - in turn populating ACTUAL for leaver headcount and lump sum payments * (Q: 2)

My second follow up.

In the previous version, the headcount matrix allowed me to cater for students who left the course and where no further payments were necessary. Can we get that option back or have I missed a trick?

Some student complete and achieve the course early hense the balancing payment, and some students withdraw from the course as they dont like it and from the month they withdraw thier funding stops.

This is a little tricker because realistically you don't want to have to use 2 input matrices for leavers [one to denote disgruntled and the other to denote early completion]

If you use matrix as we have now it means thinking up a clever way to record both simultaneously without making the dependent calculations overly complex... I am thinking this through as we speak.

Final Q, what the the Red stand for? in RED FEES

I could not think of a suitable title for the matrix (within the space) so opted for Redemption Fees (shortened) - poor terminology I grant you but the text is irrelevant so you can change as you see fit.

[DonkeyOte]

Darren, see if v3 (attached) does what you want.

In this version (as hinted at above) things become a little convoluted regards the different "types" of leaver and recording the leavers in just one matrix ...

The only difference from your perspective (data entry) is that when leavers are entered to the Headcount matrix they are entered as follows:

2.01

where:

- the integer represents total no. of leavers in the period (eg 2)
- the decimal remainder represents the no. of those that "completed" early and are thus liable for full payment (eg 1)
(note this is to two decimals to account for 0-99 leavers - Data Validation in place will prevent 2.1 for ex given .10 equates to 10 which is > 2)

In the sample you will note that I have specified the early leavers as being "disgruntled" (no decimal remainder) and some of the latter points have a mix (or are all early finishers)

The final lump sums where payable (ie based on decimal remainder of leaver headcount) are now determined by the rates of the academic year in which the departure takes place.

[Blake 7]

Don - if i were Janice from Friends, "i'd Shout....... oh myyyyyyyyyyyy Gooooooooooooooooood"

again, will read play........ and report back! so excited!

[Blake 7]

Don Don Don Don Doooon! This my friend is AMAZING and EXACTLY what I wanted.
You are a tower of power, a fountain of genius a mountain of integrity a mystery wrapped in a riddle!

I have some Q's but dont worry its only for self development purpose!!

1) I have noticed alot of the Formulae have Round function - when dealing with large amounts of cash, cumulatively, does this not throw the figures off? Do i have the opttion to remove it? Ignorance has forced this question!

2) Re O/S in Col AM. When we reach Aug 2014 for eg, how do I plot the O/S figures to becom continuers for the Academic year 2014/15?

Re all the matrices, im about to try and summerise them by putting together all your posts.

Don. Do you know how happy you have made me..................

Darren

[DonkeyOte]

1) I have noticed alot of the Formulae have Round function - when dealing with large amounts of cash, cumulatively, does this not throw the figures off? Do i have the opttion to remove it? Ignorance has forced this question!

First off - leave any ROUNDs where the num_digits is anything other than 2 as these are unrelated to £

In terms of the monetary ROUNDs - the ROUNDs are working to the nearest penny so unless the multipliers (heads etc) are enormous it's very unlikely you would have any significant rounding issues.
(they were added to really aid sanity checks of values against rates)

2) Re O/S in Col AM. When we reach Aug 2014 for eg, how do I plot the O/S figures to becom continuers for the Academic year 2014/15?

I thought about raising the issue of opening balances yesterday but opted against it... my opinion is this:

If you wish to have a three year view comprising current year + 2 future years then given the nature of your revenue profiles you MUST have a year 0 view also. An "opening balance" amount alone is not sufficient.
The various o/s amounts as at Jul-10 will wash into Yr1 revenue months differently depending on starter month / duration / headcount profile etc...
It follows that you need to replicate the Yr 1 table for Yr 0 ... and apply headcount and rate values such that Yr 0 o/s balances flow correctly into Yr1 across the various months.

In simplified terms - your Model should have 4 annual tables Yr 0 to Yr 3 inclusive with Yr 0 being "prior year". Each table has its own rate & headcount information.

The work flow - as I envisage it - should be such that when you complete Yr 1 and "roll forward" the rates & headcount for Yrs 1 to 3 should be copied (values only) to the new model but should now occupy Yrs 0 to 2 respectively - ie Yr1 now Yr0 and Yr2 is now "current" in new model etc...

If that is indeed the route you go down and you need assistance setting it up let us know.

[Blake 7]

hi Don, thanks for your knd offer, your assistence would be appreciated as i.m not quite sure how to do this. I think i can picture what you mean........ but it would be better to see it done properly! before I have a crack!

I still havent got the hang of the quote business. but below is a quote.

The work flow - as I envisage it - should be such that when you complete Yr 1 and "roll forward" the rates & headcount for Yrs 1 to 3 should be copied (values only) to the new model but should now occupy Yrs 0 to 2 respectively - ie Yr1 now Yr0 and Yr2 is now "current" in new model etc...

If that is indeed the route you go down and you need assistance setting it up let us know.[/QUOTE]

Thnaks once again.
Darren

[DonkeyOte]

I think i can picture what you mean........ but it would be better to see it done properly!

See attached.

Essentially you should have a Yr 0 which in this case represents Aug-09 to Jul-10.

Yr 0 works in the same way as Yrs 1 to 3 in so far as you specify rates (rows 3:6) and headcount (HEADCOUNT matrix)

With the above in place Yr 0 revenue will then flow into Yr 1 as appropriate.

When it comes to rolling forward for a further year it follows that Yrs 1 to 3 become Yrs 0 to 2.

Added caveat this time in that I've not had time to sanity check the values generated in the ACTUAL matrix based on new Yr 0 figures - I trust you can do this ?

Final notes:

I notice that at some point you removed the links in HEADCOUNT matrix to PLAN for future months... not sure if this was by design or not ?
In these types of models we would normally expect all Starter headcount for "future" months to be sourced from PLAN headcount.
It follows given the model's structure that Leavers can be Forecast in the same way Actual Leavers are recorded (ie via HEADCOUNT grid entries)

I have left no. conditional formula rules at 3 for the sake of backwards compatibility
(ie Yr 0 is not formatted in the matrix)

[Blake 7]

Morning Don, hows it going? all good in Suffolk? Suns shining here so about to take my son to Leeds Castle, (which is in Kent). Father and Son day!

See attached.

Essentially you should have a Yr 0 which in this case represents Aug-09 to Jul-10.

Yr 0 works in the same way as Yrs 1 to 3 in so far as you specify rates (rows 3:6) and headcount (HEADCOUNT matrix)

.........

With the above in place Yr 0 revenue will then flow into Yr 1 as appropriate.

When it comes to rolling forward for a further year it follows that Yrs 1 to 3 become Yrs 0 to 2.

Added caveat this time in that I've not had time to sanity check the values generated in the ACTUAL matrix based on new Yr 0 figures - I trust you can do this ?

Great, i'm with you until here.......... I reckon I can manage this.

Below is a quote
Final notes:

I notice that at some point you removed the links in HEADCOUNT matrix to PLAN for future months... not sure if this was by design or not ?

In these types of models we would normally expect all Starter headcount for "future" months to be sourced from PLAN headcount.

It follows given the model's structure that Leavers can be Forecast in the same way Actual Leavers are recorded (ie via HEADCOUNT grid entries)

Re the last 3 sentences...Not quite sure what you are refering to so if something is missing it is a mistake. Did you put it back in this Version? and any chance of an explanation? it sounds like a good idea, so would be good to know how it works!


I have left no. conditional formula rules at 3 for the sake of backwards compatibility
(ie Yr 0 is not formatted in the matrix)

Thanks, that sounds like a good idea.

Hope all well your end.

[Blake 7]

notice that at some point you removed the links in HEADCOUNT matrix to PLAN for future months... not sure if this was by design or not ?

In these types of models we would normally expect all Starter headcount for "future" months to be sourced from PLAN headcount.

It follows given the model's structure that Leavers can be Forecast in the same way Actual Leavers are recorded (ie via HEADCOUNT grid entries)

I know what you mean now!!!!

its where the planned starters (row 10 feeds row 57) headcount actual.

Sorry for being so slow. I;ll put the links back!!

Cheers Don.

In fact my mother in law (Grrr, sitting opp me) is reading a copy of Don Quixote de la Mancha as we speak! She's from Argentina! Should be reading Don Segundo Sombra!