Apprenticeship Carry Over Funding

Home Forums Data issues Apprenticeship Carry Over Funding

This topic contains 31 replies, has 4 voices, and was last updated by  Peter Hancock 6 years, 4 months ago.

Viewing 25 posts - 1 through 25 (of 32 total)
  • Author
    Posts

  • Garry Welborn
    Participant

    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

     
    #7491

    Martin West
    Participant

    Hi 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.
    HTH

     
    #7492

    chrislant1
    Participant

    Hi,

    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

     
    #7493

    Martin West
    Participant

    Hi 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(AU2-X2>0,AU2-X2,0),IF(AW2>12,AW2-12,0))
    Copy this cell and paste down the column to the last entry

    Copy 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 entry

    Insert 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 entry

    You 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 number

    I have not included the achievement payment but this should not be to difficult to add
    HTH

     
    #7494

    Martin West
    Participant

    I 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(AU2-X2>0,AU2-X2,0),IF(AW2>12,AW2-12,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 entry

     
    #7495

    Martin West
    Participant

    Sorry but the text does not come out as writen

     
    #7496

    Martin West
    Participant

    This 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(AU2-X2>0,AU2-X2,0),IF(AW2>12,AW2-12,0)) then Copy this cell and paste down the column to the last entry

     
    #7497

    Garry Welborn
    Participant

    Hi 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

     
    #7500

    Martin West
    Participant

    The formula in column X row 2
    =IF(W2<DATEVALUE("1/8/2013"),0,IF(YEAR(W2)=2013,MONTH(W2)-7,MONTH(W2)+5))

     
    #7501

    Martin West
    Participant

    will do one step at a time so text comes out correct

     
    #7502

    Martin West
    Participant

    Copy into column AV Row 2
    =IF(X2>0,IF(AU2-X2>0,AU2-X2,0),IF(AW2>12,AW2-12,0))

     
    #7503

    Martin West
    Participant

    Copy into empty column DQ Row 2
    =IF(X2=12,(DK2/2)*(IF(AV2>12,12,AV2)),DK2*(IF(AV2>12,12,AV2)))

     
    #7504

    Martin West
    Participant

    Afterthought you may have to change the column X format from date to number

     
    #7505

    Garry Welborn
    Participant

    Hi 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

     
    #7506

    Martin West
    Participant

    DQ 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.

     
    #7508

    Garry Welborn
    Participant

    Hi 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

     
    #7509

    Martin West
    Participant

    A 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 month

    This should give the profile for the year for carry over plus the achievement drawn down in the last period.

     
    #7510

    Garry Welborn
    Participant

    Hi 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

     
    #7511

    Martin West
    Participant

    Hi 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.

     
    #7512

    Garry Welborn
    Participant

    Hi 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

     
    #7529

    Martin West
    Participant

    Hi 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.

     
    #7530

    Peter Hancock
    Participant

    Hello 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(AU2-X2>0,AU2-X2,0),IF(AW2>12,AW2-12,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

     
    #7531

    Martin West
    Participant

    Hi Peter
    Column X represents the start period in 2013/14 or 0 if a start prior to this

    Column 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
    HTH

     
    #7532

    Peter Hancock
    Participant

    Hi 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 – (13-Period of start)) >0, then (planned installments – (13-Period 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

     
    #7541

    Martin West
    Participant

    Hi 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-(12-X2) 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 (AW2-12).
    HTH

     
    #7543
Viewing 25 posts - 1 through 25 (of 32 total)

You must be logged in to reply to this topic.