0

# Automatic Calculation of Forecast from Actual & Budget

Jun 11, 2017 at 09:42 AM

44

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

Vadim Kalinin Jun 11, 2017 at 03:14 PM
1

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```
Show 1 Share
Former Member

Best Regards,

Swakshar

Vadim Kalinin Jun 11, 2017 at 02:22 PM
0

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```
Share