{"text_format":"1","workbook":"xlsx","n_sheets":1,"sheets":["19-24 traineeships calculator"],"not_rendered":{"charts":0,"images":1,"macros":1,"external_links":0,"defined_names":2,"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 calculator","index":0,"n_rows":89,"n_cols":28,"rows_kept":89,"cut":false,"hidden":false,"merged":[[73,7,74,7],[78,7,79,7],[0,4,0,7],[70,7,71,7],[54,7,55,7],[56,7,58,7],[42,7,44,7],[39,7,40,7],[34,7,35,7],[36,7,38,7],[23,21,23,24],[3,1,3,8],[19,7,21,7],[1,1,1,8],[8,2,9,7],[6,2,6,3],[11,3,11,7],[14,5,14,7],[10,2,10,7],[47,2,47,3],[18,2,18,3],[24,2,24,3],[68,2,68,3],[64,7,66,7],[48,2,49,7],[50,2,50,3],[31,7,33,7],[51,7,53,7]]}
[]
[null,"19-24 traineeships 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,null,null,null,null,null,null,null,null,"Grant"]
[null,null,null,null,null,null,null,null,null,null,null,null,"Contract for service"]
[null,null,null,null,null,null,null,null,null,null,"Yes"]
[null,null,"Your information",null,null,null,null,null,null,null,"No","Yes",null,"AEB learner support (CfS)",0.5,0,0,0,0,0.25,0,0,0.25,0,0,0,0.75,0.25]
[null,null,null,null,null,null,null,null,null,null,null,"No",null,"AEB (CfS)",7.9299999999999995E-2,9.1399999999999995E-2,9.4399999999999998E-2,8.5400000000000004E-2,6.6699999999999995E-2,8.4699999999999998E-2,8.3799999999999999E-2,8.48E-2,8.3199999999999996E-2,7.4700000000000003E-2,8.6300000000000002E-2,8.5300000000000001E-2,0.67049999999999998,0.32949999999999996]
[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 19-24  traineeships and learner support 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.\n\nYou can find your 2019/20 delivery values on your funding summary reports in the download reports section of Submit Learner Data: https://submitlearnerdatabeta.fasst.org.uk/ \n\nYou can find your 2018/19 delivery values on the Hub (https://hub.fasst.org.uk/Pages/default.aspx), you need to click on the data returns tab and then the business reports tab to view your R14 ILR report or your final funding claim on the funding claims tiles of Manage your Education and Skills Funding (MYESF): https://skillsfunding.service.gov.uk/home",null,null,null,null,null,null,null,null,null,"Grant","AEB community learning (CfS)",8.3299999999999999E-2,8.3299999999999999E-2,8.3299999999999999E-2,8.3299999999999999E-2,8.3299999999999999E-2,8.3299999999999999E-2,8.3299999999999999E-2,8.3599999999999994E-2,8.3299999999999999E-2,8.3299999999999999E-2,8.3299999999999999E-2,8.3400000000000002E-2,0.66669999999999996,0.33330000000000004]
[null,null,null,null,null,null,null,null,null,null,null,null,"Contract for service","AEB (Grant)",0.1444,8.5800000000000001E-2,8.6699999999999999E-2,7.0800000000000002E-2,5.6899999999999999E-2,7.4399999999999994E-2,5.3900000000000003E-2,5.3600000000000002E-2,0.12690000000000001,0.1021,8.6999999999999994E-2,5.7500000000000002E-2,0.62649999999999995,0.3735]
[null,null,"You can find your current contract values on the contracts and agreements tile of MYESF: https://skillsfunding.service.gov.uk/home",null,null,null,null,null,null,null,null,null,null,"AEB community learning (Grant)",0.1444,8.5800000000000001E-2,8.6699999999999999E-2,7.0800000000000002E-2,5.6899999999999999E-2,7.4399999999999994E-2,5.3900000000000003E-2,5.3600000000000002E-2,0.12690000000000001,0.1021,8.6999999999999994E-2,5.7500000000000002E-2,0.62649999999999995,0.3735]
[]
[null,null,"a.","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,null,null,null,null,null,null,null,null,null,null,"CFS 6 month profile",{"$f":"SUM(O8:T8)","v":0.50190000000000001}]
[null,null,"b.","Provider name",null,null,null,null,null,null,null,null,null,"Grant 6 month profile",{"$f":"SUM(O10:T10)","v":0.51900000000000002}]
[]
[null,null,"c.","What type of contract do you have?",null,null,null,"Select your adult education budget contract type from the drop down menu. "]
[]
[null,null,"2018/19 delivery",null,null,null,null,null,null,null,null,null,null,"AEB (Grant)",0.62480000000000002,0.37519999999999998,"Revised profile to take NRS into account"]
[null,null,null,null,null,null,null,"Enter the value of your 19-24 traineeships full year delivery between August 2018 and July 2019 in the 2018/19 funding year as recorded in your R14 data return or final funding claim if you are funded through a grant.  Do not include learner support funding."]
[null,null,"d.","August 2018 to July 2019 (12 months) 19-24 traineeships delivery"]
[]
[null,null,"e.","August 2018 to January 2019 (6 months) 19-24 traineeships delivery",null,null,null,"Enter the value of your 19-24 traineeships delivery between August 2018 and January 2019 in the 2018/19 funding year as recorded in your R14 data return.  Do not include learner support funding."]
[]
[null,null,"2019/20 delivery and contract value"]
[]
[null,null,"f.","August 2019 to January 2020 (6 months) 19-24 traineeships delivery",null,null,null,"Enter the value of your 19-24 traineeships delivery between August 2019 and January 2020 in the 2019/20 funding year as recorded in your R06 data return.  Do not include learner support funding."]
[]
[null,null,"g.","Increase in delivery between 2018/19 and 2019/20?",null,{"$f":"IFERROR(IF(F27>F23,\"Yes\",\"No\"),\"\")","v":"No"},null,"You do not need to enter a value in the cell.  This will say yes if your 2019/20 August to January delivery is greater than your 2018/19 August to January delivery "]
[]
[null,null,"h.","National profile",null,{"$f":"IF(F17=\"Contract for service\",O14,IF(F17=\"Grant\",O15,\"Select a contract type (c)\"))","v":"Select a contract type (c)"},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 2019 to January 2020 (6 months) 19-24 traineeships delivery divided by the national profile for August to January (for contracts for service this is 50.19%, for grant funding agreements this is 51.9%).  This uplifts your delivery to give an estimate of what you would deliver for the full 2019/20 funding year if you delivered in line with the national profile.  A value will only be calculated if you increased your delivery between 2019/20 and 2018/19 (g).  "]
[null,null,"i.","2019/20 (6 months) 19-24 traineeships delivery uplifted to a full year value",null,{"$f":"IFERROR(IF(F29=\"Yes\",F27/F31,\"-\"),\"\")","v":"-"}]
[]
[null,null,"j.","Did you receive an increase to your 2019/20 19-24 traineeships contract value at PMP1?",null,null,null,"Select yes if you were offered an increase to your 19-24 traineeships contract value at performance-management point 1 (PMP1) of the 2019/20 funding year. Select no if your contract value stayed the same at PMP1.   We emailed your head of organisation in January 2020 if we awarded growth to your organisation."]
[]
[null,null,null,null,null,null,null,"Enter the value of your 2019/20 19-24 traineeships contract value as of February 2020.  Do not include your learner support allocation in this value.  If you are funded through a grant, your funding for 19-24 traineeships and learner support is allocated as one value.  You can find out what the separate values are by contacting us using the ESFA online form and asking what your 2020/21 19-24 traineeships and learner support allocations are. "]
[null,null,"k.","Current 2019/20 19-24 traineeships contract value (as at February 2020)"]
[]
[null,null,"l.","Current 2019/20 19-24 traineeships learner support contract value (as at February 2020)",null,null,null,"Enter the value of your 2019/20 19-24 traineeships learner support contract value as of February 2020.   If you are funded through a grant, your funding for 19-24 traineeships and learner support is allocated as one value.  You can find out what the separate values are by contacting us using the ESFA online form and asking what your 2020/21 19-24 traineeships and learner support allocations are. "]
[]
[]
[null,null,"m.","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,"19-24 traineeships allocation"]
[null,null,null,null,null,null,null,"We use your 2018/19 full year delivery as the baseline unless your 2019/20 August to January delivery is greater than your 2018/19 August to January delivery.  If it is, we base your allocation on the lower of your 2019/20 delivery uplifted to a full year value or your current contract value."]
[null,null,"n.","Baseline",null,{"$f":"IFERROR(IF(F29=\"no\",F21,IF(AND(F29=\"Yes\",F33>F38),F38,F33)),\"\")","v":0}]
[]
[null,null,null,null,null,null,null,"If we increased your 2019/20 contract value at performance-management point 1 (PMP1) of the 2019/20 funding year, we will uplift your allocation to at least your current 2019/20 contract value (k)."]
[null,null,"o.","Adjustment for PMP1 growth",null,{"$f":"IFERROR(IF(AND(F35=\"Yes\",F53<F38),F38-F53,0),\"\")","v":0}]
[null,null,null,null,null,null,null,"If you delivered 19-24 traineeships in the 2018/19 funding year and/or as of R06 in 2019/20 funding year then we have calculated a 2019/20 allocation of at least £5,000 for you.  If you did not deliver in this period then you will not receive an allocation.  You can request a 2020/21 allocation through our business case process if you plan to start delivering 19-24 traineeships in the 2020/21 funding year (if you had a 2019/20 contract)."]
[null,null,"p.","Adjustment for £5,000 minimum allocation",null,{"$f":"IFERROR(IF(AND(F21+F27>0,F53+F56<5000),5000-(F53+F56),0),\"\")","v":0}]
[]
[null,null,"q.","Adjustment for track record issue",null,{"$f":"IFERROR(IF(AND(F43=\"yes\",(F53+F56+F58)>F38),F38-(F53+F56+F58),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 (m)."]
[]
[null,null,"r.","Calculated 2020 to 2021 19-24 traineeships allocation",null,{"$f":"IFERROR(F53+F56+F58+F60,\"\")","v":0},null,"This is your total allocation for the 2020/21 funding year."]
[]
[null,null,"s.","August 2020 to March 2021",null,{"$f":"IFERROR(IF(F17=\"grant\",F62*O19,F62*AA8),\"\")","v":0},null,"This is your allocation for periods 1 to 8 (August 2020 to March 2021) of the 2020/21 funding year."]
[null,null,null,null,null,null,null,"This is your allocation for periods 9 to 12 (April 2021 to July 2021) of the 2020/21 funding year."]
[null,null,"t.","April 2021 to July 2021",null,{"$f":"IFERROR(F62-F64,\"\")","v":0}]
[]
[]
[null,null,"19-24 traineeships learner support allocation"]
[]
[null,null,"u.","Current 2019/20 19-24 traineeships contract value (as at February 2020)",null,{"$f":"IFERROR(F38,\"\")","v":0},null,"This is your 2019/20 19-24 traineeships contract value as of February 2020 excluding your learner support allocation."]
[]
[null,null,"v.","2020/21 calculated 19-24 traineeships allocation",null,{"$f":"IFERROR(F62,\"\")","v":0},null,"This is your 2020/21 19-24 traineeships allocation based on the information you have provided in this calculator."]
[null,null,null,null,null,null,null,"This is the percentage your 2020/21 19-24 traineeships calculated allocation is of your 2019/20 19-24 traineeships contract value as of February 2020 excluding your learner support allocation."]
[null,null,"w.","2020/21 allocation % of 2019/20 current contract value",null,{"$f":"IFERROR(F62/F38,\"\")","v":""}]
[]
[null,null,"x.","Current 2019/20 19-24 traineeships learner support contract value (as at February 2020)",null,{"$f":"IFERROR(F40,\"\")","v":0}]
[]
[null,null,"y.","Current learner support contract value multiplied by %",null,{"$f":"IFERROR(F77*F75,\"\")","v":""},null,"We multiply your current 2019/20 19-24 traineeships learner support contract value by the percentage your 2020/21 calculated allocation is of your contract value as of February 2020."]
[]
[null,null,"z.","Adjustment for £500 minimum allocation",null,{"$f":"IFERROR(IF(AND(F73>0,F79<500),500-F79,0),\"\")","v":0},null,"If the allocation we have calculated for you is less than £500, we will uplift it to £500."]
[]
[null,null,"aa.","Adjustment for track record issue",null,{"$f":"IFERROR(IF(AND(F79+F81>F77,F43=\"yes\"),F77-(F79+F81),0),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 (m)."]
[]
[null,null,"ab.","Calculated 2020 to 2021 19-24 traineeships learner support allocation",null,{"$f":"IFERROR(F79+F81+F83,0)","v":0},null,"This is your total allocation for the 2020/21 funding year."]
[]
[null,null,"ac.","August 2020 to March 2021",null,{"$f":"IFERROR(IF(F17=\"grant\",F85*O19,F85*AA7),\"\")","v":0},null,"This is your allocation for periods 1 to 8 (August 2020 to March 2021) of the 2020/21 funding year."]
[]
[null,null,"ad.","April 2021 to July 2021",null,{"$f":"F85-F87","v":0},null,"This is your allocation for periods 9 to 12 (April 2021 to July 2021) of the 2020/21 funding year."]