Question

1. Use the data below to find the linear regression equation that best represents the given data and predict the revenue in 2013 (Copy data to Excel)

2. Then create Two new columns that represent the predication y =mx+b for each year and percent of growth for each year = (Revenue/Predication)*100

3. Use Excel to graph the linear model (x-axis years, y-axis revenue) and the linear equation of best fit.

Year	Revenue	Predication	Percent of growth (%)
2001	3665
2002	4163
2003	4750
2004	5287
2005	5825
2006	6395
2007	6834
2008	6994
2009	7401
2010	7867
2011	8548
2012	9331

1. Fill out the missing entry for prediction and percent of growth
2. Find the value of the linear correlation coefficient r.
3. Find the equation of the regression line, letting Number of years be the independent (x) variable.
4. Find the coefficient of determination.
5. Find the standard error of estimate s_e.

Answer 1

1)

Process: data -> data analysis -> regression -> dependent variable = revenue ; independent variable = t

Output:

regression equation: revenue = 3282.438 + 482.958*t

for t = 13;      
predicted revenue =   =3282.438 + 482.958*13 =   9560.892

2)

Year	t	Revenue	Predication; yhat = 3282.438 + 482.958*t	Percent of growth (%) = (Revenue/Predication)*100
2001	1	3665	3765.397436	0.973336829
2002	2	4163	4248.355478	0.979908584
2003	3	4750	4731.31352	1.003949533
2004	4	5287	5214.271562	1.013947958
2005	5	5825	5697.229604	1.022426759
2006	6	6395	6180.187646	1.034758225
2007	7	6834	6663.145688	1.025641689
2008	8	6994	7146.10373	0.978715152
2009	9	7401	7629.061772	0.970106184
2010	10	7867	8112.019814	0.969795462
2011	11	8548	8594.977855	0.994534267
2012	12	9331	9077.935897	1.027876833

3)

4)

a) correlation coefficient = 0.995294997   [Excel function used CORREL(t,revenue)]
  
b) regresion line: y = 482.96x - 962634   [from the column of coefficients in regression output]
  
c) coefficient of determination, R^2 = r^2 = 0.995295^2 =    0.990612137
  
d) SE = 177.7907239   [ deom standard error in table 1]