This article is also available in PDF File.

The Child and Family Services Reviews (CFSRs) are well underway, with the first 17 States reviewed in 2001. States that are found not in substantial conformity on the national standards in the areas of safety, permanency, and well being must establish plans to meet these standards, as well as outcome measures to measure their progress. These are outlined in the Program Improvement Plan (PIP) the States develop and submit for approval to the Children's Bureau.

Outcome measures may be monitored in a variety of ways, depending on State practice and preference. As the measurement reports can only be as good as the data entered, linking the outcomes data and field practice is critical for the staff entering the data. This is often accomplished through staff training efforts, but may also be achieved through the use of reports created with Excel PivotTables. PivotTable reports are interactive and in that sense are similar to web pages with hyperlinks, allowing data in one worksheet to link to another worksheet displaying a filtered or more detailed set of data. The PivotTables allow "drill downs" to more detailed information from a large summary of data, allow cross-tabulations to be performed, and can total and subtotal data.

Pros and Cons of PivotTables
PivotTables have several key features that make them useful. First, Microsoft Office Suite products are ubiquitous, making them accessible to nearly everyone. Since PivotTables are a standard feature of Excel, it is not necessary to make additional investments in costly technology that may become quickly outdated. In addition, since most people are comfortable in examining Excel tables with "point and click" technology, training on reading PivotTable reports is not necessary. PivotTables also allow unlimited drill downs and allow different summaries of source data by rotating rows and columns. PivotTable reports can be created from Excel data, external databases or other PivotTable reports. Finally, they are useful in summarizing large amounts of data and for comparing characteristics of figures within the dataset. PivotTable size is generally only limited by the user's computer's memory.

PivotTable reports are limited, however, in that all data must first be manually manipulated. Someone must consciously decide what data will appear in the first report, how the drill downs will work, how many levels a user can drill down, and then set the PivotTables up that way. There is a limit (32,768) to the product of the number of items in all column fields. Similarly, the product of the number of items in all row fields is limited to approximately 2.1 billion items. Also, there is a limit of 8,000 unique items per row field, column field, or page field. A field will not be added to the PivotTable if the field to be added exceeds this limit of 8,000.

Vermont's Experience
Vermont was one of the first 17 States to undergo a CFS Review (May 2001) and its PIP was approved in March 2002. The PIP focused on foster and adoptive parent recruitment, placement stability, timely permanency, and assessment and documentation. Vermont recognized the importance of linking outcomes to practice early in the CFSR process (starting with the State's self-assessment). In order to enable field staff to easily see the connection between the families served and the data that are reported, a unique way of measuring progress towards the goals set forth in the PIP was developed.

Although Vermont had developed annual outcome reports on the State and district level over five years ago, they were not believed to be widely utilized. Therefore, when Vermont was scheduled for the CFSR, the decision was made to form an Outcomes Committee to assist with the State self-assessment. The process used in the self-assessment turned out to be one that could be carried through to the development of program improvement measures as well. The Committee identified the following requirements:

Data must be displayed:
• By district
  
• In number and percentage formats
  
Reports must:

• Be consistent with federal CFSR outcome measures
  
• Contain case-specific data
  
• Assist in identification of what needs to change
  
• Be accessible to and usable for everyone

The result of this process was the creation of a series of reports called "Outcomes at a Glance." The reports are produced quarterly for the Outcomes Committee and district managers, who then share the reports with their staff. Vermont decided to use Microsoft's Excel PivotTable and PivotChart features for implementing this process. The PivotTables allow users to examine detailed case data behind the higher-level data.

Vermont uses two levels of data for its reports; the first level being the PivotTable data and the case level data (although a larger State might choose to use more levels). By clicking on any of the numbers in the table (see Figure 1 below), the detailed information on the cases appears in a separate Excel worksheet (see Figure 2 below). This information includes names, family ID, DOB, first report date, first substantiation date, age, relative information, etc. This allows a very direct connection between the data targeted for improvement and the actual families served. For example, one of Vermont's outcomes indicates "at least 93.9% of children are safe" (in this case, the national standard was reversed so that the goal is to strive for 94% safety rather than striving for 6% unsafe) and the chart displayed shows 5 children in district "T" are safe. By clicking on the cell for this district's count of safe children, another worksheet will display the specific cases that are in this category.

Figure 1 is an Excel PivotTable screenshot showing Safety Outcome A: Safety from Repeat Maltreatment. Graphic contains: Indicator, Measure, National Standard and Vermont Goal towards meeting the standard. Numbers and percentages of safe and
not safe are displayed by district.

Figure 2 is an Excel PivotTable screenshot detail that shows case information
behind the figure displayed in Figure 1 for District T, Safe.

The Outcomes at a Glance Reports are created from text files extracted from the State child welfare system. There are six pages to each report; one outcome per page, with a table and chart of the data. Each outcome is listed, using the federal language title, description, indicator, measure, and national standard. As in the example in the paragraph above, the federal language was slightly modified to correspond to State practice. The goals established by Vermont are also listed and whether the PIP goal has been met by the State. The table then lists each district's data in raw numbers and as percentages. The charts display bar graphs that compare the data for each quarter. This allows comparison of data, trend analysis, and provides a baseline for comparison of future data.

The Outcomes at a Glance Reports allow for analysis of the data from the State level to the individual. According to Cindy Walcott, Vermont Division of Social and Rehabilitative Services, outcome discussions are usually focused on by managers, but the use of PivotTables/Outcomes at a Glance reports have been more inclusive. They allow high level overviews as well as detailed examinations. "It allows people to make the connection between data and who the kids are," says Ms. Walcott.

• What is it you do here to support good outcomes?
  
• What do you feel you need to do here to improve outcomes?
  
• What does the State need to do to support good outcomes?

Each district receives the full The Outcomes at a Glance Report, examines its own data to verify whether the data appears accurate based on what is known to occur in the field, and responds to the three questions above. Additionally, because districts have access to each others' data, they may also do comparative evaluations. Districts may see other districts that are having similar problems and put together ideas to solve a common problem. Conversely, a district may see that another district is doing very well in a particular area and may consult to see how such results are being achieved. "We are trying to make this a peer consultation model," says Ms. Walcott.

The data on the outcomes measures help the State staff understand what is happening in the State and assist in highlighting issues in need of attention. The data also assists in determining whether a plan in having an impact. Each district is required to develop an annual plan, which goes hand in hand with the State plan, and is then coordinated with the PIP.

Vermont is still considering ways to optimize use of the reports in daily practice, however, the PivotTables reports are bringing everyone together, as the managers meet every month to review and discuss them. "We make ourselves available for technical assistance," [in the central office] says Ms. Walcott. Vermont has been using the PivotTables since July 2001 and the reports continue to evolve as necessary, but the premise of using the PivotTables to monitor PIP outcome measurements has exceeded the State expectations.

For more information, contact:
Cindy Walcott
Vermont Division of Social and Rehabilitative Services
103 South Main Street
Waterbury, VT 05671-2104
E-mail: cwalcott@srs.State.vt.us

This document was prepared as a supplement to the material presented in a session at the April 2002 Making IT Work: Using Data for Program Improvement in Arlington, VA. We hope that it will serve to stimulate an exchange of ideas and information among States and between systems and program staff. Your feedback is important to us. If you have any additional information on the topic presented in this sheet, or if you have any comments or suggestions regarding its presentation or content, please contact Valerie Sayd of Xtria at (703) 821-3090 x247 or vsayd@xtria.com.