{"text_format":"2","workbook":"xlsx","n_sheets":3,"sheets":["Imapct Map","ASBCrime calculations ","Sheet3"],"not_rendered":{"charts":0,"images":0,"macros":0,"external_links":1,"defined_names":3,"comments":1,"pivot_tables":0},"unopened":[],"bounds":{"document_bytes":16777216,"sheet_cells":100000,"run_cost":"one","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":"Imapct Map","index":0,"n_rows":29,"n_cols":27,"rows_kept":29,"cut":false,"hidden":false,"merged":[[6,0,7,0],[6,1,7,1],[6,2,7,2],[6,3,7,3],[6,4,7,4],[0,0,0,7],[4,13,4,14],[5,2,5,3],[5,5,5,12],[5,19,5,23],[6,22,6,23],[10,5,13,5],[6,13,7,13],[6,14,7,14],[6,15,7,15],[6,16,7,16],[6,17,7,17],[6,19,6,20]]}
["Social Return on Investment - The Impact Map"]
[]
[" SROI Network","Spreadsheet for developing SROI analysis.  Only to by used as part of SROI Network training. The spreadsheet does not include any guidance "]
[]
["Stage 1",null,"Stage 2",null,null,null,"Stage 3",{"$nulls":6},"Stage 4",{"$nulls":5},"Stage 5"]
["Stakeholders","Intended/unintended changes","Inputs",null,"Outputs","The Outcomes (what changes)",{"$nulls":7},"Deadweight      %","Displacement      %","Attribution      %","Drop off         %","Impact",null,"    Calculating Social Return"]
["Who will we have an effect on?                          Who will have an effect on us?","What do we think will change for them?","What will they invest?","Value £","Summary of activity in numbers","Description","Indicator","Source","Quantity","Duration","Financial Proxy","Value £","Source","What would have happened without the activity?","What activity would we displace?","Who else would contribute to  the change?","Will the outcome drop off in future years?","Quantity times financial proxy, less deadweight, displacement and attribution",null,"  Discount rate",null,3.5000000000000003E-2]
[{"$nulls":5},"what changes? (based on how stakeholders would describe the change)","How would we measure it?","Where did we get the information from?","How much change will there be?","How long will it last?","What proxy did we use to value the change?","What is the value of the change?","Where did we get the information from?",{"$nulls":6},"Year 1   (after activity)","Year 2","Year 3","Year 4","Year 5"]
["Young People","Supported to make transitions into education, work or training",null,null,"62 mentees seen over one year","Young people are supported to progress to further education or training","Number of YP progressing into FE","Twenty Twenty  database ",11,1,"Average of wage difference with level 2 qual",1456,"Department for Children, Schools and Families and estimate and Wage scare of being NEET http://www.wikivois.org/index.php?title=The_avoidance_of_a_wage_scar_in_later_life_because_a_young_person_is_employed_rather_than_NEET_aged_16-19",0.1,0,0.71,0.1,{"$f":"I9*L9*(1-N9)*(1-O9)*(1-P9)","v":4180.1760000000004},null,{"$f":"IF(J9>=1,R9,0)","v":4180.1760000000004},{"$f":"IF(J9>1,T9,0)*(1-Q9)","v":0},{"$f":"IF(J9>2,U9,0)*(1-Q9)","v":0},{"$f":"IF(J9>3,V9,0)*(1-Q9)","v":0},{"$f":"IF(J9>4,W9,0)*(1-Q9)","v":0}]
[null,"Supported to prevent or reduce ASB",null,null,"48 mentors matched","Young people are supported to gain employment","Number of YP progressing into employment","Twenty Twenty  database",6,1,"The difference between the net increase of disposable income, including tax credit in employment compared to benefits based on working full time with an average salary of £15,921 and being on benefits £9040",6881," Reducing dependency, increasing opportunity, options for the future of welfare to work by David Freud (DWP) and tax calculations from HM Revenue and Customs",0.1,0.5,0.71,0.1,{"$f":"+I10*L10*(1-N10)*(1-O10)*(1-P10)","v":5387.8230000000012},null,5387.8230000000012,0,0,0,0]
[{"$nulls":5},"Young people at risk of ASB who are  able to engage with a mentor, feel positive about themselves, increase aspirations and independently deal with difficult situations and stay strong","1 step progress (out of 4)","Twenty Twenty  database and manager and SOUL record",13,5,"As a proportion of making more responsible decisions/Less likelihood of contact with criminal justice system",1475.5,"The economic and social return of action for children’s family intervention team/5+ project Caerphilly, 2009, nef",0.1,0,0.71,0.1,5006.3715000000011,null,5006.3715000000011,4505.7343500000015,4055.1609150000013,3649.6448235000012,3284.6803411500009]
[{"$nulls":6},"2 step progress (out of 4)","Twenty Twenty  database and manager and SOUL record",9,5,"As a proportion of making more responsible decisions/Less likelihood of contact with criminal justice system",2951,"The economic and social return of action for children’s family intervention team/5+ project Caerphilly, 2009, nef",0.1,0,0.71,0.1,6931.8990000000013,null,6931.8990000000013,6238.7091000000009,5614.8381900000013,5053.3543710000013,4548.0189339000017]
[{"$nulls":6},"3 step progress (out of 4)","Twenty Twenty  database and manager and SOUL record",20,5,"As a proportion of making more responsible decisions/Less likelihood of contact with criminal justice system",4426.5,"The economic and social return of action for children’s family intervention team/5+ project Caerphilly, 2009, nef",0.1,0,0.71,0.1,23106.33,null,23106.33,20795.697000000004,18716.127300000004,16844.514570000003,15160.063113000004]
[{"$nulls":6},"4 step progress (out of 4)","Twenty Twenty  database and manager and SOUL record",1,5,"As a proportion of making more responsible decisions/Less likelihood of contact with criminal justice system",5902,"The economic and social return of action for children’s family intervention team/5+ project Caerphilly, 2009, nef",0.1,0,0.71,0.1,1540.4220000000003,null,1540.4220000000003,1386.3798000000002,1247.7418200000002,1122.9676380000003,1010.6708742000003]
[{"$nulls":5},"Relationships break down and Young people risk feeling let down","1 step backwards","Twenty Twenty  database and manager and SOUL record",2,1,"As a proportion of making more responsible decisions/Less likelihood of contact with criminal justice system",-1475,"The economic and social return of action for children’s family intervention team/5+ project Caerphilly, 2009, nef",0.1,0,0.71,0.1,{"$f":"+I15*L15*(1-N15)*(1-O15)*(1-P15)","v":-769.95},null,{"$f":"IF(J15>=1,R15,0)","v":-769.95},{"$f":"IF(J15>1,T15,0)*(1-Q15)","v":0},{"$f":"IF(J15>2,U15,0)*(1-Q15)","v":0},{"$f":"IF(J15>3,V15,0)*(1-Q15)","v":0},{"$f":"IF(J15>4,W15,0)*(1-Q15)","v":0}]
[{"$nulls":5},"Young people are able to improve their relationships with family and friends","% of young people who improved relationships (friends or family)","Interviews with Mentees",37,5,"value pf relationships = Annual household spending on recreation & culture",2985,"The economic and social return of action for children’s family intervention team/5+ project Caerphilly, 2009, nef",0.1,0,0.71,0.1,{"$f":"+I16*L16*(1-N16)*(1-O16)*(1-P16)","v":28826.145000000004},null,{"$f":"IF(J16>=1,R16,0)","v":28826.145000000004},{"$f":"IF(J16>1,T16,0)*(1-Q16)","v":25943.530500000004},{"$f":"IF(J16>2,U16,0)*(1-Q16)","v":23349.177450000003},{"$f":"IF(J16>3,V16,0)*(1-Q16)","v":21014.259705000004},{"$f":"IF(J16>4,W16,0)*(1-Q16)","v":18912.833734500004},{"$f":"SUM(T9:X16)","v":276663.32102925004},336028.13258062338,{"$f":"SUM(Y16)/Z16*100","v":82.333380513272971}]
["Mentees",null,"3640 hours times £6 an hour",21840,null,"Mentees are able to gain training and experiences to improve their future job opportunities","Approx % of mentees who were not retired","Interviews and twenty twenty data on mentors",43,1,"The cost of an Employability course ",400,"TABS Training",0.1,0,0.71,0.1,4489.2000000000007,null,{"$f":"IF(J17>=1,R17,0)","v":4489.2000000000007},{"$f":"IF(J17>1,T17,0)*(1-Q17)","v":0},{"$f":"IF(J17>2,U17,0)*(1-Q17)","v":0},{"$f":"IF(J17>3,V17,0)*(1-Q17)","v":0},{"$f":"IF(J17>4,W17,0)*(1-Q17)","v":0},null,336029.13258062297,{"$f":"SUM(Y17)/Z17*100","v":0}]
[{"$nulls":5},"Mentees feel an increased sense of purpose ","Total number of mentees","Interviews and twenty twenty data on mentors",48,1,"Life coach (minimum for 10 weekly sessions)",500,"Website search",0.1,0,0.71,0.1,6264.0000000000009,null,6264.0000000000009,0,0,0,0,{"$f":"SUM(T17:X18)","v":10753.2},336030.13258062297,3.2000701596068946]
["Connexions/DWP",{"$nulls":4},"Reduced number of NEET young people","Number of YP progressing into EET","Twenty Twenty database",17,1,"Cost of supporting a young person who is NEET",5760,"Audit Commission/York University (see report)",0.1,0,0.71,0.1,25557.120000000003,null,25557.120000000003,0,0,0,0,{"$f":"SUM(T19:X19)","v":25557.120000000003},336031.13258062297,7.6055810078455037]
["Police",{"$nulls":4},"Reduced cases of crime/ASB","Number of young people offending less than expected","Twenty Twenty database",42,5,"Average cost saving from reduced crime/ASB per person",968,"The Economic and Social Costs of Anti-Social Behaviour: a review' (London School of Economics and Political Science, 2003), p.43 ",0,0,0.71,0.1,11790.240000000002,null,{"$f":"IF(J20>=1,R20,0)","v":11790.240000000002},{"$f":"IF(J20>1,T20,0)*(1-Q20)","v":10611.216000000002},{"$f":"IF(J20>2,U20,0)*(1-Q20)","v":9550.0944000000018},{"$f":"IF(J20>3,V20,0)*(1-Q20)","v":8595.084960000002},{"$f":"IF(J20>4,W20,0)*(1-Q20)","v":7735.5764640000016},{"$f":"SUM(T20:X20)","v":48282.211824000013},336032.13258062297,14.368331817915013]
["Family members/parents",{"$nulls":4},"Improved relationships","% of young people who improved relationships with parents","Interviews with mentees",12,5,"Family mediation couselling (£35 an hour over 12 weeks)",420,"Scotia\ndirect from\nRelationship\nScotlandhttp://www.employabilityinscotland.com/sites/default/files/articles/5677/Scotia%2520Clubhouse%2520SROI%2520Report%2520-%2520February%25202011_2106_2979.pdf",0.1,0,0.71,0.1,{"$f":"+I21*L21*(1-N21)*(1-O21)*(1-P21)","v":1315.44},null,{"$f":"IF(J21>=1,R21,0)","v":1315.44},{"$f":"IF(J21>1,T21,0)*(1-Q21)","v":1183.8960000000002},{"$f":"IF(J21>2,U21,0)*(1-Q21)","v":1065.5064000000002},{"$f":"IF(J21>3,V21,0)*(1-Q21)","v":958.95576000000017},{"$f":"IF(J21>4,W21,0)*(1-Q21)","v":863.06018400000016},{"$f":"SUM(T21:X21)","v":5386.8583440000002},336033.13258062297,1.6030735727250212]
["Community Safety Funding",null,"Funding",25282]
["Other Funding",null,null,20000]
["Total",null,null,{"$f":"SUM(D17:D23)","v":67122},{"$nulls":13},{"$f":"SUM(R9:R19)","v":110519.53650000002},null,{"$f":"SUM(T9:T22)","v":123625.21650000002},{"$f":"SUM(U9:U22)","v":70665.162750000003},{"$f":"SUM(V9:V22)","v":63598.646475000009},{"$f":"SUM(W9:W22)","v":57238.78182750001},{"$f":"SUM(X9:X22)","v":51514.903644750011}]
[]
[{"$nulls":14},"Present value of each year (after discounting)",{"$nulls":4},{"$f":"+T24/(1+V7)","v":119444.65362318844},{"$f":"+U24/((1+V7)*(1+V7))","v":65966.685570258356},{"$f":"+V24/((1+V7)*(1+V7)*(1+V7))","v":57362.335278485545},{"$f":"+W24/((1+V7)*(1+V7)*(1+V7)*(1+V7))","v":49880.291546509172},{"$f":"+X24/((1+V7)*(1+V7)*(1+V7)*(1+V7)*(1+V7))","v":43374.166562181897}]
[{"$nulls":14},"Total Present Value (PV)",{"$nulls":8},{"$f":"NPV(V7,T24:X24)","v":336028.13258062338}]
[{"$nulls":14},"Net Present Value (PV minus the investment)",{"$nulls":8},{"$f":"+X27-D24","v":268906.13258062338}]
[{"$nulls":14},"Social Return £ per £",{"$nulls":8},{"$f":"+X27/D24","v":5.0062294416230655}]

{"sheet":"ASBCrime calculations ","index":1,"n_rows":42,"n_cols":11,"rows_kept":42,"cut":false,"hidden":false,"merged":[]}
[null,"1. Previous Crime/ASB costs"]
[null,null,null,"Single ASB warning","Multiple ASB warnings","Robbery/burglary/theft/shopt lifting","Drug offenses","Arson","Known to JAG","Assault","ASB convictions"]
[null,null,"No of YP",2,15,6,2,1,2,1,1]
[null,null,"Number of incidents per person",1,3,1,1,1,1,1,1]
[null,null,"Total number of incidents",{"$f":"SUM(D3)*D4","v":2},{"$f":"SUM(E3)*E4","v":45},6,2,1,2,1,1]
[null,"Assumption: Multiple ASB warnings = 3 warnings"]
[]
[null,null,"Crime/ASB type","Number of incidents","Crime/ASB type in cost database","Unit","Cost","Total cost"]
[null,null,"ASB no further action",47,"Anti-social behaviour - no further action taken (cost of dealing with incident, simple reporting only)","Per incident",44.061650943833683,{"$f":"SUM(D9)*G9","v":2070.8975943601831}]
[null,null,"ASB and further action",1,"Anti-social behaviour - further action necessary (cost of dealing with incident)","Per incident",629.45215634048122,{"$f":"SUM(D10)*G10","v":629.45215634048122}]
[null,null,"Arrests",9,"Arrest","Per event",2240.5182151076415,20164.663935968772]
[null,null,"Arson",1,"Domestic fires","Per incident",3733.7581977151904,3733.7581977151904]
[{"$nulls":7},{"$f":"SUM(H9:H12)","v":26598.771884384627}]
[]
[null,null,"Total cost of Crime/ASB",26598.771884384627]
[null,null,"Number of YP involved in crime/ASB",27]
[null,null,"Cost per YP",{"$f":"SUM(D15)/D16","v":985.13969942165284}]
[null,"Assumption: ASB warning = cost of ASB no further action"]
[]
[null,"2. Predicted Future Crime/ASB costs"]
[null,null,"Risk type","Number of YP","Chance of offending","Evidence","Expected number","Cost per person","Total expected costs"]
[null,null,"YP with past offenses",27,1,"n/a",{"$f":"SUM(D22)*E22","v":27},985.13969942165284,{"$f":"SUM(G22)*H22","v":26598.771884384627}]
[null,null,"Additional YP with family offenders (in prison or convcited)",16,0.6,"Over 60% of boys whose fathers had been convicted were eventually convicted themselves (Farrington et al, 1996).",{"$f":"SUM(D23)*E23","v":9.6},985.13969942165284,{"$f":"SUM(G23)*H23","v":9457.3411144478669}]
[null,null,"Additional YP disengaged from eduction",11,0.45,"In the Cambridge study, between 44% and 48% of secondary school truants were offenders (West, 1982)., ",4.95,985.13969942165284,4876.4415121371821]
[{"$nulls":6},42,985.13969942165284,{"$f":"SUM(G25)*H25","v":41375.867375709422}]
[null,"Assumption: YP with past ASB/crime will continue same level of ASB/crime"]
[]
[null,"3. Actual cost of crime/ASB during Twenty Twenty"]
[{"$nulls":5},"No of incidents","Total costs"]
[null,null,"Anti-social behaviour - further action necessary (cost of dealing with incident)","Per incident",629.45215634048122,1,{"$f":"SUM(E30)*F30","v":629.45215634048122}]
[null,null,"Anti-social behaviour - no further action taken (cost of dealing with incident, simple reporting only)","Per incident",44.061650943833683,2,{"$f":"SUM(E31)*F31","v":88.123301887667367}]
[{"$nulls":6},{"$f":"SUM(G29:G31)","v":717.57545822814859}]
[]
[null,"4. Cost saving in Year 1 "]
[]
[null,null,"Total expected costs",41375.867375709422]
[null,null,"Actual costs",717.57545822814859]
[null,null,"Total Cost saving *",{"$f":"SUM(D36)-D37","v":40658.291917481271}]
[null,null,"Number of YP expected to be involved in crime/ASB",42]
[null,null,"Cost saving per person",{"$f":"SUM(D38)/D39","v":968.05456946383981}]
[]
[null,null,"* before discounting for attribution"]

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