Question

The following data represents the monthly electrical expense for an office space over the past three years. You are considering opening a new business in this office space and are preparing an expected budget for your business plan.

Month	Year 1	Year 2	Year 3
January	170	180	195
February	180	205	210
March	205	215	230
April	230	245	280
May	240	265	290
June	315	330	390
July	360	400	420
August	290	335	330
September	240	260	290
October	240	270	295
November	230	255	280
December	195	220	250

a) Using overall average develop a seasonal index for each month and report your results.

b) Using centered moving average develop a seasonal index for each month and report your results.

c) Using the centered moving average data to predict the electric bill for each month of next year. Report your results.

d) Use the data below to predict the electric bill for each month of next year using linear trend and the seasonal index from the overall average. Report your results.

Month	Year 1	Year 2	Year 3
January	247.852	262.431	284.3
February	240.378	273.764	280.441
March	250.599	262.824	281.16
April	242.058	257.845	294.68
May	239.874	264.861	289.848
June	241.83	253.345	299.408
July	242.415	269.35	282.818
August	241.287	278.728	274.568
September	241.392	261.508	291.682
October	236.894	266.506	291.183
November	238.894	264.861	290.828
December	232.998	262.87	298.716

e) Compare the results between your predictions in parts c and d. Which set of predictions would you use? Explain your answer.

Answer 1

a) Using overall average develop a seasonal index for each month and report your results.

Ans:-

Month	Year 1	Year 2	Year 3
January	170	180	195
February	180	205	210
March	205	215	230
April	230	245	280
May	240	265	290
June	315	330	390
July	360	400	420
August	290	335	330
September	240	260	290
October	240	270	295
November	230	255	280
December	195	220	250
Total	2895	3180	3460
Average	241.25	265.00	288.33
seasonal index
Month	Year 1	Year 2	Year 3
January	0.704663	0.679245	0.676301
February	0.746114	0.773585	0.728324
March	0.849741	0.811321	0.797688
April	0.953368	0.924528	0.971098
May	0.994819	1	1.00578
June	1.305699	1.245283	1.352601
July	1.492228	1.509434	1.456647
August	1.202073	1.264151	1.144509
September	0.994819	0.981132	1.00578
October	0.994819	1.018868	1.023121
November	0.953368	0.962264	0.971098
December	0.80829	0.830189	0.867052

b) Using centered moving average develop a seasonal index for each month and report your results.

Ans:-  

Month	Year 1	centeral Moving average	Year 2	centeral Moving average	Year 3
January	170		180		195
February	180		205		210
March	205		215		230
April	230		245		280
May	240	196.25	265	211.25	290
June	315	213.75	330	232.5	390
July	360	247.5	400	263.75	420
August	290	286.25	335	310	330
September	240	301.25	260	332.5	290
October	240	301.25	270	331.25	295
November	230	282.5	255	316.25	280
December	195	250	220	280	250
Total	2895	226.25	3180	251.25	3460
Average	241.25	890	265.00	981.25	288.33
seasonal index 3 month moving
Month	Year 1	Year 2	Year 3
January
February
March
April
May	0.220506	0.215287	0.213536
June	0.240169	0.236943	0.235706
July	0.27809	0.26879	0.277713
August	0.321629	0.315924	0.322054
September	0.338483	0.338854	0.333722
October	0.338483	0.33758	0.333722
November	0.317416	0.322293	0.311552
December	0.280899	0.28535	0.27888

c) Using the centered moving average data to predict the electric bill for each month of next year. Report your results.

Ans:- on the basis of 3 month central moving average

January	278.75
February	206.25
March	132.5
April	62.5

d) Use the data below to predict the electric bill for each month of next year using linear trend and the seasonal index from the overall average. Report your results.

Month		Year 1	Year 2	Year 3	Trend for year 1	T for year 2	T for Year 3
January	1	247.852	262.431	284.3	246.5878	264.4001	283.595
February	2	240.378	273.764	280.441	245.6396	264.4924	284.4509
March	3	250.599	262.824	281.16	244.6914	264.5847	285.3069
April	4	242.058	257.845	294.68	243.7431	264.677	286.1628
May	5	239.874	264.861	289.848	242.7949	264.7693	287.0188
June	6	241.83	253.345	299.408	241.8467	264.8616	287.8747
July	7	242.415	269.35	282.818	240.8985	264.9539	288.7306
August	8	241.287	278.728	274.568	239.9503	265.0462	289.5866
September	9	241.392	261.508	291.682	239.002	265.1385	290.4425
October	10	236.894	266.506	291.183	238.0538	265.2308	291.2985
November	11	238.894	264.861	290.828	237.1056	265.3231	292.1544
December	12	232.998	262.87	298.716	236.1574	265.4154	293.0104
		intercept	247.536
		Slope	-0.94822
		Linear trend forcastTt= 247.536+(-0.94822)*(t)
		intercept	264.3078
		Slope	0.092297
		Linear trend forcastTt= 264.3078+(-0.092297)*(t)
		intercept	282.739
		Slope	0.855944
		Linear trend forcastTt= 282.739+(-0.855944)*(t)

Month	Year 1	sesional index	Year 2	sesional index	Year 3	sesional index
January	247.852	1.026844	262.431	0.990651	284.3	0.986116
February	240.378	0.995879	273.764	1.033431	280.441	0.972731
March	250.599	1.038225	262.824	0.992134	281.16	0.975225
April	242.058	1.00284	257.845	0.973339	294.68	1.02212
May	239.874	0.993791	264.861	0.999824	289.848	1.00536
June	241.83	1.001895	253.345	0.956352	299.408	1.03852
July	242.415	1.004319	269.35	1.016769	282.818	0.980976
August	241.287	0.999645	278.728	1.05217	274.568	0.95236
September	241.392	1.00008	261.508	0.987166	291.682	1.011721
October	236.894	0.981445	266.506	1.006033	291.183	1.009991
November	238.894	0.989731	264.861	0.999824	290.828	1.008759
December	232.998	0.965304	262.87	0.992308	298.716	1.036119
Total	2896.471		3178.893		3459.632
Average	241.3726		264.9078		288.3027

e) Compare the results between your predictions in parts c and d. Which set of predictions would you use? Explain your answer.

Ans : According to comparison between c and d we found that the variance between data is very much higher as per the trend analysis the electricity expenses will go high in future time period.