Question

Check My Work (3 remaining Consider the following gasoline sales time series data. Click on the datafile logo to reference the data Week Sales (1000s of gallons) 20 18 17 19 21 12 a. Using a weight of for the most recent observation, for the second most recent observation, and third the most recent abaervation, compute a threa-week weightad moving avarage fos the time series (to 2 decimals). Enter nagative values as negative numbers Weighted Moving Average Forecast (Error Time-Series Value Week ocator-assignment-take Type here to search Alt Weighted Moving Average Forecast Forecast Week Time Series Value (Error)? 10 12 b. Compute the MSE for the woighted maving average in part (a). MSE the MSE for the unweighted moving aversge is 7.17 Do you prefer this weighted moving average to the unweighted moving average? Ramember that Prafer the unweighted maving average hare; it has a Select yeur answeMSE weighted moving average than c. Suppoze you are allawed to chease for an unweighted moving average? 9 as they sum to 1. Could you always find a set of msights that would make the MSE at least es small for a e m s O Type here to search 2 Alt

Answer 1

We have done all the calculations in excel sheet

Following are the excel formulas we used in excel sheet for calculating weighted moving average, error and mean error square.

MAD MAPE Microsoft Excel Home sert Page Layout Formulas Data Review View XLSTAT File Ít Σ自自囱囟自自囱爺4fP Define Formula Trace Prep en dents圛 ung63 Trace Dependents ◆ Error Checking' ぶRemove Arrows. QEvaluate Formula use in Formula- Insert AutoSum Recently Financial Logical Text Date & Lookup &Math Function More Name Calculation Options . watch Window , Function Library Manager留Create from Selection Defined Name Time. Reference , &Trig-Functions , Used · ormula Auditing Calcu 124 2 WeekTime-Series Value Weighted Moving Average Forecast Forecast Error- (Actual -Forecast) ErrorA2 16 20 -E7 E7 -E8 E8 E9 E9 E10 E10 E11FE11 -E12 E12 -E13"E13 -E14 E14 -E15*E15 -C7-D7 C1/2+C61/3) C5(1/6) C9 (1/21+C8 (1/3HC7P(1/6) C11 (1/2)+C10 (1/3)+C9* (1/6) 18 -C8-D8 -C9-D9 17 9 C10-D10 C10 (1/2+9(1/3)+8 (1/6) C11-D11 C12-D12 -C12 (1/2)+C11 (1/3)+C10 (1/6) C13-D13 C13(1/2+C12(1/3)+11(1/6C14-D14 C14 (1/2+C13*(1/3)+C12 (1/6)C15-D15 10 19 21 12 13 14 15 16 17 18 10 15 21 Forecasted Value -C15*(1/2+C14 (1/3+C13 (1/6) Total SUMIG7:G15) MSE ErrorA2)/n AVERAGE(G7:G15) 13

Following is the result obtained:

week | Time-Series Value Weighted Moving Average Forecast | Forecast Error = (Actual-Forecast) ErrorA2 16 20 20 2 19.33 21.00 19.67 18.17 18.67 19.00 20.17 20.17 17.67 18.83 7.11 9.00 7.11 3.36 0.11 4.00 0.03 26.69 4 2.67 3.00 2.67 1.83 0.33 2.00 0.17 5.17 3.33 18 17 20 19 21 20 15 21 9 10 12 13 Forecasted Value Total 68.53 MSE (ZErrorA2)/n 7.61

Please note blue values are the answers for Part a)

Answer b)

MSE = 7.61 (Please check the red text in Error^2 column of result table.

Since MSE of unweighted moving average (7.17) is less than MSE of weighted moving average (7.61), so we prefer unweighted moving average.

Prefer the unweighted moving average here; it has a smaller MSE.

Answer c)

You could always find a weighted moving average at least as good as the unweighted one; actually the unweighted moving average is a special case of the weighted ones where the weights are equal.