The attached data will provide you with the yearly average alcohol consumption (in volume (L) and per capita) of beer and the yearly crime rate (per 100K population) in Australia. We are investigating whether a linear relationship exists between the two variables. We postulate that the crime rate is the dependent variable and that beer consumption is the independent variable.

a) Write down the equation of the linear regression model we are considering.

b) Draw the scatter plot representing the relationship between the two data sets, with the dependent variable on the y-axis.

c) Using the formula presented in the lecture slides, compute the OLS estimates of the linear regression model you have written in the previous question

d) Calculate SST, SSE, SSR and the coefficient of determination R2

e) Using the regression function found in Excel under Data -Data Analysis, check your answers to Question 3 and 5 (R2)

f) In one sentence, can we say that Alcohol consumption is responsible for an increase in the crime rate and why?

Year	Crime Rate per 100K Population	Beer Consumption (L) per Capita Pure Alcohol
1990	2.2051	6.52
1991	1.978	6.29
1992	1.7297	5.82
1993	1.8917	5.51
1994	1.7979	5.51
1995	1.9785	5.46
1996	1.9462	5.32
1997	1.9796	5.31
1998	1.797	5.22
1999	2.0492	5.11
2000	1.9061	5.08
2001	1.8078	5.13
2002	1.8865	4.87
2003	1.5389	5.04
2004	1.323	4.77
2005	1.2835	4.67
2006	1.369	4.68
2007	1.2191	4.69
2008	1.2235	4.69
2009	1.2091	4.68
2010	1.0427	4.51
2011	1.1004	4.30
2012	1.061	4.14
2013	1.0535	4.04
2014	1.0298	4.02
2015	0.9861	3.76
2016	0.9356	3.87
2017	0.8257	3.68
2018	0.8916	3.71