Study about Improvement of Forecasting Future Students' Academic ...
Sample of Forecasting
-
Upload
liew-ee-hoong -
Category
Documents
-
view
238 -
download
1
Transcript of Sample of Forecasting
Exponential Smoothing
Period (t) 1 2 3 4 5 6 7 8 9
Actual Demand 11155 4593 2866 3095 7487 7580 3221 1947 2104
Forecast, alpha factor of 0.5 6000 8578 6585 4726 3910 5699 6639 4930 3439
ME - -3985 -3719 -1631 3577 1881 -3418 -2983 -1335
MAD - 3985 3719 1631 3577 1881 3418 2983 1335
MAPE - 87% 130% 53% 48% 25% 106% 153% 63%
Step 1:
Exponential smoothing formula: = ( α * the previous week demand) + ( (1 - α) * the previous week forecast)
C5 = (0.5*B4)+((1-0.5)*B5)
Step 2:
Mean error: = Actual Demand - Forecasted DemandC6 = C4-C5
Step 3:
Mean Absolute Deviation = ABS (Mean Error)C7 = ABS(C6)
Step 4:
Mean Absolute Percentage Error: = Mean Absolute Deviation / Actual Demand (format as a percentage to 1 decimal place)C8 = C7/C4
Step 5: Copy the formulas across to find the forecast, ME, MAD, MAPE until week 52.
Step 6: Move to sheet Exercise 2, using the tabs below
Use the exponential smoothing formula to calculate the forecast for week 2. Use an alpha factor
of 0.5.
Find the mean error, this is the difference between the actual demand and the demand
forecasted
Find the mean absolute deviation, this is the absolute difference between the actual demand
and the demand forecasted. Therefore all deviations are positive.
Find the mean absolute percentage error.
0.867515785
10 11 12 13 14 15 16 17 18 19 20 21 22
7184 2968 1845 1994 5190 1845 2222 2400 8723 4489 2800 3026 5855
2771 4978 3973 2909 2451 3821 2833 2527 2464 5593 5041 3921 3473
4413 -2010 -2128 -915 2739 -1976 -611 -127 6259 -1104 -2241 -895 2382
4413 2010 2128 915 2739 1976 611 127 6259 1104 2241 895 2382
61% 68% 115% 46% 53% 107% 27% 5% 72% 25% 80% 30% 41%
( α * the previous week demand) + ( (1 - α) * the previous week forecast)
0.868
Mean Absolute Deviation / Actual Demand (format as a percentage to 1 decimal place)
Copy the formulas across to find the forecast, ME, MAD, MAPE until week 52.
Correct
Use the exponential smoothing formula to calculate the forecast for week 2. Use an alpha factor
of 0.5.
Find the mean error, this is the difference between the actual demand and the demand
forecasted
Find the mean absolute deviation, this is the absolute difference between the actual demand
and the demand forecasted. Therefore all deviations are positive.
Find the mean absolute percentage error.
Correct
Correct
Correct
23 24 25 26 27 28 29 30 31 32
13118 5400 3370 3640 14710 6057 3778 2041 7671 6317
4664 8891 7146 5258 4449 9579 7818 5798 3920 5795
8454 -3491 -3776 -1618 10261 -3522 -4040 -3757 3751 522
8454 3491 3776 1618 10261 3522 4040 3757 3751 522
64% 65% 112% 44% 70% 58% 107% 184% 49% 8%
33 34 35 36 37 38 39 40 41
3941 4257 8238 14917 6142 3832 4140 13588 5595
6056 4999 4628 6433 10675 8408 6120 5130 9359
-2115 -742 3610 8484 -4533 -4576 -1980 8458 -3764
2115 742 3610 8484 4533 4576 1980 8458 3764
54% 17% 44% 57% 74% 119% 48% 62% 67%
42 43 44 45 46 47 48 49 50
3491 3770 9121 11745 4836 3017 3260 10033 4130
7477 5484 4627 6874 9310 7073 5045 4152 7093
-3986 -1714 4494 4871 -4474 -4056 -1785 5881 -2963
3986 1714 4494 4871 4474 4056 1785 5881 2963
114% 45% 49% 41% 93% 134% 55% 59% 72%
51 52
2577 278
5611 4094
-3034 -3816
3034 3816
118% 1373%
Exponential Smoothing and 3 Month Moving Average Exercise
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
A B C D E F G H I J K L M N O P Q R S T U V W X Y Z AA AB AC AD AE AF AG AH AI AJ AK AL AM AN AO AP AQ AR AS AT AU AV AW AX AY AZ BA
Exponential Smoothing
Period (t) 1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52
Actual Demand 11155 4593 2866 3095 7487 7580 3221 1947 2104 7184 2968 1845 1994 5190 1845 2222 2400 8723 4489 2800 3026 5855 13118 5400 3370 3640 14710 6057 3778 2041 7671 6317 3941 4257 8238 14917 6142 3832 4140 13588 5595 3491 3770 9121 11745 4836 3017 3260 10033 4130 2577 278
Forecast 6000 7547 6660 5522 4794 5602 6195 5303 4296 3639 4702 4182 3481 3035 3681 3130 2858 2721 4521 4512 3998 3706 4351 6981 6507 5566 4988 7905 7350 6279 5007 5806 5960 5354 5025 5989 8667 7910 6686 5922 8222 7434 6251 5507 6591 8137 7147 5908 5114 6589 5852 4869
ME - -2954 -3794 -2427 2693 1978 -2974 -3356 -2192 3545 -1734 -2337 -1487 2155 -1836 -908 -458 6002 -32 -1712 -972 2149 8767 -1581 -3137 -1926 9722 -1848 -3572 -4238 2664 511 -2019 -1097 3213 8928 -2525 -4078 -2546 7666 -2627 -3943 -2481 3614 5154 -3301 -4130 -2648 4919 -2459 -3275 -4591
MAD - 2954 3794 2427 2693 1978 2974 3356 2192 3545 1734 2337 1487 2155 1836 908 458 6002 32 1712 972 2149 8767 1581 3137 1926 9722 1848 3572 4238 2664 511 2019 1097 3213 8928 2525 4078 2546 7666 2627 3943 2481 3614 5154 3301 4130 2648 4919 2459 3275 4591
MAPE - 64% 132% 78% 36% 26% 92% 172% 104% 49% 58% 127% 75% 42% 100% 41% 19% 69% 1% 61% 32% 37% 67% 29% 93% 53% 66% 31% 95% 208% 35% 8% 51% 26% 39% 60% 41% 106% 62% 56% 47% 113% 66% 40% 44% 68% 137% 81% 49% 60% 127% 1652%
Alpha Factor 0.3
-13516 160876 5023%
EXERCISE 2: Change the alpha factor to values between 0 and 1. Try to find the best alpha factor.
0
2000
4000
6000
8000
10000
12000
14000
16000
1 4 7 10 13 16 19 22 25 28 31 34 37 40 43 46 49 52
Dem
an
d
Week
Actual Demand Forecast Linear (Forecast)
Exercise 2
Reflection:
Question 1a) Is exponential smoothing a good technique for using in the aerial simulation game?
Question 1b) Why? More react to recent changes rather than all past observation.
Question 2 What could you do to improve the accuracy of the forecast?
Is exponential smoothing a good technique for using in the aerial simulation game? No
Exponential Smoothing (monthly)
Period (t) 1 2 3 4 5 6 7 8
Actual Demand 21709 20235 14101 12014 15626 20949 27777 19807
Forecast 21661 21676 21244 19101 16975 16570 17884 20852
ME - -1441 -7143 -7087 -1349 4379 9893 -1045
MAD - 1440.723 7142.506 7086.795 1349.031 4379.099 9893.264 1044.715
MAPE - 7.1% 50.7% 59.0% 8.6% 20.9% 35.6% 5.3%
Alpha Factor 0.3
EXERCISE 4: Change the alpha factor to values between 0 and 1. Try to find the best alpha factor.
0
5000
10000
15000
20000
25000
30000
35000
1 2 3 4 5 6 7 8 9 10
De
man
d
Month
Actual Demand Forecast
9 10 11 12 13
31353 27702 21977 22858 17018
20538 23783 24958 24064 23702
10815 3919 -2981 -1206 -6684
10814.7 3919.29 2981.497 1206.048 6684.234
34.5% 14.1% 13.6% 5.3% 39.3%
Change the alpha factor to values between 0 and 1. Try to find the best alpha factor.
11 12 13
3 Month Moving Average
Period (t) 1 2 3 4 5 6 7 8 9 10
Actual History 11155 4593 2866 3095 7487 7580 3221 1947 2104 7184
3 month moving average - - - 6204.7 3518 4483 6054 6096 4249 2424
ME - - - -3110 3969 3097 -2833 -4149 -2145 4760
MAD - - - 3110 3969 3097 2833 4149 2145 4760
MAPE - - - 100% 53% 41% 88% 213% 102% 66%
182818 5697%
Step 1:Three month average: = (Month 1 Demand + Month 2 Demand + Month 3 Demand) / 3
E5 = SUM(B4:D4)/3
]
Step 2:
Mean error: = Actual Demand - Forecasted DemandE6 = E4 - E5
Step 3:
Mean Absolute Deviation = ABS (Mean Error)E7 = ABS(E6)
Step 4:Mean Absolute Percentage Error: = Mean Absolute Deviation / Actual Demand (format as a percentage to 1 decimal place)
E8 = E7/E4
Step 5: Copy the formulas across to find the forecast, ME, MAD, MAPE until week 52.
Find the average of the previous three months
Find the mean error, this is the difference between the actual demand and the demand
forecasted
Find the mean absolute deviation, this is the absolute difference between the actual
demand and the demand forecasted. Therefore all deviations are positive.
Find the mean absolute percentage error.
11 12 13 14 15 16 17 18 19 20 21 22 23 24 25
2968 1845 1994 5190 1845 2222 2400 8723 4489 2800 3026 5855 13118 5400 3370
3745 4085 3999 2269 3010 3010 3086 2156 4448 5204 5337 3438 3894 7333 8124
-777 -2240 -2005 2921 -1165 -788 -686 6567 41 -2404 -2311 2417 9224 -1933 -4754
777 2240 2005 2921 1165 787.7 685.7 6567 40.67 2404 2311 2417 9224 1933 4754
26% 121% 101% 56% 63% 35% 29% 75% 1% 86% 76% 41% 70% 36% 141%
(Month 1 Demand + Month 2 Demand + Month 3 Demand) / 3
Mean Absolute Deviation / Actual Demand (format as a percentage to 1 decimal place)
Copy the formulas across to find the forecast, ME, MAD, MAPE until week 52.
Find the average of the previous three months
Find the mean error, this is the difference between the actual demand and the demand
forecasted
Find the mean absolute deviation, this is the absolute difference between the actual
demand and the demand forecasted. Therefore all deviations are positive.
Find the mean absolute percentage error.
26 27 28 29 30 31 32 33 34 35 36 37 38 39 40
3640 14710 6057 3778 2041 7671 6317 3941 4257 8238 14917 6142 3832 4140 13588
7296 4137 7240 8136 8182 3959 4497 5343 5976 4838 5479 9137 9766 8297 4705
-3656 10573 -1183 -4358 -6141 3712 1820 -1402 -1719 3400 9438 -2995 -5934 -4157 8883
3656 10573 1183 4358 6141 3712 1820 1402 1719 3400 9438 2995 5934 4157 8883
100% 72% 20% 115% 301% 48% 29% 36% 40% 41% 63% 49% 155% 100% 65%
41 42 43 44 45 46 47 48 49 50 51 52
5595 3491 3770 9121 11745 4836 3017 3260 10033 4130 2577 278
7187 7774 7558 4285 5461 8212 8567 6533 3704 5437 5808 5580
-1592 -4283 -3788 4836 6284 -3376 -5550 -3273 6329 -1307 -3231 -5302
1592 4283 3788 4836 6284 3376 5550 3273 6329 1307 3231 5302
28% 123% 100% 53% 54% 70% 184% 100% 63% 32% 125% 1907%
Aerials demand data:
Period Previous Year Current Year1 11155 12500
2 4593 5200
3 2866 3600
4 3095 4800
5 7487 6000
6 7580 8500
7 3221 4700
8 1947 3200
9 2104 3200
10 7184 7600
11 2968 3300
12 1845 2400
13 1994
14 5190
15 1845
16 2222
17 2400
18 8723
19 4489
20 2800
21 3026
22 5855
23 13118
24 5400
25 3370
26 3640
27 14710
28 6057
29 3778
30 2041
31 7671
32 6317
33 3941
34 4257
35 8238
36 14917
37 6142
38 3832
39 4140
40 13588
41 5595
42 3491
43 3770
44 9121
45 11745
46 4836
47 3017
48 3260
49 10033
50 4130
51 2577
52 278