In: Statistics and Probability
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 5th year. Please copy and paste it to Excel and run regression analysis. Answer the following 4 questions. Please also upload your EXCEL file on Titanium, otherwise you will NOT receive any point for this problem.
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
Not yet answered
Points out of 10.00
Flag question
Question text
Develop a model for trend and seasonality. Please clearly define your variables. How many independent variables do you have in your regression?
Answer:
Question 17
Not yet answered
Points out of 10.00
Flag question
Question text
What is the intercept in your estimated regression model? Rounded to two decimal places.
Answer:
Question 18
Not yet answered
Points out of 10.00
Flag question
Question text
Use the model to forecast for sales of last quarter in the 5th year. Rounded to two decimal places.
Answer:
Question 19
Not yet answered
Points out of 10.00
Flag question
Question text
Calculate the MAE for this time series forecast. Rounded to two decimal places.
Answer:
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