Consider the data in the spreadsheet.
1. Use the three forecast methods: single moving average of period 3, single exponential smoothing with smoothing constant 0.5, and a third-order autoregressive model, to forecast the data for the next month.
2. For each of the three methods, calculate MAD and determine which is the best method.
3. For each of the three methods, calculate MSE and determine which is the best method.
Steps to calculate the 3 month moving average.
In Microsoft Excel 2007, locate the Data Analysis button on the Data tab. You will select Moving Average in the drop down list. Click the OK button. Enter the Input Range of the CPI column B5 to B132. Enter the Output Range. Enter the interval as 3. Press the OK button.
Steps to calculate the Error
Subtract the CPI from the calculated 3 month moving average B7-C7. Drag the formula down to cell D132.
Steps to calculate the Absolute Value
In cell E7, enter =abs(D7). Drag the formula down to cell E132.
Steps to Calculate the Squared Error
In cell F7, enter =(D7^2)). Drag the formula down to cell F132.
Steps to calculate the Mean Absolute Deviation
In cell E3, enter =Average(E7:E134). The MAD is 0.57353. This is the average of the absolute value.
Steps to calculate the Mean ...
The expert examines forecast methods and calculations of MAD/MSE.