Q6. (SIMPLE LINEAR REGRESSION) X4 = % of 18 to 24 year-olds in college The data for this question are provided below and are for each small American cities. Take Y as the dependent variable, and X1 - X4 as independent variables. Total Police 25yrs old in Not High Using excel, undertake the following Crime Funding College High School School (X1 Education (X2 a) 1740 (X3) 22 X4 Undertake Correlation analysis between the dependent variable 45 732 22 and each independent variable. Comment on the results. 22 15 13 1504 30 14 49 b) 1504 15 13 Undertake Correlation analysis between pairs of the independent 21 40 15 variable. Comment on the results. 22 13 45 9 c.) Which of the independent variables most explained the variation in 040 13 44 Total Crime? Which variables least explained Total Crime? 940 33 16 30 26 821 16 30 52 321 13 d) Undertake a simple hnear regression to write out the equation foreach 30 12 57 783 13 of the four independent variables. Comment on the equations. 55 12 57 23 783 11 55 72 23 ) The city authorities want to reduce crime, what should they best do? 1324 33 11 44 22 1324 82 16 67 22 () In the year when the city authority increased police funding to $100 912 16 27 44 per resident, what will approx. be the Total Crime rate per 1 million 305 21 46 9 57 residents? 14 867 39 12 52 17 657 10 33 72 792 13 28 22 57 12 491 32 11 67 11 491 32 16 57 1 1 16 The variables (Y, X1 to X4) are as follows I total overall reported crime rate per 1 million residents X1 = annual police funding in $ / resident X2 = % of people 25 years+ with 4 yrs. of high school X3 =%% of 16 to 19 year-olds not in high school and not high school graduates. Acti Go to