SOCIAL WORKER SUPPLY AND DEMAND MODEL USER GUIDE
Contents
1. 19 23 1 1 Worksheets 13 gt SOBTIIETOS saab Van de a etnia ad d a Dad n 20 2 3 12 Worksheet 14 Scenario COMparison sncinssisinsanianmianmnimnainneie 23 2 3 13 Worksheets 15 24 N Scenario 1 5 and L Scenario 5 24 2 3 14 Worksheet 25 LA compeatisOFTu ui dii po RERO O ADAIR 25 3 Section 3 Further information riis retiro etis pes tare uina petia 26 4 Acknowledg 31 SECTION 1 OVERVIEW 1 1 Introduction Welcome to the user guide for the social worker supply and demand model In this guide you will find information about how to use the model and advice on how to make the best use of the model This guide is a working document and we welcome feedback from users and contributions to help develop and refine the content If you are interested in helping us with this please email enquiries qcfwi org uk 1 2 Overview of the model The social worker supply and demand model has been developed by the Centre for Workforce Intelligence CfWI in partnership with the Social Work Reform Board The model itself is a tool to assist in predicting supply and demand to aid workforce planning 1 3 Howtousethis guide Section 2 contains information about each worksheet of the model the model contains 25 worksheets in total For each worksheet it includes Title the name of the worksheet Description what it does How to use it what you can do on the worksheet Informatio2. Mouchel Management Consulting Ltd obtains and uses information and data from third party sources and cannot guarantee the accuracy of such data The report also contains projections which are subjective in nature and constitute Mouchel Management Consulting Ltd s opinion as to likely future trends or events based on i the information known to Mouchel Management Consulting Ltd at the time the report was prepared and ii the data that it has collected from third parties Other than exercising reasonable skill care and diligence in the preparation of this report Mouchel Management Consulting Ltd does not provide any other warranty whatsoever in relation to the report whether express or implied including in relation to the accuracy of any third party data used by Mouchel Management Consulting Ltd in the report and in relation to the accuracy completeness or fitness for any particular purposes of any projections contained within the report Mouchel Management Consulting Ltd shall not be liable to any person in contract tort including negligence or otherwise for any damage or loss whatsoever which may arise either directly or indirectly including in relation to any errors in forecasts speculations or analyses or in relation to the use of third party information or data in this report For the avoidance of doubt nothing in this disclaimer shall be construed so as to exclude Mouchel Management Consulting Ltd s liability for fraud or fraudulent m
3. cent of the workforce then 50 per cent of the three leavers will be taken from the adult workforce and 50 per cent of the three leavers from the children s workforce If you wanted to ensure that three leavers leave the children s workforce then you should model the number of leavers to provide six leavers so three leave the adults workforce and three leave the children s workforce This can get complicated so please contact us for assistance if you would like to do this Is there sensitivity to show less than whole percentages Some areas showed up as 0 where there were actual workers o Thisis a rounding issue where figures are below 0 5 and so round down to You can unprotect the relevant worksheet and increase the number of decimal places How do you run more than the set number of scenarios and keep existing ones o Onceyou have created your baseline scenario and copied the results to the other scenarios you can save extra copies of the model i e Model V1 Model V2 etc Although each instance of the model only has space for a baseline and 4 other scenarios you can make more than one copy of the model and make more scenarios CfWI August 2012 30 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 4 ACKNOWLEDGEMENTS The Centre for Workforce Intelligence would like to thank the following people for the input to the social worker supply and demand model from its creation release and development Social Wor
4. registered manager mean are they social workers o Aregistered manager is registered with the Care Quality Commission to manage a regulated service They are a range of qualifications and some are social workers Only those who are qualified social workers are counted in the model Any plans or capability to sort out problems with graph labelling o We may update these at a later date If you find these to be restricting your use of the model then you can unprotect the worksheets and change the labelling Can you say what participation means on the local model tab o Participation is the ratio between HC and FTE For example work full time 37 5 hrs over 5 days in a week This is counted as a participation rate of 100 per cent as work all the hrs set as a full week If worked 30 hrs in a week this would be 20 per cent less so would have a participation rate of 80 per cent Can we separate the data and run this for just children s or adults CfWI August 2012 29 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and demand model User Guide o Thedata cannot be separated within the model but you can simply ignore the figures for adults What you will need to be aware of is that the supply figures are distributed between adults and children s services workforces based on the size of each service in the workforce For example you may model three people permanently leaving the workforce If adults services are 50 per
5. the answer CfWI August 2012 24 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 14 Worksheet 25 LA comparison Description This worksheet allows you to compare some key statistics about your local authority against up to ten others How to use it To use this worksheet simply select any number of local authorities from the drop down lists in the blue cells at the top of the page The graph below will then plot the current workforce sizes and populations Please note that you must select a LA in order If you enter in four LAs to compare and then delete the third one you will not be able to view the results of the fourth LA This is shown using grey cells Below this section is an age profile comparator table and graph that will let you compare your local authority age profile against the national age profile There is limited information that we can provide to compare with For example we cannot compare overall demand figures as each authority will tailor their own workload pages Information already included This worksheet contains summary information of the current workforce from NMDS SC and population from ONS Things you can change You can change the comparator local authorities on this worksheet Whichever local authority has been selected on the CfWI page at the beginning of the model will be set as the first authority How to get the best out of it This comparator table only allows you to com
6. You can edit three tables in this worksheet The first allows you to change how many graduates obtain jobs and enter the workforce This can be edited separately for undergraduates postgraduates and international recruits The second table allows you to change the regional allocation of students to simulate more students being trained in your region CfWI August 2012 14 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled The third table allows you to add qualified social workers who return to practice and join the workforce How to get the best out of it Get in touch with Higher Education Institutes HEls in your region or your partner HEls to work out how many social workers will be trained for you to employ Then adjust the number of graduates finding work or the regional allocation table to get the correct figures Practice Examples 1 How many undergraduate students are obtaining jobs nationally in 2016 2 If 50 per cent of postgraduates obtain jobs how many are available nationally in 2016 3 What happens to the number of social workers obtaining jobs in your local authority if you allocate 20 per cent of social work students to the region your local authority is in CfWI August 2012 15 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 9 Worksheets 9 10 Adult and Children s Workload Description These worksheets allow you to model the demand for social workers in adul
7. activity numbers For children s services the Children in need CiN census is used Things you can change These worksheets have a large amount of blue cells that you can edit This is very important as each local authority does things differently so it allows a high level of customisation How to get the best out of it We recommend starting with the social worker task activity and time allocation documents and work with your team to establish the appropriate timings for each task You could either use the current information in the documents and amend it or delete the information and start afresh to complete it for your timings Once you have this data use it to complete the tables in the worksheet There are a large number of blue cells that you can potentially enter data into so itis important that you understand what you need to include in each and how they can impact on the output of the model Once you have entered in your data and completed the worksheet check the overall total of social workers that has been calculated to be required It may not be what you are expecting so we suggest you double check you have entered everything correctly This includes checking the activity levels number of cases that you are modelling Practice Examples 1 How many social workers are required to undertake high complexity Task 2 adult services Task 1 children s services cases 2 What happens to the number of social workers required if
8. children s services to model the level of activity undertaken You can model a different activity type for example reablement services by entering in a new activity level This will overwrite an existing job type Could you provide information on those LAs who are intending to use the tool so that we can make regional arrangements to share info to compare and contrast Will there be summaries collected from LAs using this and sent out by CfWI would be useful to track our progress o Once we send the model to a local authority we do not ask for any data or results to be returned We recommend that if you would like to do some regional planning that you contact your neighbouring authorities and if they aren t using the model put them in touch with us and then bring your results together as a region Is there a version for social work teams in mental health trusts o There isn t currently a version to do this however CfWI will be working on modelling wider social care roles What about higher level managers who also get involved and are also qualified social workers o This depends on who was recorded under which job role when the NMDS SC was completed If the higher level managers weren t included under one of the roles we have provided you could change the workforce numbers to accommodate them For example if your registered managers workforce is five and you CfWI August 2012 28 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Su
9. year at varying age brackets The average retirement age can also be set Retirement calculates a steady tail off from the user defined retirement age until the age of 70 when it is assumed that everyone will retire The calculations use the age profile national or local from the Age Profile worksheet to project future retirements Information already included There is no data on this sheet It is based on user assumptions to provide the number of people using the workforce The retirements are based on the age profile Things you can change You can edit the number of people permanently leaving the workforce and the average retirement age How to get the best out of it Find out an average figure for people leaving your workforce and flex the percentages to produce these results Do people tend to retire early or late Practice Examples 1 How many people are due to retire in 2014 with an average retirement age of 65 2 How does this number change if you select an average retirement age of 60 CfWI August 2012 12 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 7 Worksheet 7 Temporary Leavers Description The temporary leavers worksheet allows you to temporarily remove people from the workforce How to use it This worksheet works similarly to the permanent leavers worksheet There are two tables The first models maternity leave and the second models any other reason for people temporarily leav
10. you change the amount of hours taken to complete a high complexity Task 2 adult services Task 1 children s services to 5 hours 3 What happens if you also change all of the Task 2 adult services Task 1 children s services undertaken by social workers to be high complexity 4 What happens if you double the activity level number of cases for Task 3 adult services Task 2 children s services CfWI August 2012 18 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 10 Worksheets 11 12 National and Local Model Description This worksheet pulls through and collates all of the data calculated in the previous worksheets to project the supply and demand How to use it This worksheet provides a summary of the previous worksheets and projects forward the results of the supply and demand The totals are split for adult and children s services Supply is calculated by taking the current workforce in a year adding in the joiners taking out permanent leavers and temporary leavers and adding back in temporary leavers from previous years This calculation is repeated each year to provide the projection forwards FTE is calculated by using the average participation rate participation is the difference between HC and FTE over the first four years of current workforce data A participation target can be set for the final year of the projection and this will change the participation each year until it reache
11. CENTRE FOR WORKFORCE INTELLIGENCE SOCIAL WORKER SUPPLY AND DEMAND MODEL USER GUIDE CfWI August 2012 TABLE OF CONTENTS 2 1 E 3 11 iion e 3 712 Overview of the model sseirtari ht arERC ers EnER EE HERE ENS 3 7 2 Howtouse this BEC ivoire edi pA CLERO 3 2 Section 2 user 4 21 Getting State sonam nhdiackaxaroneskbr eic EE Arr ORAT 4 22 IME 5 23 Te FUE GIOI HDIA 7 2 3 1 Worksheet 1 CWI model home page or eet rent inte 7 2 3 2 Worksheet 2 8 2 3 3 Worksheet 3 SOUC E 8 2 3 4 Worksheet 4 Current 6 9 2 3 5 Worksheet 52586 10 2 3 6 Worksheet 6 Permanent Leavers sssssssssssssssssssssssssssseeeeeeeeseersrrrrereeeeesesssssssssss 12 2 3 7 Worksheet 7 Temporary LeaVerS sssssssssssssssssssssssssssssseeeeeeeeersrrerrreeeeeeessessssssss 13 2 3 8 Worksheet 8 JOiNnefS sssesssseessseessseesssessseessseessseessseessseesssssetssserssseessseenssersseeesssersseers 14 2 3 9 Worksheets 9 10 Adult and Children s Workload 16 2 3 10 Worksheets 11 12 National and Local
12. NS population projections The population of varying age groups can be changed This will change the rate at which demand is projected e Changes in requirement CfWI August 2012 20 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and o This changes the activity levels to simulate more or less people being eligible for and therefore receiving services This will change the level of demand e Changes to the distribution of services o This changes the proportion of services that are received by age group in adult services This will change the rate at which demand is projected Reduction in social workers o This reduces the current workforce level This will simulate a one off reduction in the current workforce in the current year which will affect the supply e Changes in number of students studying o This changes the number of students entering training This will mean more graduates join the workforce and will affect the supply To make a change to a scenario first start by recalling a scenario by typing in the number of the scenario you would like to recall and clicking the blue rectangular recall scenario button Once the scenario has loaded you can make any changes to reflect what you want to change For example you may want to increase requirements by 5 per cent You can also change any other blue cell within the model so you may also want to increase the average retirement age from 65 to 67 on the Permanent Leavers
13. able at the bottom of the worksheet and gives the figure for the local authority or nationally If you decide that you would prefer to model a different activity type that is separate to the RAP returns or CiN census then the blue cells enable you to enter in the name of an alternative task and the activity level If you think that the RAP return or CiN census figures are inaccurate you can enter in a new figure in the blue cell for the same task type that better reflects the activity level undertaken The table to the right of the one entitled Task 1 takes the activity level and allows you to attach time to it The table contains rows for each of the job roles contained in the Current Workforce worksheet There is also space to add an alternative social work role This function allows you to split the social worker job role into more than one category if you think the role is too broad The next step is to enter in the amount of time in hours that each job role will spend to complete the task and at various complexity levels The time entered should just be the amount that is directly attributable to the task for the job role For example a social worker may not undertake a whole referral but may oversee a team of workers So although the time taken to do one referral might be 1 hour the social worker only spends 0 2 hours per referral Then allocate the number of cases by job role and complexity This is done by entering in a percentage into the re
14. ange the local authority age and gender profile on this page using the blue cells in the top right table How to get the best out of it The age profile is important because the model ages the workforce as it projects into the future If your workforce is all 50 years old now the model ensures that they are all 60 in ten years time If you think your age profile differs considerably from the national age profile then it would be a good idea to enter in your own Once you have entered in your age profile you can view a graph to show a comparison against the national profile on the LA Comparisons worksheet CfWI August 2012 10 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and Practice Examples 1 Find the age profile information for your local authority How many people are above 60 years old and may be due to retire at some point in the next 10 years 2 Which age range has the most people CfWI August 2012 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 6 Worksheet 6 Permanent Leavers Description This worksheet allows you to tailor how many people are leaving the workforce due to retirement any other reason for permanently leaving or people that have temporarily left the workforce and not returned How to use it You can tailor the number of people HC leaving the workforce by entering in percentages to provide an accurate number of people leaving the workforce each
15. e model You can use these documents to compare the case timings and edit them to reflect how your local authority works How to use it These worksheets have a large number of user defined assumptions that need to be entered as each local authority will do things differently The next section will list the user defined assumption tables that you can tailor The first table allows you to set the number of working hours per week and weeks per year The information in the table is based on a 5 day full time working week and has calculated the weeks by taking the number of working days per year which is currently based on 260 working days minus 30 days leave 8 bank holidays and 12 days other e g training or sickness giving 210 days or 42 weeks There are comments in each of the blue cells that you can view by hovering your mouse over the cell that will confirm the information above CfWI August 2012 16 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and The next table entitled Task 1 enables you to start modelling the demand for a specific job type Data from the RAP returns and CiN census is used to provide your reported activity levels In the adults worksheet the first table will model the number of referrals dealt with at the point of contact For children s services it is the number of referrals The table shows the activity level i e number of cases in the pink cell The calculation uses the data from the purple t
16. e to use the model If you require further assistance then please contact the CfWI Willi be able to get national information from the model o Yes you can the model is flexible to work at a national and at a local authority level e How upto date is the information in the model o If you go to the Sources worksheet within the model it will detail all of the data sources used and the time period that the data covers I m worried that people from other local authorities will be able to see the data from my area o Once we have sent you the model it is yours to own on your computer No other organisations will have access to your version of the model or the assumptions you put in it unless you send it to them for collaborative working The only data that other organisations can see is the data purple cells we have entered for you that is publicly available information Why do some cells show zero but the final total shows four o This occurs when calculations produce a result that is less than 0 5 in a cell that is rounded to zero decimal places It is impossible to have a headcount of less than one but the model will calculate on this basis You can amend the inputs to the calculations to provide results that are whole numbers e How has the task and time worksheets been calculated We liaised with local authorities who provided us with the data that fits their local authority For the change in population does the data
17. es of scenarios you may like to model The scenarios provided in the model are basic and change one thing but you can change several things at once as shown below 1 Weare having to reduce our headcount whilst providing more services a This can be modelled by firstly reducing the number of social workers in 2012 by X per cent and also increasing the activity for adult children s or all services by X per cent This would simulate a reduction in your headcount whilst providing an increased amount of service 2 My workforce is retiring earlier but there are less students qualifying to replace them a This can be modelled by going to the permanent leavers worksheet and reducing the average retirement age to X years old Next go back to the scenarios worksheet and reduce the number of students studying by X per cent This will simulate people retiring earlier and reduce the number of students qualifying 3 There are a large number of over 65s that will be receiving services moving into the area a This can be modelled by increasing the rate at which over 65s are projected by X per cent You could also increase the distribution of services for that age group to say they receive an increased X per cent of all services This will simulate a greater increase in over 65s in the area and the quantity of service that they will require 4 Weare considering a change in eligibility for services but are unsure of what changes to make and how they would affec
18. find your local authority How many social workers are there in total How many in adult services How many in children s services CfWI August 2012 9 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 5 Worksheet 5 Age Profile Description This worksheet provides age and gender profile for the total national registered social worker workforce local authority workforce and student populations This data is provided by the General Social Care Council GSCC How to use it The purple data cells contain the number of social workers or students and the green cells show useful percentages and aggregations The top left table shows the registered workforce the top right shows the local authority workforce the bottom left shows undergraduate students and the bottom right shows postgraduate students If you are modelling at the national level this worksheet will just provide you with information For LA level modelling the top right table uses the national age profile and applies it to the total workforce from the Current Workforce worksheet for the LA that has been loaded If the local age profile differs to the national that has been applied to it the user can enter in their own figures to the blue cells Information already included This worksheet contains the GSCC registered workforce age and gender profile and registered student age and gender profile Things you can change You are able to ch
19. have to be entered locally or is it pre populated o The population projections are pre populated in the model They are on a hidden sheet that can be unhidden if you want to see the data This uses ONS population projections and is at a local level CfWI August 2012 27 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and demand model User Guide Is there a way of incorporating information about the impact of new ways of working aimed at improving efficiency in future o This can be included by adjusting the amount of time taken to undertake activity in a new scenario For example if you think that new ways of working could increase efficiency by 10 per cent you can reduce the amount of time taken for each activity by 10 per cent I understand that when the HPC takes over from the GSCC that overseas workers will practice under the title they hold in their own country How will this affect the model o HPCwill register social workers under the provided framework They will regulate social workers practicing in the UK so if someone isn t on the register then they won t appear in the model If they are on the register then they are eligible to work as social workers Is there a way to adjust the model to reflect the different time elements for social workers delivering reablement services which is quite different from long term care and support o Bydefault the model uses the RAP returns for adults and the CiN census for
20. ing the workforce Maternity leave is calculated using Office for National Statistics ONS birth rates however there are blue cells that let you manually adjust the rate for your area if you think it differs from the rate provided The second table uses user defined percentages to calculate the number of people temporarily leaving the workforce each year Leavers are assumed to leave for one year before returning to the workforce Information already included There is no data on this sheet It is based on user assumptions to provide the number of people using the workforce Things you can change You can edit the maternity rate and the number of people temporarily leaving the workforce each year How to get the best out of it Find out an average figure for people temporarily leaving your workforce and flex the percentages to produce these results Practice Examples 1 How many people are calculated to be leaving for maternity leave each year CfWI August 2012 13 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 8 Worksheet 8 Joiners Description This worksheet models the amount of people joining the workforce including students graduating and finding work and qualified social workers returning to practice How to use it The worksheet has separate tables for undergraduate postgraduate and international students These provide data from the GSCC on student enrolments attrition rates and pass rates Th
21. is data uses the trend function to provide a projection of the number of students qualifying each year The first user defined assumption table blue cells allows you to tailor how many of the qualified students available to work actually obtain a job in a social work role This provides us with a profile of the number of graduates that obtain a job and enter the workforce The next user defined assumption table allows you to tailor the distribution of students by region They are then allocated to a local authority based on the size of the local authority workforce in the region For example if local authority A consists of 23 per cent of the workforce in region Z then they will be allocated 23 per cent of the graduates It is important that if you increase the number of students in your region that you then decrease them in another region If the total student allocation exceeds 100 per cent then you will be increasing the number of students in the system The last user defined assumption table allows you to add in qualified social workers that are returning to practice and therefore joining the workforce Please note this is separate to the temporary leavers Information already included The data already included in this spreadsheet is the GSCC enrolment attrition and pass rates for undergraduate and postgraduate social work degree students International recruits are also included from the GSCC data Things you can change
22. isrepresentation CfWI August 2012 32 www cfwi org uk The Centre for Workforce Intelligence produces quality intelligence to inform better workforce planning that improves people s lives
23. k Reform Board Social Work Task Force Hounslow Borough Council Haringey Borough Council Norfolk County Council Hampshire County Council All of the attendees on our training courses and webinars Registered users that have provided us with feedback to improve the model CfWI August 2012 31 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled DISCLAIMER The Centre for Workforce Intelligence CFWI is an independent agency working on specific projects for the Department of Health and is an operating unit within Mouchel Management Consulting Limited This report is prepared solely for the Department of Health by Mouchel Management Consulting Limited in its role as operator of the CFWI for the purpose identified in the report It may not be used or relied on by any other person or by the Department of Health in relation to any other matters not covered specifically by the scope of this report Mouchel Management Consulting Ltd has exercised reasonable skill care and diligence in the compilation of the report and Mouchel Management Consulting Ltd only liability shall be to the Department of Health and only to the extent that it has failed to exercise reasonable skill care and diligence Any publication or public dissemination of this report including the publication of the report on the CFWI website or otherwise is for information purposes only and cannot be relied upon by any other person In producing the report
24. levant cell For example if a social worker undertakes 12 per cent of all task in high complexity cases then enter that into the relevant blue cell Finally an average travel time per case can be entered The process described above for Task 1 is then repeated for the other task types that are contained in the RAP returns and CiN census There is one exception to be aware of for children s services This is the children looked after table task 6 Instead of entering the amount of hours per case you need to enter the amount of hours spent per case per week as this task does not have a finite end point as the other tasks do The last user defined assumption table is for any other business Here you can enter in the hours per week that each job role spends on any activity that isn t directly attributable to the tasks previously modelled This could include team meetings and supervision for example CfWI August 2012 17 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled The Demand Summary pink table underneath any other business summarises the totals required for each task The last table is a large data purple table that contains the activity levels for each task type held in the RAP returns and CiN census This is stored by region local authority and national totals Information already included For adults services the referrals and assessment of packages of care RAP returns data is used to provide
25. n already included what data is already there and what you will need to edit Things you can change highlights which parts of the worksheet can be edited How to get the best out of it tips on how you might find the worksheet useful Practice examples short tasks to accomplish to aid your use of the model Section 3 contains further information about the data used in the model and some frequently asked questions FAQs SECTION 2 USER GUIDE 2 1 Getting started The social worker supply and demand model is an Excel 2007 workbook containing 25 worksheets The model is also available as an Excel 2003 workbook although this hasn t undergone extensive testing Enable Macros When you open the Excel workbook at the top of the page you will see a pop up message Security Warning Macros have been disabled Select Options and then choose Enable this content to enable macros to ensure you can use all the functions within the model The Key The model is colour coded to help you identify which parts are which This is included in the first worksheet The table below describes the key in more detail Colour Description What this means You can edit any of these cells to User defined assumptions reflect what is happening in your area or nationally These are raw data that has been EE Non user defined inputs entered into the sheet for you These are formula that have been E alculations ente
26. nds you which scenario is currently loaded in the model There is a key to the colour coding used in the model How to use it In Step 1 use the blue drop down menu to select whether you would like to model at the national or local level In Step 2 if you decided to model at the national level in Step 1 then skip to Step 3 otherwise select the local authority LA you would like to model from the yellow drop down box and click the button to load the LA profile The model is only able to display one scenario for one LA at one time By clicking this button it will refresh the data that is stored for the other scenarios and update it with the LA selected A message will inform you when the profile has loaded Step 3 informs you of the current scenario that is loaded in the model You can change the current scenario by going to the Scenarios worksheet 13 Please view the scenarios section in this guide for information on how to change the scenario Please also note that if you load a new LA profile it will default to the baseline scenario Information already included A drop down list of all LAs Things you can change On this page you can change whether you will model at a national or local authority level How to get the best out of it Follow the 3 steps and have a good look at the key so you understand the colour coding used throughout the rest of the model You can always refer back to this if you need a refresh CfWI Augus
27. pare specific information but if you would like to compare workload figures you can view these on the workload pages by viewing the large data purple tables at the bottom of each sheet CfWI August 2012 25 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and 3 SECTION 3 FURTHER INFORMATION Data used in the model The SSDSOO 1 codes 2008 2010 used are as follows Social Workers children 2 30 2 33 2 35 Social Workers other 2 40 2 43 2 50 2 52 2 54 2 55 2 60 2 63 2 70 2 73 2 80 2 83 The NMDS SC job categories used are Social worker Registered manager and First line manager Data issues Some of the data in the purple cells in the model may appear to be inaccurate for your local authority The data we use comes from official sources and we make every effort to ensure that the data is entered into the model as it appears in the source If a figure used has been entered into the model incorrectly then you can make changes to the data If you do need to change some data then please be aware of what you are changing We always make sure that the data used is from the same time period e g all 2010 11 data It is not good practice to compare data of differing years for example a 2010 11 workforce with a 2011 12 activity level To change a data item start by unprotecting the worksheet Each worksheet is protected but without a password This means you can unprotect the workshee
28. pply and demand model User Guide have seven higher level managers you could increase the workforce figure to 12 How do you deactivate a variable e g for which we ve got incomplete info when running a scenario o If you would like remove a variable for example you do not want to include anyone leaving on maternity you can set the percentages to zero Why is there no split on the joiners worksheet between children and adults services o We don t currently have data to show whether graduates go into adults or children s services We split graduates into each service based on the size of the current workforce For example if adults services accounts for 45 per cent of the workforce then they will be allocated 45 per cent of graduates How are senior practitioners counted Are they in social workers or first line managers o This depends on who was recorded under which job role when the NMDS SC was completed Check with whoever recorded your NMDS SC to see where they may have been recorded Can we select a regional level o Themodel does not aggregate to a regional level however if you liaise with the authorities in your region you can work on your own versions of the model and bring the results together to aid regional planning Can we over write the data for 2008 2009 2010 as the data from 55 isn t 100 accurate o This data can be over written You can simply unprotect the worksheet and over write the data What does
29. red to calculate results These are more important formula that have been entered These are Calculations presented as results usually to show a total after a series of calculations or to draw your attention to some more important calculations In the top left corner of each worksheet the title of the page links to the contents page This can help you navigate around the model instead of using the worksheet tabs at the bottom of the page Abc Link to contents page This draws your attention to a local Highlights the name of the local authority authority s information on a worksheet 2 2 The schematic You may find it useful to begin by looking at the social worker supply and demand model schematic The schematic shows each of the key worksheets within the model and how they link together as the model flows from inputs or data and user defined assumptions to outputs or the supply and demand projections The schematic is provided to you as a pdf document when you are sent the model Social Work Supply amp Demand Model Schematic ae eee eC Supply Demand Flow m Age Profile extrapolation National Model Local Model CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 The worksheets 2 3 1 Worksheet 1 CfWI model home page Description This is the home page of the model It provides a 3 step guide to set up the model for modelling at national or local level and it remi
30. s its target in the final year Demand is projected forwards using ONS population projections It is assumed that the current caseloads are based on the population in that year and so any changes in population will have the same effect on demand As demand is calculated as a FTE HC is calculated using the participation rate in that year The Local Model works in the same way as the National Model Information already included There is no data purple cells held on these worksheets These worksheets do link to data tables in previous worksheets Things you can change The only thing that can be changed on these worksheets is the participation rate These worksheets are a summary of all the calculations and assumptions previously entered How to get the best out of it It is a good idea to familiarise yourself with the results that are presented for each year When you then go back to refine your baseline scenario or make changes to a different scenario you can build up knowledge of the sensitivity the amount a change can affect the overall result that changes have CfWI August 2012 19 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 11 Worksheets 13 Scenarios Description The Scenario worksheet allows you to manage your scenarios This will allow you to keep track of which scenarios are currently stored in the model and load or save scenarios You can also make key changes that will help to define a
31. scenario against the baseline How to use it The first question is what is a scenario The model works by modelling a baseline or do nothing scenario This will show your workforce projected forwards as it is now The scenarios allow you to make changes to project differing futures which are then compared against the baseline The scenarios can be as extreme or slight as you like Scenario Management On the worksheet there is a simple 2 step guide at the top of the worksheet to help you manage your scenarios To recall save or delete a scenario you simply enter in the number of the scenario you want to change and click on the relevant rectangular blue button in Step 2 to save recall or delete the scenario A message will inform you when the process has completed The blue user defined table in Step 1 details the current scenarios saved in the model Whenever you save a new scenario you should amend the description so you can keep track of the scenarios in the model Scenario modelling The second part of the Scenarios worksheet has key categories that can be changed to form the basis of a new scenario These categories can be edited to make a change that could occur in the future that can be compared against the baseline scenario to see the affect The categories that can be changed are as follows e Change in population projections o This changes the rate at which population will change from a baseline position of the current O
32. t CfWI August 2012 23 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and 2 3 13 Worksheets 15 24 N Scenario 1 5 and L Scenario 1 5 Description These ten worksheets contain output graphs There are five graphs for the national model N Scenario and five for the local model L Scenario How to use it Each worksheet shows the output graphs for each scenario The first graph for each national and local set of graphs is the baseline scenario and contains three graphs one for adult services one for children s services and one for total social workers The remaining four worksheets are for the other scenarios that have been modelled They have the same three graphs as the baseline and an extra fourth graph that shows the scenario s FTE supply and demand lines compared against the baseline s FTE supply and demand lines Information already included These worksheets are output graphs only and no data is contained on these worksheets Things you can change These worksheets are output graphs only and no user defined assumptions are contained on these worksheets How to get the best out of it Familiarise yourself with the supply and demand graphs to understand how to interpret the results It is important to understand that these outputs do not provide a magic number but provide an indication towards the direction of travel Workforce models should be used as a tool to aid workforce planning and not as
33. t and type in the correct figure and then protect the worksheet It is important to protect the worksheet after a change so that any calculations in pink or green cells aren t accidentally overwritten Frequently asked Questions FAQs e What does the social worker supply and demand model do model allows users to project a workforce into the future to see how many people they are likely to have This is projected against the quantity required to see where there are sufficiencies or deficiencies What if scenarios can be modelled to project differing futures e My system uses excel 2003 not 2007 can still use the model CfWI August 2012 26 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and model has been built in excel 2007 It has not been tested in 2003 but we haven t been informed of any issues from users that use the model in excel 2003 e How long do you think it will take to complete the whole model o This will depend on your current level of understanding of the model and how much of your own data you have available to tailor the assumptions Your first run through the model may take a few hours while you work through sheet by sheet with this guide to understand each page Once you have done this and are up to speed it shouldn t take long at all to make changes or to start afresh e Canlgethelpto start using the model o Thisuser guide should provide you with the information you requir
34. t 2012 7 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 23 2 Worksheet 2 Contents Description This worksheet lists all of the other worksheets within the model and provides descriptions of what is contained within each worksheet The worksheet titles are also linked so you can click to navigate around the document How to use it Each of the coloured cells with the worksheet titles are links You can click on these to navigate to the required worksheet Each worksheet has a coloured title in the top left of the screen which is linked back to the contents Click on these to navigate back to the contents page 2 3 3 Worksheet 3 Sources Description This worksheet lists the sources and the year of the data that is used in the model The data from these sources is already entered into the spreadsheet in the purple coloured boxes This information is protected and should not be changed CfWI August 2012 8 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 34 Worksheet 4 Current Workforce Description This worksheet provides information for each LA in England that has social services responsibility The LAs are grouped by region and listed in alphabetical order Headcount HC and full time equivalent FTE data is provided for adults and children s services How to use it This worksheet provides you with the current workforce You can use the filters at the top of each column header
35. t the workforce a You could use all 4 scenarios in the model to provide a spread of results Scenario 2 could model a reduction in requirement by 2 5 per cent Scenario 3 could model a reduction in requirement by 5 per cent then 7 5 per cent and 10 per cent for scenarios 4 and 5 This would then provide you with a spread of reductions that could occur so you can analyse the sensitivity of any potential changes CfWI August 2012 22 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled 2 3 12 Worksheet 14 Scenario Comparison Description This worksheet holds the national and local model HC and FTE figures for adult services children s services and total social workers There are separate tables for all scenarios How to use it This sheet is an information sheet It holds the key total figures that are used to inform the output graphs Information already included There is no data held on this worksheet it is just an information worksheet of output graphs Things you can change There are no user defined blue cells on this worksheet How to get the best out of it Some users prefer to use numbers instead of graphs or may want to copy the figures into a new worksheet This is the best place to find all of the output figures that you may want to use Each sheet is protected so if you want to copy data from this worksheet look at the Data Problems section in Section 3 for guidance on how to unprotect the workshee
36. to easily view your LA in isolation or you can compare your LA by selecting multiple LAs excel 2003 provides limited functionality or by selecting a region and you could filter further by selecting a particular job role There is also a total at the bottom that provides the national level data There are subtotals below the table that show the totals when you have filtered to show a specific selection Please be aware that some cells may show as zero This is because there wasn t any data provided The cells have been highlighted blue so the user can enter in a figure Information already included Workforce data is held for four years For years 2008 2010 the Personal Social Services Staff of Social Services Departments England SSDSOO1 data is used For 2011 National Minimum Data Set for Social Care NMDS SC data is used and will continue to be used in future data updates Please note that for the first three years where SSDSOO data is used there is not a breakdown by job role only a total for adults and children s services Things you can change Where there has been no data provided from the data source the cells have been highlighted blue to indicate that the user can enter in their own data How to get the best out of it Familiarise yourself with the sorting and filtering functions in Excel so you are easily able to find the data you are looking for in the large table Practice Examples 1 Using the current workforce sheet
37. ts or children s services The demand pages use the reported activity level from the adult services referral assessment and packages of care RAP return and the children in need CiN census The calculation works by multiplying the activity by the amount of time taken to complete it and dividing by a FTE This provides the total amount of social workers required to undertake the activity It is assumed that the activity levels are for the current population so future demand is projected using ONS population projections The data in the sheets has been included to give users a guide as to how to complete the tables The rationale behind these figures can be found in the adults children s social worker task activity and time allocation sheets which we sent to users with the model The information in these documents is based on time and task details provided by local authorities involved in developing the model It is important that you include the data for your own local authority on this page as the information given is for guidance purposes only These task activity and time documents will help you understand the criteria used to allocate the proportion of activity and time to high medium and low complexity These documents also provide you with the information to show what a high medium or low complexity case looks like the tasks undertaken to complete the case and the baseline timings that were arrived at that are entered into th
38. worksheet Once you have made the changes click on the blue rectangular save scenario button if you do not click save then the results will not be displayed on the output graphs You will also need to remember to adjust the title in the blue table to reflect the scenario you have modelled Please note that where an increase or a decrease is required use the drop down menus to select an increase or a decrease and then enter in a positive percentage Once the scenario has saved you can then view the changed scenario outputs on the National and Local model worksheets and in the output graphs The changes can also be viewed in the output graphs against the baseline scenario so you can see if requirements increased by 5 per cent and the average retirement increased by two years what workforce would be required Information already included There is no data included on this worksheet as it is all user assumptions Things you can change You can change any of the blue cells to change your scenarios CfWI August 2012 21 CENTRE FOR WORKFORCE INTELLIGENCE CfWI Supply and dled How to get the best out of it Think of some realistic scenarios that may affect your local area and model them You could always make a copy of the workbook once you have modelled your baseline and model one scenario with varying sensitivities i e 5 per cent 10 per cent Practice Examples In this section there are a number of case study exampl
Download Pdf Manuals
Related Search