Unit 5 [GB513: Business Analytics] Assignment This assignment ✓ Solved

This assignment requires you to use Excel. Question 1: Determine the error for each of the following forecasts. Compute MAD and MSE. Period Value Forecast Error 1 202 — — Question 2: The U.S. Census Bureau publishes data on factory orders for all manufacturing, durable goods, and nondurable goods industries. a. Use these data to develop forecasts for the years 6 through 13 using a 5-year moving average. b. Use these data to develop forecasts for the years 6 through 13 using a 5-year weighted moving average. Weight the most recent year by 6, the previous year by 4, the year before that by 2, and the other years by 1. c. Compute the errors of the forecasts in parts (a) and (b) and then the MAD. Which forecast is better? Question 3: Use Excel to develop a regression model to fit the trend effects for the given data. Use a linear model and then try a quadratic model. How well does either model fit the data?

Paper For Above Instructions

Introduction

The importance of accurate forecasting in business analytics cannot be overstated. Forecasting allows businesses to predict future trends and make informed decisions. This paper analyzes factory orders data using various forecasting methods, including moving averages and regression models. The analysis aims to determine the best forecasting method based on historical factory orders data in the United States.

Question 1: Error Calculation for Given Forecasts

The first step in evaluating the forecasting model involves calculating the Mean Absolute Deviation (MAD) and Mean Squared Error (MSE) for the provided forecast values. MAD is the average of the absolute differences between actual values and forecasted values, while MSE is the average of the squared differences. These metrics are essential for understanding the accuracy of the forecasting models.

Question 2: Forecast Development Using Moving Averages

According to the U.S. Census Bureau's factory orders data over a 13-year period, we can develop forecasts using different methods. The moving average and weighted moving average will be analyzed based on historical data to predict values for years 6 through 13. The actual order values (in billion dollars) for the years 1 through 5 are as follows:

  • Year 1: 2,512

  • Year 2: 739

  • Year 3: 874

  • Year 4: 934

  • Year 5: 865

a. 5-Year Moving Average Forecasts

The 5-year moving average is calculated by averaging the factory orders of the last five years. For years 6 to 13, the forecast values using moving averages would be based on the mean of the previous five years. For example, the forecast for year 6 would be calculated as follows:

Forecast for Year 6 = (Factory Orders Year 1 + Year 2 + Year 3 + Year 4 + Year 5) / 5

Thus, Forecast for Year 6 = (2512 + 739 + 874 + 934 + 865) / 5 = 979.6 billion.

Similar calculations will be done for subsequent years.

b. 5-Year Weighted Moving Average Forecasts

Using the weights of the respective years as given (recent year = 6, previous year = 4, year before that = 2, and others = 1), the 5-year weighted moving average will yield different forecast values.

For example, the weighted forecast for year 6 is calculated as follows:

Forecast for Year 6 = (6 Factory Orders Year 5 + 4 Factory Orders Year 4 + 2 Factory Orders Year 3 + 1 Factory Orders Year 2 + 1 * Factory Orders Year 1) / 14

This approach allows more recent values to have a larger impact on the forecast.

c. Forecast Error Calculation

Once the forecast values for both methods are established, the forecasting errors will be calculated, providing insights into the performance of each forecasting method. The errors can be quantified using both the MAD and MSE. The model with the smaller MAD and MSE will be determined as the better method. The definitive answer will depend on the computed errors.

Question 3: Regression Model Development

In the analysis of the manufacturers’ new and unfilled orders, a regression analysis will be conducted. This analysis will incorporate both linear and quadratic trends to gauge which model fits the data best.

Using Excel, the regression outputs will show R-squared values for both the linear and quadratic models. The model with the higher R-squared will indicate a better fit for the data.

Conclusion

The lessons gathered from this assignment highlight the importance of selecting the right forecasting methods and understanding their implications in business analytics. The results obtained from both the moving averages and regression analysis will guide strategic decisions based on accurate forecasts.

References

  • Hyndman, R. J., & Athanasopoulos, G. (2018). Forecasting: Principles and Practice (2nd ed.). OTexts.

  • Makridakis, S., Spiliotis, E., & Assimakopoulos, V. (2020). M4 Competition: Results, conclusions, and implications. International Journal of Forecasting.

  • Bowerman, B. L., & O'Connell, R. T. (2017). Forecasting Time Series and Linear Models (4th ed.). Duxbury Press.

  • Kourentzes, N., & Petropoulos, F. (2016). Forecasting with regression models: A review. International Journal of Forecasting.

  • Armstrong, J. S. (2001). Principles of Forecasting: A Handbook for Researchers and Practitioners. Springer.

  • Chatfield, C. (2000). The Analysis of Time Series: An Introduction with R (6th ed.). Chapman and Hall/CRC.

  • Shumway, R. H., & Stoffer, D. S. (2017). Time Series Analysis and Its Applications: With R Examples (4th ed.). Springer.

  • Box, G. E. P., Jenkins, G. M., & Reinsel, G. C. (2015). Time Series Analysis: Forecasting and Control (5th ed.). Wiley.

  • Hyndman, R. J., & Koehler, A. B. (2006). Another look at measures of forecast accuracy. International Journal of Forecasting.

  • Davis, J. R. (2002). Forecasting Methods for Management. Wiley & Sons.