Common Core of Data Problem Set

Directions: The problem set is to do all of the following tasks. You are expected to do all of the tasks, even if you are not asked to turn in the answers to a particular task. On Tuesday October 5, turn in hardcopies of the 4 graphs. Your four graphs should look just exactly like the following CCD Graphs with the sole change being authorship credits and that each of your graphs will take up an entire page.

Tasks:

1. Open and print the codebook so you can refer to it easily while doing the subsequent tasks.

2. Download districts_03_04.xls to your U drive.

3. Open districts_03_04.xls in Excel. Save the file as ccd_problem_set.xls, just to make sure you keep the original data separate from the changes you are about to make to it.

4. Make sure you are using ccd_problem_set.xls and that it includes 13,857 observations (13,858 rows of data given that the first row contains label variables) and 155 variables (columns A - EY).

5. In column EZ, create a new variable for per pupil revenue called pprev, which is defined as total district revenue (TOTALREV) divided by total district students (MEMBERS). That is, type =AQ2/Q2 in cell EZ2. Copy and paste this formula into all cells in the column: EZ2 - EZ13858. Make sure your new variable (and all entries in the column) have Arial 8pt font. Also, make the column report pprev with no dollar signs, decimal points, or commas. To do this, select the column, right click, select Format Cells, select Number with 0 decimal points and make sure the comma box is not clicked. Using the =AVERAGE(EZ2:EZ13858) formula, convince yourself that the average per pupil revenue is $10,510.

6. In column FA, create a variable that reports the percent of students for each district that are ESL. Call this new variable percesl. To create the variable, type into cell FA2 the following: =100*T2/Q2. Make sure all cell values are reported to 2 decimal places and have Arial 8 point font. Convince yourself that the average percent of students who are ESL is 3.88%.

7. By clicking on the column headings for EZ and FA, copy both columns. Then, leaving the columns highlighted, go to Edit then Paste Special and then click on Values. This will paste all of the values for pprev and percesl into the same two columns, but now the formulas are gone, and Excel recognizes the actual values for each cell.

8. In cell L13860, type Average. In cell L13861 type Sum/Bil.

9. In cell M13860, enter =AVG(M2:M13858). This calculates the average for this variable. (Hopefully this is how you found the average for pprev and percesl above.) In cell M13861, enter =SUM(M2:M13858)/1000000000. This calculates the total summation of all values in the column and divides it by 1 billion.

10. Notice that the average number of schools per district is 6.6, the average number of FTE teaching positions is 162.4, the average number of LEA (District) administrative positions is 4.1, the average number of LEA support staff is 11.0, the average number of diplomas awarded in each district is 191.9, and the average percent of students who are ESL is 3.88%. (You will need to use the codebook to determine where to look for each of these variables.) In the Sum/Bil row, for example, the number associated with UG should be 0.0004 and the number associated with PK12 should be 0.0471.

11. Copy row 13861 and paste special the values on top of itself.

12. Highlight rows 2 - 13860, and delete them. You should be left with two rows of data. Row 1 is variable names. Row two is the sum per billions row.

13. Graph 1: Revenue by Source

    1. Copy the variable name and the data below it for total federal revenue (AR1:AR2) to AR5:AR6. In AR5, change TFEDREV to Federal.

    2. Copy the variable name and the data below it for total state revenue (BF1:BF2) to AS5:AS6. In AS5, change TSTREV to State.

    3. Copy the variable name and the data below it for total local revenue (BU1:BU2) to AT5:AT6. In AT5, change TLOCREV to Local.

    4. Click on cell AR10. Now click on the Chart Wizard. This is under Insert. It is also on the toolbar.

    5. When the chart wizard opens, click on Column then Next.

    6. Make sure the cursor is blinking in the Data Range box. Then click and drag on the 6 cells you just created: AR5:AT6. Click Next.

    7. In the Chart Title box, enter Revenue Source by Level of Government 2003-04. (Notice that you don't put your name in here yet.)

    8. In the Value (Y) Axis box, enter Billions of Dollars.

    9. Click on Legend. Click off the Legend (Show Legend) box. Click on Next and then Finished.

    10. A graph will appear. Click in the title of the graph, insert a hard return, and type By Your Name.

    11. Click on any white space on the graph in order to highlight the entire graph, and print the graph.

14. Graph 2: Federal Revenue by Source

    1. Copy the variable name and the data below it for total federal expenditures on Title I (AS1:AS2) to BA5:BA6. In BA5, change C14 to Title I.

    2. Copy the variable name and the data below it for total federal expenditures on Children with Disabilities (AT1:AT2) to BB5:BB6. In BB5, change C15 to CWD.

    3. Copy the variable name and the data below it for total federal expenditures on Math, Science, and Teacher Quality (AU1:AU2) to BC5:BC6. In BC5, change C16 to Teacher Quality.

    4. Continue this process for Safe and Drug Free Schools (change C17 to Drug Programs), Title V (change C18 to Title V), Vocational and Tech Education (change C19 to VocTech), Child Nutrition (change C25 to Nutrition), ESL (change B11 to ESL) and Other (combine C20, C36, B10, B12, and B13 and call it Other). You should now have variable labels in BA5:BI5.

    5. Click on cell BA10. Now click on the Chart Wizard. This is under Insert. It is also on the toolbar.

    6. When the chart wizard opens, click on Column then Next.

    7. Make sure the cursor is blinking in the Data Range box. Then click and drag on the 18 cells you just created: BA5:BI6. Click Next.

    8. In the Chart Title box, enter Federal Revenue Source 2003-04.

    9. In the Value (Y) Axis box, enter Billions of Dollars.

    10. Click on Legend. Click off the Legend (Show Legend) box. Click on Next and then Finished.

    11. A graph will appear. Click in the title of the graph, insert a hard return, and type By Your Name.

    12. Right click on the X-axis labels. Edit the axis. Under alignment, make the text print at 90 degrees.

    13. Click on any white space on the graph in order to highlight the entire graph, and print the graph.

15. Graph 3: Expenditures by Type

    1. Copy the variable name and the data below it for total instructional expenditures on instruction (CP1:CP2) to CP5:CP6. In CP5, change TCURINST to Instruction.

    2. Copy the variable name and the data below it for total instructional expenditures on support services (CS1:CS2) to CQ5:CQ6. In CQ5, change TCURSSVC to Support Services.

    3. Copy the variable name and the data below it for total instructional expenditures on other (DB1:DB2) to CR5:CR6. In CR5, change TCUROTH to Other.

    4. Click on cell CP10. Now click on the Chart Wizard. This is under Insert. It is also on the toolbar.

    5. When the chart wizard opens, click on Column then Next.

    6. Make sure the cursor is blinking in the Data Range box. Then click and drag on the 6 cells you just created: CP5:CR6. Click Next.

    7. In the Chart Title box, enter Expenditures by Type: 2003-2004.

    8. In the Value (Y) Axis box, enter Billions of Dollars.

    9. Click on Legend. Click off the Legend (Show Legend) box. Click on Next and then Finished.

    10. A graph will appear. Click in the title of the graph, insert a hard return, and type By Your Name.

    11. Click on any white space on the graph in order to highlight the entire graph, and print the graph.

16. Graph 4: Salaries by Type: 2003-2004

    1. Copy the variable name and the data below it for salaries for regular education teachers (DV1:DV2) to DV5:DV6. In DV5, change Z35 to Regular Education.

    2. Copy the variable name and the data below it for salaries for special education teachers (DW1:DW2) to DW5:DW6. In DW5, change Z36 to Special Education.

    3. Copy the variable name and the data below it for salaries for LEA (District) Administrators (EB1:EB2) to DX5:DX6. In DX5, change V15 to District Admin.

    4. Copy the variable name and the data below it for salaries for School Administrators (EC1:EC2) to DY5:DY6. In DY5, change V17 to School Admin.

    5. Click on cell DV10. Now click on the Chart Wizard. This is under Insert. It is also on the toolbar.

    6. When the chart wizard opens, click on Column then Next.

    7. Make sure the cursor is blinking in the Data Range box. Then click and drag on the 8 cells you just created: DV5:DY6. Click Next.

    8. In the Chart Title box, enter Salaries by Type: 2003-04.

    9. In the Value (Y) Axis box, enter Billions of Dollars.

    10. Click on Legend. Click off the Legend (Show Legend) box. Click on Next and then Finished.

    11. A graph will appear. Click in the title of the graph, insert a hard return, and type By Your Name.

    12. Click on any white space on the graph in order to highlight the entire graph, and print the graph.