Question

1. AmeriPlas, Inc., produces 20-ounce plastic drinking cups that are embossed with the names of prominent beers and soft drinks. The sales data are:

Date	Sales
Jan-13	40,358
Feb-13	45,002
Mar-13	63,165
Apr-13	57,479
May-13	52,308
Jun-13	60,062
Jul-13	51,694
Aug-13	54,469
Sep-13	48,284
Oct-13	45,239
Nov-13	40,665
Dec-13	47,968
Jan-14	37,255
Feb-14	38,521
Mar-14	55,110
Apr-14	51,389
May-14	58,068
Jun-14	64,028
Jul-14	52,873
Aug-14	62,584
Sep-14	53,373
Oct-14	52,060
Nov-14	51,727
Dec-14	51,455
Jan-15	47,906
Feb-15	53,570
Mar-15	69,189
Apr-15	64,346
May-15	77,267
Jun-15	75,787
Jul-15	74,052
Aug-15	79,756
Sep-15	73,292
Oct-15	77,207
Nov-15	68,423
Dec-15	67,274
Jan-16	65,711
Feb-16	68,005
Mar-16	78,029
Apr-16	92,764
May-16	97,175
Jun-16	86,255
Jul-16	90,496
Aug-16	87,602
Sep-16	83,577
Oct-16	92,610
Nov-16	73,949
Dec-16	77,711

Page 277

(c5p12)

1. Prepare a time-series plot of the sales data. Does there appear to be a regular pattern of movement in the data that may be seasonal? Ronnie Mills, the product manager for this product line, believes that her brief review of sales data for the four-year period indicates that sales are slowest in November, December, January, and February than in other months. Do you agree?

1. Since production is closely related to orders for current shipment, Ronnie would like to have a monthly sales forecast that incorporates monthly fluctuations. She has asked you to develop a trend model that includes a time index and dummy variables for all but the above mentioned four months. Do these results support Ronnie’s observations? Explain.

2. Ronnie believes that there has been some increase in the rate of sales growth. To test this and to include such a possibility in the forecasting effort, she has asked that you add the square of the time index (T) to your model (call this new term T2). Is there any evidence of increase of sales growth? Compare the results of this model with those found in part (b).

3. Use the model in part (c) to forecast sales for 2017. Calculate the mean absolute percentage error (MAPE) for the first six months of 2017. Actual sales for those six months were:

Jan-2017	87327
Feb-2017	84772
Mar-2017	112499
Apr-2017	102633
May-2017	112996
Jun-2017	119807

Answer 1

1. Prepare a time-series plot of the sales data. Does there appear to be a regular pattern of movement in the data that may be seasonal? Ronnie Mills, the product manager for this product line, believes that her brief review of sales data for the four-year period indicates that sales are slowest in November, December, January, and February than in other months. Do you agree?

The time-series plot is:

Yes, sales data for the four-year period indicates that sales are slowest in November, December, January, and February than in other months.

1. Since production is closely related to orders for current shipment, Ronnie would like to have a monthly sales forecast that incorporates monthly fluctuations. She has asked you to develop a trend model that includes a time index and dummy variables for all but the above mentioned four months. Do these results support Ronnie’s observations? Explain.

The output is:

Regression Statistics
Multiple R	0.93343
R Square	0.871291
Adjusted R Square	0.822079
Standard Error	6688.479
Observations	48
ANOVA
	df	SS	MS	F	Significance F
Regression	13	1.03E+10	7.92E+08	17.70482	1.88E-11
Residual	34	1.52E+09	44735747
Total	47	1.18E+10
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	45518.48	2108.71	21.58594	2E-21	41233.07	49803.9	41233.07	49803.9
t	924.7418	71.26704	12.97573	1E-14	779.9097	1069.574	779.9097	1069.574
x1	-15281.1	3568.693	-4.28198	0.000143	-22533.5	-8028.62	-22533.5	-8028.62
x2	-12738.8	3561.57	-3.57674	0.001069	-19976.8	-5500.84	-19976.8	-5500.84
x3	0	0	65535	#NUM!	0	0	0	0
x4	0	0	65535	#NUM!	0	0	0	0
x5	0	0	65535	#NUM!	0	0	0	0
x6	0	0	65535	#NUM!	0	0	0	0
x7	0	0	65535	#NUM!	0	0	0	0
x8	0	0	65535	#NUM!	0	0	0	0
x9	0	0	65535	#NUM!	0	0	0	0
x10	0	0	65535	#NUM!	0	0	0	0
x11	-13645	3561.57	-3.83117	#NUM!	-20883	-6407.01	-20883	-6407.01
x12	-12158.7	3568.693	-3.40706	0.001703	-19411.2	-4906.28	-19411.2	-4906.28

The trend model that includes a time index and dummy variables is:

Sales = 45518.48 + 924.7418*t - 15281.1*x2 - 12738.8*x2 - 13645*x11 - 12158.7*x12

These results support Ronnie’s observations because the four-year period indicates that sales are slowest in November, December, January, and February than in other months.

2. Ronnie believes that there has been some increase in the rate of sales growth. To test this and to include such a possibility in the forecasting effort, she has asked that you add the square of the time index (T) to your model (call this new term T2). Is there any evidence of increase of sales growth? Compare the results of this model with those found in part (b).

The output is:

Regression Statistics
Multiple R	0.952126
R Square	0.906544
Adjusted R Square	0.866896
Standard Error	5785.089
Observations	48
ANOVA
	df	SS	MS	F	Significance F
Regression	14	1.07E+10	7.65E+08	22.86478	5.18E-13
Residual	33	1.1E+09	33467250
Total	47	1.18E+10
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	53731.77	2724.102	19.72459	7.81E-20	48189.55	59274	48189.55	59274
t	-45.0203	246.7313	-0.18247	0.856333	-546.999	456.9583	-546.999	456.9583
t²	19.79106	4.875659	4.059156	0.000284	9.87146	29.71067	9.87146	29.71067
x1	-15775.9	3089.087	-5.10696	1.35E-05	-22060.6	-9491.06	-22060.6	-9491.06
x2	-13035.7	3081.389	-4.23046	0.000174	-19304.8	-6766.55	-19304.8	-6766.55
x3	0	0	65535	#NUM!	0	0	0	0
x4	0	0	65535	#NUM!	0	0	0	0
x5	0	0	65535	#NUM!	0	0	0	0
x6	0	0	65535	#NUM!	0	0	0	0
x7	0	0	65535	#NUM!	0	0	0	0
x8	0	0	65535	#NUM!	0	0	0	0
x9	0	0	65535	#NUM!	0	0	0	0
x10	0	0	65535	#NUM!	0	0	0	0
x11	-13941.9	3081.389	-4.52454	#NUM!	-20211	-7672.73	-20211	-7672.73
x12	-12653.5	3089.087	-4.0962	0.000256	-18938.3	-6368.72	-18938.3	-6368.72

There is no evidence of the increase in sales growth.

3. Use the model in part (c) to forecast sales for 2017. Calculate the mean absolute percentage error (MAPE) for the first six months of 2017. Actual sales for those six months were:

Actual	Forecasted	Error
87327	83268.27	4058.732	5%
84772	87922.73	3150.732	4%
112499	102912.3	9586.707	9%
102633	104905.8	2272.752	2%
112996	106938.8	6057.206	5%
119807	109011.4	10795.58	9%
		MAPE	6%

Please give me a thumbs-up if this helps you out. Thank you!