Excel代写 | PPHA 34330: Attaining Equity in K-12: Theory and Practice

这个作业是根据给出的数据用Excel创建散点图和建立相关性
PPHA 34330: Attaining Equity in K-12: Theory and Practice
Problem Set #5
Value: 20 points+10 point extra credit opportunity
Due: May 27, by 5pm via submission to Canvas
Format: All submissions should be in Word (.docx or .doc) format. (Feel free to work from a copy of this document.)

Question 1. (8 points total)
Part A. (4 points for question A and 4 points for question B. Points awarded for answering each question completely and referencing the readings in each rationale.)
Write a response answering each question:
A. Assume it’s 2002 and instead of granting the chancellorship of NYC schools to Joel Klein, Mayor Michael Bloomberg selects you. Assume Mayor Bloomberg gives you carte blanche to run the school system as you please. What are four new policies you would enact and why? Reference the readings for each of your four rationales.
B. In the same scenario, what are four things you would have the school system stop doing in your first two years in office? Why? Again, reference the readings for each of your rationales.
Question 2. (12 points total)
For all parts of this question, work with the file titled “PS 5 Datafile.xlsx”. This file is a modified version of the “PS 4 Datafile.xlsx” used in the previous assignment. The modifications made from PS4 to PS5 are explained in the new “Introduction” tab of the file. Note that most of these changes have been made to provide you with Excel examples and to simplify the following analyses.
Part A. GPA & ACT (3 points total.)
1. Create a scatter plot showing GPA as the independent variable and ACT as the dependent variable. On this plot, display the linear regression line between the two, along with the R-squared value. (Note that GPA and ACT are both the names of the “Named Ranges” used in Excel.) (1 point, no deductions for graph style.)
2. If you calculate the correlation (in Excel, using the formula =CORREL(GPA,ACT)) and square the result, your answer should be identical to the R-squared shown here. Create a table showing the R-squared values for all 16 schools between GPA and ACT. (In Excel, you will want to use an array formula and =RSQ. Look at the Array formulas in “GPA Averages” for examples.) (1 point for a table with the correct values.)
3. You should see some variance in the table you just created. What do you expect might be happening at the schools with the lower correlations between GPA and ACT? Why? (1 point for answering the question completely.)
 
Part B. GPA and Crime Data (3 points total.)
1. Create a scatter plot showing the Serious Crime Percentile Rank as the independent variable and GPA as the dependent variable. (Serious Crime Percentile Rank is labeled PR SC14 with Named Range SCPRank in Excel.) (1 point, no deductions for graph style.)
2. The R-squared is very low on the graph above, but it’s possible this isn’t true for certain subgroups. Create a table for the subgroups [African American Male, African American Female, Hispanic Male, Hispanic Female] (this column is labeled Subgroup and has the Named Range Subgroups in Excel), showing R-squared for GPA as the dependent variable and three different independent variables: Serious Crime Percentile Rank, Serious Crime (labeled Serious Crime 1_4 with Named Range SC), and LOG(Serious Crime) (you’ll need to create this column using the LOG function, but set any error values to zero.) (1 point for a table with the correct values.)
3. What does the result from #1 and #2 indicate to you in plain language? How do you explain the vertical “bands” you see in your plot for number 1? Do you have any opinions about the three different independent variables used in the chart above? (1 point for answering the completely.)

Part C. GPA, Crime, and Construct data (6 points total.)
1. For each of the five constructs you analyzed in the prior problem set [Self_Concept (SC), Academic_Identity (AI), Growth_Mindset_Self_Efficacy (GM), Intrinsic_Motivation (IM), Self_Regulation (SR)]. Construct a table showing the R-squared, with GPA as the dependent variable and each of these constructs as the independent variable for the 4 subgroups from the previous question. (The constructs above have the following named ranges, SelfC, AI, GM, IM, SR, respectively.) (2 points. Points assigned for correct values.)
2. Now, we want to see the effect of crime on these constructs. Create a similar table as in the previous answer, but this time make Serious Crime Percentile Rank the independent variable and the constructs the dependent variables. (2 points. Points assigned for correct values.)
3. We know that typical effects can overwhelm the “signal” from outliers. The Lambda and Eta schools have the highest concentration of “highest quartile crime near home” students. For those two schools combined, construct one final table showing 10 R-squared values: 5 answering the same question as #1 and 5 answering the same question as #2. Do not split by subgroup, simply treat the entire population of the two schools combined as one single subgroup. (2 points, Points assigned for correct values.)
 
Extra Credit. (Up to 10 points total)
Any points earned here may only be applied towards lost points on graphing or table questions, not on written responses. These points will be applied towards points missed on problem sets 1-5.
Question:
In pages 63 & 64 of The Death and Life of the Great American School System, Ravitch shares two perspectives from researchers about the efficacy of San Diego’s reforms. The first, research by Raymond and Bassok, is described by Ravitch here:
The biggest surprise in the San Diego Review was an analysis of the academic results by economist Margaret E. Raymond of Stanford University and researcher Daphna Bassok. They found that “San Diego students were helped moderately” by the Blueprint, “but other districts were able to generate larger gains over equivalent periods of time.” San Diego consistently scored higher than the state average from 1999 to 2003, but its rate of change “lagged slightly behind the rate of change statewide.” Only the middle schools made the same progress as other urban districts in California, but the gains were small. Raymond and Bassok noted that in light of the many interventions in middle schools, “such as genre studies, literacy blocks, and extended days, it is surprising to see such limited growth.”
The study observed that San Diego’s reading scores in elementary school had improved since 1998, especially for low-income students and students in the lowest-performing schools. For those students, scores rose in kindergarten, first grade, and second grade, but not in third grade. In high schools, improvement in reading was “minimal at best,” as there were no gains between 1998 and 2002 on state tests. In mathematics, the gains occurred mainly in the years prior to the adoption of the Blueprint and were smaller than the gains in other urban districts in California. Although there were bright spots, it was a dispiriting summary of the academic changes in San Diego, as compared to the rest of the state, during the reform era.
Ravitch then cites the more favorable assessment by Betts:
Analyzing test scores from 1999 to 2002, Julian Betts of the University of California at San Diego came to different conclusions. He declared that the reforms were so successful in helping low-performing students in elementary schools that they could serve as a model for the state and the nation. Betts and his colleagues found that the gains were largest in elementary schools, moderate in middle schools, but nonexistent in high schools. In fact, the double-and triple-length classes in high school seemed to diminish achievement. The most effective reform strategies were summer school and the “extended day reading program,” in which low-performing students received additional instruction for three ninety-minute periods each week, before or after school. Targeting low-performing schools with extra resources and a longer school year was also effective.
The study by Raymond and Bassok is not available online, but you can find the full paper by Betts posted with this problem set. For this question, please share any evidence you find in the Betts paper that makes claims either in support of or contradicts the claims by Raymond and Bassok cited above. Was it fair of Ravitch to describe the two studies the way she did? Based on your review of this research, how effective do you believe the San Diego reforms were? (Full points will be awarded for answers that cover each claim and cite evidence from Betts, where applicable.)