Root Cause Analysis

Graph Tutorial

Created by:

 

The Office of Instruction & Accountability

Chicopee Public Schools

 

James W. Devine, Assistant Superintendent

 

Curriculum Support Staff

 

Barbara Merchant          Barbara Sheehan

Denise Ruszala               January Wilson

 

January 2004





Root Cause Analysis Graph Tutorial

 

Welcome to the RCA Graph Tutorial.  The graphs that this tutorial will enable you to create using you MCAS data will prove invaluable to you and you staff in analyzing data to determine strengths and weaknesses.  One important feature of these graphs is that you can very quickly compare various subgroups of students, i.e. SPED, LEP to school, district and state subgroups.

 

  1. Open Microsoft Excel

 

  1. Beginning in Row 1, label cells as follows: Item #; Type; Category; State; District; School Name (or subgroup)

 

                         

 

  1. Highlight all these labeled cells by dragging cursor across.  Go to Format,      

highlight Column, then select Autofit Selection and click OK.  This will adjust the columns to size.

 

  1. To automatically number cells, start in the row labeled Item #, and label each cell, 1, 2, 3.  Highlight cell 1 and drag to cell 3. Move cursor to the bottom right corner of cell 3 until you see the symbol +.  Click, hold and drag cursor down the row of cells until you reach the desired number of items (usually around 39 – 42). 

     

                                            

 

                                                       

  1. Using your MCAS data item analysis sheet, label the type of question, i.e. MC for multiple choice, OR for open response or SA for short answer.

 

  1. Using your MCAS data item analysis sheet, label the reporting category, i.e. LT for literature, LA for language, ME for Measurement, NS for number sense, etc.

 

 

                       

 

 

  1.  Click above State, District and School column one at a time and then click on % icon in the tool bar.  This will automatically list your data as percentages.  Using your MCAS data item analysis, enter data for the state, district and school. 

      Be sure that you are inputting the data in the appropriate column (having a partner 

      to read data is helpful).

 

·        For Multiple Choice and Short Answer questions, enter the data with a whole number not a decimal, i.e.  .62 would be entered as 62.

·        For Open Response questions, type the = sign, enter the number from the item analysis, followed by /4 then Enter. In other words, an open response question can earn up to 4 points.  Therefore, you have to divide by 4 in order to get average for these questions.

 

                 

 

 

 

 

  1. To label this sheet, move the cursor to the bottom where it says Sheet 1.  Right

      click your mouse on Sheet 1 and a menu box will appear.  Left click on Rename

            to highlight Sheet 1 and type in the name you wish to designate.

 

                  

 

  1. You may want to make multiple copies of this page in order to input other data from various subgroups of students. To do this, simply highlight all of the  columns you wish to copy and right click on the renamed sheet that you designated in step # 8.  Left click on Move or Copy and another window will appear.  Highlight move to end and click on create a copy.  Left click OK.  A new page will appear with (2) as the name.  Repeat this process several time anticipating subgroups for later use.  When working with a new sheet (subgroup) be sure to rename each sheet appropriately.

 

                   

 

 

 

  1.  OK, give yourself a pat on the back because the real tedious work is done.  You

       are now ready to create Root Cause Analysis Graphs.  Be sure to congratulate   

             your partner as well.

 

  1. Return to Sheet 1(or renamed sheet) to begin making your graphs.

 

 

12.  Highlight all the cells with information and click on Data on the toolbar at the top of the page.  Select Sort.  In the box labeled Sort by, click the drop down arrow next to Item Number.  Select State.  Click on Descending next to this.  Click OK.  The data will be arranged from easiest to hardest questions (per state results).

 

              

 

 

13.  Highlight all the cells with information and click on Chart Wizard on the tool bar.  The Chart Wizard looks like a colored bar graph

 

·        A new window will appear – Chart Wizard Step 1 of 4 Chart Type.  On the first tab Standard Types, highlight Line.  Click Next at the bottom of the window.

 

.

 

 

 

 

 

 

 

·        The next window will appear – Chart Wizard Step 2 of 4 Chart Source Data.  Click Next.

 

 

 


 

·        The next window will appear – Chart Wizard Step 3 0f 4 Chart Source Data. Give chart a title, click major gridlines for X axis, move legend to bottom. Click Next. When Chart Wizard Step 4 of 4 Chart Source Data appears, click Finish

 

 


 

  

 

 

 

 

 

 

 

 

 

 

14.  Right click on white area of newly created chart and a widow will appear.  Highlight Format Chart Area.  Click Font tab and select font size 8.  Click OK.

 

·        To increase the size of the chart title font, click on title to select, change font size on tool bar to desired size.

 

 

   

 

 

 

·        To move item numbers, questions types and strands to top of chart, click on a percentage on Y axis.  Format Axis widow will open. Click on Scale and then check in box that says Category (X) axis crosses at maximum value.

 

     

 

 

 

 

 

 

 

 

 

15.  To add trendline to your chart to help with data analysis, move cursor to any point on the chart on the district data line.  Right click and highlight Add Trendline.  Add Trendline window will appear.  On tab labeled Type, the linear box will be highlighted.  Move cursor to the Based on series box at the bottom.  District should be highlighted.  If not, highlight and click OK.

 

                

 

 

16.   Click on your chart and drag to right of data.  To increase chart size, move cursor   

        to the bottom right corner of the chart until you see the diagonal arrow.  Click  

        hold and drag to the bottom right hand corner to enlarge chart to the desired size.

17.   To print the chart, click on white area of chart and small black squares will appear in the corners and mid-points of the chart.  Be sure that the Name Box of tool bar in the upper left corner reads Chart Area.  Select Print from File drop box.

 

 

18.  You can use the copied sheets previously created to make additional charts with subgroup information (this data can be obtained from Testwiz).  Just add another column of cells labeled with your subgroup.  Add the data and follow the same procedures from Step 13.  Have Fun!

 

Return to Chicopee Home Page Return to Devine home page