Question

Barbara Lynch is the product manager for a line of skiwear produced by HeathCo Industries and privately branded for sale under several different names, including Northern Slopes and Jacque Monri. A new part of Ms. Lynch's job is to provide a quarterly forecast of sales for the northern United States, a region composed of 27 states stretching from Maine to Washington. A 10-year sales history is shown:

Period	Sales
Mar-07	72,962
Jun-07	81,921
Sep-07	97,729
Dec-07	142,161
Mar-08	145,592
Jun-08	117,129
Sep-08	114,159
Dec-08	151,402
Mar-09	153,907
Jun-09	100,144
Sep-09	123,242
Dec-09	128,497
Mar-10	176,076
Jun-10	180,440
Sep-10	162,665
Dec-10	220,818
Mar-11	202,415
Jun-11	211,780
Sep-11	163,710
Dec-11	200,135
Mar-12	174,200
Jun-12	182,556
Sep-12	198,990
Dec-12	243,700
Mar-13	253,142
Jun-13	218,755
Sep-13	225,422
Dec-13	253,653
Mar-14	257,156
Jun-14	202,568
Sep-14	224,482
Dec-14	229,879
Mar-15	289,321
Jun-15	266,095
Sep-15	262,938
Dec-15	322,052
Mar-16	313,769
Jun-16	315,011
Sep-16	264,939
Dec-16	301,479

a.) Because Ms. Lynch has so many other job responsibilities, she has hired you to help with the forecasting effort. First, she would like you to prepare a time-series plot of the data and to write her a memo indicating what the plot appears to show and whether it seems likely that a simple linear trend would be useful in preparing forecasts.

b.) In addition to plotting the data over time, you should estimate the least-squares trend line in the form:

SALES=a+b(TIME)

Set TIME=1 for 2007Q1 through TIME=40 for 2016Q4. Write the trend equation:

SALES=_________+/-__________(TIME)

(Circle + or - as appropriate)

c.) Do your regression results indicate to you that there is a significant trend to the data? Explain why or why not.

d.) On the basis of your results, prepare a forecast for the four quarters of 2017.

e.) A year later, Barbara gives you a call and tells you that the actual sales for the four quarters of 2017 were: Q1=334,271, Q2=328,982, Q3=317,921, and Q4=350,118. How accurate was your model? What was the mean absolute percentage error (MAPE)?

Answer 1

Result:

a.) Because Ms. Lynch has so many other job responsibilities, she has hired you to help with the forecasting effort. First, she would like you to prepare a time-series plot of the data and to write her a memo indicating what the plot appears to show and whether it seems likely that a simple linear trend would be useful in preparing forecasts.

The plot shows simple linear trend would be useful in preparing forecasts.

b.) In addition to plotting the data over time, you should estimate the least-squares trend line in the form:

SALES=a+b(TIME)

Set TIME=1 for 2007Q1 through TIME=40 for 2016Q4. Write the trend equation:

SALES= 88741.012 + 5362.623 (TIME)

(Circle + or - as appropriate)

Excel Addon Megastat used.

Menu used: correlation/Regression ---- Regression Analysis

Regression Analysis
	r²	0.866	n	40
	r	0.931	k	1
	Std. Error of Estimate	24961.564	Dep. Var.	Sales
Regression output					confidence interval
variables		coefficients	std. error	t (df=38)	p-value	95% lower	95% upper
Intercept	a =	88,741.012	8,043.906	11.032	0.0000	72,456.975	105,025.048
time	b =	5,362.623	341.907	15.684	0.0000	4,670.468	6,054.777
ANOVA table
Source	SS	df	MS	F	p-value
Regression	153,278,654,180.124	1	153,278,654,180.124	246.00	0.0000
Residual	23,677,027,738.851	38	623,079,677.338
Total	176,955,681,918.975	39

c.) Do your regression results indicate to you that there is a significant trend to the data? Explain why or why not.

To test significance of the model, Calculated F=246.00, P=0.0000 which is < 0.05 level of significance. Ho is rejected. There is a significant linear trend in the data.

d.) On the basis of your results, prepare a forecast for the four quarters of 2017.

Predicted values for: Sales
time	Predicted
41	308,608.54
42	313,971.16
43	319,333.78
44	324,696.41

e.) A year later, Barbara gives you a call and tells you that the actual sales for the four quarters of 2017 were: Q1=334,271, Q2=328,982, Q3=317,921, and Q4=350,118. How accurate was your model? What was the mean absolute percentage error (MAPE)?

time	observed	Predicted	absolute difference	absolute difference/observed
41	334,271	308,608.54	25662.4615	0.0768
42	328,982	313,971.16	15010.8389	0.0456
43	317,921	319,333.78	1412.7837	0.0044
44	350,118	324,696.41	25421.5937	0.0726
			mean	0.0499

MAPE = 0.0499*100 = 4.99%