Least squares regression in Excel, with future prediction and MSE

Least squares regression in Excel, with future prediction and MSE

1.  For the data below:

Year	Automobile Sales	Year	Automobile Sales
1990	116	1997	119
1991	105	1998	34
1992	29	1999	34
1993	59	2000	48
1994	108	2001	53
1995	94	2002	65
1996	27	2003	111

(a) Determine the least squares regression trend line using the Excel output from the Data Analysis tool. (5 pts)

(b) Determine the predicted value for 2004. Use the information from part (a). (5 pts)

(c) Determine the 3 year moving average. (10 pts)

(d) Determine the MSE for the trend line (in a) and the 3 year moving average (in c.)  Which forecasting method is better? Explain. (20 pts)

ALL WORK NEEDS TO BE SHOWN