Planning and Forecasting Tool.

Dear Community, I am appealing for help.

I am not sure of the function name which will help me hence the Wooley Tittle!

Attached is a planning and forecasting tool, its aim is to allow the “user” to plan income generated by students (I work in a college)!.

Cells AN10:AR22 are dedicated to "withdrawal scenarios" e.g. what would the effect on revenue be if a % of students left the course

I would like to be able to enter a % figure in AP11 which the gives the student equivalent in AQ11 and a revised income figure in AR11. I am not sure how this could be done.

FYI DonkeyOTE is the man behind the clever code! C11:AL22 I will not take credit!

Thank you for reading this post.

Darren Chapman

If I've understood then assuming duration can change and also that no. of heads may vary month on month perhaps:

Please Login or Register  to view this content.

above assumes duration never exceeds no. of months preceding start point.
if such a scenario is a possibility you would need to adjust the negative element of the OFFSET (ie restrict such that it can not refer to invalid range or invalid cell content)

Not entirely sure I follow... is the "Student Withdrawal" number:

a) the no. of students who are deemed to have withdrawn from the original "planned" intake for the same month

or

b) the no. of students deemed to have withdrawn from the original planned intake in that month but that originate from various starting months ?

Perhaps easiest to explain with an example, consider:

Duration of 2 months
4 starters planned for Jul
2 starters planned for Aug
2 withdrawals expected (specified) for Aug

The question is: are the 2 Aug withdrawals those students originally planned to start in Aug or are they from Jul or a mix ?

If they represent heads other than those planned to start in Aug. then unless the planned headcount is constant across all months (unlikely given you generally plan for growth) then you have a (big) problem as I see it - approaching it as you are.

I confess at this stage I'm not quite sure what the question/s is/are

Originally Posted by Darren Chapman

can you recommend a more efficient way of calculating cells ao12 and13?

Regards AO12/AO13 - as I see it - what you have is efficient - you can't get much more efficient than a SUM !

Technically speaking you could calculate the monthly totals in row 23 with just rows 9 & 10 - rows 11 to 22 aren't an absolute must.
However, that said the method you are using is IMO preferred to a more complex SUMPRODUCT type approach so I think you should stick with it (also greater transparency)

Originally Posted by Darren Chapman

this workbook is divided into two parts.......... Cells B to AR23 is based on a plan a forecast where as cells B27 to AL 41 is actual.

The issue you have as I think we both agree, is that if you assign a literal number of "leavers" then you must assign them as they relate to specific months.

This is simply due to the fact that Sep for ex. is affected by the "headcount" of prior months in addition to itself - the number of months being determined by the specified duration period.

There are obviously significant knock on effects on revenue - and if headcount is not consistent on a monthly basis then apportionment is impossible without that info (or some large assumptions!)

What I think we need to better understand is the overall aim of "leavers" on your model.

A common approach would be to have a "Plan" view (which may forecast attrition to some extent within the model for sake of being prudent) and also an Actual view for the same period.

Often, for ease the Actual view would display actuals up to and including the current period and remaining months would be sourced from Plan.

Each month the next Actuals would be written into the Actual view and potentially based on trends one might revise the remaining Plan (v2 etc)

In terms of leavers regards Actuals...

If you want to discount future month revenue in the Actual view as when leavers happen then I think you probably need a further matrix which details leavers by month of departure but specific to the month of start, eg:

Please Login or Register  to view this content.

so here for ex. we're saying that of the 4 leavers in Jul 2 started in Apr and 1 each in May & Jun

This matrix would then allow you to adjust future months based on actual headcount where actual headcount values exist (for others you would use Plan)

In essence the above matrix is used in conjunction with the Actual start values (row 35) to generate an adjusted headcount at point of calculation.
(this way prior actuals do not adjust etc as you post future leavers - ie leavers in Jul won't change when you add further leavers for Aug and as such Jul's figures don't change from those first generated when they the Jul leavers were first added)

Does any of the above make sense ?
Is that what you want to do ?

we need to be careful this question doesn't become too vast... I think it's a case of nailing down precisely what it is you want the end result to be irrespective of formulae etc...

In terms of what's next etc given what we know and what you have presently...

I've thought a little bit about this and based on your existing file one possibility might be along the lines of the attached.

NOTES:

-- it is assumed that duration can never exceed 12 months - if it can then the model config. you've adopted won't work.

-- the 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 enrollment dates (eg 4 leave in Dec - 2 re: Aug, 1 re: Sep & 1 re: Oct)

So it follows that ACTUAL will calculate "actual" months based on actual headcount inclusive of leavers and "future" months will be based on plan albeit with whatever knock on effects from Actual persist into future months.

Using the attached file as sample...

For sake of transparency I've set Actual Starter headcount equal to Plan purely to prove that where no leavers occur the values generated in the ACTUAL matrix match those in the PLAN matrix
(eg rev. associated with Nov starters in Year 1 - both matrices return £93,232.10 for period Nov-08 to Oct-09)

You can see the impact of actual Leavers recorded in matrix 3 by looking at matrix 2 for one of the affected starter months - let's use Aug-09->Jul-10 for Aug starts.

Aug-09 is 14 * first instalment

Sep-09 is 12 * subsequent instalment given 2 Aug-09 starters left in Sep-09

Oct-09 is 9 * subsequent instalment given 3 Aug-09 starters left in Oct-09 (cumulative being 5 leavers of original 14)

The Oct-09 balance persists until Jul-10 at which point we have 9 * final instalment

Whether or not this is what you want to do is another matter of course but in principle you can adjust the ACTUAL view by altering any of:

a) PLAN headcount for future months

b) ACTUAL headcount for current month - starters & leavers

c) DURATION (must be < 12 months)

On an aside there's a little bit of conditional formatting in place to highlight Yr1, 2 & 3 values and also a little validation (which should be extended) re: Headcount matrix.

The formulae used in the matrix have been adjusted such that the formula applied to top left most cell of each matrix can be applied to the 3 year matrix in it's entirety without need for modification.

It is obviously important to note that this is not meant as a "finished article".
The file is simply intended as a suggestion and of course it's validity is entirely dependent upon whether or not I have correctly interpreted your requirements!
(I've also not done a huge amount of error checking so there could well be issues I've not yet considered)

Originally Posted by Darren Chapman

Question:

The formulae you have used. (for the condtional formatting) and this one

=IF(OR(ROWS(B$11:B11)>COLUMNS($C11:C11),MOD(MONTH(C$9)-MONTH(1&$B11),12)>=$AM$10),"",ROUND(INDEX($A$10:C$10,COLUMN(C11)-MOD(MONTH(C$9)-MONTH(1&$B11),12))*IF(MOD(MONTH(C$9)-MONTH(1&$B11),12)=0,ROUND($L$3,2),ROUND(IF(MOD(MONTH(C$9)-MONTH(1&$B11),12)=$AM$10-1,$J$3,$M$3),2)),2))

I have tried and tried to interpret them but am lost...

The confusion here probably stems from the MODs etc... in reality it looks more complicated than it is.

First things first you need to test to see if a calculation needs to be performed at all...

Please Login or Register  to view this content.

we know no calculation is required if either:

a) starter month (B) is post revenue month (row 9) - see ROWS>COLUMNS above

or

b) time passed between starter month (B) and revenue month (row 9) exceeds duration - ie fully paid up already

Point b) ie Duration test is conducted by the 2nd half of the above OR clause, namely:

Please Login or Register  to view this content.

In truth the MOD construct is used repeatedly* thereafter so in homage to Allo Allo "I will say this only once"

*The MOD test is used repeatedly through the function in part so as to allow for a 1-for-all formula - ie one function which can be applied across every cell in the matrix without need for modification

MONTH(C$9) -> will return the month number (1-12) of the revenue month

MONTH(1&$B11) -> will do the same but for the "starter" month

In the case of C11 (from which the formula is taken) and where C9 is Aug-08 and B11 "Aug" this means we get:

Please Login or Register  to view this content.

In short - not a great example to work through the logic of what's going on but what it does show us is that where the months match we get a 0 result - ie the value is exclusive of current month (ie Aug-08 to Aug is really 1 instalment)

We can better illustrate the MOD impact if we apply the same formula but based on say H11 (rather than C11) where Revenue Month (H9) is Jan-09 and Starter month remains as before (same row - ie Aug), we then get:

Please Login or Register  to view this content.

So in essence what the MOD value is returning is the current duration (exclusive of current month) at that point in time.
So the 5 in fact represents that there will have been a max. of 5 prior instalments for an "Aug" starter up to an including Dec-08

We use MOD because as we can see sometimes the month difference will be positive and sometimes negative - the MOD with a divisor of 12 (months in year) will always return for us the correct duration.

It follows that if the MOD returns a value >= duration requirement (AM10) then we know there are no further instalments to be made in the current revenue month for those heads starting in the starter month.

If we assumed duration was always to be 12 months then this calculation is superfluous - however - I have assumed duration can be adjusted from 1-12 months and as such it is required to prevent revenue calculations beyond the final month.


The next section determines what is to happen when a calculation does need to be made...

Please Login or Register  to view this content.

and can itself be broken into three distinct parts

i) outer ROUND to round revenue amount to 2 decimals

ii) calculation to determine "starters" for the appropriate starter month in relation to revenue month

iii) calculation to determine appropriate instalment value (based on which of the "n" instalments the current calculation represents)

i) I won't dwell on the ROUND elements as these are I think self explanatory ?

ii) "starters"

Please Login or Register  to view this content.

In the case of PLAN matrix the headcount values are sourced from row 10
(in ACTUAL matrix they are sourced from row 57 which in part answers your part about how ACTUAL talks to HEADCOUNT)

The above relates to C11 so basically it uses the same MOD calc as before (which returns 0 for Aug-08) and subtracts that value the column number of the current column.

In the case of C11 this means:

Please Login or Register  to view this content.

In the case of H11 (Jan-09) we'd get:

Please Login or Register  to view this content.

In the case of H12 (Jan-09 but Sep starters) we'd get:

Please Login or Register  to view this content.

Hopefully thus far this makes sense but in essence the formula is designed such that the correct starter headcount value is used for each cell in the matrix

As mentioned before this model only works in terms of it's current set up if duration never exceeds 12 months.

iii) Instalment - determined by:

Please Login or Register  to view this content.

Here we're basically working out whether to use instalment rate L3 (first instalment), J3 (final) or M3 (standard)

We do that using the same MOD construct we've seen previously as we know this gives us an (exclusive) duration/no.instalments figure for the current month.

Where the result of the MOD calc is:

-- 0 then we know current month is first month thus we use L3

-- equal to AM10-1 then we know that current month is final instalment and thus use J3

-- any other value means the current month is a standard instalment and thus we use M3

we ensure the rate we use is rounded to 2 decimals

And hopefully that covers the mechanics.

Originally Posted by Darren Champan

i am trying to ascertain how you managed to get the HEADCOUNT to speack to ACTUAL.

It's actually the other way around - ACTUAL calculates off HEADCOUNT which itself calculates off PLAN for future months.

What we would expect the user to do each month is update the current month ACTUAL headcount figures - ie override the existing formulae.
In the sample you will note that AC57 (Oct-10) is a formula as are the remaining cells whereas C57:AB57 are manual entry.
That is to say we (the user) have overridden the links back to the original PLAN headcount for historic months with the real figures as they arose at the time - so in Oct-10 we will add the ACTUAL headcount (starters leavers) in AC57:AC69 as appropriate.

Worth adding at this point that the PLAN matrix uses a slightly more complex formula than the above in order to determine the appropriate leaver-adjusted headcount, eg:

Please Login or Register  to view this content.

so here you can see that we have an additional SUM subtraction.
The SUM is tallying leavers for the appropriate Starter month that took place up to and including the revenue month and subtracts that figure from the Actual starter headcount as entered by user manually per aforementioned work flow.

Originally Posted by Darren Chapman

DO: The formulae used in the matrix have been adjusted such that the formula applied to top left most cell of each matrix can be applied to the 3 year matrix in it's entirety without need for modification.

DC: What does this mean? i can copy and past and drag it anywhere?

The top left cell of each matrix can be copied across that particular matrix in its entirety yes.
As mentioned there are functional differences between PLAN & ACTUAL formulae so they are not interchangeable (though similar) - ie PLAN formula can not be copied to ACTUAL and vice-versa

Originally Posted by Darren Chapman

DO: It is obviously important to note that this is not meant as a "finished article".

DC: What else do you have in mind?

Simply that I don't know if you need to tweak it - it may be that you will want/need to build in some tolerance to your future PLAN headcount based on historic attrition - esp. if it has an impact on revenue (best to be prudent).

For ex. it might become clear from the actual headcount values that you are losing say 25% of starters over the first 6 months.
On which basis you might decide to build some formulae into the HEADCOUNT section specifically aimed at future months (eg Oct-10 onwards) in which leavers are "forecast" incrementally so as to give a more accurate reflection of revenue profile.
(this is potentially important if they do not get refunded what they pay - ie you should not understate starters given you get some revenue but equally you should not assume a full duration for each head)

This is not something we would do for you by the way... you might want to implement yourself if deemed worthwhile.

On a final note I've not read through this since composing it so it might have a few typos - I will correct them when I see them so you might see that I've edited the post etc - that will be why.

Incidentally - when you quote another poster it's much easier for others to follow if you encase within QUOTE tags as I have done here - much easier to tell who is saying what.