Waterfall charts with multiple totals

andrewdempsey
andrewdempsey
edited December 23 in Modeling

Hi,

I am wanting to create a waterfall chart with multiple line items across financial years. This is also dimensioned by forecast version, where the closing balance of a given year depends on the version, as well as the forecast range. I have created a dynamic hierarchical list as per the below screenshot to get around this. This works well, but ideally I want to exclude the 'starting balance' items and have the total reset at each closing balance (as per the first year).

Anaplan is aware that these are 'intermediate totals', but these do not reset the sum to 0.

Thanks!

waterfall chart.PNG

Answers

  • seymatas1
    seymatas1

    Hi Andrew,

    I recommend creating a dedicated module for the waterfall chart. You can remove the unwanted line items or reference the line items from the original module to avoid the "auto-detected" totals.

    I hope this helps.

    Seyma 🌷🙂

  • TristanS
    TristanS

    @andrewdempsey If my understanding is correct, you want your waterfall chart to look like example below? If yes, you would have create a "fake" waterfall chart using a stacked column chart like the one below. In the example below, in theory you can get rid of the FY22 Opening Volume and Opening Volumes for future years as the assumption would be the opening balance of one year is the closing volume of the previous year.

    image.png

    Below is how I built it

    1. Create a module having the financial metrics as line items and the list dimension "Waterfaller" (screenshot 1)
    2. Create a list "Waterfaller" with the following (screenshot 2)

    Screenshot 1:

    The formula section "can" have calculations in it but my intention is to just show how you can fake a waterfall using a stacked column so will leave it to you how to either auto calculate or populate using actions.

    image.png

    Screenshot 2

    List has 3 components:

    • FY Closing - not really needed but helps with color coding the end of the waterfall
    • Components - individual components you had in your waterfall
    • . - this is used to create the fake waterfall effect. Intentionally named the lineitem a period (.) so that users will barely see it in the chart
    image.png

    Below is the data I entered

    image.png

    This is how it is configured in the UX card

    image.png
    image.png

    image.png

    For cancellations, if you want it to appear as red then add another list item "FY Cancellations" to the Waterfaller list, set the data up accordingly and then in the chart options have it display as red

  • TristanS
    TristanS
    edited December 23

    @andrewdempsey lol just realised you can achieve what you want and still use a waterfall chart if you are willing to remove the "Opening Volume" item starting from FY26. To be clear, keep FY25 Opening Volume and just remove Opening Volumes for future years. That should give you the chart you want, that is if removing the opening volume for future years is acceptable. You can rename the "FY25 Closing Volume" to "FY25 Closing Volume / FY26 Opening Volume" and so forth

  • andrewdempsey
    andrewdempsey

    @TristanS thanks this is exactly what I am after! The column graph looks good as a backup solution, but will try to retain the waterfall if I can as it's a bit cleaner. I did attempt the above prior, but because the closing balances are in the parent list of the others, the sums don't work as intended. Could you please provide an example if you have managed to get it working?

  • TristanS
    TristanS
    edited December 25

    @andrewdempsey

    So as not to touch the original line item formulas as I am not familiar why it was done that way. E.g. the closing balances for FY26 onwards seems to have been calculated as negative. Gut feeling was that it was only done for the waterfall chart.

    1. Introduce new closing balance line items below with the following formulas
    • FY26 Closing Balance PoC = FY25 Closing Balance + FY26 Sales + Cancellations FY26 + Net Others FY26 (note that closing balance line item uses previous years while all other line items in the formula uses the current year)
    • FY27 Closing Balance PoC = FY26 Closing Balance + FY27 Sales + Cancellations FY27 + Net Others FY27 (note that closing balance line item uses previous years while all other line items in the formula uses the current year)
    • FY28 Closing Balance PoC = Use same patterns as above
    • FY29 Closing Balance PoC = Use same patterns as above

    2. In the waterfall chart

    • Hide the original FY Closing balance line items
    • Hide the original FY Opening Volume line items (starting from FY26 .. you need to keep the FY25 Opening Volume)
    • Include the new Closing Balance PoC line items

    You should get a result similar to below

    image.png

Categories