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