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 eac

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

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
0 0
Add a comment Improve this question Transcribed image text
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

Add a comment
Know the answer?
Add Answer to:
Ch8-B Hudson Marine has been an authorized dealer for C&D marine radios for the past six years. T...
Your Answer:

Post as a guest

Your Name:

What's your source?

Earn Coins

Coins can be redeemed for fabulous gifts.

Not the answer you're looking for? Ask your own homework help question. Our experts will answer your question WITHIN MINUTES for Free.
Similar Homework Help Questions
  • A relatively recent firm has been active for the last five years and now wants to...

    A relatively recent firm has been active for the last five years and now wants to forecast annual sales for the current year, 2020, based on the following historical data: Sales YEAR t (y) t*y t? 1 2015 1 3.000 3.000 2016 2 6.000 12.000 4 2017 3 8.000 24.000 9 2018 4 7.000 28.000 16 2019 5 10.000 50.000 25 SUM 15 34000 117.000 55 a. What is the forecast for this year, 2020, using a four-year simple moving...

ADVERTISEMENT
Free Homework Help App
Download From Google Play
Scan Your Homework
to Get Instant Free Answers
Need Online Homework Help?
Ask a Question
Get Answers For Free
Most questions answered within 3 hours.
ADVERTISEMENT
ADVERTISEMENT
ADVERTISEMENT