**Please show steps or use excel and show formulas** Forecasting: Choosing the M
ID: 2929987 • Letter: #
Question
**Please show steps or use excel and show formulas**
Forecasting: Choosing the Most Accurate Model
The problem below asks you to compare 4 different forecasting methodologies to determine which forecasting model results in the most accurate forecasts. Accuracy is determined by the lowest mean absolute deviation.
A toy company buys large quantities of plastic pellets for use in the manufacturing of its products. The production manager wants to develop a forecasting system for plastic pellet prices and is considering four different approaches and 6 different models. He plans to use historical data to test the different models for accuracy. The price per pound of plastic pellets (actual) has varied as shown:
Month
Price/Pound
1
$0.39
2
0.41
3
0.45
4
0.44
5
0.40
6
0.41
7
0.38
8
0.36
9
0.35
10
0.38
11
0.39
12
0.43
13
0.37
14
0.38
15
0.36
16
0.39
SIMPLE MOVING AVERAGE
Using an averaging period of 3, calculate the simple moving average for months 7-16. Calculate the MAD for this forecasting model.
Using an averaging period of 4, calculate the simple moving average for months 7-16. Calculate the MAD for this forecasting model.
WEIGHTED MOVING AVERAGE
Using an averaging period of 3 with the most recent period weighted .5, the next most recent .3, and the oldest period weighted .2, forecast months 7-16. Calculate the MAD for this forecasting model.
EXPONENTIAL SMOOTHING
Compute the forecasts for months 7-16 using = .1. Assume that the forecast for month 1 is .39. You will need to forecast months 2-6 to get to the starting forecast for month 7, however, only include months 7-16 in the calculation of the MAD.
Compute the forecasts for months 7-16 using = .3. Assume that the forecast for month 1 is .39. You will need to forecast months 2-6 to get to the starting forecast for month 7, however, only include months 7-16 in the calculation of the MAD.
SIMPLE LINEAR REGRESSION
Use all of the data, months 1-16, to calculate the regression equation for this data. Use the months (1-16) as the independent variable (x) and price as the dependent variable (y). Once you have the equation, forecast months 7-16 (enter 7, 8, etc. as the x value in the equation). Calculate the MAD for this forecasting model. Comment on the goodness of fit (R2) and significance of the model (F significance) to determine if this forecast model should be included in the consideration of the different approaches. (If the model is not significant, it cannot be considered, however, you must still make the forecasts and calculate the MAD).
Show your work in a table like the one below
SMA AP=3
SMA AP=4
WMA
ETC
Month
Actual
Forecast
Absolute Deviation
Forecast
Absolute Deviation
Forecast
Absolute Deviation
7
0.38
8
0.36
Month
Price/Pound
1
$0.39
2
0.41
3
0.45
4
0.44
5
0.40
6
0.41
7
0.38
8
0.36
9
0.35
10
0.38
11
0.39
12
0.43
13
0.37
14
0.38
15
0.36
16
0.39
Explanation / Answer
Simple Moving Average
Month Price/Pound SMA (3) SMA (4) Dev SMA (3) Dev SMA (4) Abs Dev SMA (3) Abs Dev SMA (4) 1 0.39 2 0.41 3 0.45 4 0.44 0.416666667 5 0.4 0.433333333 0.4225 6 0.41 0.43 0.425 7 0.38 0.416666667 0.425 -0.036666667 -0.045 0.036666667 0.045 8 0.36 0.396666667 0.4075 -0.036666667 -0.0475 0.036666667 0.0475 9 0.35 0.383333333 0.3875 -0.033333333 -0.0375 0.033333333 0.0375 10 0.38 0.363333333 0.375 0.016666667 0.005 0.016666667 0.005 11 0.39 0.363333333 0.3675 0.026666667 0.0225 0.026666667 0.0225 12 0.43 0.373333333 0.37 0.056666667 0.06 0.056666667 0.06 13 0.37 0.4 0.3875 -0.03 -0.0175 0.03 0.0175 14 0.38 0.396666667 0.3925 -0.016666667 -0.0125 0.016666667 0.0125 15 0.36 0.393333333 0.3925 -0.033333333 -0.0325 0.033333333 0.0325 16 0.39 0.37 0.385 0.02 0.005 0.02 0.005 MAD=> 0.030666667 0.0285 Weighted Moving Average Month Price/Pound WMA Dev WMA Abs Dev WMA 1 0.39 2 0.41 3 0.45 4 0.44 0.426 5 0.4 0.437 6 0.41 0.422 7 0.38 0.413 -0.033 0.033 8 0.36 0.393 -0.033 0.033 9 0.35 0.376 -0.026 0.026 10 0.38 0.359 0.021 0.021 11 0.39 0.367 0.023 0.023 12 0.43 0.379 0.051 0.051 13 0.37 0.408 -0.038 0.038 14 0.38 0.392 -0.012 0.012 15 0.36 0.387 -0.027 0.027 16 0.39 0.368 0.022 0.022 MAD=> 0.0286 Exponential Smoothing Month Price/Pound Exp Smo Dev Exp Smo Abs Dev Exp Smo 1 0.39 0.39 2 0.41 0.39 3 0.45 0.394 4 0.44 0.4052 5 0.4 0.41216 6 0.41 0.409728 7 0.38 0.4097824 -0.0297824 0.0297824 8 0.36 0.40382592 -0.04382592 0.04382592 9 0.35 0.395060736 -0.045060736 0.045060736 10 0.38 0.386048589 -0.006048589 0.006048589 11 0.39 0.384838871 0.005161129 0.005161129 12 0.43 0.385871097 0.044128903 0.044128903 13 0.37 0.394696877 -0.024696877 0.024696877 14 0.38 0.389757502 -0.009757502 0.009757502 15 0.36 0.387806002 -0.027806002 0.027806002 16 0.39 0.382244801 0.007755199 0.007755199 MAD=> 0.024402326 Regression Month Price/Pound Forecast Dev Abs_Dev 1 0.39 0.414632353 2 0.41 0.411764706 3 0.45 0.408897059 4 0.44 0.406029412 5 0.4 0.403161765 6 0.41 0.400294118 7 0.38 0.397426471 -0.017426471 0.017426471 8 0.36 0.394558824 -0.034558824 0.034558824 9 0.35 0.391691176 -0.041691176 0.041691176 10 0.38 0.388823529 -0.008823529 0.008823529 11 0.39 0.385955882 0.004044118 0.004044118 12 0.43 0.383088235 0.046911765 0.046911765 13 0.37 0.380220588 -0.010220588 0.010220588 14 0.38 0.377352941 0.002647059 0.002647059 15 0.36 0.374485294 -0.014485294 0.014485294 16 0.39 0.371617647 0.018382353 0.018382353 MAD=> 0.019919118Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.