{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["Facility Calculator","Bursary Calculator"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":4,"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":"Facility Calculator","index":0,"n_rows":58,"n_cols":12,"rows_kept":58,"cut":false,"hidden":false,"merged":[[49,6,50,7],[18,6,20,7],[45,6,47,7],[55,6,55,7],[56,6,58,7],[39,6,41,7],[25,6,26,8],[27,6,28,8],[29,6,30,8],[31,6,32,8],[21,6,22,7],[42,6,43,7],[53,6,54,6],[37,2,38,6],[1,1,1,6],[3,1,3,9],[8,2,9,7],[16,6,17,7],[13,4,13,6]]}
[]
[null,"Advanced learner loans (ALL) facility 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,"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 advanced learn loans facility allocation.  If you have merged with another organisation/s then you need to include the total for all of the organisations involved in the merger in the values you provide below."]
[]
[]
[null,null,"UKPRN"]
[]
[null,null,"Provider legal name"]
[]
[null,null,"Historic years delivery"]
[null,null,null,null,null,null,"Enter the value of your approved, attended and paid ALLs applications for the 2016/17 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 in 2016/17 enter £0."]
[null,"a.","Value of approved, attended and paid ALL applications in 2016 to 2017"]
[null,null,null,null,null,null,"Enter the value of your approved, attended and paid ALL applications for the 2017/18 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 loans facility in 2017/18 enter £0.  "]
[null,"b.","Value of approved, attended and paid ALL applications in 2017 to 2018"]
[]
[null,"c.","Value of your 2017 to 2018 facility",null,null,null,"Enter the value of your ALL facility for the 2017/18 funding year.  This should include any growth or reductions from in-year performance management.  You should not include your loans bursary, as this is a separate allocation."]
[]
[null,null,"2018/19 delivery and contract value"]
[]
[null,"d.","Value of approved, attended and paid ALL applications in 2018 to 2019 (to-date)",null,null,null,"Enter the value of your approved, attended and paid ALL applications as at 9 February 2019 for the 2018/19 funding year.  You can find this on your SLC portal.  If you haven't yet used any of your ALL facility in 2018/19 enter £0.  "]
[]
[null,"e.","Value of your 2018 to 2019 ALL facility, as at February 2019",null,null,null,"Enter the value of your ALL facility for the 2019/20 funding year as at February 2019.  This should include any growth at performance-management point 1 (in September 2018).  You should not include your loans bursary, as this is a separate allocation."]
[]
[null,"f.","Did you receive an increase to your 2018/19 facility contract value at PMP1?",null,null,null,"Select yes if you submitted a advanced learner loans growth request and it was accepted at performance-management point 1 (PMP1) of the 2018/19 funding year. Select no if your contract value was reduced or stayed the same at PMP1."]
[]
[null,"g.","Do you have a track record issue?",null,null,null,"Select yes if you are under notice for failure of inspection or financial control or if you are not under a notice to improve, additional conditions of funding or additional contractual obligations in relation to minimum quality standards for 19+ education and training."]
[]
[]
[]
[]
[null,null,"Calculation steps "]
[null,null,"This section will automatically populate when you complete the your information section above."]
[]
[null,null,null,null,null,null,"The 2019/20 baseline is calculated by taking the first  value from your either 2017/18 approved, attended & paid ALL figure (b) OR 2018/19 approved, attended & paid ALL figure (d) OR 50% of 2016/17 approved, attended & paid (a)"]
[null,null,"Baseline",null,{"$f":"IF(E20>0,E20,\nIF(E28=0,0,\nIF(E26>0,E26,\nIF(E18>0,E18*50%,\nIF(E22=0,E28,0)))))","v":0}]
[]
[null,null,"Eligible for 1% increase?",null,{"$f":"IF(OR(E32=\"Yes\",E22=0),\"No\",\"Yes\")","v":"No"},null,"If you meet our track-record criteria we have increased your baseline by 1%, otherwise you will not receive the 1% increase to your ALL facility."]
[]
[null,null,"Baseline + 1% increase (if eligible)",null,{"$f":"IF(E43=\"Yes\",E41*101%,E41)","v":0}]
[null,null,null,null,null,null,"If you were awarded growth at PMP1, your 2019/20 allocation will be the baseline or the 2018/19 PMP1 value, whichever is greater. \nIf your 2018/19 facility value did not change at PMP1, your 2019/20 allocation will be the baseline or 2018/19 delivery-to-date, whichever is greater."]
[null,null,"Adjustment to align with performance management",null,{"$f":"IF(AND(E30=\"Yes\",E45<E28),E28-E45,\nIF(E45<E26,E26-E45,0))","v":0}]
[]
[]
[null,null,"Initial calculated 2018 to 2019 allocation",null,{"$f":"E45+E47","v":0}]
[]
[null,null,"Adjustment for deminimus",null,{"$f":"IFERROR(IF(AND(E50>0,E50<1000),1000-E50,0),\"\")","v":0},null,"If your allocation for 2019/20 calculated to be under £1000, then we uplift it to £1000."]
[]
[null,null,"Calculated 2018 to 2019 advanced learner loans facility",null,{"$f":"E50+E52","v":0},null,"This is your total allocation for the 2019/20 funding year."]
[]
[null,null,"August 2019 to March 2020",null,{"$f":"IFERROR(E54*63.17%,\"\")","v":0},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,"This is your allocation for periods 9 to 12 (April 2020 to July 2020) of the 2019/20 funding year."]
[null,null,"April 2020 to July 2020",null,{"$f":"IFERROR(E54-E56,\"\")","v":0}]

{"sheet":"Bursary Calculator","index":1,"n_rows":54,"n_cols":11,"rows_kept":54,"cut":false,"hidden":false,"merged":[[41,6,42,6],[29,6,30,6],[36,2,37,6],[3,1,3,6],[52,6,54,7],[31,6,32,6],[51,6,51,7],[27,6,28,6],[39,6,40,6],[43,6,45,6],[49,6,50,6],[1,1,1,6],[5,1,5,7],[10,2,11,6],[21,6,22,6],[23,6,24,6],[15,4,15,6],[18,6,20,6]]}
[]
[null,"Advanced learner loans bursary 2019 to 2020 allocations calculator"]
[]
[null,"IMPORTANT! Ensure you have your facility details completed on 'ALL Facility Calculator' first!"]
[]
[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,"Your information",null,null,null,null,null,null,null,"Yes"]
[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 advanced learn loans facility allocation.  If you have merged with another organisation/s then you need to include the total for all of the organisations involved in the merger in the values you provide below."]
[]
[]
[null,null,"UKPRN"]
[]
[null,null,"Provider legal name"]
[]
[null,null,"Historic years delivery"]
[null,null,null,null,null,null,"Enter the value of your loans bursary claimed or delivered in 2016/17 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 2016/17 enter £0."]
[null,"a.","Value of loans bursary claimed and delivered in 2016 to 2017"]
[]
[null,"b","Value of your 2017 to 2018 loans bursary",null,null,null,"Enter the value of your loans bursary contract value for 2017/18 funding year.  You can find this on your Advanced Learner Loans contract. If you didn't have a loans facility enter £0.  "]
[]
[null,"c.","Value of loans bursary claimed and delivered in 2017 to 2018",null,null,null,"Enter the value of your loans bursary claimed or delivered in 2017/18 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 2017/18 enter £0."]
[]
[null,null,"2018/19 delivery and contract value"]
[]
[null,"d.","Value of your 2018 to 2019 loans bursary, as at February 2019",null,null,null,"Enter the value of your loans bursary for the 2019/20 funding year as at February 2019.  This should include any growth at performance-management point (PMP) 1 (in September 2018).  You should not include your facility as this is a separate allocation."]
[]
[null,"e.","Did you receive an increase to your 2018/19 facility contract value at PMP1?",null,null,null,"Select yes if you submitted an advanced learner loan bursary growth request and it was accepted at performance-management point 1 (PMP1) of the 2018/19 funding year. Select no if your contract value was reduced or stayed the same at PMP1."]
[]
[null,"f.","Do you have a track record issue?",null,null,null,"Select yes if you are under notice for failure of inspection or financial control or if you are not under a notice to improve, additional conditions of funding or additional contractual obligations in relation to minimum quality standards for 19+ education and training."]
[]
[]
[]
[null,null,"Calculation steps "]
[null,null,"This section will automatically populate when you complete the your information section above."]
[]
[]
[null,null,"Baseline",null,{"$f":"IF(E24>0,E24,\nIF(E28=0,0,\nIF(E20>0,E20*50%,\nIF(E22=0,E28,0))))","v":0},null,"The 2019/20 baseline is calculated by taking either your 2017/18 bursary claimed (c) OR 50% of 2016/17 bursary claimed (a)"]
[]
[null,null,"Baseline after 200% growth cap applied",null,{"$f":"IFERROR(IF(AND(E28>0,(E40/E28)>200%),E28*200%-E40,0),0)","v":0},null,"There is a 200% cap on growth, which taken as the % change between the 2018/19 loans bursary value and baseline."]
[]
[null,null,"Adjustment to align with performance management",null,{"$f":"IF('Facility Calculator'!E54=0,-E40,\nIF(OR('Facility Calculator'!E47>0,E30=\"Yes\"),\nIF(E40<E28,E28-E42-E40,0),0))","v":0},null,"If you were awarded growth at PMP1, your 2019/20 loans bursary value will be the baseline or the 2018/19 PMP1 value, whichever is greater. "]
[]
[null,null,"Initial calculated 2018 to 2019 loans bursary",null,{"$f":"E42+E44+E40","v":0}]
[]
[null,null,"Adjustment for deminimus",null,{"$f":"IFERROR(IF(AND(E46>0,E46<500),500-E46,0),\"\")","v":0},null,"If your allocation for 2019/20 calculated to be under £500, then we uplift it to £500."]
[]
[null,null,"Calculated 2018 to 2019 advanced learner loans facility",null,{"$f":"E46+E48","v":0},null,"This is your total allocation for the 2019/20 funding year."]
[]
[null,null,"August 2019 to March 2020",null,{"$f":"IFERROR(E50*63.17%,\"\")","v":0},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,"This is your allocation for periods 9 to 12 (April 2020 to July 2020) of the 2019/20 funding year."]
[null,null,"April 2020 to July 2020",null,{"$f":"IFERROR(E50-E52,\"\")","v":0}]