Question

OC Music Company has been in business for 4 years. Data about the sales of each quarter were collected below. The manager wants use these data to forecast sales of the 5^th year. Please copy and paste it to Excel and run regression analysis. Answer the following 4 questions.

Year	Quarter	Sales
1	Q1	7
	Q2	2
	Q3	4
	Q4	10
2	Q1	6
	Q2	3
	Q3	8
	Q4	14
3	Q1	10
	Q2	3
	Q3	5
	Q4	16
4	Q1	12
	Q2	4
	Q3	7
	Q4	22

Question 16

Develop a model for trend and seasonality. Please clearly define your variables. How many independent variables do you have in your regression?

Question 17

What is the intercept in your estimated regression model? Rounded to two decimal places.

Question 18

Use the model to forecast for sales of last quarter in the 5th year. Rounded to two decimal places.

Question 19

Calculate the MAE for this time series forecast.

I need to see entire Excel file and how you set up the Intercepts.

Answer 1

Using Excel, input data in the following manner:

Year	Q1	Q2	Q3	Q4	Sales
1	1	0	0	0	7
1	0	1	0	0	2
1	0	0	1	0	4
1	0	0	0	0	10
2	1	0	0	0	6
2	0	1	0	0	3
2	0	0	1	0	8
2	0	0	0	0	14
3	1	0	0	0	10
3	0	1	0	0	3
3	0	0	1	0	5
3	0	0	0	0	16
4	1	0	0	0	12
4	0	1	0	0	4
4	0	0	1	0	7
4	0	0	0	0	22

Go to Data, select Data Analysis, choose Regression. Put Year, Q1,Q2, Q3 and Q4 in X input range. Put Sales in Y input range. Tick Residuals.

	Coefficients	Standard Error	t Stat	P-value
Intercept	11.1875	1.673458103	6.685258	5.46E-05
Year	1.725	0.49892881	3.457407	0.006149
Q1	-6.75	1.57775143	-4.27824	0.001616
Q2	-12.5	1.57775143	-7.92267	1.28E-05
Q3	-9.5	1.57775143	-6.02123	0.000128
Q4	0	0	65535	--

16. Sales = 11.19 + 1.73*Year - 6.75*Q1 - 12.5*Q2 - 9.5*Q3

Independent variables: Q1, Q2, Q3, Year (4)

Dependent variable: Sales

17. Intercept = 11.19

18. Forecast for last quarter of 5th year:

Sales = 11.19 + 1.73*5 - 6.75*0 - 12.5*0 - 9.5*0 = 19..84

19.

Sales	Predicted	Residuals	Absolute Error
7	6.163	0.837	0.837
2	0.413	1.588	1.588
4	3.413	0.587	0.587
10	12.913	-2.913	2.913
6	7.888	-1.888	1.888
3	2.138	0.863	0.863
8	5.138	2.863	2.863
14	14.638	-0.638	0.638
10	9.613	0.387	0.387
3	3.863	-0.862	0.862
5	6.863	-1.863	1.863
16	16.363	-0.363	0.363
12	11.338	0.663	0.663
4	5.588	-1.588	1.588
7	8.588	-1.588	1.588
22	18.088	3.913	3.913
		Total	23.4

MAE = 23.4/16 = 1.463