1 / 31

Making the Most of Your Composite Computational Spreadsheet: Tools from California

Learn how to make the most of your composite computational spreadsheet with tools from California Department of Social Services and UC Berkeley.

roberthporter
Download Presentation

Making the Most of Your Composite Computational Spreadsheet: Tools from California

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Making the Most of Your Composite Computational Spreadsheet: Tools from California Linda Hockman Debbie Williams California Department of Social Services William Dawson Barbara Needell Daniel Webster Center for Social Services Research, UC Berkeley The Performance Indicators Project is a collaboration of the California Department of Social Services and the University of California at Berkeley, and is supported by the California Department of Social Services and the Stuart Foundation

  2. http://www.nrccwdt.org/resources/cfsr/data_tools.html

  3. S1.1 (%) To calculate county-level performance for a given year, the inputs are county-level performance for the 17 federal measures Safety S2.1 (%) The denominator provides the count of children in care during the year (at the county level) C1.1 (%) C1.2 (m) C1.3 (%) C1.4 (%) Component A Component B Composite 1: Reunification C2.1 (%) C2.2 (m) C2.3 (%) C2.4 (%) C2.5 (%) Component A Component B Component C Composite 2: Adoption Permanency Composite 3: Long-Term C3.1 (%) C3.2 (%) C3.3 (%) Component A Component B Composite 4: Stability C4.1 (%) C4.2 (%) C4.3 (%)

  4. Composite Calculations • In our presentation of state and county composite estimates, we have automated the process to allow us to calculate estimates across all available time periods and to update and extend the estimates as new data become available each quarter. • Based on the Excel tool, there are six steps in the calculation of the composites at the state level. • Calculations are made at the county level to determine the state composite scores. • The screen captures use C1 Alameda County as an example. • Cells with dashed borders contain calculation under discussion.

  5. Steps… • Standardize performance scores to produce 'computed standardized scores‘

  6. Standardize performance scores to produce 'computed standardized scores'

  7. Steps… • Standardize performance scores to produce 'computed standardized scores‘ • Sum weighted 'computed standardized scores' to produce 'computed component derived scores'

  8. (2) Sum weighted 'computed standardized scores' to produce 'computed component derived scores'

  9. Steps… • Standardize performance scores to produce 'computed standardized scores‘ • Sum weighted 'computed standardized scores' to produce 'computed component derived scores' • Average 'computed component derived scores' to produce 'unweighted county composite scores‘

  10. (3) Average 'computed component derived scores' to produce 'unweighted county composite scores'

  11. Steps… • Standardize performance scores to produce 'computed standardized scores‘ • Sum weighted 'computed standardized scores' to produce 'computed component derived scores' • Average 'computed component derived scores' to produce 'unweighted county composite scores‘ • STATE ONLY: Multiply 'unweighted county composite scores' by 'children served by county' to produce 'weighted county composite scores' (for all ‘included’ counties)

  12. STATE ONLY: Multiply 'unweighted county composite scores' by 'children served by county' to produce 'weighted county composite scores' (for all ‘included’ counties)For the estimation of composite scores at the county level, we skip steps (4) and (5).

  13. Steps… • Standardize performance scores to produce 'computed standardized scores‘ • Sum weighted 'computed standardized scores' to produce 'computed component derived scores' • Average 'computed component derived scores' to produce 'unweighted county composite scores‘ • STATE ONLY: Multiply 'unweighted county composite scores' by 'children served by county' to produce 'weighted county composite scores' (for all ‘included’ counties) • STATE ONLY: Divide the sum of 'weighted county composite scores' by the sum of 'children served by counties' to produce 'computed state composite score‘

  14. (5) STATE ONLY: Divide the sum of 'weighted county composite scores' by the sum of 'children served by counties' to produce 'computed state composite score'

  15. Steps… • Standardize performance scores to produce 'computed standardized scores‘ • Sum weighted 'computed standardized scores' to produce 'computed component derived scores' • Average 'computed component derived scores' to produce 'unweighted county composite scores‘ • STATE ONLY: Multiply 'unweighted county composite scores' by 'children served by county' to produce 'weighted county composite scores' (for all ‘included’ counties) • STATE ONLY: Divide the sum of 'weighted county composite scores' by the sum of 'children served by counties' to produce 'computed state composite score‘ (6)(a) STATE: Scale 'computed state composite score' to produce 'computed scaled state composite score'.

  16. (6)(a) STATE: Scale 'computed state composite score' to produce 'computed scaled state composite score'.

  17. Steps… • Standardize performance scores to produce 'computed standardized scores‘ • Sum weighted 'computed standardized scores' to produce 'computed component derived scores' • Average 'computed component derived scores' to produce 'unweighted county composite scores‘ • STATE ONLY: Multiply 'unweighted county composite scores' by 'children served by county' to produce 'weighted county composite scores' (for all ‘included’ counties) • STATE ONLY: Divide the sum of 'weighted county composite scores' by the sum of 'children served by counties' to produce 'computed state composite score‘ (6)(a) STATE: Scale 'computed state composite score' to produce 'computed scaled state composite score'. (6)(b) COUNTY: For the estimation of composite scores at the county level— having skipped steps (4) and (5) —we scale the 'unweighted county composite scores'. This calculation is added to the federal spreadsheet.

  18. (6)(b) COUNTY: For the estimation of composite scores at the county level—having skipped steps (4) and (5) —we scale the 'unweighted county composite scores'. This calculation is added to the federal spreadsheet.

  19. (6)(b) COUNTY (continued): For small counties, this approach sometimes produces composite scores <50 or >150; we present these as 50 and 150 respectively. If performance is not available (0 denominator) for one or more measures for a county, a composite score is not calculated (“N.A.”).

  20. Measure Contributions to Composites Reentry Following Reunification (Exit Cohort) Reunification Within 12 Months (Entry Cohort) Median Time To Reunification (Exit Cohort) Reunification Within 12 Months (Exit Cohort) Note: Measures may not sum to exactly 100% due to rounding.

  21. Measure Contributions to Composites Adoption Within 12 Months (Legally Free) Legally Free Within 6 Months (17 Months In Care) Adoption Within 12 Months (17 Months In Care) Median Time To Adoption (Exit Cohort) Adoption Within 24 Months (Exit Cohort) Note: Measures may not sum to exactly 100% due to rounding.

  22. Measure Contributions to Composites In Care 3 Years Or Longer (Emancipated/Age 18) Exits to Permanency (Legally Free At Exit) Exits to Permanency (24 Months In Care) Note: Measures may not sum to exactly 100% due to rounding.

  23. Measure Contributions to Composites Placement Stability (At Least 24 Months In Care) Placement Stability (12 To 24 Months In Care) Placement Stability (8 Days To 12 Months In Care) Note: Measures may not sum to exactly 100% due to rounding.

  24. Measure Contributions to Composites Note: Measures may not sum to exactly 100% due to rounding.

  25. Additional Documentation County Composite Formula Measure Contributions Estimate of Statewide Composites

  26. Composite Viewer (Alameda)

  27. Composite Planner (Alameda)

  28. Barbara Needell bneedell@berkeley.edu 510-290-6334 CSSR.BERKELEY.EDU/UCB_CHILDWELFARE Needell, B., Webster, D., Armijo, M., Lee, S., Dawson, W., Magruder, J., Exel, M., Glasser, T., Williams, D., Zimmerman, K., Simon, V., Putnam-Hornstein, E., Frerer, K., Cuccaro-Alamin, S., Winn, A., Lou, C., & Peng, C. (2009). Child Welfare Services Reports for California. Retrieved April 1, 2009, from University of California at Berkeley Center for Social Services Research website. URL: <http://cssr.berkeley.edu/ucb_childwelfare> Slides developed by Emily Putnam-Hornstein

More Related