Hudson Marine is an authorized dealer for C&D marine radios. Quarterly sales of

Question

Hudson Marine is an authorized dealer for C&D marine radios. Quarterly sales of radios (in number of units) are given in the file HudsonMarine.xlsx.

Can you please show all steps used in Excel and calculations please.

a. Develop an estimated regression equation estimate the seasonal and linear trend effects. Use number of period And Qtr1`, Qtr2 and Qtr3 as the predictor variables, where Qtr1 = 1 if the observation is from the first quarter and 0 otherwise, Qrt2 =1 if second quarter and 0 otherwise, and Qtr3 = 1 if third quarter, 0 otherwise.

b. Compute the quarterly forecasts for next year.

c. Compute centered (four-point) moving averages for this time series.

d. Construct a second time series plot that shows both the centred moving average and original time series data on the same plot. Discuss the differences between the original time series plot and the centred moving averages time series.

e. Compute the seasonal indexes as well as the adjusted seasonal indexes for all four quarters.

f. When does Hudson Marine experience the largest seasonal effect? Does this result seem reasonable? Explain.

g. Deseasonalize the data and obtain a time series graph of the deseasonalized data. Use this plot to identify the trend.

h. Estimate a simple regression model for the deseasonalized based on the single predictor “Period”. Develop forecasts for next year’s “seasonally adjusted” quarterly sales based on this model.

i. Use the adjusted seasonal indexes of part (e) to adjust the forecasts developed in part (h) to account for the effect of season.

Year Quarter Sales 1 1 6 2 15 3 10 4 4 2 1 10 2 18 3 15 4 7 3 1 14 2 26 3 23 4 12 4 1 19 2 28 3 25 4 18 5 1 22 2 34 3 28 4 21 6 1 24 2 36 3 30 4 20 7 1 28 2 40 3 35 4 27

Explanation / Answer

b.

Quarter 1 – 8.9167

Quarter 2 – 6.9167

Quarter 3 – 7.9167

Quarter 4 – 9.9167

Related Questions

Navigate

• Browse (All)
• Browse H
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.