Home › Forums › Data issues › Apprenticeship Carry Over Funding
Tagged: Apprenticeship, Carry Over, funding
This topic contains 31 replies, has 4 voices, and was last updated by Peter Hancock 6 years, 4 months ago.

AuthorPosts

Hi, Apologies if this topic has already been discussed but my search of the forum hasn’t identified anything for me.
Could anyone advise on the best way of working out the carry over value of our continuing Apprenticeship learners into 14/15?
Thank you
Garry
July 17, 2014 at 6:31 am #7491Hi Garry,
Paul Baker did produce a profile tool last year but this is no longer available for download as I expect it is out of date.
If you are any good with Excel formula you could extend your PFR Skills Budget Occupancy Report where there is a July payment for each month in next year as the planned number of months is recorded together with the other factors needed.I normally use the SFA funding summary at period 12 for each age band to estimate the funding for next year and then use FIS when it is available in or before period 1.
Hope someone has a better solution for you.
HTHJuly 17, 2014 at 7:54 am #7492Hi,
If you want something simple you can use the OCC report and count on how many payments you have recieved with then deduct it from the Planned number of on programme instalments column. Then if you have 1 payment copy the July Payment to August, if you have 2 then copy it to August and September. You will also need to work out any achievement payments as well and use the counts to work out when these will be due.
I not had chance to start mine yet but it shouldnt take more than a few hours when i finally get a chance.
Cheers
Chris
July 17, 2014 at 10:16 am #7493Hi All,
Quick and dirty method using the PFR
It is best to sort this first by Funding line type, Learner reference number and Aim sequence number so that you can total by what you need.Insert column after [Learning start date], this should now be column X
Copy into column X Row 2
=IF(W20,IF(AU2X2>0,AU2X2,0),IF(AW2>12,AW212,0))
Copy this cell and paste down the column to the last entryCopy into empty column DQ Row 2
=IF(W2<DATEVALUE("1/8/2013"),0,IF(YEAR(W2)=2013,MONTH(W2)7,MONTH(W2)+5))
Copy this cell and paste down the column to the last entryInsert column after [Planned number of on programme instalments], this should now be column AV
Copy into column AV Row 2
=IF(X2=12,(DK2/2)*(IF(AV2>12,12,AV2)),DK2*(IF(AV2>12,12,AV2)))
Copy this cell and paste down the column to the last entryYou could add columns after this for each month using the same process using the basic formula for each month but adjusted for each month.
=IF(AV2>=X,IF(X2=12,DK2/2,DK2),0) where X is the month/period numberI have not included the achievement payment but this should not be to difficult to add
HTHJuly 17, 2014 at 10:32 am #7494I will try again as it did not come out as intended
Insert column after Learning start date, this should now be column X, Copy into column X Row 2 =IF(W20,IF(AU2X2>0,AU2X2,0),IF(AW2>12,AW212,0)) then Copy this cell and paste down the column to the last entry
Copy into empty column DQ Row 2 =IF(X2=12,(DK2/2)*(IF(AV2>12,12,AV2)),DK2*(IF(AV2>12,12,AV2))) Copy this cell and paste down the column to the last entryJuly 17, 2014 at 10:46 am #7495Sorry but the text does not come out as writen
July 17, 2014 at 10:54 am #7496This is missing
Insert column after Planned number of on programme instalments, this should now be column AV, Copy into column AV Row 2 =IF(X2>0,IF(AU2X2>0,AU2X2,0),IF(AW2>12,AW212,0)) then Copy this cell and paste down the column to the last entry
July 17, 2014 at 10:55 am #7497Hi Martin,
Thanks for your time on this, I really appreciate your help.
I’ve entered all the calculation as written (taking account account of the updates) and for some reason do not seem to be getting any data. I think the issue may lie in the first calculation which is in column X. I’ve added the calculation but all that’s being returned is 00/01/1900 which normally indicate that there is no data to report on?
Regards
Garry
July 17, 2014 at 11:59 am #7500The formula in column X row 2
=IF(W2<DATEVALUE("1/8/2013"),0,IF(YEAR(W2)=2013,MONTH(W2)7,MONTH(W2)+5))July 17, 2014 at 12:05 pm #7501will do one step at a time so text comes out correct
July 17, 2014 at 12:08 pm #7502Copy into column AV Row 2
=IF(X2>0,IF(AU2X2>0,AU2X2,0),IF(AW2>12,AW212,0))July 17, 2014 at 12:09 pm #7503Copy into empty column DQ Row 2
=IF(X2=12,(DK2/2)*(IF(AV2>12,12,AV2)),DK2*(IF(AV2>12,12,AV2)))July 17, 2014 at 12:09 pm #7504Afterthought you may have to change the column X format from date to number
July 17, 2014 at 12:16 pm #7505Hi Martin,
Are now in a position where I have figures in Column DQ. Can you confirm what the figures are in this column, i.e is it just the carry over figure without the achievement factor?
Regards
Garry
July 17, 2014 at 2:41 pm #7506DQ is the total of the monthly payments in 14/15
If you add additional columns Aug to Jul and in row 2 enter the following
For Aug =IF(AV2>=1,IF(X2=12,DK2/2,DK2),0)
For Sep =IF(AV2>=2,IF(X2=12,DK2/2,DK2),0)
For Oct =IF(AV2>=3,IF(X2=12,DK2/2,DK2),0)
For Nov =IF(AV2>=4,IF(X2=12,DK2/2,DK2),0)
For Dec =IF(AV2>=5,IF(X2=12,DK2/2,DK2),0)
For Jan =IF(AV2>=6,IF(X2=12,DK2/2,DK2),0)
For Feb =IF(AV2>=7,IF(X2=12,DK2/2,DK2),0)
For Mar =IF(AV2>=8,IF(X2=12,DK2/2,DK2),0)
For Apr =IF(AV2>=9,IF(X2=12,DK2/2,DK2),0)
For May =IF(AV2>=10,IF(X2=12,DK2/2,DK2),0)
For Jun =IF(AV2>=11,IF(X2=12,DK2/2,DK2),0)
For Jul =IF(AV2>=12,IF(X2=12,DK2/2,DK2),0)
This should give the profile for the year
As the funding rate is in column J you can X this by 0.2 to get the achievement payment more about how to modify the above to include this later.July 17, 2014 at 3:08 pm #7508Hi Martin,
I think we’re close!
I’ve added the columns but no figures appear. I think the above should refer to a different column than DK as the heading for this column is ‘June Learning Support earned cash’?
Kind Regards
Garry
July 17, 2014 at 4:06 pm #7509A few changes to tidy it up
Instead of DK2 use July On Programme Earned Cash column
DQ is the total of the monthly payments in 14/15
Revised to include only carry over aims
=IF(Z2=1,IF(X2=12,(DK2/2)*(IF(AV2>12,12,AV2)),DK2*(IF(AV2>12,12,AV2))),0)
If you revise the additional columns Aug to Jul and in row 2 enter the following
For Aug =IF(Z2=1,IF(AV2>=1,IF(X2=12,DK2/2,DK2+(IF(AV2=1,(J2*0.2),0))),0),0)
Change the value of AV2 to the month number for each monthThis should give the profile for the year for carry over plus the achievement drawn down in the last period.
July 17, 2014 at 4:09 pm #7510Hi Martin,
Really sorry about this, but I’m finding it difficult to understand your latest instructions. One of things I’m confused about is the reference within the calculation to column X as being a number but it is the learners start date?
Kind Regards
Garry
July 18, 2014 at 7:25 am #7511Hi Garry,
The reference X2 is the column you put the first formula in which calculates the Start Period and you may need to change this as I inserted this column after column W the learning start date.
You should have now one column that has the total for 14/15
The second formula also has a X reference but this I used for the following:
You can add extra columns one for each month and X represents the month number, 1 for Aug 14, 2 Sep 14………12 Jul 15 and the last formula relates to these additional columns.
This will enable you to see the profile for the year for carry over learners.July 18, 2014 at 7:41 am #7512Hi Martin,
Thanks for the latest explanation which allowed me to complete this task. I have checked the results and are confident that the funding is bang on.
Thank you so much for your time (and patience) you really have been a great help in helping me achieve this for tcollege.
I definately owe you a coffee when we meet 🙂
Cheers
Garry
July 18, 2014 at 12:05 pm #7529Hi Garry,
No quite bang on.
The monthly funding should be correct for carry over learners but it excludes those learners who have no funding remaining but have yet to achieve. The achievement payment added to the last period for each learner is only indicative as age factor and or funding adjustment have not been taken into account but for the norm it should be ok.
After doing this it is clear it would be better if all the additional added columns were place to the right of the spreadsheet as the top two rows for these columns could then be copied and pasted into any PFR produced in the future.
I needed to do the exercise myself so only too glad I could be of help.July 18, 2014 at 1:22 pm #7530Hello Martin,
I’ve been thiking of doing something like this for ages, and I got drawn in when I saw this thread. I’ve followed your instructions and remain a bit baffled.
We have some aims with funding in 13/14 which are due to complete in 14/15, but the column DQ shows no funding for some of them. So, I tried to unpick your formulas to see what you were trying to do.
I can see that Column “X” is the period of start in 13/14.
What is Column AV doing? Although it starts with If X2>0, I think that X2 will always be greater than zero. In AV2, my formula is: =IF(X2>0,IF(AU2X2>0,AU2X2,0),IF(AW2>12,AW212,0)).
Your further input would be valued.
I think we all require this information about future funding committment and it is really disappointing that the SFA reports do not calculate it for us automatically. I wonder if the SFA systems do calculate future funding committments for internal consumption only?
Regards
Peter
July 18, 2014 at 3:36 pm #7531Hi Peter
Column X represents the start period in 2013/14 or 0 if a start prior to thisColumn AV represents the months left after the end of 2013/13 and for:
Starts prior to 13/14 calculates this from the Transitional Planned number of programme instalments from 1 August 2013
Starts in 13/14 calculates this from the Planned number of on programme instalments
HTHJuly 18, 2014 at 4:19 pm #7532Hi Martin,
Thanks for your reply. I am on the same wavelength as you – you have replied as I thought you would. But I’m still grappling with the formula in AV as I’m not sure it does what you say.Your formula is:
Months remaining = If (planned installments – Period of start >0), Then (planned installments – Period of start), Else 0.Shouldn’t this be:
Months remaining = If (planned installments – (13Period of start)) >0, then (planned installments – (13Period of start) ), Else 0.
In the following example:
Start in 13/14 period 12 with 12 planned installments:
We would want months remaining = 11.Your formula would give 0 months left (in column AV).
My formula would give 11.What do you think? Hope you don’t mind me bouncing this around?
Peter
July 21, 2014 at 10:07 am #7541Hi Peter,
Column X should only returns the start period if it was in 2103/14 so the +1 will already have been used and it should have been AU2(12X2) but where the start period returns 0 it could be in any prior year thus that is why I have used the Transitional number of instalments with (AW212).
HTHJuly 21, 2014 at 10:50 am #7543
You must be logged in to reply to this topic.