Question

Question 2

A supply chain analyst is analyzing past sales data. He obtained the following numbers:

Week	Demand
1	2200
2	2100
3	2050
4	2120
5	2550
6	2340
7	2400
8	2370
9	2290
10	2020
11	2110
12	2105
13	2340
14	2410
15	2560

He thinks that three methods could be used to forecast future sales: 4-weeks simple moving average, linear regression and weighted moving average.

2.1       Using Excel, please run linear regression on this data. Based on the results you obtain, do you think that linear regression is appropriate in this case? Why?

2.2       For the weighted moving average, the analyst wants to calculate a four-month weighted moving average with weights 0.4 for the most recent month, 0.3 for 2 months ago, 0.2 for 3 months ago and 0.1 for 4 months ago. Please apply the weighted moving average method to calculate the sales forecast in weeks 5-15.

2.3        For the simple moving average, the analyst wants to calculate a four-month simple moving average, Please apply the simple moving average methods to calculate the sales forecast in weeks 5-15.

4. For each of the two methods above (simple moving average & weighted moving average), calculate MAD and MAPE. Comment on these values. Which method do you recommend and why?

Answer 1

2.1)linear regression

Excel > Data > Data Analysis > Regression

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.333823913
R Square	0.111438405
Adjusted R Square	0.043087513
Standard Error	173.1718746
Observations	15
ANOVA
	df	SS	MS	F	Significance F
Regression	1	48892.85714	48892.85714	1.630386986	0.223986121
Residual	13	389850.4762	29988.49817
Total	14	438743.3333
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	2158.619048	94.094352	22.94100551	6.68342E-12	1955.340559	2361.897536	1955.340559	2361.897536
Week	13.21428571	10.34899895	1.27686608	0.223986121	-9.143367233	35.57193866	-9.143367233	35.57193866

Demand = 2158.619 + 13.2143 * week

If week = 16,

Demand = 2158.619 + 13.2143 * 16 = 2370.048

MSE = 29988.498

R^2 = 0.1114

Adjusted R Square = 0.043, nearer to 0 which means worst fit

2.2)weighted moving average

Forecast = (Y1*0.1+Y2*0.2+Y3*0.3+Y4*0.4)/0.1+0.2+0.3+0.4

Week	Demand(Y)	Forecast(Y^)	e = Y-Y^	Abs erro = |Y-Y^|	e^2	Abs % = Abs error/Demand
1	2200
2	2100
3	2050
4	2120
5	2550	2103	447	447	199809	0.175294118
6	2340	2276	64	64	4096	0.027350427
7	2400	2330	70	70	4900	0.029166667
8	2370	2384	-14	14	196	0.005907173
9	2290	2391	-101	101	10201	0.044104803
10	2020	2341	-321	321	103041	0.158910891
11	2110	2209	-99	99	9801	0.046919431
12	2105	2145	-40	40	1600	0.019002375
13	2340	2108	232	232	53824	0.099145299
14	2410	2191.5	218.5	218.5	47742.25	0.0906639
15	2560	2298	262	262	68644	0.10234375
Average				169.8636364	45804.93	0.072618985
Weights	0.1
	0.2
	0.3
	0.4

MAD=169.86

MSE=45804.93

MAPE=7.26%

2.3)simple moving average

Week	Demand(Y)	Forecast(Y^)	e = Y-Y^	Abs error = |e|	e^2	Abs % = Abs error/Demand
1	2200
2	2100
3	2050
4	2120
5	2550	2117.5	432.5	432.5	187056.3	0.169607843
6	2340	2205	135	135	18225	0.057692308
7	2400	2265	135	135	18225	0.05625
8	2370	2352.5	17.5	17.5	306.25	0.007383966
9	2290	2415	-125	125	15625	0.054585153
10	2020	2350	-330	330	108900	0.163366337
11	2110	2270	-160	160	25600	0.075829384
12	2105	2197.5	-92.5	92.5	8556.25	0.043942993
13	2340	2131.25	208.75	208.75	43576.56	0.089209402
14	2410	2143.75	266.25	266.25	70889.06	0.110477178
15	2560	2241.25	318.75	318.75	101601.6	0.124511719
Average				201.9318182	54414.63	0.086623298

MAD=201.93

MSE=54414.63

MAPE=8.66%

MAD of weighted moving average < MAD of simple moving average

MSE of weighted moving average < MSE of simple moving average

SO, prefer weighted moving average and it is more effective