Exercise questions 8,9,10,12, and 13 using the EXCEL spreadsheet attached Questions are attachment...

Question

Exercise questions 8,9,10,12, and 13 using the EXCEL spreadsheet attached Questions are attachment in chapter 6 from pages 29-34

C1P2 CSIS 405 Chapter 6: Time-Series Decomposition 1. Homework: Exercises 8, 9,10, 12, and 13 For this homework, choose "Decomposition" as Forecast Method Exercise 8: a. The forecast = (CMAT)(SI)(CF)(I) For example, the forecast for the 1st quarter equals (12.315+0.37 * (21))*1.24*1.01*1=25.155 Exeercise 9: Open Forecast X, choose "Decomposition" as forecast . For "Type", choose "Multiplicative" > Reports > Audit > Finish You will have data for answering a., b., and c.

chapter-6-np1moor3.pdf hintsforexercise62-j45umliw-5rask1rb.xls

Sanchi · Accepted Answer

C1P2
		CSIS 405
		Chapter 6: Time-Series Decomposition
		1. Homework: Exercises 8, 9,10, 12, and 13
				For this homework, choose "Decomposition" as Forecast Method
			Exercise 8:
				a. The forecast = (CMAT)(SI)(CF)(I)
				For example, the forecast for the 1st quarter equals
				(12.315+0.37 * (21))*1.24*1.01*1=25.155
			Exeercise 9:
			Open Forecast X, choose "Decomposition" as forecast .  For "Type", choose "Multiplicative" > Reports > Audit > Finish
			You will have data for answering a., b., and c.
Question8
	CMAT =	12.315+ 0.196(T)
	(a)	Prepare a forecast for each quarter of the coming year
	Quarter	T	CF	SI	CMAT= 12.315 + 0.196 (T)	Forecast = CMAT * SI* CF
	1	21	1.01	1.27	16.431	21.0760437
	2	22	1.04	1.02	16.627	17.6379216
	3	23	1.06	0.73	16.823	13.0176374
	4	24	1.04	0.98	17.019	17.3457648
	The forecast for the four quarters are 21.07, 17.63,13.01,17.34 respectively
	(b)
	Formula to be used
	MAPE =			where		actual sales for period i
						sales forecast
					n	no. of periods which is 4
	Quarter	T	CF	SI	CMAT= 12.315 + 0.196 (T)	F_i	A_i	(A_i - F_i)	(A_i - F_i)/A_i
	1	21	1.01	1.27	16.43	21.08	17.2	-3.88	0.23
	2	22	1.04	1.02	16.63	17.64	13.2	-4.44	0.34
	3	23	1.06	0.73	16.82	13.02	10.8	-2.22	0.21
	4	24	1.04	0.98	17.02	17.35	14.2	-3.15	0.22
								MAPE	0.25
	The mean absolute percentage error for the forecast period will be 0.25
√1/N ∑▒〖abs(〖(A_i  -F_i)/(A_i  ))〗^ 〗
A_i
F_i
Question9
	(a)
	Period	Customer (Y)	Time Index (X)	MA4	CMA	CMAT	SF	CF
	Feb-12	3.5	1
	May-12	2.9	2
	Aug-12	2	3	2.9	3.0	2.902	0.672	1.025
	Nov-12	3.2	4	3.1	3.1	3.098	1.028	1.005
	Feb-13	4.1	5	3.2	3.3	3.293	1.247	0.998
	May-13	3.4	6	3.4	3.5	3.489	0.986	0.989
	Aug-13	2.9	7	3.5	3.6	3.685	0.797	0.987
	Nov-13	3.6	8	3.8	3.9	3.880	0.920	1.008
	Feb-14	5.2	9	4.1	4.1	4.076	1.276	1.000
	May-14	4.5	10	4.1	4.2	4.272	1.068	0.986
	Aug-14	3.1	11	4.3	4.4	4.467	0.699	0.993
	Nov-14	4.5	12	4.6	4.6	4.663	0.976	0.989
	Feb-15	6.1	13	4.7	4.8	4.859	1.261	0.996
	May-15	5	14	5	5.2	5.054	0.964	1.026
	Aug-15	4.4	15	5.4		5.250
	Nov-15	6	16			5.446
	slope	0.1956730769
	intercept	2.3149038462
	Formulaes Used
	MA4	(Y_t-2 + Y_t-1 + Y_t + Y_t+1)/4
	CMA	(MA_t + MA_t+1)/2
	CMAT	intercept + slope *( time_index)
	SF	Y/CMA
	CF	CMA/CMAT
	(b)
	Seasonal Index for each quarter
	SI_Feb	(SF_Feb13 + SF_Feb14 +SF_Feb15)/3
		1.261
	SI_May	(SF_May13 + SF_May14 +SF_May15)/3
		1.006
	SI_Aug	(SF_Aug12 + SF_Aug13 +SF_Aug14)/3
		0.723
	SI_Nov	(SF_Nov12 + SF_Nov13 +SF_Nov14)/3
		0.975
	Therefore, the seasonal indices for Feb, May, Aug, Nove are 1.261,1.006,0.723,0.975 respectively
	©
	Period	Customer (Y)	Time Index (X)	MA4	CMA	CMAT	SI	CF	1	TSD Forecast = CMAT *SI*CF
	Feb-12	3.5	1				1.261		1
	May-12	2.9	2				1.006		1
	Aug-12	2	3	2.9	3.0	2.902	0.723	1.025	1	2.150925
	Nov-12	3.2	4	3.1	3.1	3.098	0.975	1.005	1	3.0346875
	Feb-13	4.1	5	3.2	3.3	3.293	1.261	0.998	1	4.1455375
	May-13	3.4	6	3.4	3.5	3.489	1.006	0.989	1	3.4707
	Aug-13	2.9	7	3.5	3.6	3.685	0.723	0.987	1	2.6299125
	Nov-13	3.6	8	3.8	3.9	3.880	0.975	1.008	1	3.8146875
	Feb-14	5.2	9	4.1	4.1	4.076	1.261	1.000	1	5.138575
	May-14	4.5	10	4.1	4.2	4.272	1.006	0.986	1	4.237775
	Aug-14	3.1	11	4.3	4.4	4.467	0.723	0.993	1	3.2083125
	Nov-14	4.5	12	4.6	4.6	4.663	0.975	0.989	1	4.4971875
	Feb-15	6.1	13	4.7	4.8	4.859	1.261	0.996	1	6.1000875
	May-15	5	14	5	5.2	5.054	1.006	1.026	1	5.218625
	Aug-15	4.4	15	5.4	5.5	5.250	0.723	1.006	1	3.81744
	Nov-15	6	16	5.6	5.6	5.446	0.975	0.999	1	5.304
	Feb-16		17	5.6	5.6	5.641	1.261	0.990	1	7.042685
	May-16		18	5.7	5.7	5.837	1.006	0.988	1	5.79959
	Aug-16		19	5.8		6.033	0.723	1.007	1	4.392225
	Nov-16		20			6.228	0.975	1.000	1	6.07265625
	(d)
	Quarters	Forcast_value	Observed_value	(O-F)/O
	Feb-16	7.0427	6.8	0.0357
	May-16	5.7996	5.1	0.1372
	Aug-16	4.3922	4.7	0.0655
	Nov-16	6.0727	6.5	0.0657
			MAPE	0.0760
	€'
Question9
	
Customer (Y)
CMA
CMAT
TSD Forecast = CMAT *SI*CF
Time series
Question10
		Quarter	Period (t)	Sales	MA	CMA	Seasonal Factors	SI	CMAT	CF	One	TSD	Error	abs error	abs error %	deseasonlised data	Trend  (Y=5.178-0.019T)	Forecast
		Mar-05	1	6.44				1.27			1					6.44	5.159
		Jun-05	2	4.85				0.85			1					4.85	5.14
		Sep-05	3	4.67	5.43	5.41	0.86	0.80	5.27	1.03	1	4.323	0.347	0.347	7.44%	3.81	5.121	5.9850148011
		Dec-05	4	5.77	5.38	5.30	1.09	1.09	5.25	1.01	1	5.763	0.007	0.007	0.13%	4.68	5.102	6.1901659595
		Mar-06	5	6.22	5.23	5.16	1.21	1.27	5.23	0.99	1	6.560	-0.340	0.340	5.46%	5.01	5.083	6.2881344151
		Jun-06	6	4.25	5.10	5.04	0.84	0.85	5.21	0.97	1	4.265	-0.015	0.015	0.35%	3.41	5.064	5.9070448797
		Sep-06	7	4.14	4.99	4.97	0.83	0.80	5.20	0.96	1	3.974	0.166	0.166	4.01%	3.31	5.045	5.8782075472
		Dec-06	8	5.34	4.95	4.96	1.08	1.09	5.18	0.96	1	5.395	-0.055	0.055	1.02%	4.26	5.026	6.1017995467
		Mar-07	9	6.07	4.98	4.97	1.22	1.27	5.16	0.96	1	6.315	-0.245	0.245	4.04%	4.85	5.007	6.2286352201
		Jun-07	10	4.36	4.96	5.02	0.87	0.85	5.15	0.98	1	4.249	0.111	0.111	2.55%	3.49	4.988	5.8560935789
		Sep-07	11	4.07	5.09	5.08	0.80	0.80	5.13	0.99	1	4.066	0.004	0.004	0.10%	3.27	4.969	5.7695901156
		Dec-07	12	5.84	5.08	5.07	1.15	1.09	5.11	0.99	1	5.507	0.333	0.333	5.70%	4.69	4.95	6.1024420326
		Mar-08	13	6.06	5.05	5.07	1.20	1.27	5.10	0.99	1	6.442	-0.382	0.382	6.31%	4.86	4.931	6.1265610358
		Jun-08	14	4.24	5.09	5.03	0.84	0.85	5.08	0.99	1	4.258	-0.018	0.018	0.43%	3.40	4.912	5.7543143779
		Sep-08	15	4.2	4.98	5.05	0.83	0.80	5.06	1.00	1	4.035	0.165	0.165	3.93%	3.37	4.893	5.7255074331
		Dec-08	16	5.43	5.11	5.11	1.06	1.09	5.05	1.01	1	5.552	-0.122	0.122	2.25%	4.37	4.874	5.9368823098
		Mar-09	17	6.56	5.11	5.08	1.29	1.27	5.03	1.01	1	6.450	0.110	0.110	1.68%	5.27	4.855	6.1476108374
		Jun-09	18	4.25	5.04	5.02	0.85	0.85	5.01	1.00	1	4.246	0.004	0.004	0.10%	3.40	4.836	5.6828244085
		Sep-09	19	3.92	5.00	5.01	0.78	0.80	5.00	1.00	1	4.006	-0.086	0.086	2.19%	3.14	4.817	5.5996303968
		Dec-09	20	5.26	5.02	5.04	1.04	1.09	4.98	1.01	1	5.479	-0.219	0.219	4.16%	4.22	4.798	5.8413920159
		Mar-10	21	6.65	5.06	5.08	1.31	1.27	4.96	1.02	1	6.461	0.189	0.189	2.84%	5.34	4.779	6.0870895009
		Jun-10	22	4.42	5.11	5.14	0.86	0.85	4.95	1.04	1	4.345	0.075	0.075	1.69%	3.56	4.76	5.6205500122
		Sep-10	23	4.09	5.17	5.16	0.79	0.80	4.93	1.05	1	4.129	-0.039	0.039	0.95%	3.30	4.741	5.533251816
		Dec-10	24	5.51	5.16	5.14	1.07	1.09	4.91	1.05	1	5.582	-0.072	0.072	1.31%	4.44	4.722	5.7947670966
		Mar-11	25	6.61	5.12	5.10	1.30	1.27	4.90	1.04	1	6.483	0.127	0.127	1.91%	5.31	4.703	5.9987608429
		Jun-11	26	4.25	5.09	5.09	0.83	0.85	4.88	1.04	1	4.308	-0.058	0.058	1.37%	3.42	4.684	5.5185606284
		Sep-11	27	3.98	5.10	5.05	0.79	0.80	4.86	1.04	1	4.040	-0.060	0.060	1.50%	3.19	4.665	5.4529237812
		Dec-11	28	5.55	5.01	5.02	1.11	1.09	4.84	1.04	1	5.452	0.098	0.098	1.77%	4.44	4.646	5.7524041864
		Mar-12	29	6.24	5.03	5.03	1.24	1.27	4.83	1.04	1	6.393	-0.153	0.153	2.45%	5.00	4.627	5.86755666
		Jun-12	30	4.34	5.03	5.01	0.87	0.85	4.81	1.04	1	4.237	0.103	0.103	2.37%	3.47	4.608	5.4744836536
		Sep-12	31	4	4.99	5.01	0.80	0.80	4.79	1.04	1	4.003	-0.003	0.003	0.07%	3.20	4.589	5.3882007992
		Dec-12	32	5.36	5.03	4.96	1.08	1.09	4.78	1.04	1	5.393	-0.033	0.033	0.62%	4.28	4.57	5.6501007557
		Mar-13	33	6.4	4.90	4.84	1.32	1.27	4.76	1.02	1	6.153	0.247	0.247	3.86%	5.08	4.551	5.872972631
		Jun-13	34	3.84	4.78	4.71	0.82	0.85	4.74	0.99	1	3.980	-0.140	0.140	3.65%	3.02	4.532	5.3481530287
		Sep-13	35	3.53	4.63	4.50	0.78	0.80	4.73	0.95	1	3.598	-0.068	0.068	1.92%	2.75	4.513	5.2976624062
		Dec-13	36	4.74	4.37	4.34	1.09	1.09	4.71	0.92	1	4.713	0.027	0.027	0.58%	3.65	4.494	5.5871104065
		Mar-14	37	5.37	4.30	4.28	1.26	1.27	4.69	0.91	1	5.435	-0.065	0.065	1.21%	4.11	4.475	5.7307731657
		Jun-14	38	3.57	4.25	4.29	0.83	0.85	4.68	0.92	1	3.632	-0.062	0.062	1.75%	2.74	4.456	5.287441048
		Sep-14	39	3.32	4.34	4.42	0.75	0.80	4.66	0.95	1	3.535	-0.215	0.215	6.47%	2.57	4.437	5.1881312217
		Dec-14	40	5.09	4.50	4.55	1.12	1.09	4.64	0.98	1	4.949	0.141	0.141	2.77%	3.97	4.418	5.5357600878
		Mar-15	41	6.03	4.61	4.64	1.30	1.27	4.63	1.00	1	5.892	0.138	0.138	2.28%	4.73	4.399	5.6996201132
		Jun-15	42	3.98	4.67	4.65	0.86	0.85	4.61	1.01	1	3.931	0.049	0.049	1.24%	3.12	4.38	5.2366047888
		Sep-15	43	3.57	4.63	4.64	0.77	0.80	4.59	1.01	1	3.712	-0.142	0.142	3.97%	2.80	4.361	5.1301893348
		Dec-15	44	4.92	4.66	4.63	1.06	1.09	4.58	1.01	1	5.036	-0.116	0.116	2.36%	3.86	4.342	5.4037750202
		Mar-16	45	6.16	4.61	4.59	1.34	1.27	4.56	1.01	1	5.831	0.329	0.329	5.35%	4.82	4.323	5.6657792916
		Jun-16	46	3.79	4.57	4.51	0.84	0.85	4.54	0.99	1	3.819	-0.029	0.029	0.75%	2.95	4.304	5.1436566048
		Sep-16	47	3.39	4.46	4.37	0.78	0.80	4.52	1.00	1	3.619	-0.229	0.229	6.75%	2.61	4.285	5.0614099628
		Dec-16	48	4.51	4.27	4.24	1.06	1.09	4.51	1.00	1	4.899	-0.389	0.389	8.63%	3.45	4.266	5.329365753
		Mar-17	49	5.39	4.21	4.17	1.29	1.27	4.49	1.00	1	5.708	-0.318	0.318	5.90%	4.10	4.247	5.5403413317
		Jun-17	50	3.56	4.12	4.06	0.88	0.85	4.47	1.00	1	3.785	-0.225	0.225	6.33%	2.68	4.228	5.1043076923
		Sep-17	51	3.03	4.00	3.77	0.80	0.80	4.46	1.00	1	3.565	-0.535	0.535	17.66%	2.23	4.209	5.0124471329
		Dec-17	52	4.03	3.54	3.54	1.14	1.09	4.44	1.00	1	4.826	-0.796	0.796	19.76%	2.89	4.19	5.3284180791
														MAPE	3.48%
	SLOPE	-0.0168256		INTERCEPT	5.178
	INTERCEPT	5.3157494715		X VAR COEFFICIENT	-0.019
	(a)
	(b)
	Refer the table above
	Seasonal Factor is calculated
	using Formula
	SF =  sales/ CMA
	©
	(d)
	MAPE has been calucluated in the table abve
Question10
	
Sales
deseasonlised data
Question12
	
Sales
deseasonlised data
Trend  (Y=5.178-0.019T)
Forecast
Question13
	Period	Sales	CMA	SI
	Mar-02	23.86
	Jun-02	23.97
	Sep-02	29.23	25.345	1.1532846715
	Dec-02	24.32	25.3525	0.9592742333
	Mar-03	23.89	26.07	0.9163789797
	Jun-03	26.84	26.1025	1.0282539987
	Sep-03	29.36	26.5975	1.

C1P2 CSIS 405 Chapter 6: Time-Series Decomposition 1. Homework: Exercises 8, 9,10, 12, and 13 For this homework, choose "Decomposition" as Forecast Method Exercise 8: a. The forecast =...

Answer To: C1P2 CSIS 405 Chapter 6: Time-Series Decomposition 1. Homework: Exercises 8, 9,10, 12, and 13 For...

Answer To This Question Is Available To Download

Related Questions & Answers

Submit New Assignment