{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["19-24 traineeships calculator"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":2,"comments":0,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"rows_first":2000,"rows_last":500,"rows_between":2000,"halvings":0,"cell_chars":16384,"cell_head_chars":4096},"cells_cut":0,"cells_raw":0,"read":"whole","date_system":"1900"}

{"sheet":"19-24 traineeships calculator","index":0,"n_rows":119,"n_cols":13,"rows_kept":119,"cut":false,"hidden":false,"merged":[[39,7,40,7],[110,7,111,7],[108,7,109,7],[103,7,104,7],[112,7,113,7],[105,7,107,7],[90,7,91,7],[92,7,93,7],[33,7,34,7],[22,13,22,20],[22,21,22,24],[29,13,29,20],[29,21,29,24],[26,7,27,7],[28,7,30,7],[3,1,3,8],[1,1,1,8],[8,2,9,7],[6,2,6,3],[18,21,18,24],[18,13,18,20],[13,5,13,7],[19,7,20,7],[21,7,22,7],[14,3,14,7],[52,7,54,7],[58,2,58,3],[17,2,17,3],[15,7,17,7],[23,7,24,7],[35,2,35,3],[25,2,25,3],[37,2,37,7],[45,7,47,7],[43,7,44,7],[41,7,42,7],[50,7,51,7],[31,7,32,7],[84,2,84,3],[100,2,100,3],[48,2,48,3],[67,7,68,7],[63,7,64,7],[80,7,82,7],[59,2,60,7],[61,2,61,3],[75,7,76,7],[88,7,89,7],[72,7,74,7]]}
[]
[null,"19-24 traineeships 2019 to 2020 allocations calculator "]
[]
[null,"Disclaimer: \nThis is a tool to help you understand how your allocation has been calculated, it is not your allocation value.  Your digital allocation statement on MyESF sets out your allocation values.\nPlease read the information on our 19+ funding allocations page and our technical guidance before using this calculator.  \nIf you have experienced a data submission problem that has significantly affected the allocation we have calculated for you then you can raise a business case through your territorial team contact.  Please see the business case section of our 19+ funding allocations page for more information about this process.  We will not accept requests for additional funding not linked to technical errors.  ",null,null,null,null,null,null,null,null,null,null,"Grant"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Contract for service"]
[]
[null,null,"Your information",null,null,null,null,null,null,null,null,"Yes"]
[null,null,null,null,null,null,null,null,null,null,null,"No"]
[null,null,"Complete the yellow cells and the calculation steps will populate to show how we have worked the value of your 2019 to 2020 19-24 traineeships allocation.  If you have a grant funding agreement in addition to a contract for service awarded through the AEB procurement, these remain separate allocations so you will need to calculate each allocation separately.  The cells below will grey out depending on what type of contract you select.  You only need to complete the yellow cells, leave the grey cells blank.\n\nIf you have merged with another organisation then you need to include the total for all of the organisations involved in the merger in the values you provide below."]
[]
[]
[null,null,null,"UKPRN"]
[]
[null,null,null,"Provider legal name"]
[]
[null,null,"a.","What type of contract do you have?",null,null,null,"Select your adult education budget contract type from the drop down menu.  The cells below will grey out depending on what type of contract you select.  You only need to complete the yellow cells, leave the grey cells blank."]
[]
[null,null,"2017/18 delivery - grant funding agreement"]
[]
[null,null,"b.","August 2017 to July 2018 (full year) 19-24 traineeships delivery",null,null,null,"Enter the value of your 19-24 traineeships programme and learning support delivery in the 2017/18 funding year as recorded in your R14 data return.  Do not include learner support funding."]
[]
[null,null,"c.","August 2017 to July 2018 (full-year) AEB delivery",null,null,null,"Enter the value of your total adult education budget delivery in the 2017/18 funding year as recorded in your final funding claim. This should include adult skills (including traineeships), community learning excluding any unfunded over delivery (where applicable) and learner support."]
[]
[null,null,"d.","August 2017 to July 2018 (full-year) learner support delivery",null,null,null,"Enter the value of your learner support delivery in the 2017/18 funding year as recorded in your final funding claim."]
[]
[null,null,"2017/18 delivery - contract for service - procured contract"]
[null,null,null,null,null,null,null,"Enter the value of your 19-24 traineeships programme and learning support delivery on your procured contract in the 2017/18 funding year as recorded in your R14 data return.  Do not include learner support funding."]
[null,null,"e.","November 2017 to July 2018 19-24 traineeships delivery"]
[null,null,null,null,null,null,null,"You do not need to enter a value in this cell, it has been calculated based on the information you have provided.  To uplift your delivery to a 12 month value we divide your November 2017 to July 2018 19-24 traineeships delivery (f) by 73.49% (the standard national profile for November to July) and then multiply the outcome by 100%."]
[null,null,"f.","November 2017 to July 2018 19-24 traineeships delivery uplifted to a full year value",null,{"$f":"IF(F16=\"contract for service\",(F28/73.49%)*1,\"\")","v":""}]
[]
[null,null,"g.","November 2017 to July 2018 adult skills delivery",null,null,null,"Enter the value of your total adult skills delivery on your procured contract in the 2017/18 funding year as recorded in your R14 data return.  Include traineeships delivery."]
[]
[null,null,"h.","November 2017 to July 2018 learner support delivery",null,null,null,"Enter the value of your learner support delivery on your procured contract in the 2017/18 funding year as recorded in your final funding claim.  If you did not have a learner support allocation then enter £0."]
[]
[null,null,"2017/18 delivery - contract for service - run-down contract"]
[]
[null,null,"We notified you on 29 September 2017 that we would be extending your 2017/18 contract on a 9 month run-down basis.  If you did not receive a run-down contract extension (e.g. you received an allocation for the first time through the AEB procurement) then enter £0 in the boxes in this section. "]
[]
[null,null,"i.","November 2017 to July 2018 19-24 traineeships delivery",null,null,null,"If you received a run-down extension then enter the value of your delivery on this contract between November 2017 and July 2018. If you didn't receive a run-down extension then enter £0."]
[]
[null,null,"j.","November 2017 to July 2018 19-24 traineeships delivery uplifted to a full year value",null,{"$f":"IF(F16=\"contract for service\",(F40/73.49%)*1,\"\")","v":""},null,"You do not need to enter a value in this cell, it has been calculated based on the information you have provided.  To uplift your delivery to a 12 month value we divide your November 2017 to July 2018 19-24 traineeships delivery by 73.49% (the standard national profile for November to July) and then multiply the outcome by 100%."]
[]
[null,null,"k.","November 2017 to July 2018 adult skills delivery",null,null,null,"If you received a run-down extension then enter the value of your total adult skills delivery between November 2017 and July 2018 as recorded in your R14 data return.  Include traineeships delivery.  If you didn't receive a run-down extension then enter £0."]
[]
[null,null,"l.","August 2017 to July 2018 (full year) learner support delivery",null,null,null,"If you received a run-down extension then enter the value of your learner support delivery in the 2017/18 funding year (August 2017 to July 2018) as recorded in your final funding claim.  If you didn't receive a run-down extension then enter £0.  If you did not have a learner support allocation then enter £0."]
[]
[]
[null,null,"2018/19 delivery and contract value"]
[]
[null,null,"m.","August to December 2018 19-24 traineeships delivery as of R05",null,null,null,"Enter the value of your 19-24 traineeships programme and learning support delivery between August and December 2018 as recorded in your R05 data return.  Do not include learner support funding."]
[]
[null,null,"n.","August to December 2018 19-24 traineeships delivery as of R05 uplifted to a full year value",null,{"$f":"IF(F16=\"contract for service\",(F51/41.72%)*1,IF(F16=\"grant\",(F51/44.46%)*1,\"\"))","v":""},null,"You do not need to enter a value in this cell, it has been calculated based on the information you have provided.  To uplift your delivery to a 12 month value we divide your August to December 2018 19-24 traineeships delivery by 44.46% for grant providers and 41.72% for contract for services (the standard national profile for August to December) and then multiply the outcome by 100%."]
[]
[]
[]
[]
[]
[null,null,"Calculation steps "]
[null,null,"This section will automatically populate when you complete the your information section above."]
[]
[null,null,"19-24 traineeships allocation"]
[]
[null,null,"o.","2017/18 full year delivery",null,{"$f":"IF(F16=\"contract for service\",F42+F30,F20)","v":0},null,"For contract for services we take your 19-24 traineeships delivery on procured and run-down allocations between November 2017 and July 2018 and uplift this to a full year value using the standard national profile (f + j)."]
[]
[null,null,"p.","2018/19 delivery uplifted to a full year value",null,{"$f":"F53","v":""},null,"We uplift your 2018/19 delivery between July and December 2018 using the standard national profile (n)."]
[]
[null,null,"q.","Baseline",null,{"$f":"IF(F66>F64,F66,F64)","v":""},null,"This is the higher of your  2017/18 full year delivery or your 2018/19 delivery uplifted to a full year value.  The highest value is shown in green."]
[]
[null,null,"r.","5% increase",null,{"$f":"IFERROR(F68*5%,\"\")","v":""},null,"We have applied a 5% increase."]
[]
[null,null,"s.","Baseline + 5% increase",null,{"$f":"IFERROR(F68+F70,\"\")","v":""}]
[null,null,null,null,null,null,null,"If the 2019/20 allocation we have calculated for you is less than £1,000, we will not give you an allocation.  You can request an allocation through our business case process if you plan to start delivering 19-24 traineeships in 2019/20."]
[null,null,"t.","Adjustment if below £1,000",null,{"$f":"IF(F72<1000,-F72,0)","v":0}]
[]
[null,null,"u.","Adjustment if more than £1,000 but less than £5,000",null,{"$f":"IF(AND(F72>1000,F72<5000),5000-F72,0)","v":0},null,"If the 2019/20 allocation we have calculated for you is greater than £1,000 but less than £5,000 then we will uplift your allocation to £5,000."]
[]
[null,null,"v.","Calculated 2019 to 2020 19-24 traineeships allocation",null,{"$f":"IFERROR(F72+F76+F74,\"\")","v":""},null,"This is your total allocation for the 2019/20 funding year."]
[]
[null,null,"w.","August 2019 to March 2020",null,{"$f":"IFERROR(ROUND(IF(F16=\"contract for service\",F78*67.05%,F78*62.65%),0),\"\")","v":""},null,"This is your allocation for periods 1 to 8 (August 2019 to March 2020) of the 2019/20 funding year."]
[null,null,null,null,null,null,null,"This is your allocation for periods 9 to 12 (April 2020 to July 2020) of the 2019/20 funding year."]
[null,null,"x.","April 2020 to July 2020",null,{"$f":"IFERROR(F78-F80,\"\")","v":""}]
[]
[]
[null,null,"19-24 traineeships learner support allocation - grant funding agreement"]
[]
[null,null,"y.","2019/20 traineeships allocation",null,{"$f":"IF(F16=\"grant\",F78,\"\")","v":""},null,"This is the allocation value that has been calculated using the information you have provided (v)."]
[]
[null,null,"z.","% of your total AEB used for learner support in 2017/18",null,{"$f":"IFERROR(IF(F16=\"grant\",F24/F22,0),\"\")","v":0},null,"To calculate this value we divide your 2017/18 full year learner support delivery (d) by your 2017/18 full year total AEB delivery (c)."]
[]
[null,null,"aa.","2019/20 traineeships allocation x % of your AEB used for learner support in 2017/18",null,{"$f":"IFERROR(F87*F89,\"\")","v":""},null,"We then multiply your 2019/20 traineeships allocation by the % of AEB used for learner support."]
[]
[null,null,"ab.","Adjustment for £500 minimum allocation",null,{"$f":"IF(AND(F16=\"grant\",F91<500,F87>0),500-F91,0)","v":0},null,"We have applied a £500 minimum allocation, if the allocation we have calculated for you is less than £500 then we will uplift it to £500."]
[]
[null,null,"ac.","Calculated 2019 to 2020 19-24 traineeships learner support allocation",null,{"$f":"IFERROR(F91+F93,\"\")","v":""},null,"This is your total allocation for the 2019/20 funding year."]
[]
[null,null,"ad.","August 2019 to March 2020",null,{"$f":"IFERROR(ROUND(F95*62.65%,0),\"\")","v":""},null,"This is your allocation for periods 1 to 8 (August 2019 to March 2020) of the 2019/20 funding year."]
[]
[null,null,"ae.","April 2020 to July 2020",null,{"$f":"IFERROR(F95-F97,\"\")","v":""},null,"This is your allocation for periods 9 to 12 (April 2020 to July 2020) of the 2019/20 funding year."]
[]
[null,null,"19-24 traineeships learner support allocation - contract for service"]
[]
[null,null,"af.","2019/20 traineeships allocation",null,{"$f":"IF(F16=\"contract for service\",F78,\"\")","v":""},null,"This is the allocation value that has been calculated using the information you have provided (v)."]
[null,null,null,null,null,null,null,"This your learner support delivery on your procured contract between November 2017 and July 2018 plus your learner support delivery on your run-down contract between August 2017 and July 2018 where applicable (h + l)."]
[null,null,"ag.","2017/18 learner support delivery",null,{"$f":"IF(F16=\"contract for service\",F34+F46,\"\")","v":""}]
[null,null,null,null,null,null,null,"This your adult skills and learner support delivery on your procured and run-down contract between November 2017 and July 2018 plus your learner support delivery on your run-down contract between August 2017 and July 2018 (g + k + h + l)."]
[null,null,"ah.","2017/18 adult skills and learner support delivery",null,{"$f":"IF(F16=\"contract for service\",F32+F34+F44+F46,\"\")","v":""}]
[]
[null,null,"ai.","% of your AEB used for learner support in 2017/18",null,{"$f":"IFERROR(F105/F107,0)","v":0},null,"To calculate this value we divide your 2017/18 learner support delivery (ag) by your 2017/18 adult skills and learner support delivery (ah)."]
[]
[null,null,"aj.","2019/20 traineeships allocation x % of your AEB used for learner support in 2017/18",null,{"$f":"IFERROR(F103*F109,\"\")","v":""},null,"We then multiply your 2019/20 traineeships allocation by the % of AEB used for learner support."]
[]
[null,null,"ak.","Adjustment for £500 minimum allocation",null,{"$f":"IF(AND(F16=\"contract for service\",F103>0,F111<500),500-F111,0)","v":0},null,"We have applied a £500 minimum allocation, if the allocation we have calculated for you is less than £500 then we will uplift it to £500."]
[]
[null,null,"al.","Calculated 2019 to 2020 19-24 traineeships learner support allocation",null,{"$f":"IFERROR(F111+F113,\"\")","v":""},null,"This is your total allocation for the 2019/20 funding year."]
[]
[null,null,"am.","August 2019 to March 2020",null,{"$f":"IFERROR(ROUND(F115*75%,2),\"\")","v":""},null,"This is your allocation for periods 1 to 8 (August 2019 to March 2020) of the 2019/20 funding year."]
[]
[null,null,"an.","April 2020 to July 2020",null,{"$f":"IFERROR(F115-F117,\"\")","v":""},null,"This is your allocation for periods 9 to 12 (April 2020 to July 2020) of the 2019/20 funding year."]