Question

Consider using a simple moving average model. Experiment with models using five weeks’ and three weeks’ past data. The past data in each region are given below (week 21 is the week before week 1 in the table, 22 is two weeks before week 1, etc.). Evaluate the forecasts that would have been made over the 13 weeks using the overall (at the end of the 13 weeks) mean absolute deviation, mean absolute percent error, and tracking signal as criteria.

WEEK	-5	-4	-3	-2	-1
Atlanta	45	38	30	58	37
Boston	62	18	48	40	35
Chicago	62	22	72	44	48
Dallas	42	35	40	64	48
LA	43	40	54	46	35
Total	254	153	244	252	198

Short answer

Using the AVERAGE function in the excel sheet:

Step-by-step solution

By using an excel sheet, determine the 5-weeks and 3-weeks moving averages for the chart below

Using the AVERAGE function in the excel sheet:

By using an excel sheet, determine the 5-weeks and 3-weeks moving averages for the 14th week for the chart below

Using the AVERAGE function in the excel sheet:

Absolute Value, Average demand, mean absolute deviation, mean absolute percent error, and tracking signal

Absolute Value

It is the difference between the anticipated 5-week moving average using a 5-week and 13-week table and the actual 5-week moving average.

Deviation

It is the discrepancy according to what is forecasted and what's happening.

The running sum of forecast errors

It is referred to as a progressive deviation. For the 5-week data from the above table, median demand is calculated by averaging the 3 and 5-week moving average values (i. e. columns C and D respectively).

Add all of the (C-A) and (D-B) columns together to get the absolute values of 11 and 28.33, respectively.

Since there are 5 cities, so n = 5.

Now, the Mean absolute deviation for 3 weeks:

$$\begin{gathered} \text{Mean absolute deviation} \text{=} \frac{\sum \left|\text{A - F}\right|}{\text{n}} \\ \text{=} \frac{\text{28.33}}{\text{5}} \\ \text{=} \text{5.67} \end{gathered}$$

Now, the Mean absolute percent errorfor 3 weeks:

$$\begin{gathered} \text{Mean absolute percent error} \text{=} \frac{\text{Mean absolute deviation}}{\text{Average} \text{actual} \text{demand}} \\ \text{=} \frac{\text{5.67}}{\text{46.27}} \\ \text{=} \text{12.25\%} \end{gathered}$$

Now, tracking signalfor 3 weeks:

Running sum of forecast errors = 14.33

$$\begin{gathered} \text{Tracking} \text{signal} \text{=} \frac{\text{Running sum of forecast errors}}{\text{Mean absolute deviation}} \\ \text{=} \frac{\text{14.33}}{\text{5.67}} \\ \text{=} \text{2.53} \end{gathered}$$

Now, the Mean absolute deviationfor 5 weeks:

$$\begin{gathered} \text{Mean absolute deviation} \text{=} \frac{\sum \left|\text{A - F}\right|}{\text{n}} \\ \text{=} \frac{\text{11}}{\text{5}} \\ \text{=} 2.2 \end{gathered}$$

Now, the Mean absolute percent error for 5 weeks:

$$\begin{gathered} \text{Mean absolute percent error} \text{=} \frac{\text{Mean absolute deviation}}{\text{Average} \text{actual} \text{demand}} \\ \text{=} \frac{2.2}{44.04} \\ \text{=} 4.99\text{\%} \end{gathered}$$

Now, tracking signalfor 5 weeks:

Running sum of forecast errors =-8.6

$$\begin{gathered} \text{Tracking} \text{signal} \text{=} \frac{\text{running sum of forecast errors}}{\text{Mean absolute deviation}} \\ \text{=} \frac{-\text{8.6}}{2.2} \\ \text{=} -3.91 \end{gathered}$$