{"text_format":"1","workbook":"xlsx","n_sheets":2,"sheets":["AEBCalculator","Level3FreeCoursesCalculator"],"not_rendered":{"charts":0,"images":3,"macros":0,"external_links":5,"defined_names":191,"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":"AEBCalculator","index":0,"n_rows":92,"n_cols":14,"rows_kept":92,"cut":false,"hidden":false,"merged":[[57,3,58,3],[57,9,59,13],[59,3,60,3],[71,13,72,13],[53,7,53,9],[45,2,45,3],[47,11,47,13],[49,11,49,13],[51,7,51,9],[51,11,51,13],[39,3,40,3],[0,4,0,7],[1,1,1,8],[3,1,3,13],[5,2,5,3],[16,2,16,3],[2,1,2,8],[19,9,20,13],[21,7,21,13],[25,11,29,13],[33,11,37,13],[17,2,17,13]]}
[]
[null,"ESFA funded Adult Education Budget 2023 to 2024 allocations calculator"]
[null,"This calculator only is only for grant-funded providers with Education and skills funding agreements"]
[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. \nYou will need the latest version of Microsoft Excel or Excel 365 to use this calculator. If you are unable to use the calculator you can request a PDF version from ESFA."]
[]
[null,null,"Methodology"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 2023 to 2024 adult education budget allocation(AEB) allocation."]
[]
[null,null,null,"UKPRN",null,null,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,"Historic delivery",null,null,null,null,"2021 to 2022",null,"2020 to 2021"]
[]
[null,null,"a","Value of AEB adult skills delivery",null,null,null,null,null,null,null,"This information can be found in your final claims made in 2020 to 2021 and 2021 to 2022"]
[]
[null,null,"b","Value of AEB community learning delivery"]
[]
[null,null,"c","Total AEB delivery",null,null,null,{"$f":"SUM(H26:H28)","v":0},null,{"$f":"SUM(J26:J28)","v":0}]
[]
[null,null,"Historic contract values",null,null,"2022 to 2023",null,"2021 to 2022",null,"2020 to 2021"]
[]
[null,null,"e","Value of AEB contract value adult skills",null,null,null,null,null,null,null,"This information can be found on Manage Your Skills and Education Funding, Contracts and Agreements"]
[]
[null,null,"f","Value of AEB contract value community learning"]
[]
[null,null,"g","Total AEB contract value",null,{"$f":"SUM(F34,F36)","v":0},null,{"$f":"SUM(H34:H36)","v":0},null,{"$f":"SUM(J34:J36)","v":0}]
[]
[null,null,"d","Have you received funding for innovative provision as part of your 2023 to 2024 AEB allocation?"]
[]
[]
[]
[null,null,"Calculation step 1 - determining performance and values"]
[null,null,"This section will automatically populate when you complete the your information section above."]
[null,null,"Determine performance"]
[null,null,null,null,null,null,null,"2021 to 2022",null,"2020 to 2021"]
[null,null,"h","Percentage of the total allocation claimed",null,null,null,{"$f":"IFERROR(H30/H38,0)","v":0},null,{"$f":"IFERROR(J30/J38,0)","v":0},null,"This is delivery (d) divided by contract value (g) for the corresponding years."]
[]
[null,null,"i","Reconciliation tolerance",null,null,null,0.97,null,0.9,null,"We compare your percentage to the published funding claims reconciliation threshold for that year."]
[]
[null,null,"j","Delivered above reconciliation tolerance at least once",null,null,null,{"$f":"_xlfn.IFS(OR(H48>1),\"Over\",\nOR(H48>H50,J48>J50),\"Rollover\",\nOR(H48<H50,J48<J50),\"Under\")","v":"Under"},null,null,null,"Delivery used within your allocation calculation includes monitoring and audit adjustments to accurately reflect final delivery. "]
[]
[null,null,"k","2020 to 2021 delivery greater than 100%",null,null,null,{"$f":"IF(H48>1,\"Yes\",\"No\")","v":"No"}]
[]
[null,null,"Determine 2021 to 2022 ESFA funded delivery, using 2020 to 2021 percentage"]
[]
[null,null,"l","ESFA funded AEB delivery for 2021 to 2022 adult skills",null,null,null,{"$f":"IFERROR(IF(AND(H30<F38,J48>H48),(H26/H48)*J48,H26),0)","v":0},null,"If you delivered below the reconciliation threshold in each of the last 2 years, but your 2020 to 2021 delivery percentage is greater than 2021 to 2022, we rebased the 2021 to 2022 delivery value using the 2020 to 2021 delivery percentage."]
[]
[null,null,"m","ESFA funded AEB for 2021 to 2022 community learning",null,null,null,{"$f":"IFERROR(IF(AND(H30<F38,J48>H48),(H28/H48)*J48,H28),0)","v":0}]
[]
[null,null,"n","Total ESFA funded AEB for 2021 to 2022",null,null,null,{"$f":"SUM(H58,H60)","v":0}]
[]
[]
[]
[null,null,"Calculation step 2 - apply calculation to 2021 to 2022 contract values"]
[]
[null,null,"2021 to 2022 non-devolved contract value",null,null,"Adult skills",null,"community learning",null,"Innovative Funding",null,"Total"]
[null,null,"o","2021 to 2022 ESFA funded contract values",null,{"$f":"F34","v":0},null,{"$f":"F36","v":0},null,null,null,{"$f":"SUM(F69,H69)","v":0}]
[]
[null,null,"Where applicable, under delivery calculation"]
[null,null,"p","Difference where 2021 to 2022 delivery percentage is applied",null,{"$f":"IF(H52=\"under\",H58-H26,0)","v":0},null,{"$f":"IF(H52=\"under\",H60-H28,0)","v":0},null,null,null,{"$f":"SUM(F72,H72)","v":0},null,"If you delivered below the reconciliation threshold in the last 2 years, but your 2020 to 2021 delivery percentage is greater than 2021/2022, we rebased 2021 to 2022 delivery value using the 2020 to 2021 delivery percentage."]
[]
[null,null,"q","Remove under delivery",null,{"$f":"IF(AND(H52=\"under\",H30<F38),H26-H34,0)","v":0},null,{"$f":"IF(AND(H52=\"under\",H30<F38),H28-H36,0)","v":0},null,null,null,{"$f":"SUM(F74,H74)","v":0},null,"This is the difference between your 2021 to 2022 delivery and 2022 to 2023 contract value"]
[]
[null,null,"r","Plus 10% buffer",null,{"$f":"IF(H52=TRUE,0,MIN(L69-SUM(L69,L74),L69*10%))","v":0},null,0,null,null,null,{"$f":"SUM(F76,H76)","v":0},null,"If you delivered below the reconciliation threshold in each of the last 2 years, we take your 2021 to 2022 delivery. We then add a buffer by adding 10% of your 2022 to 2023 contract value."]
[]
[null,null,"Where applicable, over delivery calculation"]
[null,null,"s","up to 10% delivery increase if above contract value",null,{"$f":"_xlfn.IFS(H54=\"no\",0,\nH30<L69,0,\nH30>L69,MIN(F69*110%,H26))","v":0},null,{"$f":"_xlfn.IFS(H54=\"no\",0,\nH30<L69,0,\nH30>L69,MIN(H69*110%,H28))","v":0},null,null,null,{"$f":"SUM(F79,H79)","v":0},null,"If you delivered above 100% in 2021 to 2022, we have awarded you an increase up to the value of your 2021 to 2022 delivery or 10% of your 2022 to 2023 contract value, whichever is lower. "]
[]
[null,null,"Final allocation ",null,null,"Adult skills",null,"community learning",null,"Innovative Funding",null,"Total"]
[null,null,"t","Total",null,{"$f":"SUM(F69:F79)","v":0},{"$f":"SUM(G69:G79)","v":0},{"$f":"SUM(H69:H79)","v":0},{"$f":"SUM(I69:I79)","v":0},null,null,{"$f":"SUM(L69:L79)","v":0}]
[]
[null,null,"u","Alignment of community learning value",null,{"$f":"-H84","v":0},null,{"$f":"_xlfn.IFS(H82<H69,MIN(F82,H69-H82),\nH82>H69,H69-H82,\nTRUE,0)","v":0},null,null,null,{"$f":"SUM(F84,H84)","v":0}]
[]
[null,null,"v","Minimum allocation",null,{"$f":"_xlfn.IFS(AND(H30>0,L82<10000),10000-L82,\nAND(H30=0,L82<10000),-F82-F84,\nTRUE,0)","v":0},null,{"$f":"_xlfn.IFS(AND(H30=0,L82<10000),-H82-H84,\nTRUE,0)","v":0},null,null,null,{"$f":"SUM(F86,H86)","v":0},null,"We have applied a minimum allocation of £10,000. "]
[]
[null,null,"w","Calculated 2023 to 2024 ESFA funded AEB allocation",null,{"$f":"ROUND(SUM(F69,F72,F74,F76,F79,F84,F86),0)","v":0},null,{"$f":"ROUND(SUM(H69,H72,H74,H76,H79,H84,H86),0)","v":0},null,null,null,{"$f":"SUM(F88,H88)","v":0}]
[]
[null,null,"x","of which funding for innovative provision (where applicable)",null,{"$f":"-J90","v":0},null,{"$f":"IF(H70=\"under\",H78-#REF!,0)","v":0},null,{"$f":"IF(H40=\"Yes\",L88*3%,0)","v":0},null,{"$f":"SUM(F90,J90)","v":0},null,"Where eligible, your ESFA funded AEB will be less the funding which may be used for funding for innovative provision. "]
[]
[null,null,"y","Final 2023 to 2024 ESFA funded AEB allocation",null,{"$f":"F88+F90","v":0},null,{"$f":"H88+H90","v":0},null,{"$f":"J88+J90","v":0},null,{"$f":"L88+L90","v":0},null,"We have not adjusted 2023 to 2024 allocations based on policy or application of the ‘earnings boost’. This will be applied at the end of the funding year."]

{"sheet":"Level3FreeCoursesCalculator","index":1,"n_rows":79,"n_cols":11,"rows_kept":79,"cut":false,"hidden":false,"merged":[[59,5,59,7],[78,5,78,7],[71,9,72,10],[74,5,74,7],[76,5,76,7],[62,9,64,10],[1,1,1,8],[38,3,39,3],[34,10,35,10],[36,10,37,10],[20,2,20,10],[22,2,22,9],[23,2,23,9],[6,2,6,4],[19,2,19,3],[21,2,21,9],[27,5,27,9],[49,10,50,10],[59,9,59,10],[75,9,77,10],[66,9,67,10],[3,1,3,10],[25,7,26,10],[47,10,48,10],[61,2,61,3],[71,5,71,7],[68,5,68,7],[44,2,44,3],[62,5,62,7],[47,3,48,3],[49,5,49,9],[64,5,64,7],[66,5,66,7]]}
[]
[null,"ESFA funded Free Courses for jobs 2023 to 2024 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. \nYou will need the latest version of Microsoft Excel or Excel 365 to use this calculator. If you are unable to use the calculator you can request a PDF version from ESFA."]
[]
[]
[null,null,"Level 3 free courses for jobs allocation methodology:"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[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 the providers 2022 to 2023 allocation for Level 3 Free Courses for Jobs"]
[null,null,"Please note:"]
[null,null,"This calculator only calculates the Level 3 Free courses for jobs 24+ allocation"]
[null,null,"Free courses for jobs funding for 19 - 24 year olds is included in the ESFA funded AEB funding"]
[]
[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,null,null,null,"2022 to 2023",null,"2021 to 2022",null,"2020 to 2021"]
[]
[null,null,null,"Value of Free courses for jobs contract values",null,null,null,null,null,null,"You can find this on Manage your Education and Skills Funding, Contracts and agreements"]
[]
[null,null,null,"Value of Free courses for jobs delivery",null,null,null,null,null,null,"You can find this on your final claims. Note: when inputting delivery for 2022 to 2023, please enter delivery to date only from your 2022 to 2023 mid-year claim (as at R06)"]
[]
[null,null,null,"Were you in receipt of growth as part of the 2022 to 2023 performance management exercise? ",null,null,null,"You will have received notification from the ESFA to notify you of this"]
[]
[]
[]
[null,null,"Calculation step 1 - determining performance and values"]
[null,null,"This section will automatically populate when you complete the your information section above."]
[null,null,"Step 1 - Determine performance"]
[null,null,null,null,null,"2022 to 2023 (At R06)",null,"2021 to 2022",null,"2020 to 2021 "]
[]
[null,null,null,"Percentage of contract value delivered",null,{"$f":"_xlfn.IFNA(IF(F35=0,0,F37/(F35*0.519)),0)","v":0},null,{"$f":"_xlfn.IFNA(IF(H35=0,0,H37/H35),0)","v":0},null,{"$f":"_xlfn.IFNA(IF(J35=0,0,J37/J35),0)","v":0},"This is delivery divided by contract value for the corresponding years."]
[]
[null,null,null,"Is Delivery tolerance of 80% met in 2020 to 2021 or 2021 to 2022?",null,{"$f":"_xlfn.IFS(\nOR(J48>=100%,H48>=100%),\"Over\",\nOR(J48>=80%,H48>=80%,F39=\"Yes\"),\"Rollover\",\nOR(J48<80%,H48<80%),\"Under\")","v":"Under"},null,null,null,null,"This looks at you delivery percentages in those two full years, 2021 to 2022 and 2020 to 2021 and compares to the current delivery threshold which is 80%"]
[]
[null,null,null,"where applicable, rebased 2021 to 2022 delivery based on 2022/23",null,null,null,{"$f":"IF(AND(F50=\"Under\",F48>H48),(H37/H48)*MIN(1,F48),0)","v":0}]
[]
[]
[]
[]
[null,null,"Step 2 - apply calculations to 2022 to 2023 contract values"]
[]
[]
[null,null,null,"2022 to 2023 contract value",null,{"$f":"F35","v":0},null,null,null,"This is the value entered above "]
[]
[null,null,"Where applicable, under delivery calculation"]
[null,null,null,"Difference where 2022 to 2023 delivery percentage is applied",null,{"$f":"IF(AND(F50=\"Under\",F48>H48),H52-H37,0)","v":0},null,null,null,"If you did not meet the 80% delivery threshold in either 2020 to 2021 or 2021 to 2022, but your 2022 to 2023 delivery proportion to date at R06 is greater, we rebase the 2021 to 2022 delivery value using the 2022 to 2023 delivery proportion."]
[]
[null,null,null,"Remove under delivery",null,{"$f":"IF(F50=\"Under\",H37-F60,0)","v":0}]
[]
[null,null,null,"Plus 10% buffer",null,{"$f":"IF(F50=\"under\",MIN(F60*10%,-(F65+F63)),0)","v":0},null,null,null,"If you delivered below the 80% delivery threshold in each of the last two years, we take your 2021 to 2022 delivery. We then add a buffer by adding 10% of your 2022 to 2023 contract value."]
[]
[null,null,null,"Subtotal",null,{"$f":"SUM(F63,F65,F67)","v":0}]
[]
[null,null,"Where applicable, over delivery calculation"]
[null,null,null,"up to 10% delivery increase",null,{"$f":"IF(F50=\"over\",F60*10%,0)","v":0},null,null,null,"Where the delivery threshold has been exceeded, we will allocate funding based on your 2022 to 2023 Free Courses for Jobs Allocation plus an additional 10%"]
[]
[null,null,"Final allocation"]
[null,null,null,"Subtotal once Delivery measure applied",null,{"$f":"SUM(F60,F69,F72)","v":0}]
[null,null,null,null,null,null,null,null,null,"We have applied a minimum allocation of £10,000. If your calculated allocation is less than £10,000, we have uplifted your allocation to £10,000.\nWhere a calculated allocation is between £10,000 and £45,000, we have uplifted your allocation to £45,000. "]
[null,null,null,"Minimum allocation applied (If Applicable)",null,{"$f":"IF(AND(F75<10000,F60>0),(10000-F75)+F75,IF(AND(F60>45000,F75>0,F75<45000),(45000-F75)+F75,0))","v":0}]
[]
[null,null,null,"Calculated 2023 to 2024 FCFJ allocation",null,{"$f":"SUM(F75:H77)","v":0}]