Jaqui's Import Beers' Sales Seasonality

Background

Jaqui was the owner of a beer and wine distributorship. She had been in the business for a number of years and thought she knew the trends in beer sales fairly well. However, over the last 4 years, or so she thought, she saw a run-up in import beer sales within the United States. In fact, she also believed that in addition to an upward trend in import beer sales, there existed a seasonal component.

Seasonal Hypothesis

Her hypothesis was as follows: Import beer sales follow a bimonthly seasonal pattern. She deduced through her experience that January and February commonly were down months for import beer sales because of the after-holiday lag. Therefore, she would group sales in bimonthly units starting with January and February, then March and April, and so on for the rest of the year. Her groupings would resemble the following table.

MONTH SEASONAL GROUP

January S1

February S1

March S2

April S2

May S3

June S3

July S4

August S4

September S5

October S5

November S6

December S6

Jaqui knew of a technique called linear regression, but she also knew that if a data set contains seasonal variation, a standard linear regression model does not provide very good results. With seasonal effects, the data tend to drop well below the trend lines or ascend sharply above the trend lines in noticeable patterns. Forecasts for future time periods would be much more accurate if the regression model reflected these drops and ascents in the data.

*** Jaqui's data set is attached below. ***

	Actual Beer
	Import Sales
Time	Millions Brls
1	1.183
2	1.462
3	1.716
4	1.624
5	1.926
6	1.982
7	2.004
8	2.107
9	1.57
10	1.629
11	1.393
12	1.391
13	1.535
14	1.46
15	1.958
16	1.894
17	2.195
18	2.155
19	2.049
20	1.961
21	1.599
22	1.781
23	1.478
24	1.69
25	1.526
26	1.699
27	2.026
28	2.193
29	2.244
30	2.076
31	2.263
32	2.006
33	1.774
34	1.877
35	1.73
36	1.656
37	1.396
38	1.584
39	1.959
40	2.176
41	2.106
42	2.285
43	2.266
44	1.936
45	2.009
46	2.007
47	2.011
48	1.784
49	1.391
50	1.625
51	2.021
52	2.075
53	2.301
54	2.488
55	2.404
56	2.194
57	1.872
58	1.757
59	1.871
60	1.846
61	1.486
62	1.79
63	2.195
64	2.354
65	2.378
66	2.508
67	2.249
68	2.393
69	2.212

*** I have created another table with more details just in case if it helps. ***

				Indicator for	Actual Beer
		Quarterly		Quarterly Period	Import Sales
Year	Month	Period	Time	Q1	Q2	Q3	Millions Brls
2000	Jan	1	1	1	0	0	1.183
	Feb	1	2	1	0	0	1.462
	Mar	1	3	1	0	0	1.716
	Apr	2	4	0	1	0	1.624
	May	2	5	0	1	0	1.926
	Jun	2	6	0	1	0	1.982
	Jul	3	7	0	0	1	2.004
	Aug	3	8	0	0	1	2.107
	Sep	3	9	0	0	1	1.57
	Oct	4	10	0	0	0	1.629
	Nov	4	11	0	0	0	1.393
	Dec	4	12	0	0	0	1.391
2001	Jan	1	13	1	0	0	1.535
	Feb	1	14	1	0	0	1.46
	Mar	1	15	1	0	0	1.958
	Apr	2	16	0	1	0	1.894
	May	2	17	0	1	0	2.195
	Jun	2	18	0	1	0	2.155
	Jul	3	19	0	0	1	2.049
	Aug	3	20	0	0	1	1.961
	Sep	3	21	0	0	1	1.599
	Oct	4	22	0	0	0	1.781
	Nov	4	23	0	0	0	1.478
	Dec	4	24	0	0	0	1.69
2002	Jan	1	25	1	0	0	1.526
	Feb	1	26	1	0	0	1.699
	Mar	1	27	1	0	0	2.026
	Apr	2	28	0	1	0	2.193
	May	2	29	0	1	0	2.244
	Jun	2	30	0	1	0	2.076
	Jul	3	31	0	0	1	2.263
	Aug	3	32	0	0	1	2.006
	Sep	3	33	0	0	1	1.774
	Oct	4	34	0	0	0	1.877
	Nov	4	35	0	0	0	1.73
	Dec	4	36	0	0	0	1.656
2003	Jan	1	37	1	0	0	1.396
	Feb	1	38	1	0	0	1.584
	Mar	1	39	1	0	0	1.959
	Apr	2	40	0	1	0	2.176
	May	2	41	0	1	0	2.106
	Jun	2	42	0	1	0	2.285
	Jul	3	43	0	0	1	2.266
	Aug	3	44	0	0	1	1.936
	Sep	3	45	0	0	1	2.009
	Oct	4	46	0	0	0	2.007
	Nov	4	47	0	0	0	2.011
	Dec	4	48	0	0	0	1.784
2004	Jan	1	49	1	0	0	1.391
	Feb	1	50	1	0	0	1.625
	Mar	1	51	1	0	0	2.021
	Apr	2	52	0	1	0	2.075
	May	2	53	0	1	0	2.301
	Jun	2	54	0	1	0	2.488
	Jul	3	55	0	0	1	2.404
	Aug	3	56	0	0	1	2.194
	Sep	3	57	0	0	1	1.872
	Oct	4	58	0	0	0	1.757
	Nov	4	59	0	0	0	1.871
	Dec	4	60	0	0	0	1.846
2005	Jan	1	61	1	0	0	1.486
	Feb	1	62	1	0	0	1.79
	Mar	1	63	1	0	0	2.195
	Apr	2	64	0	1	0	2.354
	May	2	65	0	1	0	2.378
	Jun	2	66	0	1	0	2.508
	Jul	3	67	0	0	1	2.249
	Aug	3	68	0	0	1	2.393
	Sep	3	69	0	0	1	2.212

Problem at Hand and Discussion Questions

Therefore Jaqui had decided to develop a seasonally adjusted regression model for the import beer sales data.

1) She needs your help to construct a simple weighting method for developing seasonal factors, to generate a linear trend line for the import sales over time, and then combine the two in order to forecast sales for 2005.

2) How is Jaqui's model doing? Is it predicting import beer sales over time accurately? Interpret the R^2, F, and t statistics when answering this question.