{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["advanced learner loans"],"not_rendered":{"charts":0,"images":1,"macros":0,"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":"advanced learner loans","index":0,"n_rows":89,"n_cols":8,"rows_kept":89,"cut":false,"hidden":false,"merged":[[20,7,21,7],[49,7,52,7],[34,7,35,7],[82,7,83,7],[86,7,87,7],[28,7,29,7],[30,7,31,7],[32,7,33,7],[57,7,58,7],[69,7,70,7],[59,7,62,7],[88,7,89,7],[53,7,56,7],[9,3,9,7],[38,7,39,7],[40,7,41,7],[45,2,45,3],[46,2,47,7],[36,7,37,7],[10,7,11,7],[12,5,12,7],[13,3,13,7],[16,2,16,3],[18,7,19,7],[22,7,23,7],[24,7,25,7],[26,2,26,3],[0,4,0,7],[1,1,1,8],[3,1,3,8],[6,2,6,3],[7,2,8,7],[73,2,73,3],[74,7,75,7],[65,7,66,7],[71,7,72,7],[80,7,81,7]]}
[]
[null,"Advanced learner loans 2021 to 2022 allocations calculator"]
[]
[null,"Disclaimer: \nThis is a tool to help you understand how your allocation has been calculated, it is not your allocation and values may differ.  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.  Please see the business case section of our 19+ funding allocations page for more information about this process. "]
[]
[]
[null,null,"Your information"]
[null,null,"Complete the yellow cells and the calculation steps will populate to show how we have worked the value of your 2021 to 2022 advanced learner loans  (ALL) allocation.  If 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,"You only need to complete your UKPRN and provider name if you plan to share your calculations with the ESFA."]
[]
[null,null,null,"Provider legal name"]
[]
[null,null,null,"What type of contract do you have?"]
[]
[null,null,"Historic year delivery and contract values"]
[]
[null,null,"a.","Value of approved, attended and paid ALL applications in 2018 to 2019","2018/19",null,null,"Enter the value of your approved, attended and paid ALLs applications for the 2018 to 2019 funding year.  You can find this on your SLC portal.  If you didn't have a ALL facility or didn't use any of your facility enter £0."]
[]
[null,null,"b.","Value of approved, attended and paid ALL applications in 2019 to 2020 as at 27 January 2020",null,null,null,"Enter the value of your approved, attended and paid ALL applications as at 27 January 2020 for 2019 to 2020.  You can find this on your SLC portal. If you didn't have a ALL facility or didn't use any of your facility enter £0."]
[]
[null,null,"c.","Value of approved, attended and paid ALL applications in 2019 to 2020","2019/20",null,null,"Enter the value of your approved, attended and paid ALLs applications for the 2019 to 2020 funding year.  You can find this on your SLC portal.  If you didn't have a ALL facility or didn't use any of your facility enter £0."]
[]
[null,null,"d.","Value of ALL bursary delivered in 2019 to 2020",null,null,null,"Enter the value of your loans bursary claimed or delivered in 2019 to 2020 funding year.  You can find this on your final funding claim or funding summary report.  If you didn't have a loans bursary or didn't deliver any of your loan bursary enter £0."]
[]
[null,null,"Current years delivery and contract value"]
[]
[null,null,"e.","Value of approved, attended and paid ALL applications in 2020 to 2021 (to-date)","2020/21",null,null,"Enter the value of your approved, attended and paid ALL applications as at 1 February 2021 for the 2020 to 2021 funding year.  You can find this on your SLC portal. If you didn't have a ALL facility or didn't use any of your facility enter £0."]
[]
[null,null,"f.","Value of your 2020 to 2021 ALL facility, as at February 2021",null,null,null,"Enter the value of your ALL facility for the 2020 to 2021 funding year as at February 2021.  This should include any growth at performance-management reviews.  You should not include your loans bursary as this is a separate allocation."]
[]
[null,null,"g.","Value of your 2020 to 2021 ALL bursary, as at February 2021",null,null,null,"Enter the value of your ALL bursary for the 2020 to 2021 funding year as at February 2021.  This should include any growth at performance-management reviews."]
[]
[null,null,"h.","Value of your 2021 to 2022 calculated facility",null,null,null,"Enter the value of your ALL facility for the 2021 to 2022 funding year.  This is the value we have calculated for your you and can be found on 'Manage your education and skills funding'.  You should not include your loans bursary as this is a separate allocation."]
[]
[null,null,"i.","Have you received an increase to your 2020 to 2021 facility value?",null,null,null,"Select yes if you were awarded advanced learner loans growth at the performance the two performance management reviews of the 2020 to 2021 funding year. Select no if your contract value was reduced or stayed the same or had a new facility."]
[]
[null,null,"j.","Have you received a new ALL facility in 2020 to 2021?",null,null,null,"Select yes if you applied and awarded a new facility allocation at a performance management review of the 2020 to 2021 funding year."]
[]
[null,null,"k.","Do you have a track record issue?",null,null,null,"Select yes if any of the following apply to you:\n- you are in formal intervention for minimum standards or inspection \n- you have been issued with a notice for financial control\n- you have been issued with a notice to improve, additional conditions of funding or additional contractual obligations in relation to minimum quality standards for 19+ education and training\nSelect no if none of the above apply to your organisation."]
[]
[]
[]
[]
[null,null,"Calculation steps "]
[null,null,"This section will automatically populate when you complete your information in the section above."]
[]
[]
[null,null,"l.","Historic proportion of delivery between February and July",null,0,null,"To get a full years delivery in 2020 to 2021 we take an average of historic delivery between February and July from 2018 to 2019 and 2019 to 2020. For the basis of this calculator we are using the data included for 2019 to 2020. It is calculated by taking your February to July 2020 delivery in the 2019 to 2020 funding year divided by your 2019 to 2020 full year delivery (as a %). The maximum percentage is 55% based on the standard national profile.\n\nThis is then multiplied by your 2020 to 2021 approved attended and paid applications in 2020 to 2021 (e)"]
[]
[null,null,"m.","Approved, attended and paid ALL applications in 2020 to 2021 uplifted to a full year","2020/21",{"$f":"F29*(1+F50)","v":0}]
[]
[null,null,"n.","The year in which your allocation is based on",null,{"$f":"IF(F31=0,\"out of scope\",\nIF(F39=\"Yes\",\"2020/21 facility\",\nINDEX(E19:E53,MATCH(MAX(F19,F23,F52),F19:F53,0))))","v":"out of scope"},null,"The 2021 to 2022 baseline is calculated by taking the higher of your 2018 to 2019 (a), 2019 to 2020 (c) or 2020 to 2021 (e) approved, attended, paid & submitted ALL figure.  If you have a new facility this is the value of your 2020 to 2021 facility (f).\n\nIf you have delivery in 2018 to 2019 only, your baseline cannot be more than your 2020 to 2021 facility."]
[]
[null,null,"o.","Baseline",null,{"$f":"IF(F31=0,0,\nIF(F39=\"Yes\",F31,\n(MAX(F19,F23,F52))))","v":0}]
[]
[null,null,"p.","Adjustment to align with performance management and tolerances",null,{"$f":"IF(F37=\"yes\",IF(((F56-F31)/F31)>50%,F31-F56,0),\nIF(F56>F31,F31-F56,0))","v":0},null,"If your 2020 to 2021 loans facility was reduced or did not change at the review points in 2020 to 2021 (in October and February), we reduce your baseline to current 2020 to 2021 loans facility (f)."]
[]
[null,null,null,"National skills fund level 3 proportion",null,{"$f":"IFERROR(IF(-(SUM(F56:F58)-F35)/SUM(F56:F58)>0,0,-(SUM(F56:F58)-F35)/SUM(F56:F58)),0)","v":0},null,"We reduce your allocation to account for the delivery that is included in the National Skills Fund level 3 adult offer using 2019 to 2020 funding year data. We do this by identifying what proportion of loans delivery recorded in the ILR is eligible for the National Skills Fund level 3 adult offer where prior attainment of the learner is below level 3. This is created as a proxy. For the full method please see the 2021 to 2022 allocations technical guidance."]
[]
[null,null,"q.","National skills fund level 3",null,{"$f":"SUM(F56:F58)*F60","v":0}]
[]
[null,null,"r.","Adjustment for minimum allocation",null,{"$f":"IF(AND(F56>0,SUM(F56:F58,F62)<5000),5000-SUM(F56:F58,F62),0)","v":0},null,"If your allocation is calculated to be under £2,500, then we uplift it to £2,500."]
[null,null,null,null,null,"+"]
[null,null,"s.","Adjustment for track record",null,{"$f":"IF(AND(F41=\"Yes\",SUM(F56:F58)>F31),F31-SUM(F56:F58),0)","v":0},null,"In line with our performance-management rules, we will not give you an allocation greater than your 2020 to 2021 contract value if you have a track record issue (k)."]
[null,null,null,null,null,"="]
[null,null,"t.","Calculated 2020 to 2021 advanced learner loans facility allocation",null,{"$f":"ROUND(SUM(F56,F64,F66,F58,F62),0)","v":0},null,"This is your total allocation for the 2021 to 2022 funding year"]
[]
[null,null,"u.","August 2021 to March 2022",null,{"$f":"ROUND(F68*63.17%,0)","v":0},null,"This is your allocation for periods 1 to 8 of the 2021 to 2022 funding year. This is based on the standard national profile published in advanced learner loans funding rules"]
[]
[null,null,"v.","April 2022 to July 2022",null,{"$f":"F68-F70","v":0},null,"This is your allocation for periods 9 to 12 of the 2021 to 2022 funding year. This is based on the standard national profile published in advanced learner loans funding rules"]
[]
[null,null,"Advanced learner loans bursary allocation"]
[null,null,"w.","Proportion of bursary delivery",null,{"$f":"ROUNDUP(IFERROR(MIN(50%,F25/F23),0),3)","v":0},null,"Your ALL bursary is calculated by taking proportion of bursary against facility. We have divided your ALL bursary delivered in 2019 to 2020 (d) by value of approved, attended and paid ALL applications in 2019 to 2020 (c). The maximum proportion is 50%."]
[null,null,null,null,null,"x"]
[null,null,"x.","Calculated 2020 to 2021 advanced learner loans facility allocation",null,{"$f":"F68","v":0},null,"The proportion is then multiplied by your total allocation for the 2021 to 2022 funding year (t)."]
[null,null,null,null,null,"="]
[null,null,"y.","Initial calculated 2021 to 2022 allocation",null,{"$f":"F75*F77","v":0}]
[null,null,null,null,null,"+"]
[null,null,"z.","Adjustment for minimum allocation",null,{"$f":"IF(OR(AND(F79>0,F79<500),AND(F79=0,F33>0,F77>0)),500-F79,0)","v":0},null,"If you previously had a bursary allocation and your allocation is calculated to be under £500, then we uplift it to £500."]
[null,null,null,null,null,"+"]
[null,null,"aa.","Adjustment for track record",null,{"$f":"IF(AND(F41=\"Yes\",SUM(F79:F82)>F33),F33-SUM(F79:F82),0)","v":0},null,"In line with our performance-management rules, we will not give you an allocation greater than your 2019 to 2020 contract value if you have a track record issue (k)."]
[null,null,null,null,null,"="]
[null,null,"ab.","Calculated 2021 to 2022 advanced learner loans bursary allocation",null,{"$f":"ROUND(SUM(F79,F81,F83),0)","v":0},null,"This is your total allocation for the 2021 to 2022 funding year."]
[]
[null,null,"ac.","August 2021 to March 2022",null,{"$f":"ROUND(IF(F15=\"grant\",F85*75%,F85*58%),0)","v":0},null,"This is your allocation for periods 1 to 8 of the 2021 to 2022. funding year. This is based on the standard national profile published in advanced learner loans funding rules."]
[]
[null,null,"ad.","April 2022 to July 2022",null,{"$f":"F85-F87","v":0},null,"This is your allocation for periods 9 to 12 of the 2021 to 2022 funding year. This is based on the standard national profile published in advanced learner loans funding rules."]