Automatic Calculation of Forecast from Actual & Budget

[Former Member]

Dear Experts,

I have a requirement to achieve automatic Forecast calculation. There are 12 Forecast versions, one version
for each month of the year, example, FORECAST_JAN, FORECAST_FEB, FORECAST_MAR, ....FORECAST_DEC.

So, if the year is 2017 then,
FORECAST_JAN for 2017.01 to 2017.12 is same as the BUDGET that was set for the year 2017.
FORECAST_FEB is ACTUAL of 2017.01 + [(BUDGET of 2017 - ACTUAL of 2017.01)/11 and copied to 2017.02, 2017.03 to 2017.12]
FORECAST_MAR is ACTUAL of 2017.01, 2017.02 + [(BUDGET of 2017 - ACTUAL of 2017.01+2017.02)/10 and copied to 2017.03, 2017.04 to 2017.12]
....
FORECAST_NOV is ACTUAL of 2017.01 to 2017.10 + [(BUDGET of 2017 - ACTUAL of 2017.01 to 2017.12)/2 and copied to 2017.11 and 2017.12]
FORECAST_DEC is ACTUAL of 2017.01 to 2017.11 + BUDGET of 2017.12

Hope i have been able to explain.

Is this possible to achieve through script logic? If yes, can anyone give me an idea as to how I should approach this?

Thanks and Regards,
Swakshar

[former_member186338 · Accepted solution]

To make the script universal you have to create property in CATEGORY dimension like LASTACTUALMONTH

For FORECAST_MAR fill this property with 02, FORECAST_APR: 03 etc.

You have to pass variable $Y$ from the advanced script (for example using PROMPT COMBOBOX with a list of Years...)

Then the script will be:

//User input: %CATEGORY_SET%=FORECAST_MAR $Y$=2017
*SELECT(%LM%,[LASTACTUALMONTH],CATEGORY,ID=%CATEGORY_SET%)
//%LM%=02
*SELECT(%AM%,[ID],TIME,[TIMEID]<=$Y$%LM%00 AND [YEAR]=$Y$ AND [CALC]=N) // $Y$%LM%00 = 20170200
//%AM%=2017.01,2017.02
*SELECT(%FM%,[ID],TIME,[TIMEID]>$Y$%LM%00 AND [YEAR]=$Y$ AND [CALC]=N)
//%FM%=2017.03,2017.04,2017.05,2017.06,2017.07,2017.08,2017.09,2017.10,2017.11,2017.12
*SELECT(%YM%,[ID],TIME,[YEAR]=$Y$ AND [CALC]=N)
//%YM%=2017.01,2017.02,2017.03,2017.04,2017.05,2017.06,2017.07,2017.08,2017.09,2017.10,2017.11,2017.12
*XDIM_MEMBERSET TIME=%YM%
*XDIM_MEMBERSET CATEGORY=ACTUAL,BUDGET
*WHEN CATEGORY
*IS ACTUAL
  *WHEN TIME
  *IS %AM%
  *REC(EXPRESSION=%VALUE%,CATEGORY=%CATEGORY_SET%) //Copy actual months
  *FOR %T%=%FM%
    *REC(EXPRESSION=-VALUE/(12-%LM%),TIME=%T%,CATEGORY=%CATEGORY_SET%) // minus actual from forecast months
  *NEXT
  *ENDWHEN
*IS BUDGET
*FOR %T%=%FM%
  *REC(EXPRESSION=VALUE/(12-%LM%),TIME=%T%,CATEGORY=%CATEGORY_SET%) // plus budget for forecast months
*NEXT
*ENDWHEN

[Former Member · Accepted solution]

Thanks a lot Vadim for your help as always!

Best Regards,

Swakshar

[former_member186338]

Something like: FORECAST_MAR for 2017.03...2017.12

*XDIM_MEMBERSET CATEGORY=ACTUAL,BUDGET
*XDIM_MEMBERSET TIME=BAS(2017.TOTAL)
*WHEN CATEGORY
*IS ACTUAL
  *WHEN TIME
  *IS 2017.01,2017.02
  *FOR %T%=2017.03,2017.04,2017.05,2017.06,2017.07,2017.08,2017.09,2017.10,2017.11,2017.12
    *REC(EXPRESSION=-VALUE/10,TIME=%T%,CATEGORY=FORECAST_MAR)
  *NEXT
  *ENDWHEN
*IS BUDGET
*FOR %T%=2017.03,2017.04,2017.05,2017.06,2017.07,2017.08,2017.09,2017.10,2017.11,2017.12
  *REC(EXPRESSION=VALUE/10,TIME=%T%,CATEGORY=FORECAST_MAR)
*NEXT
*ENDWHEN