-1

# Forecast year into monthly by getting the 3 year monthly average BPC Logic Script

Oct 24, 2017 at 05:26 PM

48

Hi All

I am new to BPC and very limited knowledge of logic scripts

I have the following requirement:

"Distribute Forecast from year to Months based on recent 3 year monthly average based on cost center"

From a logic standpoint I think I have it figured out but I am not sure how to code it

1. Take the actuals for last 3 years.
2. Calculate the rate of distribution for current Year -1 Jan,Feb,Mar ... Dec i.e. 10%,15%,10%....15% etc
3. Store the values of each month for current Year -1
4. Calculate the rate of distribution for current Year -2 Jan,Feb,Mar ... Dec i.e. 10%,15%,10%....15% etc
5. Store the values of each month for current Year -2
6. Calculate the rate of distribution for Year 1 Jan,Feb,Mar ... Dec i.e. 10%,15%,10%....15% etc
7. Store the values of each month for current Year -3
8. Calculate the average of Jan for previous 3 years, and other months and store the percent
9. allocate the jan forecast amount and other months with their respective calculated values from step 8 dist percent.

For Dimensions I have CATEOGRY, TIME,DATASOURCE,SCENARIO,CC_WBS. cost center and wbs are linked.

This is the actual requirements

Ability to in year forecast at the year level. Planning system should perform a breakdown allocation to a period level using three year average to distribute to the period.The three year average is by cost center

I have put the screenshots of the solution in excel

g8ksd.png (76.0 kB)
tut48.png (75.3 kB)

Vadim Kalinin Oct 24, 2017 at 06:38 PM
1

Sorry, but your description is absolutely unclear! And the attached screenshots are unreadable due to the small size. If you want to get an answer, please start again - and with clear screenshots.

Allocation calculation is not illustrated on the screenshot.

Show 1 Share

P.S. For each dimension explain:

What: Member or members with value to allocate

Where: Member or members to store result of allocation

Using: Member or members used as allocation ratio or weight

Total: Same as Using (Factor=Using/Total)

Fahad Qureshi Oct 25, 2017 at 01:41 PM
0

*XDIM_MEMBERSET CATEGORY = %CATEGORY_SET%
*XDIM_MEMBERSET FUND_CTR = BAS(3371AA_BPC)
*XDIM_MEMBERSET TIME AS %IN_TIME% = %TIME_SET%

*XDIM_MEMBERSET TIME AS %TIMEMTH% = 01,02,03,04,05,06,07,08,09,10,11,12

*XDIM_MEMBERSET DATASOURCE = BW,INPUT

*XDIM_MEMBERSET FUND = BAS(RCN_VOTE)
*XDIM_MEMBERSET PAA = BAS(ALL_BPC)
*XDIM_MEMBERSET CMMT_ITEM <> CI_NA
*XDIM_MEMBERSET SCENARIO = SC_0

Show 1 Share

What is this meaningless code for???

Fahad Qureshi Oct 25, 2017 at 02:10 PM
0

Category Members

ACTUALS for calculation

Storing in FORECAST

user is selecting the year the member value is ID 2015.00

capture7.jpg (41.6 kB)
Show 1 Share

What do you mean by "user is selecting the year the member value is ID 2015.00"???

Please spend some time to provide CLEAR information!

Fahad Qureshi Oct 24, 2017 at 07:14 PM
0

I am attaching higher screenshots

Ability to in year forecast at the year level. Planning system should perform a breakdown allocation to a period level using three year average to distribute to the period.The three year average is by cost center

So they want to allocate current year forecast based on last 3 years of actuals. capture4.jpg captures on the allocation part.

They want to take 3 years of actuals. average them . take the distribution percentage and multiply it to the respective month and cc

So for example: Cost Center ABC for January the current forecast is \$1000

Actuals for

Year 1 Jan is \$1000 and year total is \$12000,

Year 2 Jan \$1100 and year total is \$13200,

Year 3 Jan is \$1050 and year total is \$12600

Average total of 3 years is \$12600 (Capture 2)

Average total of January is \$1050

Distribution percentage of Jan is \$1050/\$12600 = 0.08333 (Capture 3)

This is the percent they want the current Forecast of Jan \$1000 to be multiplied with and stored so it will be current forecast \$1000 * (1+.08333) = \$1083.33 for Jan.

Samething has to be done for all of the months .

capture.jpg (111.1 kB)
capture2.jpg (117.3 kB)
capture3.jpg (116.0 kB)
capture4.jpg (92.7 kB)
Show 1 Share

Now the screenshots are visible, but it's absolutely bad practice to attach pictures as links instead of normal "Insert Image" - VERY HARD TO READ! -> Low motivation to answer!

"This is the percent they want the current Forecast of Jan \$1000 to be multiplied with and stored so it will be current forecast \$1000 * (1+.08333) = \$1083.33 for Jan." - something absolutely meaningless!!! No business value at all!

I can understand the following:

JANWeight=(YEAR1.JAN+YEAR2.JAN+YEAR3.JAN)/(YEAR1.TOTAL+YEAR2.TOTAL+YEAR3.TOTAL) = 0.07

FEBWeight=(YEAR1.FEB+YEAR2.FEB+YEAR3.FEB)/(YEAR1.TOTAL+YEAR2.TOTAL+YEAR3.TOTAL) = 0.08

...

JANWeight+FEBWeight+...+DECWeight = 1.0

Then If you have the Forecast YEARF.INP (some base member containing total year forecast value) this amount can be distributed over months:

YEARF.JAN = YEARF.INP * JANWeight

YEARF.FEB = YEARF.INP * FEBWeight

...

As a result:

YEARF.JAN+YEARF.FEB+...+YEARF.DEC=YEARF.INP

But you have to explain members used!