Moving Average Issue

Monpero · June 2023

Hi,

For my Forecast Jan 2023 I want to bring the (Actuals) AVERAGE of Jan 2021 and Jan 2022. And for my Feb 2023 I want the AVERAGE of Feb 2021 and Feb 2022, e.t.c.

To achieve this I split the line item actuals values into the ones below, for calculation and then bring it to the forecast.

for 2021 Values line item
for 2022 Values line item
Same Period Last Yr = (2021 Values line item + 2022 Values line item) / 2
Input line item = 30
Previous 12 Month Average = Previous(Average 12 months)
Average 12 months = Movingsum(Data, -12,-1,Average)

The calculation:

IF Actuals THEN Data

ELSE Same Period Last Yr > Previous 12 Month Average + Input line item

OR

Same Period Last Yr < Previous 12 Month Average - Input line item

THEN

Previous 12 Month Average

After doing this I don't get value for the forecast and the values are not correct. Please is there a better way to bring the Average Value of 2021 and 2022 of equal months to forecast months.

Any solution or suggestion will be really helpful.

EXAMPLE SCREEN SHOT

AkhilEmmanual · June 2023

Hi @innocent you can achieve this by taking previous year and 2 years back data by offset then you can take the average of both and if its actual take actual data else this average value for your reference you can check the example below.

First I made a time settings module to identify actual and forecast

Screenshot 2023-06-07 10.59.33 AM.png

Screenshot 2023-06-07 10.58.06 AM.png

i have actual data from 2021-2022 and forecast 2023

Screenshot 2023-06-07 11.00.29 AM.png

then in main module i had created some line items

Data for actuals- actual data

Previous year no - helps for doing offset to get previous year data

2 year before no -helps for doing offset to get 2 year before data

previous year data- where we got previous year data

2 year before data- where we got 2 year before data

average- average of above 2

forecast- if actual will take it from data for actual else the average

Screenshot 2023-06-07 11.07.01 AM.png

Screenshot 2023-06-07 11.07.24 AM.png

I hope this screenshots will be helpful for you.

Monpero · June 2023

Thanks a lot @AkhilEmmanual for the build, with the formula (YEAR(ITEM(Time)) - 2024) * 12 to get the Previous year no which is used for forecast 2023.

I know this is a silly question, if the forecast is from 2023,2024 and 2025 is this formula (YEAR(ITEM(Time)) - 2024) * 12 valid

or will it be (YEAR(ITEM(Time)) - 2026) * 12 and the (YEAR(ITEM(Time)) - 2026) * 24 for 2 years before no?

AkhilEmmanual · June 2023

@innocent if forecast starts from 23 till any year formula will be ((YEAR(ITEM(TIME))-2024)*12. If forecast starts from 24 then just change it to -2025 instead of -2024. Just one year greater than forecast start year.

Here it will function like for 2023 average of 2021 and 2022 will come. for 2024 average of 2023 and 2022 will come and so on.

Monpero · June 2023

Hi @AkhilEmmanual thanks I really appreciate your effort. The formula does not provide value for 2024, I'm thinking it's subtracting it out. Is there a different calculation to bring numbers in for 2024.