Question

Month	Price per Chip	Month	Price per Chip
January	$1.80	July	1.80
February	1.67	August	1.83
March	1.70	September	1.70
April	1.85	October	1.65
May	1.90	November	1.70
June	1.87	December	1.75

1. Use a 3-month moving average and add the 3-month plot to the graph created in part (a).

2. Which is better (using the mean absolute deviation): the 2-month average or the 3-month average?

3. Compute the forecasts for each month using exponential smoothing, with an initial forecast for January of $1.80. Use α = .1, then α = .3, and finally α = .5. Using MAD, which a is the best?

Answer 1

a. 3 months moving average:

It is calculated as Simple Moving Average (SMA) for n month = Average of (n, n-1, n-2) months

Excel formulation

A B С D 1 Month Price per Chip 3 months moving average 5 5 2 3 4 5 6 7 8 9 10 11 12 13 14 15 January February March April May June July August September October November December 1.8 1.67 1.7 1.85 1.9 1.87 1.8 1.83 1.7 1.65 1.7 1.75 =AVERAGE(C3:05) =AVERAGE(C4:C6) =AVERAGE(C5:07) =AVERAGE(C6:08) =AVERAGE(C7:09) =AVERAGE(C8:C10) =AVERAGE(C9:C11) =AVERAGE(C10:C12) =AVERAGE(C11:C13) =AVERAGE(C12:C14) LO Mean =AVERAGE(C3:C14) =AVERAGE(D5:014) 17

A B С D 1 Month Price per Chip 3 months moving average 2 3 4 5 6 1.723 1.740 8 7 8 9 10 11 January February March April May June July August September October November December 1.8 1.67 1.7 1.85 1.9 1.87 1.8 1.83 1.7 1.65 1.7 1.75 9 1.817 1.873 1.857 1.833 1.777 12 1.727 1.683 1.700 13 14 15 16 17 Mean 1.768 1.773

Graph for Price per Chip and 3 months moving average

Price per Chip & Moving Averages 1.95 1.9 1.85 1.8 1.75 Price per Chip 1.7 1.65 1.6 1.55 1.5 April May June July October January March August February September November December Month -Price per Chip 3 months moving average

b. MAD for 2 & 3 months moving average:

Based on the approach discussed in 3 month SMA, 2 month SMA & respective MAD is calculated

Excel Formulation

A B D E F G Н. J K 1 2 3 months 2 months Month Price per Chip Absolute Deviation Moving Average Absolute Deviation Moving Average Absolute Deviation 1.8 1.67 1.7 1.85 1.9 1.87 3 4. 5 6 7 8 9 10 11 12 13 14 15 16 17 January February March April May June July August September October November December =ABS($D$17-D4) =ABS($D$17-05) =ABS($D$17-06) =ABS($D$17-07) =ABS($D$17-08) =ABS($D$17-09) =ABS($D$17-D10) =ABS($D$17-D11) =ABS($D$17-D12) =ABS($D$17-D13) =ABS($D$17-D14) =ABS($D$17-D15) 1.8 1.83 1.7 1.65 1.7 1.75 =AVERAGE(D4:06) =ABS($G$17-G6) =AVERAGE(D5:07) =ABS($G$17-G7) =AVERAGE(D6:08) =ABS($G$17-G8) =AVERAGE(D7:09) =ABS($G$17-G9) =AVERAGE(D8:010) =ABS($G$17-G10) =AVERAGE(D9:D11) =ABS($G$17-G11) =AVERAGE(D10:012) |=ABS($G$17-12) =AVERAGE(D11:D13) =ABS($G$17-613) =AVERAGE(D12:D14) =ABS($G$17-614) =AVERAGE(D13:D15) =ABS($G$17-G15) =AVERAGE(G4:46) =ABS($J$17-J6) =AVERAGE(G5:G7) =ABS($J$17-J7) =AVERAGE(66:68) =ABS($J$17-18) =AVERAGE(G7:49) =ABS($J$17-19) =AVERAGE(G8:G10) =ABS($J$17-J10) =AVERAGE(G9:G11) =ABS($J$17-J11) =AVERAGE(G10:G12) =ABS($J$17-J12) =AVERAGE(G11:G13) =ABS($J$17-J13) =AVERAGE(G12:G14) =ABS($J$17-J14) =AVERAGE(G13:G15) |=ABS($J$17-J15) Mean =AVERAGE(D4:D15) =AVERAGE(G6:G15) =AVERAGE(J6:J15) Mean Absolute deviation 18 =AVERAGE(E4:E15) =AVERAGE(H6:H15) =AVERAGE(K6:K15)

A B с D E F G H - J K L 1 3 months 2 months N Month Price per Absolute Chip Deviation Moving Absolute Average Deviation Moving Absolute Average Deviation 3 4 5 6 7 8 9 January February March April May June July August September October November December 1.8 1.67 1.7 1.85 1.9 1.87 1.8 1.83 1.7 1.65 1.7 1.75 0.032 0.098 0.068 0.082 0.132 0.102 0.032 0.062 0.068 0.118 0.068 0.018 10 1.723 1.740 1.817 1.873 1.857 1.833 1.777 1.727 1.683 1.700 0.050 0.033 0.044 0.100 0.084 0.060 0.004 0.046 0.090 0.073 1.723 1.732 1.760 1.810 1.849 1.854 1.822 1.779 1.729 1.703 0.053 0.044 0.016 0.034 0.073 0.078 0.046 0.003 0.047 0.073 11 12 13 14 15 16 17 1.768 1.773 1.776 Mean Mean Absolute deviation 0.073 0.058 0.047 18 19 20 21

MAD for 2 months SMA (0.047) is lower than 3 months SMA (0.058).

So, 2 months SMA is better option.

c. Exponential Smoothening:

This method gives higher weightage to latest value and decreases weightage exponentially as we move older values.

Forecast (n month) = a * (n-1) + (1-a) * (n-1)

Excel formulation

A B C D E F G - H 1 0.1 0.5 0.3 Mean Deviation 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 a 0.1 0.3 0.5 Period Actual Forecast January 1.8 February 1.67 =$C$4 =$C$4 =$C$4 March 1.7 =D$2* $C5+(1-D$2)* D5 =E$2*$C5+(1-E$2)* E5 =F$2* $C5+(1-F$2)* F5 April 1.85 =D$2* $C6+(1-D$2)* D6 =E$2* $C6+(1-E$2)*E6 =F$2* $C6+(1-F$2)* F6 May 1.9 =D$2* $C7+(1-D$2)* D7 =E$2* $C7+(1-E$2)*E7 =F$2* $C7+(1-F$2)*F7 June =D$2*$C8+(1-D$2)*D8 =E$2*$C8+(1-E$2)*E8 =F$2*$C8+(1-F$2)*F8 July 1.8 =D$2* $C9+(1-D$2)* D9 =E$2* $C9+(1-E$2)* E9 =F$2*$C9+(1-F$2)*F9 August 1.83 =D$2*$C10+(1-D$2)*D10 =E$2*$C10+(1-E$2)*E10 = $2*$C10+(1-F$2)*F10 September 1.7 =D$2*$C11+(1-D$2)*D11 |=E$2*$C11+(1-E$2)*E11 =F$2*$C11+(1-F$2)* F11 October 1.65 =D$2*$C12+(1-D$2)*D12 =E$2*$C12+(1-E$2)*E12 =F$2*$C12+(1-F$2)* F12 November 1.7 =D$2*$C13+(1-D$2)*D13 =E$2*$C13+(1-E$2)*E13 =F$2*$C13+(1-F$2)* F13 December 1.75 =D$2*$C14+(1-D$2)*D14 =E$2*$C14+(1-E$2)*E14 =F$2*$C14+(1-F$2)*F14 January =D$2*$C15+(1-D$2)*D15 =E$2*$C15+(1-E$2)*E15 =F$2*$C15+(1-F$2)* F15 1.87 =ABS(D$18-05) =ABS(D$18-06) =ABS(D$18-D7) =ABS(D$18-D8) =ABS(D$18-09) =ABS(D$18-D10) =ABS(D$18-D11) =ABS(D$18-D12) =ABS(D$18-D13) =ABS(D$18-D14) =ABS(D$18-D15) =ABS(D$18-D16) =ABS(E$18-E5) =ABS(E$18-56) =ABS(E$18-E7) =ABS(E$18-E8) =ABS(E$18-E9) =ABS(E$18-E10) =ABS(E$18-E11) =ABS(E$18-E12) =ABS(E$18-E13) =ABS(E$18-E14) =ABS(E$18-E15) =ABS(E$18-E16) =ABS(F$18-F5) =ABS(F$18-F6) =ABS(F$18-F7) =ABS(F$18-F8) =ABS(F$18-F9) =ABS(F$18-F10) =ABS(F$18-F11) =ABS(F$18-F12) =ABS(F$18-F13) =ABS(F$18-F14) =ABS(F$18-F15) =ABS(F$18-F16) Mean =AVERAGE(D5:016) =AVERAGE(E5:E16) =AVERAGE(F5:F16) =AVERAGE(H5:H16) =AVERAGE(15:116) =AVERAGE(J5:16)

A B с D E F G H - J к 1 a 0.1 0.5 0.5 N 0.3 Forecast 0.1 0.3 Mean Deviation 3 4. 5 0.010 0.026 0.039 6 7 0.057 0.009 8 Period January February March April May June July August September October November December January Actual 1.8 1.67 1.7 1.85 1.9 1.87 1.8 1.83 1.7 1.65 1.7 1.75 1.800 1.787 1.778 1.785 1.797 1.804 1.804 1.806 1.796 1.800 1.761 1.743 1.775 1.812 1.830 1.821 1.824 9 10 11 12 13 14 15 16 17 18 19 20 21 1.800 1.735 1.718 1.784 1.842 1.856 1.828 1.829 1.764 1.707 1.704 1.727 0.003 0.012 0.005 0.007 0.014 0.014 0.016 0.006 0.009 0.017 0.019 0.019 0.020 0.038 0.006 0.032 0.049 0.040 0.043 0.006 0.035 0.049 0.043 0.067 0.082 0.054 0.055 0.010 1.781 1.786 1.746 1.732 0.067 0.071 1.773 1.771 1.737 0.048 Mean 1.790 1.781 1.774 0.011 0.032 0.049

Based on above table values,

MAD for a = 0.1 is lowest among three. Hence 0.1 is the best value for a.

If you like the answer please give an up-vote, this will be quite encouraging for me, thank you.

If you have further suggestions in answer, please write below on the comment section, I will certainly look into it.