Skip to Content
0
Former Member
Nov 18, 2010 at 03:16 AM

Calculate last 6months moving average in report

59 Views

Hi Experts,

I have a requirement to caculate the last 6months moving average in report.

Refer to my report layout below -

Forecast Month 01.2009 02.2009 03.2009 04.2009 05.2009 06.2009 07.2009 08.2009

Actuals 104 96 108 91 128 116 134 154

Forecast 90 90 100 100 120 120 170 180

Variance -14 -6 -8 9 -8 4 36 26

%PE -13 -6 -7 10 -6 3 27 17

6months avg -13 -10 -9 -4 -5 -3 3 7

Assume no data is available before 01.2009, so the actual 6month moving avg can only be seen from 06.2009 forecast month.

Calculations:

Variance = Forecast - Actuals

% PE = (Forecast- Actuals)/ Actuals

6 Month (moving average) = it is the average value on the last 6months %PE including current month for each month

i.e 6months avg for 06.2009 will be from AVG(01.2009 to 06.2009)

for 03.2009, the average will be from AVG(01.2009 to 03.2009)

Please suggest your solutions to the above requirement.

Thanks,

SJ