Question

Date	Tempreture
21st june	39
22nd june	37
23rd june	34
24th june	42
25th june	42
26th june	41
27th june	41
28th june	43
29th june	42
30th june	41
1st july	43
2nd july	43
3rd july	41
4th july	42
5th july	40
6th july	39
7th july	43
8th july	43
9th july	44
10th july	45
11th july	46
12th july	47
13th july	46
14th july	46
15th july	44
16th july	46
17th july	44
18th july	44
19th july	44
20th july	42
21st july	42

Question 2. Using a moving average with three periods (3MA), determine the forecast for 22d of July, 2020. Recalculate a moving average with five periods (5MA). Record your results into two separated columns: “3MA” and “5MA”.

Question 3. Forecast the weather using exponential smoothing method 22d of July, 2020. Assume an initial forecast for 21st of June, 2020 is the same as the actual temperature for 21st of June, 2020, use α = 0.4. Recalculate for A= 0.6. Two additional columns should be generated: “Forecast alpha 0.4” and “Forecast alpha 0.6”.

Question 4. Compute MAD for all previously obtained forecasts (additional columns should be created).

Question 5. Compute MSE for all previously obtained forecasts (additional columns should be created).

Question 6. Based on ONE of the results in questions 4 OR 5, select the best forecasting technique. Provide explanation.

Question 7. Which time series method provided a superior forecast? Why?

Answer 1

Steps for calculating moving averages in Excel:

1. Go to tab "Data".

2. Then click on "Data Analysis".

3. Select "Moving Average" then click OK.

4. Select the "Input range" and "Interval: 3 for 3MA or 5 for 5MA.

5.Enyer the "Output range".

6.Click OK.

Steps for Forcasting using Exponential Smoothing in Excel:

1. Go to tab "Data".

2. Then click on "Data Analysis".

3. Select "Exponential Smoothing" then click OK.

4. Select the " Input Range" and "Damping factor : (1 - alpha) .

5. Select "Output Range".

6. Click OK.

The Excel OUTPUT is given Below:

40 1 25th N o Р Q R S T U V w х Z AA AB 1 Date Temperature ЗМА SMA Forcast alpha 0.4 Forcast alpha 0.6 Error for 3MA JError for SMA Error for forcast a=0.4 Error for a=0.6 Error2 for 3MA Error^2 for 5MA Error 2 for forcast a=0.4 Error^2 for forcast a=0.6 2 21st june 39 #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A #N/A 3 22nd june 37 #N/A #N/A 39 39 #N/A #N/A 2 2 #N/A #N/A 4 4 4 23rd june 34 #N/A #N/A 38.2 37.8 #N/A #N/A 4.2 3.8 #N/A #N/A 17.64 14.44 5 24th june 42 36.6667 #N/A 36.52 35.52 5.333333333 #N/A 5.48 6.48 28.44444444 #N/A 30.0304 41.9904 6 42 37.6667 #N/A 38.712 39.408 4.333333333 #N/A 3.288 2.592 18.77777778 #N/A 10.810944 6.718464 una 7 26th June 41 39.3333 38.8 40.0272 40.9632 1.666666667 2.2 0.9728 0.0368 2.777777778 4.84 0.94633984 0.00135424 es wers 0.000 www 8 27th June 41 41.6667 39.2 40.41632 40.98528 0.666666667 1.8 0.58368 0.01472 0.444444444 3.24 0.340682342 0.000216678 14 9 28th june 43 41.3333 40.649792 40.994112 1.666666667 3 2.350208 2.005888 2.777777778 9 5.523477643 12 4.023586669 *** 000 29th june 444 10 One 42 41.6667 41.8 42.1976448 41.5898752 0.333333333 0.2 0.4101248 0.1976448 0.111111111 0.04 0.168202352 0.039063467 . 11 ---- 30th June 0203 41 421 41.8 41.75392512 42.07905792 1 0.8 0.75392512 1.07905792 1 0.64 0.568403087 1.164365995 wan 100 12 1st july 43 42" 41.6 41.45235507 41.43162317 1 1.4 1.547644928 1.568376832 1 1.96 2.395204823 2.459805887 var 13 2nd july 43 42 42 42.07141304 42.37264927 1 1 0.928586957 0.627350733 1 0.862273736 0.393568942 1 2001 14 3rd july 41 42.3333 42.4 42.44284783 42.74905971 1.333333333 1.4 1.442847826 1.749059707 1.777777778 1.96 2.081809849 3.059209858 We 104 15 mer 4th july 42 42.3333 42 41.69962388 0.333333333 41.8657087 0 0.134291304 0.300376117 0.111111111 0 0.018034154 0.090225812 04 --- 2014 16 5th july 40 42 42 41.91942522 41.87984955 2 2 1.919425217 1.879849553 4 4 3.684193165 3.533834342 raccian 20 17 6th july 39 41 418 41.15165513 40.75193982 2 2.8 2.15165513 1.751939821 4 7.84 4.6296198 3.069293137 220 * 18 7th july 43 40.3333 41 40.29099308 39.70077593 2.666666667 2 2.709006922 3.299224072 7.111111111 4 7.338718502 10.88487947 wi ***** ce 19 8th july 43 40.6667 41 41.37459585 41.68031037 2.333333333 2 007 1.625404153 1.319689629 5.444444444 4 2.641938661 1.741580716 V roca * 20 9th july 44 41.6667 41.4 42.02475751 42.47212415 2.333333333 2.6 1.975242492 1.527875851 5.444444444 6.76 3.901582902 Wewe 2.334404617 coroa 21 45 10th july 43.3333 41.8 42.8148545 43.38884966 1.666666667 3.2 2.185145495 2.777777778 2 1.611150341 10.24 4.774860835 2.59580542 weg - AMAMONO Annot 22 461 2007 11th july 44 42.8 43.6889127 44.35553986 2 3.2 2.311087297 1.644460136 4 10.24 5.341124495 2.70424914 or 23 471 ra 12th july 45 44.2 44.61334762 45.34221595 2 2.8 2.386652378 1.657784054 4 7.84 5.696109575 2.748247971 . 24 467 13th july 46 45 45.56800857 46.33688638 0 1 0.431991427 * 0.336886378 0 1 0.186616593 0.113492432 25 14th july 46 46.3333 45.6 45.74080514 46.13475455 0.333333333 0.4 0.259194856 0.134754551 0.111111111 0.16 0.067181973 0.018158789 26 15th july 44 46.3333 200440 46 45.84448309 46.05390182 2.333333333 2 1.844483086 2.053901821 5.444444444 4 3.402117856 4.218512688 27 16th july 46' 45.3333 45.8 45.10668985 44.82156073 0.666666667 0.2 0.893310148 1.178439272 0.444444444 0.04 0.798003021 1.388719117 20 17th july 28 44' 45.3333 45.8 45.46401391 45.52862429 1.333333333 1.8 1.464013911 1.528624291 1.777777778 3.24 2.143336732 2.336692224 29 18th july 45.2 44.87840835 44.61144972 0.666666667 1.2 0.878408347 0.611449717 0.444444444 1.44 0.771601223 0.373870756 30 30 19th july 44' 44.6667 44.8 44.52704501 44.24457989 0.666666667 0.8 0.527045008 0.244579887 0.444444444 0.64 0.27777644 0.059819321 31 31 20th july 42 44 44.4 44.316227 44.09783195 2 A 2.316227005 2.4 2.097831955 4 5.76 5.364907538 4.40089891 32 21st july 42 43.3333 44 43.3897362 42.83913278 1.333333333 2 1.389736203 0.839132782 1.777777778 4 1.931366714 0.704143826 33 22nd july 42.6667 43.2 42.83384172 42.33565311 34 44' 44.6667

N Р w Q 36.6667 42 5 6 42' 37.6667 7 41 39.3333 8 9 41' 41.6667 1 29 10 431 431 43 41.3333 42 41.6667 41 42 42 42 41 42.3333 42' 42.3333 42 41 43 40.3333 29 40 391 o 24th june 25th june 26th june 27th June 28th June 29th june 30th june 1st july 2nd july 3rd july 4th july 5th july 6th july 7th july 8th july 9th july 10th july 11th july 12th july 13th july 14th july 15th july 16th july 17th july 18th july 19th july 20th july 21st july 22nd july R #N/A #N/A 38.8 39.2 40 41.8 41.8 41.6 42 42.4 42 42 41.8 41 41 41.4 41.8 42.8 44.2 45 45.6 46 45.8 45.8 45.2 44.8 44.4 44 43.2 T 35.52 5.333333333 39.408 4.333333333 40.9632 1.666666667 40.98528 0.666666667 00 40.994112 1.666666667 44 42.1976448 0.333333333 42.07905792 2020 24 41.43162317 1 42.37264927 1 42.74905971 1.333333333 41.69962388 0.333333333 41.87984955 2 40.75193982 2 39.70077593 2.666666667 ware 41.68031037 2.333333333 42.47212415 2.333333333 100 43.38884966 1.666666667 44.35553986 2 . 45.34221595 2 46.33688638 0 Ince 46.13475455 0.333333333 mes 46.05390182 2.333333333 44.82156073 0.666666667 ARDEN 45.52862429 1.333333333 MA 44100 44.61144972 0.666666667 22 44.24457989 0.666666667 44.09783195 2 42.83913278 1.333333333 42.33565311 S 36.52 38.712 40.0272 40.41632 40.649792 41.5898752 41.75392512 41.45235507 42.07141304 20140240 42.44284783 2004 41.8657087 41.91942522 41.15165513 40.29099308 41.37459585 42.02475751 42.8148545 43.6889127 44.61334762 45.56800857 45.74080514 45.84448309 00 45.10668985 45.46401391 44.87840835 44.52704501 44.316227 43.3897362 42.83384172 V #N/A #N/A 2.2 1.8 3 0.2 0.8 1.4 1 1.4 0 2 2.8 2 2 2.6 3.2 3.2 2.8 1 0.4 2 0.2 1.8 1.2 0.8 2.4 2 10 11 12 13 14 15 16 17 18 19 a 20 21 22 23 24 ar 25 26 27 28 29 30 31 32 33 34 35 36 37 38 X 6.48 2.592 0.0368 0.01472 2.005888 0.1976448 con 1.07905792 1.568376832 0.627350733 1.749059707 OSTA 0.300376117 1.879849553 1.751939821 3.299224072 LOCK 1.319689629 1.527875851 1.611150341 1.644460136 1.657784054 0.336886378 Terra 0.134754551 2.053901821 1.178439272 AOC 1.528624291 -4444023 0.611449717 0.244579887 2.097831955 0.839132782 5.48 3.288 0.9728 coace 0.58368 2.350208 0.4101248 ca 0.75392512 100 1.547644928 0.928586957 1.442847826 0.134291304 21 1.919425217 Darren 2.15165513 2.709006922 1.625404153 1.975242492 orar 2.185145495 . 2.311087297 2.386652378 re 0.431991427 0.259194856 1.844483086 0.893310148 an 1.464013911 0.878408347 0.527045008 2.316227005 1.389736203 43' 40.6667 z #N/A #N/A 4.84 3.24 9 0.04 0.64 1.96 1 1.96 0 4 7.84 4 4 6.76 10.24 10.24 7.84 1 0.16 4 0.04 3.24 1.44 0.64 5.76 4 28.44444444 18.77777778 2.777777778 0.444444444 2.777777778 444 0.111111111 1 1 1 1.777777778 0.111111111 4 . 4 7.111111111 5.444444444 5.444444444 2.777777778 4 . 4 0 1494999 0.111111111 5.444444444 0.444444444 1.777777778 0.444444444 0.444444444 4 1.777777778 AA 30.0304 10.810944 0.94633984 2C 0.340682342 Nein 5.523477643 0.168202352 0.568403087 10 2.395204823 24 0.862273736 2.081809849 0.018034154 3.684193165 4.6296198 2990740cc 7.338718502 2.641938661 3.901582902 INC 4.774860835 AMC 5.341124495 --- 5.696109575 0.186616593 40404 0.067181973 1004420 3.402117856 0.798003021 2.143336732 T470421 0.771601223 0.27777644 5.364907538 1.931366714 AB 41.9904 6.718464 0.00135424 0.000216678 can een 4.023586669 es 0.039063467 cisco 1.164365995 ar 2.459805887 0.393568942 3.059209858 0.090225812 3.533834342 -1425 3.069293137 10.88487947 1 rooms 1.741580716 2.334404617 ora 2.59580542 2.70424914 2.748247971 440100425 0.113492432 para 0.018158789 Paco 4.218512688 1.388719117 2.336692224 0.373870756 0.059819321 4.40089891 0.704143826 ar 44 45 41.6667 43.3333 44 461 471 46 45 46 46 46.3333 223 44 46.3333 223 46 45.3333 44 45.3333 44 44.6667 44' 44.6667 42' 44 42 43.3333 42.6667 1.607142857 1.7 1.7120046 1.538961607 MAD MSE 3.908730159 3.764615385 4.277894262 4.053562148

6. Based on MAD , forcasting using Exponential Smoothing with alpha = 0.4 is best as it has minimum MAD among all other methods.

7. Comapring both MAD and MSE for all forcasting methods, the 5 moving average is the best.

minimum the MSE and MAD the best is the method.