Question

Assignment Total points 100. Submit through D2L in Word/Excel format, single or multiple files. Must show your work, answer all parts of the question, show all relevant graphs, and write necessary explanations to earn full points. Grading depends on three components of your answer- accuracy and completeness of your calculations (numerical results); accuracy of relevant graphs; and accuracy and completeness of necessary explanations/interpretations/conclusions. You 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 2022 using linear regression. Finally, make the forecast for the 12 months of 2022 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 2022. Plot the errors for in-sample periods and calculate RMSE. Comment on the error plot with respect to the existence of pattern or lack of visible pattern. Month/Year 2018 2019 2020 2021 Jan 110 140 185 220 Feb 120 150 195 230 Mar 130 155 205 245 Apr 140 175 215 265 May 160 200 235 295 Jun 180 230 250 330 Jul 190 252 270 345 Aug 180 232 260 330 Sep 170 225 250 315 Oct 160 215 230 300 Nov 140 195 210 265 Dec 130 180 205 250 (Hint: First, you have to type the data in a single column form in Excel)

Get Instant Homework HelpOn Your Mobile

Get Instant Homework HelpOn Your Mobile

Get Instant Homework HelpOn Your Mobile