{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["16-18 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":"16-18 traineeships","index":0,"n_rows":85,"n_cols":8,"rows_kept":85,"cut":false,"hidden":false,"merged":[[78,7,79,7],[51,7,52,7],[53,3,54,3],[53,7,54,7],[55,7,57,7],[58,7,59,7],[60,7,61,7],[69,2,69,3],[70,7,71,7],[76,7,77,7],[29,7,30,7],[27,2,27,3],[23,7,24,7],[51,3,52,3],[48,2,49,7],[38,7,39,7],[33,7,35,7],[40,7,41,7],[42,7,43,7],[47,2,47,3],[36,7,37,7],[25,7,27,7],[0,4,0,7],[1,1,1,8],[3,1,3,8],[6,2,6,3],[7,2,8,7],[17,2,17,3],[19,7,20,7],[11,3,11,7],[21,7,22,7],[9,2,9,7],[10,2,10,7],[12,7,13,7],[14,5,14,7],[15,3,15,7]]}
[]
[null,"16-18 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 16 to 18 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 16-18 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 16-18 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(F20/F22,0)","v":0},null,"You do not need to enter a value in this cell.  This is your 2019 to 2020 16-18 traineeships delivery divided by 2019 to 2020 16-18 traineeships contract value.  This percentage is to know how much of your allocation your delivered."]
[]
[null,null,"d.","2019 to 2020 16-18 traineeships bursary delivery",null,null,null,"Enter the value of your 16-18 traineeships bursary full year delivery between August 2019 and July 2020 in the 2019/20 funding year as recorded in your R14 data return. "]
[]
[null,null,"Current years delivery and contract value"]
[]
[null,null,"e.","August 2020 to January 2021 (6 months) 16 to 18 traineeship delivery",null,null,null,"Enter the value of your 16-18 traineeship delivery between August 2020 and January 2021 in the 2020 to 2021 funding year as recorded in your R06 data return."]
[]
[null,null,"f.","National profile",null,0.498,null,"You do not need to enter a value in the cell.  "]
[]
[null,null,"g.","2020 to 2021 16 to18 traineeships delivery uplifted to a full year estimate",null,{"$f":"F30/F32","v":0},null,"You do not need to enter a value in this cell.  This is your August 2020 to January 2021 16-18 traineeships delivery divided by the national profile for August to January (49.8%).  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,"h.","2020 to 2021 16 to 18 traineeship contract value (as at March 2020)",null,null,null,"Enter the value of your 2020 to 2021 16-18 traineeships intial 16 to 18 traineeships allocation for the 2020 to 2021 funding year. This was issued in March last year on Manage your education and skills funding (MyESF)."]
[]
[null,null,"i.","2020 to 2021 16 to 18 traineeship contract value (as at March 2021)",null,null,null,"Enter the value of your 2020 to 2021 16-18 traineeships contract value as of February 2021.  "]
[]
[null,null,"i.","2020 to 2021 16 to 18 traineeships bursary contract value (as at March 2021)",null,null,null,"Enter the value of your 2020 to 2021 16-18 traineeships bursary contract value as of February 2021.  "]
[]
[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\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,"l.","Is your 2020 to 2021 estimated full year delivery less than your initial 16 to 18 traineeships allocation?",null,{"$f":"IF(F34<=F37,\"yes\",\"no\")","v":"yes"},null,"This compares your full year estimate 16-18 traineeships delivery in 2020 to 2021 (g) to your initial 16 to 18 traineeships allocation for the 2020 to 2021 funding year (h). If delivery is less then allocation then yes will apply."]
[]
[null,null,"m.","Is your 2019 to 2020 full year delivery less than 75% of your final 16 to 18 traineeships allocation?",null,{"$f":"IF(F24<75%,\"yes\",\"no\")","v":"yes"},null,"This compares your 16 to 18 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 75% of your allocation then yes will apply."]
[]
[null,null,"n.","Calculated baseline allocation",null,{"$f":"IF(AND(F52=\"yes\",F54=\"yes\"),F37,F39)","v":0},null,"If your 2020 to 2021 estimated full year delivery is less than your initial allocation (l = yes) AND your 2019 to 2020 delivery was less than 75% (m = yes), your allocation will be what was allocated to you in March 2020 (h).\nIf your 2020 to 2021 estimated full year delivery is more than your initial allocation (l = no) OR your 2019 to 2020 delivery was more than 75% (m = no), your allocation will be your current allocation as of March 2021 (i)."]
[]
[]
[null,null,"o.","Adjustment for £5,000 mimimum allocation",null,{"$f":"IF(AND(F20+F30+F37>0,F56<5000),5000-(F56),0)","v":0},null,"We have applied a minimum allocation of £5,000. We have uplifted your 2021 to 2022 allocation to £5,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(F43=\"yes\",(F56+F59)>F39),F39-(F56+F59),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 16 to 18 traineeships allocation",null,{"$f":"SUM(F56:F61)","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(F63*66.45%,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,"s.","April 2022 to July 2022",null,{"$f":"F63-F65","v":0},null,"This is your allocation for periods 9 to 12 (April 2021 to July 2022) of the 2021 to 2022 funding year."]
[]
[]
[null,null,"16 to 18 traineeships bursary allocation"]
[null,null,"t.","Proportion of bursary delivery",null,{"$f":"ROUNDUP(IFERROR(MIN(50%,F20/F26),0),3)","v":0},null,"Your 16 to 18 traineeships bursary is calculated by taking proportion of bursary against traineeships allocation. We have divided your bursary delivered in 2019 to 2020 (d) by your 2019 to 2020 16-18 traineeships delivered (a). The maximum proportion is 50%."]
[null,null,null,null,null,"x"]
[null,null,"u.","Calculated 2021 to 2022 16 to 18 traineeships allocation",null,{"$f":"F63","v":0},null,"The proportion is then multiplied by your total allocation for the 2021 to 2022 funding year (q)."]
[null,null,null,null,null,"="]
[null,null,"v.","Initial calculated 2021 to 2022 allocation",null,{"$f":"F71*F73","v":0}]
[null,null,null,null,null,"+"]
[null,null,"w.","Adjustment for minimum allocation",null,{"$f":"IF(OR(AND(F75>0,F75<500),AND(F75=0,F41>0,F73>0)),500-F75,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,"x.","Adjustment for track record",null,{"$f":"IF(AND(F43=\"Yes\",SUM(F75:F78)>F26),F26-SUM(F75: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 (k)."]
[null,null,null,null,null,"="]
[null,null,"y.","Calculated 2021 to 2022 traineeships bursary allocation",null,{"$f":"ROUND(SUM(F75,F77,F79),0)","v":0},null,"This is your total allocation for the 2021 to 2022 funding year."]
[]
[null,null,"z.","August 2021 to March 2022",null,{"$f":"ROUND(IF(F14=\"grant\",F81*33.55%,F81*66.45%),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,"aa.","April 2022 to July 2022",null,{"$f":"F81-F83","v":0},null,"This is your allocation for periods 9 to 12 (April 2021 to July 2022) of the 2021 to 2022 funding year."]