Revenue Distribution Help

S-Blaeser
S-Blaeser
edited April 22 in Modeling

Hello all ,

I am trying to find the best way to distribute forecasted revenue across multiple periods based on a distribution chart (so to say). I will attach screen shots to better explain.

Each month has a forecasted value:

image.png

Each month's forecasted revenue will be allocated based on this chart: Period 1 would correspond to the first value that revenue is multiplied by.

image.png

This is the calculation that I would like Anaplan to conduct:

image.png

The result I am looking for :

image.png

I need some help on how I should dimension this and the best way to solve this in Anaplan. Any suggestions or ideas are greatly appreciated! Thanks!

Best Answers

  • PujithaB
    PujithaB
    Answer ✓

    Hi @S-Blaeser

    In this scenario, basically you need cumulative value of last all months with existing month.
    It can be achieved as below.
    Forecasted Amount —- This is by Month
    Percentage value——This is by Month

    Forecasted with Percentage value —- This is by month and Formulate as Forecasted Amount * Percentage value.
    Cumulate period count ———This is by Month and each month will have 1, 2, 3, 4, corresponding and will reset as 1 for next year ——-Formula——-if previous (Cumulate period count) = 0 then 1 else if previous(parent(parent(item(time)))) <> (parent(parent(item(time)))) then 1 else 1+

    Final forecasted amount —- This is by month and Formula—-Cumulate (Forecasted with Percentage value, false, Cumulate period count)

    Let me know if this helps out.

    Thanks
    Pujitha

  • HimanshuRaj
    HimanshuRaj
    Answer ✓

    @S-Blaeser You can also check Profile formula https://help.anaplan.com/profile-41b5fb84-395b-489f-80be-521add72c581 if you are only interested in Final Total column. It will profile the % spread for each period and cumulate values accordingly and give you total value in your line item

    Cheers!!

Categories