Question:

Must show work, answer all parts of the question, show all relevant graphs, and write necessary explanations. Grading depends on three components of the answer- accuracy, and completeness of calculations (numerical results); accuracy of relevant graphs; and accuracy and completeness of necessary explanations/interpretations/conclusions. Must use Excel to solve this numerical problem (project).

The following table gives monthly data for four years in million tons of consumption of ice cream in a country. Plot the series and comment on the visible seasonality and trend. Estimate the centered moving averages for this monthly series. Plot CMA and comment. Next, estimate the S, I component which only includes seasonal and irregular movements of the series. Then find the seasonal indexes for the twelve months removing the irregular component. Find the de-seasonalized levels for the series. Plot De-seasonalized Y and comment. Then estimate the trend values for the four sample years and the 12 months of the year 2021 using linear regression. Finally, make the forecast for the 12 months of 2021 using the Ratio-to-Moving Average method to capture the Trend and Seasonal patterns, using Excel. Plot the forecasted values for the 60 periods including 12 months of the year 2021. Plot the errors for in-sample periods and calculate RMSE. Comment on the error plot concerning the existence of a pattern or lack of a visible pattern.

Month/Year	2017	2018	2019	2020
Jan	210	242	280	322
Feb	220	248	290	332
Mar	230	252	300	342
Apr	240	275	310	362
May	260	300	330	395
Jun	280	330	350	425
Jul	290	352	370	445
Aug	280	332	360	428
Sep	270	325	350	415
Oct	260	315	330	395
Nov	240	290	310	355
Dec	230	275	305	348

Upload Excel Work here

(Hint: First, type the data in a single-column form in Excel)