Need answer in spreadsheet with business analytical model in it

Hillside is a small charter school in an inner city neighborhood of Newark New Jersey. Feeling pressure from its board to increase student scores on the state standardized test, the school administration recently piloted an intervention program called SIS (Student Intervention for Success) aimed at improving the scores of the lowest-performing students by providing tutoring. SIS provides an intensive help for students, including tutoring, an after-school study skills workshop and peer advising.

The state test has a maximum score of 25. Students who receive a score of 11 or less are considered to be performing significantly under grade level. For its pilot, Hillside decided to enroll any student who had a 2011 score of 11 or lower in the SIS program at the start of 2012. The 2012 academic year was now over and Hillside administrators wanted to evaluate the results of SIS and report back to the board.

The worksheet pb1_data contains a sample of 100 Hillside students. Their performance on the past three standardized tests (2010, 2011 and 2012) are reported along with an indicator of whether the student was enrolled in SIS for 2012. Based on these data, answer the following questions:

(a) Using 2011 as the "before" period and 2012 as the "after" period, perform a differencein-difference analysis on the change in the average test scores of the SIS students. Based on your DiD estimate, what is the increase in test scores from SIS?

(b) Recalling your experience playing Cornhole in Business Analytics, you suspect the results in part (a) may be overly optimistic because of the effects of regression to the mean. That is, because only the students who performed poorly on the 2011 exam were enrolled in SIS, some increase in their 2012 scores would be expected due simply to regression to the mean. To test this idea, consider the performance of the students between 2010

1 and 2011. Construct a scatter plot of 2011 vs. 2010 test scores and estimate the slope of the linear regression line from your scatter plot. Does your analysis indicate there is regression to the mean in Hillside test scores?

(c) Using the slope of the regression from part (b) as your shrinkage coefficient, construct a shrinkage estimate of 2012 scores based on the 2011 test results. What is the RMSE of your predictions?

(d) Lastly to correct your DiD analysis for the shrinkage effect, compute the average of the estimated and actual 2012 scores for both the SIS students and non-SIS students. Considering the estimated 2012 scores as the "before" scores and the actual 2012 scores as the "after" scores, perform another DiD analysis of the SIS program. With this correction for shrinkage, what is your new estimate of the increase in test scores from SIS?

(e) Briefly comment on why the method in part (a) and the method in part (d) produce different estimates of the effect of the SIS program.

ID	2010 ST	2011 ST	Treated	2012 ST
1	13	11	1	15
2	16	21	0	20
3	11	13	0	21
4	14	17	0	14
5	8	7	1	22
6	15	12	0	16
7	15	16	0	17
8	17	15	0	12
9	10	8	1	8
10	8	6	1	7
11	13	12	0	10
12	11	14	0	9
13	24	18	0	23
14	15	13	0	12
15	15	17	0	21
16	14	16	0	18
17	20	17	0	13
18	21	14	0	8
19	17	20	0	13
20	24	17	0	14
21	17	14	0	16
22	17	14	0	17
23	8	11	1	7
24	19	15	0	11
25	17	22	0	14
26	15	16	0	11
27	10	10	1	15
28	14	12	0	18
29	17	16	0	23
30	17	16	0	15
31	12	10	1	15
32	21	24	0	22
33	23	22	0	20
34	21	19	0	20
35	17	20	0	24
36	25	23	0	25
37	15	19	0	16
38	12	14	0	15
39	20	18	0	16
40	7	12	0	5
41	13	10	1	12
42	9	15	0	21
43	10	10	1	13
44	17	15	0	9
45	10	16	0	12
46	20	18	0	19
47	20	18	0	21
48	18	21	0	20
49	12	12	0	5
50	21	17	0	15
51	12	12	0	12
52	13	12	0	13
53	11	11	1	17
54	19	19	0	17
55	14	16	0	19
56	5	3	1	15
57	12	12	0	13
58	19	15	0	19
59	19	13	0	12
60	9	15	0	14
61	13	15	0	16
62	18	20	0	12
63	15	15	0	15
64	8	11	1	19
65	11	6	1	12
66	13	15	0	19
67	17	17	0	11
68	13	14	0	24
69	19	16	0	20
70	17	14	0	13
71	13	6	1	9
72	16	18	0	16
73	23	24	0	25
74	15	21	0	15
75	17	25	0	25
76	12	14	0	7
77	10	11	1	15
78	15	13	0	18
79	14	19	0	16
80	1	11	1	5
81	18	21	0	14
82	19	13	0	14
83	21	21	0	19
84	23	16	0	20
85	10	17	0	6
86	21	14	0	11
87	11	12	0	9
88	17	18	0	20
89	14	22	0	19
90	21	20	0	21
91	14	19	0	16
92	14	22	0	17
93	18	22	0	22
94	25	17	0	20
95	16	15	0	22
96	16	16	0	13
97	17	20	0	20
98	12	12	0	13
99	14	13	0	18
100	15	18	0	19