Unit 5 Assignment: Forecasting Problems Using Excel ✓ Solved

Unit 5 Assignment: Forecasting Problems Using Excel Template

This assignment requires you to use Excel. Use the Unit 5 Assignment template located in Course Resources when you turn in your answers.

Question 1: Determine the forecast errors for each forecast, then calculate MAD and MSE.

Question 2: The U.S. Census Bureau publishes factory orders for all manufacturing, durable goods, and nondurable goods industries. Shown here are factory orders data over a 13-year period. First, use the data to develop forecasts for years 6 through 13 using a 5-year moving average. Then use the data to develop forecasts for years 6 through 13 using a 5-year weighted moving average. Weights: most recent year by 6, the previous year by 4, the year before that by 2, and the other years by 1.

Answer the following: a) forecast for year 13 with the 5-year moving average; b) forecast for year 13 with the 5-year weighted moving average; c) MAD for the moving average forecast; d) MAD for the weighted moving average forecast; e) which forecasting model is better, with justification.

Question 3: The Economic Report to the President of the United States includes data on the amounts of manufacturers’ new orders in millions of dollars for a 21-year period. Use Excel to fit a linear trend model and a polynomial (order 2) model. Create charts showing the line formulas and R-squared values.

Answer the following: How well do the models fit the data? Which model should be used for forecasting? Explain using the metrics.

Note: Use the Unit 5 Assignment template and, where applicable, include charts showing the trendlines and formulas.

Paper For Above Instructions

Question 1: Forecast errors and accuracy metrics. Let e_t denote the forecast error for period t, defined as e_t = actual_t − forecast_t. The mean absolute deviation (MAD) and mean squared error (MSE) summarize forecast accuracy. MAD = (1/n) Σ|e_t|, and MSE = (1/n) Σ(e_t^2), where n is the number of forecast periods with known actuals (Makridakis et al., 1998; Hyndman & Athanasopoulos, 2018). To complete this item, list the forecasted values, compute the errors, and provide the resulting MAD and MSE. When data are limited, report the formulas used and show a worked example with the given subset of periods, then note how MAD and MSE would be computed once all errors are known (Chatfield, 2003).

Question 2: Forecasts from 5-year moving and weighted moving averages. For a 5-year moving-average forecast, F_t^MA = (Y_{t-1} + Y_{t-2} + Y_{t-3} + Y_{t-4} + Y_{t-5}) / 5 for t = 6,...,13. For a 5-year weighted moving average, F_t^WMA = (6Y_{t-1} + 4Y_{t-2} + 2Y_{t-3} + 1Y_{t-4} + 1Y_{t-5}) / 14, aligned with years t-1 through t-5. Use the actual factory orders data provided in the prompt to compute the forecasts for year 13 under each approach, then compute MADs by comparing forecasts to the actual year 13 value. In practice, Excel or a calculator can perform these sums and divisions quickly, and the same structure applies to each forecast year t from 6 to 13; the only difference is the data window used. The model with the lower MAD is the preferred approach from a pure accuracy standpoint (Hyndman & Athanasopoulos, 2018; Makridakis et al., 1998). When presenting results, show both formulas and the numeric MADs to justify the model choice (Montgomery et al., 2015).

Question 3: Regression models for the 21-year new orders data. Fit a linear trend model y = a + b t, where t indexes the year, using Excel’s regression tools or chart trendline with the displayed equation and R-squared. Then fit a quadratic model y = a + b t + c t^2 and display its equation and R-squared. Compare models using R-squared, RMSE or MAE, and residual diagnostics. A better first pass is to look for substantial increases in R-squared and reductions in RMSE with the quadratic model, but guard against overfitting and changes in underlying dynamics (Chatfield, 2003; Box et al., 2015). Include the charts showing both fits with their respective formulas and R-squared values, and provide a short interpretation of which model is more appropriate for forecasting and why (Hyndman & Athanasopoulos, 2018).

Conclusion. The exercises reinforce a disciplined approach to forecasting: define the metric, compute on the available data, compare models using objective accuracy measures, and justify the final method for forecasting with data-driven reasoning. Excel is an appropriate tool for performing these calculations, visualizations, and model summaries, provided formulas and outputs are correctly interpreted (James et al., 2013; Wooldridge, 2013).

References

  • Hyndman, R. J., & Athanasopoulos, G. (2018). Forecasting: Principles and Practice. OTexts. https://otexts.com/fpp2/
  • Makridakis, S., Wheelwright, S. C., & Hyndman, R. J. (1998). Forecasting: Methods and Applications. Wiley.
  • Box, G. E. P., Jenkins, G. M., Reinsel, G. C., & Ljung, G. M. (2015). Time Series Analysis: Forecasting and Control (5th ed.). Wiley.
  • Chatfield, C. (2003). The Analysis of Time Series: An Introduction (6th ed.). CRC Press.
  • Montgomery, D. C., Jennings, C. L., & Kulahci, M. (2015). Introduction to Time Series Analysis and Forecasting. Wiley.
  • Pindyck, R. S., & Rubinfeld, D. L. (2012). Econometric Models and Economic Forecasting. Pearson.
  • James, G., Witten, D., Hastie, T., & Tibshirani, R. (2013). An Introduction to Statistical Learning. Springer.
  • Wooldridge, J. M. (2013). Introductory Econometrics: A Modern Approach. Cengage.
  • Ljung, G. M. (1999). System Identification: Theory for the User. Prentice Hall.
  • Armstrong, J. S. (2001). Principles of Forecasting: A Handbook for Researchers and Practitioners. Kluwer Academic Publishers.

Related Assignments