Question

Ch8-B Hudson Marine has been an authorized dealer for C&D marine radios for the past six years. The number of radios sold each quarter is shown in the Excel template. Hudson Marine would like to forecast the quarterly sales for year 7. (a) Construct a time series plot. (Hint: You need to create a line chart of the sales column. Make sure to label both axes.) (b) Construct a multiple regression model with dummy variables to develop an equation that takes into account both trend and seasonality (Hint: Give the explicit regression equation in this kind of format: F = 2.100 + 2.345 Q1-1.234 Q2 + 5.678 Q3+0.567 t and the 4 quarterly forecasts. Round each coefficient to the nearest 3rd decimal digit. recommend using the SUMPRODUCT (Intercept+SUMPRODUCT(b1 b2 b3 b4 cells, Q1 Q2 Q3 t cells)), but you can do it any way you are comfortable with.) (c) Give the quarterly forecasts for year 7 (d) Out of four quarters of the year, which quarter has the peak demand? Which quarter has the lowest demand? (Hint: You can either look at the chart or the coefficients to determine which out of the 4 quarters has the highest demand and which has the lowest demand. With the coefficients, highest coefficient means highest demand and the lowest coefficient indicates lowest demand (the coefficient for Q4 is 0 because Q4 doesn't appear in the equation.)) Hudson Marines 6 Year Quarter Sales (units) 13 19 10 10 12 13 14 31 28 15 23 16 30 26 40 19 20 21 25 29 42 35 24 23 24 25 26 27 28 29 30 46 32

Answer 1

t	y
1	13
2	22
3	19
4	10
5	18
6	31
7	28
8	15
9	23
10	33
11	30
12	22
13	26
14	40
15	33
16	25
17	29
18	42
19	35
20	24
21	33
22	46
23	41
24	32

A)

45 35 30 25 20 15 10 30 25 20 15

b)

y	t	Q1	Q2	Q3
13	1	1	0	0
22	2	0	1	0
19	3	0	0	1
10	4	0	0	0
18	5	1	0	0
31	6	0	1	0
28	7	0	0	1
15	8	0	0	0
23	9	1	0	0
33	10	0	1	0
30	11	0	0	1
22	12	0	0	0
26	13	1	0	0
40	14	0	1	0
33	15	0	0	1
25	16	0	0	0
29	17	1	0	0
42	18	0	1	0
35	19	0	0	1
24	20	0	0	0
33	21	1	0	0
46	22	0	1	0
41	23	0	0	1
32	24	0	0	0

regression result

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.983550519
R Square	0.967371623
Adjusted R Square	0.960502491
Standard Error	1.858732691
Observations	24
ANOVA
	df	SS	MS	F	Significance F
Regression	4	1946.190476	486.547619	140.8287994	7.71494E-14
Residual	19	65.64285714	3.454887218
Total	23	2011.833333
	Coefficients	Standard Error	t Stat	P-value	Lower 95%
Intercept	7.0833	1.08647126	6.51957727	3.03226E-06	4.809322851
t	1.0179	0.055540262	18.32647348	1.54974E-13	0.901610038
Q1	5.3869	1.085997955	4.960326799	8.68698E-05	3.113884918
Q2	16.3690	1.078873466	15.17235166	4.50121E-12	14.1109395
Q3	10.6845	1.074576099	9.943012706	5.77009E-09	8.435410185

F = 7.0833 + 5.3869 Q1 + 16.3690 Q2 + 10.6845 Q3 + 1.0179 t

c)

Year	Quarter	Period	Ft
4	1	25	37.917
4	2	26	49.917
4	3	27	45.250
4	4	28	35.583

d)

Q2 has peak demand

Q4 has lowest demand