Easy Business analysis project

decom10272015.xls

decomposition


Example of classical decomposition
		Moving	Centered	Raw
Quarter	Sales(Y)	Average	Average	Indices
1	50
2	100
3	70	75	76.25	0.9180327869
4	80	77.5	80	1
5	60	82.5	86.25	0.6956521739
6	120	90	90	1.3333333333
7	100	90	95	1.0526315789
8	80	100

		Forecasting : Decomposition of the Trend and Seasonality
											Seasonal
							Year 1	Year 2	Year 3	Year 4	Index
	This Moving Average is					Q1		0.88	0.84	0.90	0.87
	Centered Between Quarters 2 and 3.					Q2		0.89	0.88	0.96	0.91
	The next one is centered between 3 and 4,					Q3	0.97	1.03	1.01		1.00
	and so on.					Q4	1.25	1.22	1.17		1.21	4.0
			Moving	Centered	Raw		Seasonal		De-seas		Predicted	(Y - Y-hat)	=(25+28+35+50 ) /4 = 34.5
year	Quarter	Sales (Y)	Average	Average	Indices		Index		Sales (Yd)		Sales (Y-hat)	Error
1	1	25					0.8707372657		28.71		18.19	6.8	=(28+35+50+39 ) /4 = 38.0
1	2	28					0.9087799197		30.81		24.49	3.5
1	3	35	34.50	36.25	0.97		0.9997805244		35.01		32.99	2.0
1	4	50	38.00	40.00	1.25		1.2140993556		41.18		47.42	2.6	=(34.50+ 38.0 ) /2 = 36.25
2	5	39	42.00	44.50	0.88		0.8707372657		44.79		39.28	-0.3
2	6	44	47.00	49.50	0.89		0.9087799197		48.42		46.49	-2.5
2	7	55	52.00	53.63	1.03		0.9997805244		55.01		57.20	-2.2	= 35 / 36.25 = 0.97
2	8	70	55.25	57.25	1.22		1.2140993556		57.66		76.81	-6.8
3	9	52	59.25	62.00	0.84		0.8707372657		59.72		60.36	-8.4
3	10	60	64.75	68.50	0.88		0.9087799197		66.02		68.50	-8.5
3	11	77	72.25	76.38	1.01		0.9997805244		77.02		81.41	-4.4
3	12	100	80.50	85.50	1.17		1.2140993556		82.37		106.21	-6.2
4	13	85	90.50	94.75	0.90		0.8707372657		97.62		81.44	3.6
4	14	100	99.00	104.00	0.96		0.9087799197		110.04		90.50	9.5
4	15	111	109.00				0.9997805244		111.02		105.62	5.4
4	16	140					1.2140993556		115.31		135.61	4.4
												-0.1
												BIAS
										Calculated on output page
										and copied here.

Sales (Y)

Quarter

$ Million

Sales

Sales (Yd)

Quarter

$ Million

Deseasonlized Sales

SUMMARY OUTPUT
Regression Statistics
Multiple R	0.9797
R Square	0.9598
Adjusted R Square	0.9569
Standard Error	6.1049
Observations	16
ANOVA
	df	SS	MS	F	Significance F
Regression	1	12457.959	12457.959	334.267	0.000
Residual	14	521.774	37.270
Total	15	12979.733
	Coefficients	Standard Error	t Stat	P-value	Lower 95%	Upper 95%	Lower 95.0%	Upper 95.0%
Intercept	14.8419	3.2014	4.6360	0.0004	7.9755	21.7083	7.9755	21.7083
Quarter	6.0532	0.3311	18.2830	0.0000	5.3431	6.7633	5.3431	6.7633
					Reseasonalize the predicted Yd
RESIDUAL OUTPUT					Values to predict the true sales
	Deseasonalized			Seasonal	(Multiply by the seasonal index)
Observation	Predicted Sales (Yd Hat)	Residuals		Index	Predicted Y
1	20.8951	7.8162		0.871	18.19
2	26.9483	3.8623		0.909	24.49
3	33.0014	2.0062		1.000	32.99
4	39.0546	2.1282		1.214	47.42
5	45.1078	-0.3182		0.871	39.28
6	51.1610	-2.7444		0.909	46.49
7	57.2142	-2.2021		1.000	57.20
8	63.2674	-5.6115		1.214	76.81
9	69.3206	-9.6010		0.871	60.36
10	75.3737	-9.3512		0.909	68.50
11	81.4269	-4.4100		1.000	81.41
12	87.4801	-5.1145		1.214	106.21
13	93.5333	4.0851		0.871	81.44
14	99.5865	10.4512		0.909	90.50
15	105.6397	5.3847		1.000	105.62
16	111.6928	3.6190		1.214	135.61

Perform regression with desasonalized sales to get the underlying trend (Output Sheet)