Skip to Content
avatar image
Former Member

Automatic Calculation of Forecast from Actual & Budget

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

Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

2 Answers

  • Best Answer
    Jun 11, 2017 at 03:14 PM

    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
    Add comment
    10|10000 characters needed characters exceeded

  • Jun 11, 2017 at 02:22 PM

    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
    Add comment
    10|10000 characters needed characters exceeded