Moving average in excel

[Tanu]

Can anyone suggest me how can I calcluate the 6 months moving avearge for unit sales volume data for different categories of products?

 

[srinidhi]

http://chandoo.org/wp/2009/04/28/calculate-moving-average/


Check out the info in chandoo site

 

[Tanu]

I have already gone through the above link. I dont know how to proceed further? Sorry if my question sounds stupid. I am not savvy in excel, just started learning recently.

 

[Hui]

It depends on how your data is organised

can you post a sample file and description of what fields you want averaged

 

[Tanu]

Hi, I have calculated 6 months moving avg for the years 2007 to 2011 as follows.

Months Sales 6 Months Moving average

2007

Jan 6802 #N/A

Feb 5063 #N/A

Mar 7533 #N/A

Apr 10999 #N/A

May 5889 #N/A

Jun 10408 7782.333333

Jul 10111 8333.833333

Aug 6685 8604.166667

Sep 5878 8328.333333

Oct 6686 7609.5

Nov 8188 7992.666667

Dec 10862 8068.333333

2008

Jan 9505 7967.333333

Feb 6896 8002.5

Mar 10587 8787.333333

Apr 9282 9220

May 8826 9326.333333

Jun 7562 8776.333333

Jul 14266 9560.571429

Aug 8261 9382.857143

Sep 9313 9728.142857

Oct 8406 9416.571429

Nov 6639 9039

Dec 12272 9531.285714

2009

Jan 4648 8256.5

Feb 6674 7992

Mar 6898 7589.5

Apr 6953 7347.333333

May 8482 7654.5

Jun 10516 7361.833333

Jul 9458 7661.285714

Aug 5340 7760.142857

Sep 9914 8223

Oct 9881 8649.142857

Nov 8244 8833.571429

Dec 9598 8993

2010

Jan 25612 11431.5

Feb 27535 15130.66667

Mar 44728 20933

Apr 29072 24131.5

May 41839 29730.66667

Jun 34348 33855.66667

Jul 39072 34600.85714

Aug 48231 37832.14286

Sep 38606 39413.71429

Oct 43196 39194.85714

Nov 35173 40066.42857

Dec 51653 41468.42857

2011

Jan 38297 42526

Feb 25507 38738.66667

Mar 34635 38076.83333

Is this correct?? I want someone to help me to plot it on the graph.. Can u also let me know by using these data, Is there are any possibily for predecting the 2011 ( Q2 to 4) and 2012 data??

 

[Hui]

Tanu

Your very close


I would rearrange your data so that you don't have gaps where the years cross

If you want to highlight the years put them in another column


Some of your numbers are incorrect and maybe because of the gaps

My calculations are shown below


In the cell where 7,782.3 is I have used the equation =Average(C2:C7) and then copy it down

[pre]

Year	Months		Sales	6mma		6mma Hui	Diff
2007	1/01/2007	6802	#N/A
1/02/2007	5063	#N/A
1/03/2007	7533	#N/A
1/04/2007	10999	#N/A
1/05/2007	5889	#N/A
1/06/2007	10408	 7,782.3 	 7,782.3 	 0.0
1/07/2007	10111	 8,333.8 	 8,333.8 	 0.0
1/08/2007	6685	 8,604.2 	 8,604.2 	-0.0
1/09/2007	5878	 8,328.3 	 8,328.3 	 0.0
1/10/2007	6686	 7,609.5 	 7,609.5 	 -
1/11/2007	8188	 7,992.7 	 7,992.7 	-0.0
1/12/2007	10862	 8,068.3 	 8,068.3 	 0.0
2008	1/01/2008	9505	 7,967.3 	 7,967.3 	 0.0
1/02/2008	6896	 8,002.5 	 8,002.5 	 -
1/03/2008	10587	 8,787.3 	 8,787.3 	 0.0
1/04/2008	9282	 9,220.0 	 9,220.0 	 -
1/05/2008	8826	 9,326.3 	 9,326.3 	 0.0
1/06/2008	7562	 8,776.3 	 8,776.3 	 0.0
1/07/2008	14266	 9,560.6 	 9,569.8 	 9.3
1/08/2008	8261	 9,382.9 	 9,797.3 	 414.5
1/09/2008	9313	 9,728.1 	 9,585.0 	-143.1
1/10/2008	8406	 9,416.6 	 9,439.0 	 22.4
1/11/2008	6639	 9,039.0 	 9,074.5 	 35.5
1/12/2008	12272	 9,531.3 	 9,859.5 	 328.2
2009	1/01/2009	4648	 8,256.5 	 8,256.5 	 -
1/02/2009	6674	 7,992.0 	 7,992.0 	 -
1/03/2009	6898	 7,589.5 	 7,589.5 	 -
1/04/2009	6953	 7,347.3 	 7,347.3 	 0.0
1/05/2009	8482	 7,654.5 	 7,654.5 	 -
1/06/2009	10516	 7,361.8 	 7,361.8 	 0.0
1/07/2009	9458	 7,661.3 	 8,163.5 	 502.2
1/08/2009	5340	 7,760.1 	 7,941.2 	 181.0
1/09/2009	9914	 8,223.0 	 8,443.8 	 220.8
1/10/2009	9881	 8,649.1 	 8,931.8 	 282.7
1/11/2009	8244	 8,833.6 	 8,892.2 	 58.6
1/12/2009	9598	 8,993.0 	 8,739.2 	-253.8
2010	1/01/2010	25612	 11,431.5 	 11,431.5 	 -
1/02/2010	27535	 15,130.7 	 15,130.7 	-0.0
1/03/2010	44728	 20,933.0 	 20,933.0 	 -
1/04/2010	29072	 24,131.5 	 24,131.5 	 -
1/05/2010	41839	 29,730.7 	 29,730.7 	-0.0
1/06/2010	34348	 33,855.7 	 33,855.7 	-0.0
1/07/2010	39072	 34,600.9 	 36,099.0 	 1,498.1
1/08/2010	48231	 37,832.1 	 39,548.3 	 1,716.2
1/09/2010	38606	 39,413.7 	 38,528.0 	-885.7
1/10/2010	43196	 39,194.9 	 40,882.0 	 1,687.1
1/11/2010	35173	 40,066.4 	 39,771.0 	-295.4
1/12/2010	51653	 41,468.4 	 42,655.2 	 1,186.7
2011	1/01/2011	38297	 42,526.0 	 42,526.0 	 -
1/02/2011	25507	 38,738.7 	 38,738.7 	-0.0
1/03/2011	34635	 38,076.8 	 38,076.8 	 0.0

[/pre]

Having your data like this now makes it a lot easier to plot

Select the Date Column and the 6MMA Column and Insert Chart

 

[Tanu]

Thanks a lot

 

[Tanu]

Can u also answer my question Is there are any possibily for predecting the 2011 ( Q2 to 4) and 2012 data??

 

[Hui]

as per your other post


You can use Forecast or Trend

Refer:

http://chandoo.org/wp/2011/01/24/trendlines-and-forecasting-in-excel/

or

http://chandoo.org/wp/2011/01/26/trendlines-and-forecasting-in-excel-part-2/