Please don't give me wrong data for answers already on Chegg. Column D3 is 123559 not 1235559.
Figure 1. below displays the quarterly number of U.S. passengers (in thousands) using light rail as a mode of transportation. The series begins with the first quarter of 2009 and ends with the first quarter of 2014. We can see a regularity to the series: the first quarter's ridership tends to be lowest; then there is a progressive rise in ridership going into the second and third quarters, followed by a decline in the fourth quarter. Superimposed on the series are the moving-average forecasts based on a span of k=4. Notice that the seasonal pattern in the time series is not present in the moving averages. The moving averages are a smoothed-out version of the original time series, reflecting only the general trending in the series, which is upward. Figure 1. also shows the moving-average model forecasts and prediction limits projected into the future. Notice that the moving-average model makes no accommodation for the trend in its forecasts. FIGURE 1: Time series plot of quarterly light rail usage (first quarter 2009 through first quarter 2014) along with moving-average forecasts based on a span of k=4 and prediction limits. Data Analysis Questions: 1. Calculate the moving averages for each observation in the data based on periods 1,2,3, and 4. It should be centered on period t=2. Use the function of =AVERAGE (D2:D5) in cell E3 and copy the formula down to cell E20. Show the moving averages plotted on the passenger series y(t). Do not forget to label x and y axes and adjust the values on the plot using the Figure1. Show your plot space provided below: Even though the moving averages help highlight the long-run trend of a time series, the moving-average model is not designed for making forecasts in the presence of trends. Explain the reason why we should not rely on moving averages for predicting future observations of a trending series. The second step of averaging the averages is only necessary when the number of seasons is even, as with semiannual, quarterly, or monthly data. However, if the number of seasons is odd, then the initial moving average is the centered moving average. This average is referred to as centered moving average (CMA). 3. Calculate the centered moving average (CMA) that will be centered on t=3. This value can be seen in the spreadsheet and was obtained by entering = AVERAGE (E3:E4) in cell F4. This first centered moving average is properly sitting on t=3. The remaining centered moving averages can be found by copying the formula in cell F4 down to cell F20. Show the centered moving averages plotted on the passenger series y(t) and compare and interpret the trend of two series. Do not forget to label x and y axes and adjust the values on the plot using the Figure 1. Show your plot space provided below: Now you have an appropriate baseline estimate for the level of the process historically, so you can compute the seasonal component. This seasonal ratio says that we can isolate the seasonal component by dividing the data by the level of series as estimated by the trend component. 4. Calculate seasonal ratio for the passenger series y(t). The basic idea behind seasonal ratios can be seen by rearranging the trend-times-seasonal model: TRENDy^=SEASON For each of the estimated levels given by the centered moving averages, we calculate the ratio of actual sales divided by the centered moving average and enter = D4 / F4 in cell G4 and then copy the formula down to cell G20. This ratio tells us how to seasonally adjust a series. Because we have more than one seasonal ratio observation for a given quarter, we average these ratios by quarter. That is, we compute the average for all the quarter 1 ratios, then the average for all the quarter 2 ratios, and so on. These averages become our seasonal ratio estimates. Show the resulting ratios based on the periods of 1,2,3 and 4 in the table below: When you divide the original series by the seasonal component, you are then left with the trend component with no seasonality. This adjusted series does not show the regular ups and downs of the original unadjusted data. At this stage, you can come up with a trend line equation. a) Regress adjusted seasonality (deseasonality component) to time component. Show the trend line equation using regression for trend-and-season predictive model here and substitute the intercept and slope using the coefficient from the simple linear regression output: y^t=[a+b(t)]SR Show the regression output here: Time Series plot of quarterly light rail usage (first quarter 2009 through first quarter 2014) Figure 1. below displays the quarterly number of U.S. passengers (in thousands) using light rail as a mode of transportation. The series begins with the first quarter of 2009 and ends with the first quarter of 2014. We can see a regularity to the series: the first quarter's ridership tends to be lowest; then there is a progressive rise in ridership going into the second and third quarters, followed by a decline in the fourth quarter. Superimposed on the series are the moving-average forecasts based on a span of k=4. Notice that the seasonal pattern in the time series is not present in the moving averages. The moving averages are a smoothed-out version of the original time series, reflecting only the general trending in the series, which is upward. Figure 1. also shows the moving-average model forecasts and prediction limits projected into the future. Notice that the moving-average model makes no accommodation for the trend in its forecasts. FIGURE 1: Time series plot of quarterly light rail usage (first quarter 2009 through first quarter 2014) along with moving-average forecasts based on a span of k=4 and prediction limits. Data Analysis Questions: 1. Calculate the moving averages for each observation in the data based on periods 1,2,3, and 4. It should be centered on period t=2. Use the function of =AVERAGE (D2:D5) in cell E3 and copy the formula down to cell E20. Show the moving averages plotted on the passenger series y(t). Do not forget to label x and y axes and adjust the values on the plot using the Figure1. Show your plot space provided below: Even though the moving averages help highlight the long-run trend of a time series, the moving-average model is not designed for making forecasts in the presence of trends. Explain the reason why we should not rely on moving averages for predicting future observations of a trending series. The second step of averaging the averages is only necessary when the number of seasons is even, as with semiannual, quarterly, or monthly data. However, if the number of seasons is odd, then the initial moving average is the centered moving average. This average is referred to as centered moving average (CMA). 3. Calculate the centered moving average (CMA) that will be centered on t=3. This value can be seen in the spreadsheet and was obtained by entering = AVERAGE (E3:E4) in cell F4. This first centered moving average is properly sitting on t=3. The remaining centered moving averages can be found by copying the formula in cell F4 down to cell F20. Show the centered moving averages plotted on the passenger series y(t) and compare and interpret the trend of two series. Do not forget to label x and y axes and adjust the values on the plot using the Figure 1. Show your plot space provided below: Now you have an appropriate baseline estimate for the level of the process historically, so you can compute the seasonal component. This seasonal ratio says that we can isolate the seasonal component by dividing the data by the level of series as estimated by the trend component. 4. Calculate seasonal ratio for the passenger series y(t). The basic idea behind seasonal ratios can be seen by rearranging the trend-times-seasonal model: TRENDy^=SEASON For each of the estimated levels given by the centered moving averages, we calculate the ratio of actual sales divided by the centered moving average and enter = D4 / F4 in cell G4 and then copy the formula down to cell G20. This ratio tells us how to seasonally adjust a series. Because we have more than one seasonal ratio observation for a given quarter, we average these ratios by quarter. That is, we compute the average for all the quarter 1 ratios, then the average for all the quarter 2 ratios, and so on. These averages become our seasonal ratio estimates. Show the resulting ratios based on the periods of 1,2,3 and 4 in the table below: When you divide the original series by the seasonal component, you are then left with the trend component with no seasonality. This adjusted series does not show the regular ups and downs of the original unadjusted data. At this stage, you can come up with a trend line equation. a) Regress adjusted seasonality (deseasonality component) to time component. Show the trend line equation using regression for trend-and-season predictive model here and substitute the intercept and slope using the coefficient from the simple linear regression output: y^t=[a+b(t)]SR Show the regression output here: Time Series plot of quarterly light rail usage (first quarter 2009 through first quarter 2014)
