A convenience store recently started to carry a new brand of soft drink. Managem
ID: 340251 • Letter: A
Question
A convenience store recently started to carry a new brand of soft drink. Management is interested in estimating future sales volume to determine whether it should continue to carry the new brand or replace it with another brand. The following table provides the number of cans sold per week. Use both the trend projection with regression and the exponential smoothing? (let alpha=0.4 with an initial forecast for week 1 of 600) methods to forecast demand for week 13.
Compare these methods by using the mean absolute deviation and mean absolute percent error performance criteria. Does your analysis suggest that sales are trending and if? so, by how? much?
?(i) Obtain the trend projection with regression forecast.
The forecast for week 13 is ________. ?(Enter your response rounded to the nearest whole? number.)
Specify the mean absolute deviation? (MAD) and mean absolute percent error? (MAPE).?(Enter your responses rounded to two decimal? places.)
MAD
MAPE
____%
_____%
?(ii)Obtain the exponential smoothing forecast.
The forecast for week 13 is _______. ?(Enter your response rounded to the nearest whole? number.)
Specify the mean absolute deviation? (MAD) and mean absolute percent error? (MAPE).?(Enter your responses rounded to two decimal? places.)
MAD
MAPE
______%
______%
MAD
MAPE
____%
_____%
Period Sales 2 4 7 9 10 12 600 632652745 649 624 724 687 731 724 684 756Explanation / Answer
Let the linear regression equation be :
Y = A +B.X
Y ( dependent variable ) = Sales forecast
X ( Independent variable ) = period
A, B = Constants
We place all the values of X and Y in 2 adjacent columns in excel and apply the formula LINEST ( ) and obtain following values of X and Y
A = 619.636 and B = 9.902
Therefore ,
Y = 619.636 + 9.902.X
Therefore forecast for week 13 ( by putting X = 13 ) = 619.636 + 9.902x13 = 619.636+128.726= 748.362 ( 748 rounded to nearest whole number)
FOECAST FOR WEEK 13 AS PRE REGRESSION EQUATION METHOD = 748
For exponential smoothing method :
Ft = alpha x At-1 + ( 1 – alpha) x Ft-1
Where,
Ft , Ft-1 = forecasts for period t and t-1 respectively
At-1 = Actual sales for period t-1
Alpha = exponential smoothing constant = 0.4
Therefore ,
Ft = 0.4 x At-1 + 0.6 x Ft-1
Using Forecast for period 1 = 600 , forecasted values are presented in below table
Accordingly forecast for period 13 = 722.36 ( 722 rounded to nearest whole number )
FORECAST FOR PERIOD 13 = 722
Following may be noted :
Absolute deviation = Absolute difference in values of actual sales and forecasted sales
Mean absolute deviation ( MAD ) = Sum of absolute deviation / Number of data
Absolute percentage error = Absolute deviation /Actual sales x 100
Mean Absolute Percentage Error ( MAPE) = Sum of absolute percentage error/Number of data
The calculations for Absolute deviation and absolute Percentage error are presented below :
Period
Sales
Regression forecast
Absolute Deviation
Absolute percentage error
Exponential smoothing forecast
Absolute deviation
Absolute percentage error
1
600
629.54
29.54
4.92
600
0.00
0.00
2
632
639.44
7.44
1.18
600
32.00
5.06
3
652
649.34
2.66
0.41
612.80
39.20
6.01
4
745
659.24
85.76
11.51
628.48
116.52
15.64
5
649
669.15
20.15
3.10
675.09
26.09
4.02
6
624
679.05
55.05
8.82
664.65
40.65
6.51
7
724
688.95
35.05
4.84
648.39
75.61
10.44
8
687
698.85
11.85
1.73
678.64
8.36
1.22
9
731
708.75
22.25
3.04
681.98
49.02
6.71
10
724
718.66
5.34
0.74
701.59
22.41
3.10
11
684
728.56
44.56
6.51
710.55
26.55
3.88
12
756
738.46
17.54
2.32
699.93
56.07
7.42
13
748.36
722.36
TOTAL :
337.18
49.13
492.49
70.01
Thus MAD for regression forecast = 337.18 / 12 = 28.098 ( 280.10 rounded to 2 decimal places)
MAPE for regression forecast = 49.13/12 = 4.094 ( 4.09 rounded to 2 decimal places )
MAD for Exponential smoothing forecast = 492.49/12 = 41.04
MAPE for exponential smoothing forecast = 70.01 / 12 = 5.83
FOECAST FOR WEEK 13 AS PRE REGRESSION EQUATION METHOD = 748
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.