Question

 

For planning purposes, senior executives at a large national clothing maker and retailer need to understand and forecast quarterly sales revenue. The available data are contained in the table attached below. The data is in units of hundreds of millions of dollars ($100M).

You have been tasked with describing the historical data and with developing preliminary forecasts for 2018 based on the historical data from the first quarter of 2011 (quarter 1) through the last quarter of 2017 (quarter 28).

Year	Qtr	revenue ($M)
2011	1	5.889
	2	6.141
	3	8.272
	4	9.302
2012	1	6.436
	2	6.932
	3	8.987
	4	10.602
2013	1	7.517
	2	7.731
	3	9.883
	4	12.098
2014	1	8.487
	2	8.685
	3	11.559
	4	15.221
2015	1	11.132
	2	11.203
	3	13.83
	4	16.979
2016	1	12.312
	2	13.452
	3	17.659
	4	21.655
2017	1	17.197
	2	19.05
	3	22.499
	4	25.629

 

a. Perform a linear time series regression (“Trend Analysis” in Minitab or “Trendline” in Excel) of the historical data using excel or minitab

b.   State the equation of the fitted regression line.

c. On the basis of this regression analysis, calculate and state the sales revenue forecasts for all four quarters of 2018.

d. Calculate and state the RMSE of this simple linear regression. [Hint: Different from forecasting, for regression RMSE = ?SSE/?(n-2).]

e. Calculate and state the forecast values for all four quarters of 2018 by the following time series decompositions. Perform the time series decompositions in Minitab. (Go to Stat > Time Series > Decomposition; for all decompositions, set the seasonal length)

e1.   Additive with seasonal only.

e2.   Additive with trend plus seasonal.

e3.   Multiplicative with seasonal only.

e4.   Multiplicative with trend plus seasonal.

e5.   On the basis of the Minitab time series decomposition plots, do you recommend forecasting with the trend component alone (as you were asked to do in part d above), the seasonal component alone (parts e(1) and e(3), or with both the trend and seasonal components together (parts e(2) and e(4). Briefly state why.

f. Calculate and state the accuracy of each of the forecasting methods in part e using the RMSE as the measure. (Note: MSE is stated on the Minitab graphs as “MSD.” RMSE is the square root of this value.)

f1. Which is the most accurate method of the decomposition methods used in part e? Briefly state why.

   f2. What are the most accurate forecasts? Briefly state why

Answer 1

			Yt	MA(3)	St,It	St	Yt/St
t	Year	Qtr	Revenue ($M)	Moving Average(3)	Seasonality St,It	St	De-seasonalised = Yt/St	Tt	Forecast
1	2011	1	5.889			0.87	6.75	4.87	4.25
2		2	6.141	6.77	0.91	0.93	6.62	5.43	5.04
3		3	8.272	7.91	1.05	1.01	8.23	5.98	6.01
4		4	9.302	8	1.16	1.18	7.90	6.53	7.68
5	2012	1	6.436	7.56	0.85	0.87	7.37	7.08	6.18
6		2	6.932	7.45	0.93	0.93	7.47	7.63	7.08
7		3	8.987	8.84	1.02	1.01	8.94	8.18	8.22
8		4	10.602	9.04	1.17	1.18	9.01	8.73	10.28
9	2013	1	7.517	8.62	0.87	0.87	8.61	9.28	8.10
10		2	7.731	8.38	0.92	0.93	8.33	9.83	9.13
11		3	9.883	9.9	1.00	1.01	9.83	10.39	10.44
12		4	12.098	10.16	1.19	1.18	10.28	10.94	12.87
13	2014	1	8.487	9.76	0.87	0.87	9.72	11.49	10.03
14		2	8.685	9.58	0.91	0.93	9.36	12.04	11.18
15		3	11.559	11.82	0.98	1.01	11.50	12.59	12.65
16		4	15.221	12.64	1.20	1.18	12.93	13.14	15.47
17	2015	1	11.132	12.52	0.89	0.87	12.75	13.69	11.95
18		2	11.203	12.06	0.93	0.93	12.07	14.24	13.22
19		3	13.83	14	0.99	1.01	13.76	14.80	14.87
20		4	16.979	14.37	1.18	1.18	14.43	15.35	18.06
21	2016	1	12.312	14.25	0.86	0.87	14.11	15.90	13.88
22		2	13.452	14.47	0.93	0.93	14.49	16.45	15.27
23		3	17.659	17.59	1.00	1.01	17.57	17.00	17.09
24		4	21.655	18.84	1.15	1.18	18.40	17.55	20.66
25	2017	1	17.197	19.3	0.89	0.87	19.70	18.10	15.80
26		2	19.05	19.58	0.97	0.93	20.52	18.65	17.32
27		3	22.499	22.39	1.00	1.01	22.39	19.20	19.30
28		4	25.629			1.18	21.78	19.76	23.25
29	2018	1				0.87		20.31	17.72
30		2				0.93		20.86	19.36
31		3				1.01		21.41	21.52
32		4				1.18		21.96	25.84
Number of peaks ~ 24/7 ~ 3 = time series of span 3
SUMMARY OUTPUT
Regression Statistics
Multiple R	0.947237
R Square	0.897258
Adjusted R Square	0.893306
Standard Error	1.563407
Observations	28
ANOVA
	df	SS	MS	F	Significance F
Regression	1	554.991	554.9909547	227.060589	2.3163E-14
Residual	26	63.55028	2.444241677
Total	27	618.5412
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	4.32309	0.607105	7.120831336	1.46008E-07	3.075168627	5.571012	3.075168627	5.571012
t	0.551155	0.036577	15.06852975	2.3163E-14	0.475970722	0.626339	0.475970722	0.626339