Skip to Content
-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)
Add comment
10|10000 characters needed characters exceeded

  • Get RSS Feed

4 Answers

  • Best Answer
    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.

    Add comment
    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)

  • 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

    Add comment
    10|10000 characters needed characters exceeded

  • 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

    Add comment
    10|10000 characters needed characters exceeded

  • Oct 24, 2017 at 07:14 PM

    Hi Vadim

    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.jpgcapture2.jpgcapture3.jpg

    Add comment
    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!