The ABC Company has provided us with the following data on the demand for their
ID: 386702 • Letter: T
Question
The ABC Company has provided us with the following data on the demand for their product (stated in 1000 units) during the last 8 years. Years 1 2 3 4 5 6 7 8 Demand 5.0 8.3 13.9 16.2 15.4 18.6 16.4 17.5 A)- Use a three-year moving average to forecast demand for year 9. If the actual demand for year 9 is 18.3, forecast the demand for year 10. B)- Predict the demand for years 9 and 10, using the Exponential smoothing method. Do those with two different values of , ( =0,35, and =060). Which value of results in better forecast? C)- Use linear regression to forecast the demand for years 9 and 10.
Explanation / Answer
Please refer below table which captures all relevant calculations:
Years
Demand
Forecast ( 3 year moving average)
Forecast ( alpha = 0.35)
Absolute Deviation ( basis alpha = 0.35)
Forecast ( alpha = 0.60)
Absolute deviation ( basis alpha = 0.60)
Forecast ( Linear regression method)
1
5
5.00
5.00
14.40
2
8.3
5.00
5.00
19.25
3
13.9
6.16
6.98
27.48
4
16.2
9.07
8.87
7.33
11.13
5.07
30.86
5
15.4
12.80
11.43
3.97
14.17
1.23
29.69
6
18.6
15.17
12.82
5.78
14.91
3.69
34.39
7
16.4
16.73
14.84
1.56
17.12
0.72
31.16
8
17.5
16.80
15.39
2.11
16.69
0.81
32.78
9
18.3
17.50
16.13
2.17
17.18
1.12
33.95
10
17.40
16.89
17.85
SUM =
22.92
12.64
Answer to question a :
Forecast using 3 year moving average :
Ft = ( Dt-1 + Dt-2 + Dt-3 ) / 3
Ft = Forecast value
Dt-1, Dt-2, Dt-3 = demand for period t-1, t-2 and t-3 respectively
Forecasted demand for year 10 = 17.40
Answer to question b :
Smoothing constant = 0.35
Smoothing constant = 0.60
Demand for Year 9
16.13
17.18
Demand for year 10
16.89
17.85
Mean absolute deviation ( MAD) basis forecast using exponential smoothing constant of 0.35
= 22.92/6( i.e. corresponding number of observations )
= 3.82
Mean absolute deviation ( MAD ) basis forecast using exponential smoothing constant of 0.6 = 12.64 / 6 ( i.e. corresponding number of observations )
= 2.11 ( rounded to 2 decimal places )
A lower MAD indicates better forecast accuracy. Therefore , forecast using exponential smoothing constant of 0.6 results in better foreacst
Answer to question # c :
Let the linear regression equation is :
Y = a + b.X
Y ( dependent variable ) = Forecasted demand
X ( independent variable ) = Year number
A, b = constants
We place values of year and corresponding demand ( as provided ) in two adjacent columns in excel and apply the formula LINEST ( ) and obtain following values of a and b :
A = 7.05
B = 1.47
Therefore ,
Y = 7.05 + 1.47.X
Forecast values basis linear regression equation are placed in excel using above formula
Forecast for year 9 = 32.78
Forecast for year 10 = 33.95
Years
Demand
Forecast ( 3 year moving average)
Forecast ( alpha = 0.35)
Absolute Deviation ( basis alpha = 0.35)
Forecast ( alpha = 0.60)
Absolute deviation ( basis alpha = 0.60)
Forecast ( Linear regression method)
1
5
5.00
5.00
14.40
2
8.3
5.00
5.00
19.25
3
13.9
6.16
6.98
27.48
4
16.2
9.07
8.87
7.33
11.13
5.07
30.86
5
15.4
12.80
11.43
3.97
14.17
1.23
29.69
6
18.6
15.17
12.82
5.78
14.91
3.69
34.39
7
16.4
16.73
14.84
1.56
17.12
0.72
31.16
8
17.5
16.80
15.39
2.11
16.69
0.81
32.78
9
18.3
17.50
16.13
2.17
17.18
1.12
33.95
10
17.40
16.89
17.85
SUM =
22.92
12.64
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.