{"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":1,"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":86,"n_cols":8,"rows_kept":86,"cut":false,"hidden":false,"merged":[[68,2,68,3],[69,7,70,7],[60,7,61,7],[66,7,67,7],[75,7,76,7],[77,7,78,7],[35,7,36,7],[52,7,53,7],[56,7,57,7],[64,7,65,7],[23,7,24,7],[25,2,25,3],[27,7,28,7],[29,7,30,7],[31,7,32,7],[0,4,0,7],[1,1,1,8],[3,1,3,8],[6,2,6,3],[8,2,9,7],[79,7,80,7],[83,7,84,7],[85,7,86,7],[48,7,51,7],[10,3,10,7],[37,7,38,7],[39,7,40,7],[44,2,44,3],[45,2,46,7],[33,7,34,7],[11,7,12,7],[13,5,13,7],[14,3,14,7],[17,2,17,3],[19,7,20,7],[21,7,22,7]]}
[]
[null,"Advanced learner loans 2020 to 2021 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.  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 2020 to 2021 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"]
[]
[null,null,"a.","Value of approved, attended and paid ALL applications in 2017 to 2018",null,null,null,"Enter the value of your approved, attended and paid ALLs applications for the 2017 to 2018 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 2018 to 2019",null,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,"c.","Value of ALL bursary delivered in 2018 to 2019",null,null,null,"Enter the value of your loans bursary claimed or delivered in 2018 to 2019  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 in 2018 to 2019 enter £0."]
[]
[null,null,"Current years delivery and contract value"]
[]
[null,null,"d.","Value of approved, attended and paid ALL applications in 2019 to 2020 (to-date)",null,null,null,"Enter the value of your approved, attended and paid ALL applications as at 10 February 2020 for the 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,"e.","Value of your 2019 to 2020 ALL facility, as at February 2020",null,null,null,"Enter the value of your ALL facility for the 2019 to 2020 funding year as at February 2019.  This should include any growth at performance-management reviews (in August and December 2019).  You should not include your loans bursary as this is a separate allocation."]
[]
[null,null,"f.","Value of your 2019 to 2020 ALL bursary, as at February 2020",null,null,null,"Enter the value of your ALL bursary for the 2019 to 2020 funding year as at February 2019.  This should include any growth at performance-management reviews (in August and December 2019)."]
[]
[null,null,"g.","Have you received an increase to your 2019 to 2020 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 2019 to 2020 funding year. Select no if your contract value was reduced or stayed the same or had a new facility."]
[]
[null,null,"h.","Have you received an increase to your 2019 to 2020 bursary value?",null,null,null,"Select yes if you were awarded advanced learner loans bursary growth at the performance the two performance management reviews of the 2019 to 2020 funding year or had a new bursary. Select no if your contract value was reduced or stayed the same."]
[]
[null,null,"i.","Have you received a new ALL facility in 2019 to 2020?",null,null,null,"Select yes if you applied and awarded a new facility allocation at a performance management review of the 2019 to 2020 funding year."]
[]
[null,null,"j.","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,"k.","The year in which your allocation is based on",null,{"$f":"IF(F30=0,\"No contract\",\nIF(F38=\"Yes\",\"1920 facility\",\nIF(F28>0,IF(F28>F22,\"1920\",\"1819\"),\nIF(F28>0,\"1920\",\nIF(F20>0,\"1718\",\"no delivery\")))))","v":"No contract"},null,"The 2020 to 2021 baseline is calculated by taking the higher of your 2019 to 2020 approved, attended, paid & submitted ALL figure (e) or 2018 to 2019 approved, attended, paid & ALL figure (b). \n\nIf you have delivery in 2017 to 2018 only, your baseline is £2,500 and if you have a new facility this is the value of your 2019 to 2020 facility (f)"]
[]
[null,null,"l.","Baseline",null,{"$f":"IF(F49=\"1718\",2500,\nIF(F49=\"1819\",F22,\nIF(F49=\"1920\",F28/90%,\nIF(F49=\"1920 facility\",F30,0))))","v":0}]
[]
[null,null,"m.","Are you eligible for an increase?",null,{"$f":"IF(F40=\"no\",\nIF(F51=F30,FALSE,TRUE))","v":false},null,"Your are not eligible for an increase if you have\n•  answered yes to track-record criteria (j)\n•  your allocation is based on your 2019 to 2020 facility"]
[null,null,null,null,null,"+"]
[null,null,"n.","3% increase",null,{"$f":"IF(F53,F51*3%,0)","v":0},null,"This is the national affordability factor applied to your allocation."]
[null,null,null,null,null,"+"]
[null,null,"o.","Adjustment to align with performance management tolerances",null,{"$f":"IF(F34=\"Yes\",MAX(SUM(F51:F55),F30)-SUM(F51:F55),MIN(SUM(F51:F55),F30)-SUM(F51:F55))","v":0},null,"If you were awarded growth, your allocation will be the calculated allocation or 2019 to 2020 facility value, whichever is greater.  If your facility did not change, your allocation will be the calculated allocation or 2019 to 2020 facility value, whichever is lower."]
[null,null,null,null,null,"+"]
[null,null,"q.","Adjustment for minimum allocation",null,{"$f":"IF(AND(F51>0,SUM(F51:F57)<2500),2500-SUM(F51:F57),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,"r.","Adjustment for track record",null,{"$f":"IF(AND(F40=\"Yes\",SUM(F51:F57)>F30),F30-SUM(F51:F57),0)","v":0},null,"In line with our performance-management rules, we will not give you an allocation greater than your 2019/20 contract value if you have a track record issue (j)."]
[null,null,null,null,null,"="]
[null,null,"s.","Calculated 2020 to 2021 advanced learner loans facility allocation",null,{"$f":"ROUND(SUM(F51,F55,F59,F61,F57),0)","v":0},null,"This is your total allocation for the 2020 to 2021 funding year"]
[]
[null,null,"t.","August 2020 to March 2021",null,{"$f":"ROUND(F63*63.17%,0)","v":0},null,"This is your allocation for periods 1 to 8 of the 2020 to 2021 funding year. This is based on the standard national profile published in advanced learner loans funding rules"]
[]
[null,null,"u.","April 2021 to July 2021",null,{"$f":"F63-F65","v":0},null,"This is your allocation for periods 9 to 12 of the 2020 to 2021 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,"v.","Proportion of bursary delivery",null,{"$f":"ROUNDUP(IFERROR(MIN(50%,F24/F22),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 2018 to 2019 (c) by value of approved, attended and paid ALL applications in 2018 to 2019 (b). The maximum proportion is 50%."]
[null,null,null,null,null,"x"]
[null,null,"w.","Calculated 2020 to 2021 advanced learner loans facility allocation",null,{"$f":"F63","v":0},null,"The proportion is then multiplied by your total allocation for the 2020 to 2021 funding year (s)."]
[null,null,null,null,null,"="]
[null,null,"x.","Initial calculated 2018 to 2019 allocation",null,{"$f":"F70*F72","v":0}]
[null,null,null,null,null,"+"]
[null,null,"y.","Adjustment for minimum allocation",null,{"$f":"IF(OR(AND(F74>0,F74<500),AND(F74=0,F32>0,F72>0)),500-F74,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,"z.","Adjustment to align with performance management tolerances",null,{"$f":"IF(F36=\"Yes\",F32-F74-F76,0)","v":0},null,"If you were awarded growth, your allocation will be the calculated allocation or 2019 to 2020 bursary value, whichever is greater."]
[null,null,null,null,null,"+"]
[null,null,"z.","Adjustment for track record",null,{"$f":"IF(AND(F40=\"Yes\",SUM(F74:F78)>F32),F32-SUM(F74:F78),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 (j)."]
[null,null,null,null,null,"="]
[null,null,"aa.","Calculated 2020 to 2021 advanced learner loans bursary allocation",null,{"$f":"ROUND(SUM(F74,F76,F78,F80),0)","v":0},null,"This is your total allocation for the 2020 to 2021 funding year."]
[]
[null,null,"ab.","August 2020 to March 2021",null,{"$f":"ROUND(IF(F16=\"grant\",F82*75%,F82*58%),0)","v":0},null,"This is your allocation for periods 1 to 8 of the 2020 to 2021 funding year. This is based on the standard national profile published in advanced learner loans funding rules."]
[]
[null,null,"ac.","April 2021 to July 2021",null,{"$f":"F82-F84","v":0},null,"This is your allocation for periods 9 to 12 of the 2020 to 2021 funding year. This is based on the standard national profile published in advanced learner loans funding rules."]