Excel assignment, due 11/20

profileJJJQQQZZZ
Class_Exercise_3_answers.xlsx
Home>Business & Finance homework help>Operations Management homework help>Excel assignment, due 11/20

Data

Period Month Sales
1 Jan 37
2 Feb 40
3 Mar 41
4 Apr 37
5 May 45
6 June 50
7 July 43
8 Aug 47
9 Sep 56
10 Oct 52
11 Nov 55
12 Dec 54

Moving Average

Question: What is the forecast for next January? Period Month Sales 3 Mo. MA Error Absolute Error Square Error 4 Mo. MA Error Absolute Error Square Error
Calculate it using 3-months and 4 months average 1 Jan 37
2 Feb 40
3 Mar 41
4 Apr 37 39.3333333333 -2.3333333333 2.3333333333 5.4444444444
5 May 45 39.3333333333 5.6666666667 5.6666666667 32.1111111111 38.75 6.25 6.25
6 June 50 41 9 9 81 40.75 9.25 9.25
7 July 43 44 -1 1 1 43.25 -0.25 0.25
8 Aug 47 46 1 1 1 43.75 3.25 3.25
9 Sep 56 46.6666666667 9.3333333333 9.3333333333 87.1111111111 46.25 9.75 9.75
10 Oct 52 48.6666666667 3.3333333333 3.3333333333 11.1111111111 49 3 3
11 Nov 55 51.6666666667 3.3333333333 3.3333333333 11.1111111111 49.5 5.5 5.5
12 Dec 54 54.3333333333 -0.3333333333 0.3333333333 0.1111111111 52.5 1.5 1.5
53.6666666667 Mean Absolute Value Mean Square Error 54.25 Mean Absolute Value MSE
January 3.9259259259 25.5555555556 January 4.84375 33.9765625
25.5555555556

Exponantial Smoothing

Question: What is the forecast for next January? alpha 0.3
Calculate it using alpha=0.3 and 0.5 Period Month Sales Forecast error abs
1 Jan 37 37
2 Feb 40 37 3 3
3 Mar 41 37.9 3.1 3.1
4 Apr 37 38.83 -1.83 1.83
5 May 45 38.281 6.719 6.719
6 June 50 40.2967 9.7033 9.7033
7 July 43 43.20769 -0.20769 0.20769
8 Aug 47 43.145383 3.854617 3.854617
9 Sep 56 44.3017681 11.6982319 11.6982319
10 Oct 52 47.81123767 4.18876233 4.18876233
11 Nov 55 49.067866369 5.932133631 5.932133631
12 Dec 54 50.8475064583 3.1524935417 3.1524935417
51.7932545208 Mean Absolute Value MSE
January 4.8532934912 34.1528985121

Exponantial Smoothing (2)

Question: What is the forecast for next January? alpha 0.5
Calculate it using alpha=0.3 and 0.5 Period Month Sales Forecast error abs
1 Jan 37 37
2 Feb 40 37 3 3
3 Mar 41 38.5 2.5 2.5
4 Apr 37 39.75 -2.75 2.75
5 May 45 38.375 6.625 6.625
6 June 50 41.6875 8.3125 8.3125
7 July 43 45.84375 -2.84375 2.84375
8 Aug 47 44.421875 2.578125 2.578125
9 Sep 56 45.7109375 10.2890625 10.2890625
10 Oct 52 50.85546875 1.14453125 1.14453125
11 Nov 55 51.427734375 3.572265625 3.572265625
12 Dec 54 53.2138671875 0.7861328125 0.7861328125
53.6069335938 Mean Absolute Value MSE
January 4.0364879261 24.6443881122

Comparison

Fill out the following table and display the data series on a line graph Models MAD MSE
Moving Avg 3 Months 3.92593 25.55556
Moving Avg 4 Months 4.84375 33.97656
Exp Smooth. Alpha 0.3 4.85329 34.15290
Exp Smooth. Alpha 0.5 4.03649 24.64439
Month Original Data Moving Avg 3 Months Moving Avg 4 Months Exp Smooth. Alpha 0.3 Exp Smooth. Alpha 0.5
Jan 37 37 37
Feb 40 37 37
Mar 41 37.9 38.5
Apr 37 39.3333333333 38.83 39.75
May 45 39.3333333333 38.75 38.281 38.375
June 50 41 40.75 40.2967 41.6875
July 43 44 43.25 43.20769 45.84375
Aug 47 46 43.75 43.145383 44.421875
Sep 56 46.6666666667 46.25 44.3017681 45.7109375
Oct 52 48.6666666667 49 47.81123767 50.85546875
Nov 55 51.6666666667 49.5 49.067866369 51.427734375
Dec 54 54.3333333333 52.5 50.8475064583 53.2138671875

Original Data Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec 37 40 41 37 45 50 43 47 56 52 55 54 Moving Avg 3 Months Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec 39.333333333333336 39.333333333333336 41 44 46 46.666666666666664 48.666666666666664 51.666666666666664 54.333333333333336 Moving Avg 4 Months Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec 38.75 40.75 43.25 43.75 46.25 49 49.5 52.5 Exp Smooth. Alpha 0.3 Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec 37 37 37.9 38.83 38.280999999999999 40.296700000000001 43.207689999999999 43.145382999999995 44.301768099999997 47.811237669999997 49.067866369000001 50.847506458299996 Exp Smooth. Alpha 0.5 Jan Feb Mar Apr May June July Aug Sep Oct Nov Dec 37 37 38.5 39.75 38.375 41.6875 45.84375 44.421875 45.7109375 50.85546875 51.427734375 53.2138671875