Question

The quarterly sales of the TRK-50 mountain bike for the previous four years by a bicycle shop in Switzerland are presented in the table:

Year	Quarter	Q = Sales
2010	1	10
	2	31
	3	43
	4	16
2011	1	11
	2	33
	3	45
	4	17
2012	1	13
	2	34
	3	48
	4	19
2013	1	15
	2	37
	3	51
	4	21

PLEASE USE EXCEL AND SHOW ALL WORK.

1. Plot the sales against time.
2. Use the data to estimate the monthly trend in sales using a linear trend model of the form:
   Q_t = a + bt. Does your statistical analysis indicate a trend? If so, is it an upward or downward trend and how great is it? Is it a statistically significant trend at 5 percent level of significance?
3. Now adjust your statistical model to account for seasonal variation in sales. Estimate this model of sales:
   Q_t = a + bt + cD₂ + dD₃ + eD₄
   where D₂, D₃, D₄ are the appropriately defined dummy variables for quarters 2, 3, and 4.
   Do the data indicate a statistically significant seasonal pattern at 5 percent level of significance? If so, what is the seasonal pattern of sales?
4. Comparing your estimates of the trend in sales in parts b and c, which estimate is likely to be more accurate? Why?
5. Using the estimated forecast equation from part c, forecast sales for Quarters 1 and 4 of 2014 and Quarters 2 and 3 of 2015.

Answer 1

ANSWER:

Reaaranged data:

Year	Quarter	Q = Sales	time	D2	D3	D4
2010	1	10	1	0	0	0
	2	31	2	1	0	0
	3	43	3	0	1	0
	4	16	4	0	0	1
2011	1	11	5	0	0	0
	2	33	6	1	0	0
	3	45	7	0	1	0
	4	17	8	0	0	1
2012	1	13	9	0	0	0
	2	34	10	1	0	0
	3	48	11	0	1	0
	4	19	12	0	0	1
2013	1	15	13	0	0	0
	2	37	14	1	0	0
	3	51	15	0	1	0
	4	21	16	0	0	1

Answer(a):

Answer(b):

We have to fit the linear model:

The excel output of the regression model fit is as below:

	Coefficients	Standard Error	t Stat	P-value
Intercept	22.20	7.4822	2.9670	0.0102
time	0.6529	0.7738	0.8438	0.4130

The fitted model is

From the regression output, we can see that the p-value for time is 0.4130 which is greater than 0.05 and it indicates that the trend is not significant at 5% level of significance.

Answer(c):

We have to fit the model

The excel output of the regression model fit is as below:

	Coefficients	Standard Error	t Stat	P-value
Intercept	8.75	0.4281	20.4409	0.0000
time	0.50	0.0377	13.2665	0.0000
D2	21.00	0.4782	43.9130	0.0000
D3	33.50	0.4827	69.4079	0.0000
D4	4.50	0.4900	9.1845	0.0000

The fitted model is

From the regression output, we can see that the p-value for all the coefficients is <0.001 which is less than 0.05 and it indicates that the seasonal pattern is significant is highly significant at 5% level of significance.

Answer(d):

The estimate of trend in answer (c) is more accurate than trend in answer (b).

We can also observe that the p-value for coefficient of time (i.e. trend) in second model is <0.001 which indicates that the trend is highly significant for this model while it was not significant for the first model. In second model the trend id adjusted for the seasonal variation while in first model it was not adjusted, so we can say the estimate of trend in second model (i.e. answer c) is more accurate.

Answer(e):

We have to predict Qt using model in answer(c).

For Quarter 1 of 2014, t= 17, D2=0, D3=0, D4=0

For Quarter 4 of 2014, t= 20, D2=0, D3=0, D4=1

For Quarter 2 of 2015, t= 22, D2=1, D3=0, D4=0

For Quarter 3 of 2015, t= 23, D2=0, D3=1, D4=0

NOTE:: I HOPE YOUR HAPPY WITH MY ANSWER....***PLEASE SUPPORT ME WITH YOUR RATING...

***PLEASE GIVE ME "LIKE"...ITS VERY IMPORTANT FOR ME NOW....PLEASE SUPPORT ME ....THANK YOU