-1

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

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

Posted on Oct 24, 2017 at 06:38 PM

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.

10|10000 characters needed characters exceeded
• 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)

• Posted on Oct 24, 2017 at 07:14 PM

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)
10|10000 characters needed characters exceeded
• 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!

• Posted on Oct 25, 2017 at 01:41 PM

*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

10|10000 characters needed characters exceeded
• What is this meaningless code for???

• Posted on Oct 25, 2017 at 02:10 PM

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)