{"text_format":"1","workbook":"xlsx","n_sheets":8,"sheets":["Contents","Instructions","Simple","Detailed","V1 presets","Sheet3","Factors used","Options (Summary)"],"not_rendered":{"charts":0,"images":2,"macros":1,"external_links":0,"defined_names":3,"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":"Contents","index":0,"n_rows":26,"n_cols":2,"rows_kept":26,"cut":false,"hidden":false,"merged":[[25,0,25,9]]}
["Covering all bases: Workforce Calculator"]
[]
["Contents"]
[]
["Instructions","How to use the calculator tool"]
[]
["Simple Calculator","To use when detailed breakdown of individual clinics is not known"]
[]
["Detailed Calculator","To use when you want to enter local values and/or data for individual clinics"]
[]
["Factors used in the calculators","Standard factors used in the calculator"]
[]
["Options Summary","To store multiple versions of the simple calculator summary"]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
[]
["© 2017 British Association for Community Child Health and Royal College of Paediatrics and Child Health."]

{"sheet":"Instructions","index":1,"n_rows":15,"n_cols":11,"rows_kept":15,"cut":false,"hidden":false,"merged":[[8,1,8,8],[1,0,1,17],[6,1,6,8],[7,1,7,8],[3,0,3,8],[4,0,4,1],[3,9,3,17],[6,10,6,17],[7,10,7,17],[8,10,8,17],[9,1,9,8],[9,0,13,0],[11,1,11,8],[12,1,12,8],[13,1,13,8],[10,1,10,8],[9,9,13,9],[9,10,9,17],[10,10,10,17],[11,10,11,17],[12,10,12,17],[13,10,13,17]]}
["How many consultants are required for your community child health service? "]
["This spreadsheet contains two calculators designed to estimate the WTE of consultants (including equivalent SAS doctors) for a community child health service."]
["Simple Calculator",null,null,null,null,null,null,null,null,"Detailed Calculator"]
["This can be used when you only know or can reasonably estimate the total number of new and follow appointments for your CCH service, plus an estimate for unmet demand (if any) i.e. where the detail for each clinic is not known. You will also need to be able to estimate the PAs required for lead roles and child protection rotas in your service. This calculator applies fixed values for appointment length, patient admin and prospective cover. These are shown in Factors used tab.                                                                                                                                                  You can save multiple versions of the calculator by using the Save option button at the top of the screen which will transfer your inputs and outputs to the Options (Summary) tab in this worksheet. It will always input your latest calculation to the left hand data column, moving previous calculations to the right. You are advised to name each column for your own reference. Use the hyperlink at the bottom to return to the simple spreadsheet. The Clear inputs button resets the calculator for you to enter new values.",null,null,null,null,null,null,null,null,"This can be used firstly when you can estimate the total number of new and follow appointments for your CCH service AND you wish to input your own local values for DNA rate, appointment length, patient admin and the number of SPAs per doctor instead of using the fixed options in the simple calculator. Secondly, this model allows you to enter the number of appointments etc. for individual clinics within your service and vary the factors for each clinic if required. The save option is not currently available for the detailed model.                                                                                          You can save a copy of the detailed calculation by using the standard Excel \"Move or Copy\" function when right clicking the worksheet tab. (Remember to check \"Create a copy\").                                                                                                                         The Clear inputs button resets the calculator for you to enter new values."]
["Factors used"]
["How to use",null,null,null,null,null,null,null,null,"How to use"]
[1,"Enter the number of new appointments (Cell B9) in your service per annum. We recommend that you include DNA appointments in this estimate to ensure the time is included in the calculation. You can also enter additional appointments needed to meet demand per annum (B10)",null,null,null,null,null,null,null,1,"Using one row for each service you wish to enter, name the service and then enter each factor along the row, as indicated in the example.  This can be repeated for each row until all your services are entered.  If you only have overall estimates, you will require just the first row."]
[2,"Enter the number of follow up appointments (B11) in your service per annum. We recommend that you include DNA appointments in this estimate to ensure the time is included.",null,null,null,null,null,null,null,2,"DNAs can be entered in two ways.  If you know the NUMBER of DNAs, you can include them in your total new and/or follow up appointments needed and enter 0% in the DNA columns.  If you know the total appointments offered and your DNA RATE, enter the total offered in the appointments column and the rate in the DNA column. "]
[3,"Enter the weekly PAs for statutory roles (B14), Child Protection Rota allocation (B15), average SPAs per doctor per week (B16) and average travel time per doctor per week (B17). ",null,null,null,null,null,null,null,3,"Enter the weeks for prospective cover, weekly PAs for statutory roles, Child Protection Rota allocation, weekly average SPAs per doctor and weekly average PAs for travel per doctor in column E."]
[4,"Outputs",null,null,null,null,null,null,null,4,"Outputs"]
[null,"A) Total Clinic Programmed Activities per annum",null,null,null,null,null,null,null,null,"A) Total Clinic Programmed Activities per annum"]
[null,"B) Total Clinic Programmed Activities  per week ",null,null,null,null,null,null,null,null,"B) Total Clinic Programmed Activities  per week "]
[null,"C) Total DCC PAs required per week for your CCH service",null,null,null,null,null,null,null,null,"C) Total DCC PAs required per week for your CCH service"]
[null,"D) Total WTE consultants and SAS doctor equivalents required",null,null,null,null,null,null,null,null,"D) Total WTE consultants and SAS doctor equivalents required"]
["Simple Calculator",null,null,null,null,null,null,null,null,"Detailed Calculator"]

{"sheet":"Simple","index":2,"n_rows":41,"n_cols":4,"rows_kept":41,"cut":false,"hidden":false,"merged":[[38,0,38,1],[39,0,39,1],[40,0,40,1],[31,0,31,1],[32,0,32,1],[33,0,33,1],[34,0,34,1],[35,0,35,1],[36,0,36,1],[22,2,23,3],[24,2,24,3],[25,2,25,3],[26,3,26,7],[27,3,27,7],[37,0,37,1],[30,0,30,1],[26,0,26,1],[27,0,27,1],[28,0,28,1],[29,0,29,1],[5,2,5,3],[6,2,6,3],[7,0,7,3],[8,2,8,3],[9,2,9,3],[10,2,10,3],[21,2,21,3],[20,2,20,3],[19,2,19,3],[18,2,18,3],[17,0,17,3],[11,2,11,3],[12,0,12,3],[13,2,13,3],[14,2,14,3],[15,2,15,3],[16,2,16,3],[4,2,4,3],[3,2,3,3],[2,2,2,3],[1,2,1,3],[0,0,0,3],[28,3,28,7],[29,3,29,7],[30,3,30,7],[31,3,31,7],[32,3,32,7],[38,3,38,7],[39,3,39,7],[40,3,40,7],[33,3,33,7],[34,3,34,7],[35,3,35,7],[36,3,36,7],[37,3,37,7]]}
["How many career grade paediatricians are required for your community child health service? "]
["Simple Calculator"]
[]
["Please enter data for your service in the yellow cells "]
["Calculated outputs are shaded pink. "]
["Factors used in the calculations"]
["User Input factors","Value","Guidance and notes"]
["Clinics - new and follow up appointments in your service"]
["New appointments in your service per annum (include DNAs - did not attends)",null,"For information, the average number of new appointments according to our UK survey of community child health services 2016 was 1413"]
["Addional new appointments required to meet demand per annum",null,"This could reflect the increase in the number on your waiting lists, or the difference between the number of accepted referrals and appointments. If no or negligible increase, enter zero"]
["Follow up appointments in your service per annum  (include DNAs - did not attends)",null,"For information, the average number of follow up appointments according to our UK survey of community child health services 2016 was 3060"]
["Additional follow up appointments required to meet demand (calculated)",{"$f":"IFERROR(B10*(B11/B9), 0)","v":0},"Calculated by applying follow up ratio to your new and additional appointments"]
["Other workload and contract variables"]
["Statutory roles PAs per week for the team",null,"The number of PAS recommended for each role in guidance documents is set out below. If a service (in England)  provides all roles the total should be entered as 13.5"]
["Child Protection Rota PAs per week for the team",null,"We suggest that the total current contractual allocation for child protection rotas by consultants and SAS doctors in your CCH service is input here"]
["SPAs (Supporting professional activities) - enter the average no. of SPAs per doctor in your service per week",null,"The BMA consultant contract is for 2.5 SPAs for a whole time 10 PA contract. This is supported by the College, although we also acknowledge regional variations e.g. 3 PAs is standard in Wales"]
["Travel time - enter an estimate of average travel time in PAs per week per doctor",null,"Reminder, 1 PA=4 hours"]
["Calculator Outputs"]
["Total Clinic Programmed Activities per annum  ",{"$f":"(((B9+B10)*'V1 presets'!B3)+((B11+B12)*'V1 presets'!B4))/('V1 presets'!B6)*(1+'V1 presets'!B5)","v":0},"Number of new and follow up appointments multiplied by appointment length plus factor for clinical admin"]
["Total Clinic Programmed Activities  per week ",{"$f":"B19/42","v":0},"Calculated to take account of prospective cover i.e. 42 weeks per year"]
["Total DCC PAs required per week for your CCH service",{"$f":"B20+B14+B15","v":0},"Includes clinics, statutory roles and child protection rotas. Does not include supporting professional activities (SPAs) and travel time"]
["Total WTE consultants and SAS doctor equivalents required",{"$f":"B21/('V1 presets'!B8-B16-B17)","v":0},"Accounts for SPAs and travel  time."]
[]
["Notes"]
["1. Consultants also include SAS doctors who work at a consultant level of responsibility"]
["2. PA allocation for statutory roles (National guidance)"]
["Lead role",null,"Average PAs","Notes/Source of Guidance"]
["Designated doctor for safeguarding",null,"4.5-5","For LA or CCG area (RCPCH - Designated Doctor/Lead Paediatrician Model Job Description and Competencies)"]
["Named doctor for safeguarding",null,"2-2.5","For LA or CCG area (RCPCH - Named Doctor Model Job Description and Competencies)"]
["Lead Paediatrician in Child Protection (Scotland)",null,"4.5-5","According to the size and needs of the population"]
["Paediatrician with a special interest in child protection (Scotland)",null,4.5,"According to the size and needs of the population"]
["Designated doctor for looked after children",null,2,"For 400 LACs per year"]
["Named doctor for looked after children",null,1]
["Designated doctor for child deaths",null,1]
["Child death overview panel child health representative",null,1]
["PRUDiC practitioner (Wales)",null,2,"Estimate based on resource needed in England"]
["Designated medical officer for SEND",null,1]
["Designated medical officer for ALN (Wales)",null,1,"Estimate based on resource needed in England"]
["Medical adviser for adoption/fostering",null,2,"For 400 LACs per year"]
["Healthy Child Programme coordinator",null," ","Not available"]
["Immunisation coordinator",null," ","Not available"]

{"sheet":"Detailed","index":3,"n_rows":50,"n_cols":11,"rows_kept":50,"cut":false,"hidden":false,"merged":[[46,6,46,10],[47,6,47,10],[48,6,48,10],[49,6,49,10],[40,6,40,10],[41,6,41,10],[42,6,42,10],[43,6,43,10],[44,6,44,10],[45,6,45,10],[39,6,39,10],[28,5,28,10],[29,5,29,10],[30,5,30,10],[35,6,35,10],[36,6,36,10],[37,6,37,10],[38,6,38,10],[5,0,5,3],[22,0,22,3],[22,5,22,10],[23,0,23,3],[23,5,23,10],[24,0,24,3],[24,5,24,10],[30,0,30,3],[29,0,29,3],[28,0,28,3],[25,0,25,3],[25,5,25,10],[26,0,26,3],[26,5,26,10],[27,0,27,3],[27,5,27,10]]}
["How many consultants are required for your community child health service? "]
["Detailed Calculator"]
[]
["Please enter data for your service in the yellow cells "]
["You can enter data in one row for the service as a whole or create additional rows for each clinic or service provided."]
["Calculated outputs are shown shaded pink. "]
[]
["Clinic","New appointments in your service per annum  ","Addional new appointments required to meet demand per annum","DNA rate for new appointments","Length of new appointment in minutes","Follow up appointments in your service per annum   ","Additional follow up appointments required to meet demand (calculated)","DNA rate for follow up appts","Length of follow up appointment  in minutes","Patient admin allocation for this clinic (PAs per 1 Clinic PA)","Total PAs for clinic per annum"]
["Example",200,20,0.108,60,400,{"$f":"IFERROR((F9/B9)*C9, 0)","v":40},0.12,30,1,{"$f":"((((B9+C9)*(1+D9)*E9)+((F9+G9)*(1+H9)*I9))/240)*(1+J9)","v":245.08}]
[null,null,null,null,null,null,0,null,null,null,{"$f":"((((B10+C10)*(1+D10)*E10)+((F10+G10)*(1+H10)*I10))/240)*(1+J10)","v":0}]
[null,null,null,null,null,null,0,null,null,null,{"$f":"((((B11+C11)*(1+D11)*E11)+((F11+G11)*(1+H11)*I11))/240)*(1+J11)","v":0}]
[null,null,null,null,null,null,{"$f":"IFERROR((F12/B12)*C12, 0)","v":0},null,null,null,0]
[null,null,null,null,null,null,{"$f":"IFERROR((F13/B13)*C13, 0)","v":0},null,null,null,0]
[null,null,null,null,null,null,{"$f":"IFERROR((F14/B14)*C14, 0)","v":0},null,null,null,0]
[null,null,null,null,null,null,0,null,null,null,0]
[null,null,null,null,null,null,0,null,null,null,0]
[null,null,null,null,null,null,{"$f":"IFERROR((F17/B17)*C17, 0)","v":0},null,null,null,{"$f":"((((B17+C17)*(1+D17)*E17)+((F17+G17)*(1+H17)*I17))/240)*(1+J17)","v":0}]
[null,null,null,null,null,null,0,null,null,null,0]
[null,null,null,null,null,null,{"$f":"IFERROR((F19/B19)*C19, 0)","v":0},null,null,null,{"$f":"((((B19+C19)*(1+D19)*E19)+((F19+G19)*(1+H19)*I19))/240)*(1+J19)","v":0}]
[null,null,null,null,null,null,0,null,null,null,0]
[null,null,null,null,null,null,0,null,null,null,0]
[null,null,null,null,null,null,{"$f":"IFERROR((F22/B22)*C22, 0)","v":0},null,null,null,{"$f":"((((B22+C22)*(1+D22)*E22)+((F22+G22)*(1+H22)*I22))/240)*(1+J22)","v":0}]
["Total Clinic Programmed Activities per annum  ",null,null,null,{"$f":"SUM(K10:K22)","v":0},"PAs for new and follow up appointments, accounting for DNAs, appointment length and clinical admin"]
["Average Consultant availability in weeks (prospective cover)",null,null,null,null,"In order to account for annual leave, study leave etc. Standard = 42 weeks"]
["Total Clinic Programmed Activities  per week ",null,null,null,{"$f":"IFERROR(E23/E24,0)","v":0},"DCC per week calculated to take account of prospective cover "]
["Statutory roles PAs per week for the team",null,null,null,null,"The number of PAS recommended for each role in guidance documents is set out below. If a service (in England)  provides all roles the total should be entered as 13.5"]
["Child Protection Rota PAs per week for the team",null,null,null,null,"Total contractual allocation for CP rotas by consultants/SAS doctors in CCH service"]
["Total DCC PAs required per week for your CCH service ",null,null,null,{"$f":"IFERROR(SUM(E25+E26+E27),0)","v":0},"Includes DCC, statutory roles, and child protection rotas"]
["SPAs (Supporting professional activities) - enter the average no. of SPAs per doctor in your service per week",null,null,null,null,"2.5 PAs = standard contract"]
["Travel time - enter an estimate of average travel time in PAs per week per doctor",null,null,null,null,"Reminder, 1 PA=4 hours"]
["Total WTE consultants and SAS doctor equivalents required",null,null,null,{"$f":"E28/('V1 presets'!B8-E30-E29)","v":0}]
[]
["Notes"]
["1. Consultants also include SAS doctors who work at a consultant level of responsibility"]
["2. PA allocation for statutory roles (National Guidance)"]
["Lead role",null,null,null,null,"Average PAs","Notes/Source of Guidance"]
["Designated doctor for safeguarding",null,null,null,null,"4.5-5","For LA or CCG area (RCPCH - Designated Doctor/Lead Paediatrician Model Job Description and Competencies)"]
["Named doctor for safeguarding",null,null,null,null,"2-2.5","For LA or CCG area (RCPCH - Named Doctor Model Job Description and Competencies)"]
["Lead Paediatrician in Child Protection (Scotland)",null,null,null,null,"4.5-5","According to the size and needs of the population"]
["Paediatrician with a special interest in child protection (Scotland)",null,null,null,null,4.5,"According to the size and needs of the population"]
["Designated doctor for looked after children",null,null,null,null,2,"For 400 LACs per year"]
["Named doctor for looked after children",null,null,null,null,1]
["Designated doctor for child deaths",null,null,null,null,1]
["Child death overview panel child health representative",null,null,null,null,1]
["PRUDiC practitioner (Wales)",null,null,null,null,2,"Estimate based on resource needed in England"]
["Designated medical officer for SEND",null,null,null,null,1]
["Designated medical officer for ALN (Wales)",null,null,null,null,1,"Estimate based on resource needed in England"]
["Medical adviser for adoption/fostering",null,null,null,null,2,"For 400 LACs per year"]
["Healthy Child Programme coordinator",null,null,null,null,null,"Not available"]
["Immunisation coordinator",null,null,null,null,null,"Not available"]

{"sheet":"V1 presets","index":4,"n_rows":9,"n_cols":5,"rows_kept":9,"cut":false,"hidden":true,"merged":[]}
["Simple Factors"]
["Preset data factors","Preset value","Measure","Preset calculation/rationale","Notes"]
["New patient appointment length",60,"Minutes","Modal length = 46-60 minutes for all except CSA (76-90), enuresis (31-45)","Adjusted after working group meeting in June"]
["Follow up patient appointment length",30,"Minutes","Estimate","Needs to be calculated for all clinics"]
["Patient admin per clinic time",1,"Percentage of clinic time","Mean admin time allocated for services where allocation not inadequate","Possibly allow user override?"]
["Minutes per PA",240,"Minutes","BMA standard"]
["Consultant working weeks per year",42,"weeks","Account for annual leave, study leave etc."]
["PAs per wte per week",10,"PAs","Standard contract"]
["SPAs per week",2.5,"SPAs","Standard contract"]

{"sheet":"Sheet3","index":5,"n_rows":0,"n_cols":0,"rows_kept":0,"cut":false,"hidden":true,"merged":[]}

{"sheet":"Factors used","index":6,"n_rows":8,"n_cols":4,"rows_kept":8,"cut":false,"hidden":false,"merged":[]}
["Factors used in Simple Model"]
["Factors","Preset value","Measure","Preset calculation/rationale"]
["New patient appointment length",60,"Minutes","Modal length = 46-60 minutes for all except CSA (76-90), enuresis (31-45) (from survey data"]
["Follow up patient appointment length",30,"Minutes","Estimate"]
["Patient admin per clinic time",1,"Percentage of clinic time","Mean admin time allocated for services where allocation not inadequate (from survey data)"]
["Minutes per PA",240,"Minutes","Contract standard"]
["Consultant working weeks per year (prospective cover)",42,"weeks","Account for annual leave, study leave etc."]
["PAs per wte per week",10,"PAs","Contract standard"]

{"sheet":"Options (Summary)","index":7,"n_rows":25,"n_cols":1,"rows_kept":25,"cut":false,"hidden":false,"merged":[]}
[]
[]
["Calculator Summary Options "]
[]
["Option Name"]
[]
["Clinics - new and follow up appointments in your service"]
[]
["New appointments in your service per annum (include DNAs - did not attends)"]
["Addional new appointments required to meet demand per annum"]
["Follow up appointments in your service per annum  (include DNAs - did not attends)"]
["Additional follow up appointments required to meet demand (calculated)"]
["Other workload and contract variables"]
["Statutory roles PAs per week for the team"]
["Child Protection Rota PAs per week for the team"]
["SPAs (Supporting professional activities) - enter the average no. of SPAs per doctor in your service per week"]
["Travel time - enter an estimate of average travel time in PAs per week per doctor"]
["Calculator Outputs"]
["Total Clinic Programmed Activities per annum  "]
["Total Clinic Programmed Activities  per week "]
["Total DCC PAs required per week for your CCH service"]
["Total WTE consultants and SAS doctor equivalents required"]
[]
["Simple Calculator"]
["Detailed Calculator"]