{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["19-24 traineeships"],"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":"19-24 traineeships","index":0,"n_rows":65,"n_cols":8,"rows_kept":65,"cut":false,"hidden":false,"merged":[[0,4,0,7],[1,1,1,8],[3,1,3,8],[6,2,6,3],[7,2,8,7],[54,7,55,7],[56,7,57,7],[58,7,59,7],[63,7,65,7],[11,3,11,7],[36,7,37,7],[38,7,39,7],[43,2,43,3],[44,2,45,7],[34,7,35,7],[12,7,13,7],[14,5,14,7],[16,2,16,3],[18,7,19,7],[22,7,23,7],[47,3,48,3],[47,7,48,7],[49,3,50,3],[49,7,50,7],[51,7,53,7],[9,2,9,7],[10,2,10,7],[29,7,31,7],[20,7,21,7],[32,7,33,7],[26,7,27,7],[24,2,24,3]]}
[]
[null,"19-24 traineeships 2021 to 2022 allocations calculator (funded through Education and Skills Contract only)"]
[]
[null,"Disclaimer: \nThis is a tool to help you understand how your allocation has been calculated, it is not your allocation value and values may differ to your actual allocation.  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 out the value of your 2021 to 2022 19 to 24 traineeships 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,"You can find your delivery values on your funding summary reports in the download reports of Submit Learner Data: https://submitlearnerdatabeta.fasst.org.uk/"]
[null,null,"You can find your contract values and allocations on Manage your Education and Skills Funding (MYESF): https://skillsfunding.service.gov.uk/home"]
[]
[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,"Historic delivery and contract values"]
[]
[null,null,"a.","2019 to 2020 19-24 traineeship delivery ",null,null,null,"Enter the value of your 16-18 traineeships full year delivery between August 2019 and July 2020 in the 2019/20 funding year as recorded in your R14 data return. Do not include bursary funding."]
[]
[null,null,"b.","2019 to 2020 19-24 traineeships contract value",null,null,null,"Enter the value of your 16-18 traineeships contract value as of February 2021.Do not include bursary funding."]
[]
[null,null,"c.","calculate proportion delivered",null,{"$f":"IFERROR(F19/F21,0)","v":0},null,"You do not need to enter a value in this cell.  This is your 2019 to 2020 19-24 traineeships delivery divided by 2019 to 2020 19-24 traineeships contract value.  This percentage is to know how much of your allocation your delivered."]
[]
[null,null,"Current years delivery and contract value"]
[]
[null,null,"d.","August 2020 to January 2021 (6 months) 19 to 24 traineeships delivery",null,null,null,"Enter the value of your 19-24 traineeships delivery between August 2020 and January 2021 in the 2020 to 2021 funding year as recorded in your R06 data return."]
[]
[null,null,"e.","National profile",null,0.51900000000000002,null,"You do not need to enter a value in the cell.  The national profile is based on your contract type (c)."]
[null,null,null,null,null,null,null,"You do not need to enter a value in this cell.  This is your August 2020 to January 2021 19-24 traineeships delivery divided by the national profile for August to January (for grant funding agreements this is 51.9%).  This uplifts your delivery to give an estimate of what you would deliver for the full 2020 to 2021 funding year if you delivered in line with the national profile."]
[null,null,"f.","2020 to 2021 19-24 traineeships delivery uplifted to a full year estimate",null,{"$f":"F27/F29","v":0}]
[]
[null,null,"g.","2020 to 2021 19 to 24 traineeship contract value (as at March 2020)",null,null,null,"Enter the value of your 2020 to 2021 19-24 traineeships intial 19 to 24 traineeships allocation for the 2020 to 2021 funding year. This was issued in March on Manage your education and skills funding (MyESF)."]
[]
[null,null,"h.","2020 to 2021 19 to 24 traineeship contract value (as at February 2021)",null,null,null,"Enter the value of your 2020 to 2021 19-24 traineeships contract value as of February 2021.  Include your learner support allocation in this value."]
[]
[null,null,"i.","Have you received a new 19 to 24 traineeship in 2020 to 2021?",null,null,null,"Select yes if you applied and awarded a new 19 to 24 traineeship allocation at theperformance management review in December 20/January 21 of the 2020 to 2021 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\n- you are in formal intervention for 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 the your information section above."]
[]
[]
[null,null,"k.","Is your 2020 to 2021 estimated full year delivery less than your initial 19 to 24 traineeships allocation?",null,{"$f":"IF(F31<=F33,\"yes\",\"no\")","v":"yes"},null,"This compares your full year estimate 19-24 traineeships delivery in 2020 to 2021 (f) to your initial 19 to 24 traineeships allocation for the 2020 to 2021 funding year (g). If delivery less than allocation then yes will apply."]
[]
[null,null,"l.","Is your 2019 to 2020 full year delivery less than 50% of your final 19 to 24 traineeships allocation?",null,{"$f":"IF(F23<50%,\"yes\",\"no\")","v":"yes"},null,"This compares your 19 to 24 traineeships delivery in 2019 to 2020 (a) to your  final allocation for the 2019 to 2020 funding year as of July 2020 (b). If delivery is less than 50% of your allocation then yes will apply."]
[]
[null,null,"m.","Calculated baseline allocation",null,{"$f":"IF(AND(F48=\"yes\",F50=\"yes\"),F33,F35)","v":0},null,"If your 2020 to 2021 estimated full year delivery is less than your initial allocation (k = yes) AND your 2019 to 2020 delivery was less than 50% (l = yes), your allocation will be what was allocated to you in March 2020 (g).\nIf your 2020 to 2021 estimated full year delivery is more than your initial allocation (k = no) OR your 2019 to 2020 delivery was more than 50% (l = no), your allocation will be your current allocation as of March 2021 (h)."]
[]
[null,null,null,null,null,"+"]
[null,null,"n.","Adjustment for new allocation",null,{"$f":"IF(F37=\"no\",0,F35-F52)","v":0},null,"If you received a new allocation we increased your 2020 to 2021 contract value at"]
[null,null,null,null,null,"+"]
[null,null,"o.","Adjustment for £10,000 mimimum allocation",null,{"$f":"IF(AND(F19+F27+F33>0,F55+F52<10000),10000-(F52+F55),0)","v":0},null,"We have applied a minimum allocation of £10,000. We have uplifted your 2021 to 2022 allocation to £10,000 if the allocation we calculated for you is lower than this (unless you have a track record issue). "]
[null,null,null,null,null,"+"]
[null,null,"p.","Adjustment for track record issue",null,{"$f":"IF(AND(F39=\"yes\",(F52+F55+F57)>F35),F35-(F52+F55+F57),0)","v":0},null,"In line with our perfirmance-management rules, we will not give you an allocation greater than your 2020 to 2021 contract value if you have a track record issue."]
[null,null,null,null,null,"="]
[null,null,"q.","Calculated 2021 to 2022 19 to 24 traineeships allocation",null,{"$f":"SUM(F52:F59)","v":0},null,"This is your total allocation for the 2021 to 2022 funding year"]
[]
[null,null,"r.","August 2021 to March 2022",null,{"$f":"ROUND(F61*62.65%,0)","v":0},null,"This is your allocation for periods 1 to 8 (August 2021 to March 2022) of the 2021 to 2022 funding year."]
[null,null,null,null,null,null,null,"This is your allocation for periods 9 to 12 (April 2021 to July 2022) of the 2021 to 2022 funding year."]
[null,null,"s.","April 2022 to July 2022",null,{"$f":"F61-F63","v":0}]