Question

Module 9 Homework MINDTAP work Exercise 17.08 Algorithmic Consider the following gasoline sales time series data. Click on the datafile logo to reference the data. Week Sales (1000s of gallons) 18 21 18 24 18 16 20 17 21 21 14 21 8 10 12 a. Using a weight of 7 for the most recent observation, for the second most recent observation, and third the m average for the time series (to 2 decimals). Enter negative values as negative numbers. Weighted Moving Average Forecast Forecast Week Time-Series Value (Error)2 Error 2,087 20 100000000 1000000000 E | MINDTAP moving average? 20

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 Insert Page Layout Formulas Data ReviewViewXLSTAT File Define Name ▼ Trace Precedents Show Formulas P Use in FormulaTrace Dependents Error Checking Calc ) Insert AutoSum Recently Financial Logical Text Date & Lookup & Math More Name Manager İ Watch Window 水Remove Arrows , Q Evaluate Formula Formula Auditing Create from Selection Time-Reference ▼ & Trig-Functions- Function Used ▼ Function Library Defined Names H20 week | Time-Series Value l weighted Moving Average Forecast | Forecast Error (Actual-Forecast ErrorA2 18 21 18 24 18 16 20 17 21 21 1114 21 6 C7-D7 E7 ET -E8 E8 E9 E9 E10 E10 E11 E11 E12 E12 C10-D10 C11-D11 C11 (1/2+C10 (1/3)+C9*(1/6)C12-D12 C12 (1/2)+C11 (1/3+C10 (1/6)C13-D13 C13 (1/2+C12(1/3)+C11/6-C14-D14 C14 (1/2)+C13 (1/3)+012 (1/6)C15-D15 10 C10 (1/2+C9"(1/3)+C8 (1/6) 12 13 14 15 16 17 E13 E13 10 E14 E14 -E15 E15 12 13 Forecasted Value -C15 (1/2)+C14*(1/3)+C13 (1/6) Total SUMIG7:G15) MSE = (EErrorAZYn I-AVERAGE(G7:G15)

Following is the result obtained:

ErrorA2 Week Time-Series Value Weighted Moving Average Forecast Forecast Error (Actual -Forecast) 18 21 18 24 18 16 20 17 21 21 14 21 2 25.00 12.25 16.00 4.00 1.78 10.03 2.25 40.11 12.25 123.67 5.00 3.50 -4.00 2.00 1.33 3.17 1.50 -6.33 3.50 19.00 21.50 20.00 18.00 18.33 17.83 19.50 20.33 17.50 18.67 4 9 10 12 Total 13 Forecasted Value MSE (ZErrorA2)/n 13.74

Please note blue values are the answers for Part a)

Answer b)

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

Since MSE of unweighted moving average (11.43) is less than MSE of weighted moving average (13.74), 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.