5 Years Planning

HugoManchon
HugoManchon
in Modeling

Hi there,

I am trying to build this kind of P&L view :

image.png

where 2023 FY figures come from another module where the line items are planned on a monthly basis.

The idea is to forecast every year after 2023 based on Y-1. Meaning that the revenues of FY 2024 will be = Revenue FY 2023 * x%, Revenues of 2025 = Revenue FY 2024 * x% etc...

I initially prepared a list where am storing all my growth % for each line item but I am a bit struggling as we have different projects and each of those has a different %, which makes me create too many different lists. Ultimately, the sum of each project makes it to the group view.

I was wondering if someone ever build something similar and what would be the best practice here as it feels wrong to use a list for FY23 to FY28 as I can not build formula this way.

Thanks for your help;)

Answers

  • megha09
    megha09

    Hi @HugoManchon

    Maybe try creating FY 2023, FY 2024 .... and so on as Year formatted line items. And if you want them to use as list for further calculations create a Line item subset out of them.


    Thanks,

    Megha

  • HugoManchon
    HugoManchon

    Hi @megha09,

    Thanks for your swift answer!

    If I understand it correctly, I should have my years as line items(currently a list) and my current line items should actually be a list?

    If so how can I have a different formula for each member of the list?

    Cheers,

  • megha09
    megha09

    Hi @HugoManchon


    Apologies for being unclear. So as I am understanding you already have a P&L module with monthly data for FY23. So you must have same line items created as you have in this module that you have put in screenshot. You have to create a line item subset of the line items of the monthly view and then create a new module which will be your year module with FY23, FY24... so on as your line items. You will apply collect() on FY23 to bring in your yearly data for FY23 from your monthly data module and then you can individually apply formula for FY24 and FY25 and so on. Attaching screenshots for reference -


    image.png
    image.png
    image.png
    image.png

    Last Picture shows the P&L Line Item subset.

    Hope this helps


    Thanks,

    Megha

  • megha09
    megha09

    @HugoManchon Also I missed Growth Percentage for different projects and different Line Items you can create a module using P&L Line Item Subset and Projects as dimensions. Feed all the growth% respectively and use it in the final module for calculations. Attaching screenshot for this too -

    image.png
    image.png

    Thanks again!

  • HugoManchon
    HugoManchon

    Hello again @megha09 ;)

    Thanks for the clarifications!

    My problem is that I did not create a list of my P&L line items. I've created them as line items directly in the module. Is there a way to go around this?

    Cheers,

  • megha09
    megha09

    @HugoManchon In the module where you are calculating line items on monthly basis are you using the same P&L Line items?

  • HugoManchon
    HugoManchon

    @megha09 yes they are the same, just not in a list.🙈

  • megha09
    megha09

    @HugoManchon Yes Sir! I understand that. So I am asking you to create a "LINE ITEM SUBSET" of those line items using the monthly module.


    PS : A line item subset is created from line items of existing modules and can be used as a dimension. Also a line item subset is not same as list item subset. Use this link for more understanding.

    https://help.anaplan.com/fd6bfccc-fd3b-4d55-b838-59cdda9c572c-Line-item-subsets


    Request you to go through the documentation and my above response again haha! Aldo try not to be confused with "Line item subsets" and "List Item subset".🙈


    Thanks

  • HugoManchon
    HugoManchon

    Hi @megha09,

    I have managed to create the module with the Line Item Sub Set and bring the FY23 values with the COLLECT() formula. Thanks again for your help here!!

    image.png


    I now want to run calculations on these Line Items ( FY23, FY24 ,etc...) How can I do that? I am trying to bring them into a new module but I am getting only "0"

    image.png

    I've tried to SUM and LOOKUP but I am not getting it, it always returns me "0".

    The idea is to play with % of Net Revenues afterward to estimate costs.

    For example: Cost A will be x% of my Net Revenues 23 and y% of my Net Revenues 24

    Any thoughts on that?

    Cheers,

Categories