Custom waterfall methodology
Author: Anya Skvortsova is a Certified Master Anaplanner and CoE Lead at Asana.
Purpose: There is more than one way to tackle the waterfall calculations. I’d like to share one custom variety that proved to be especially handy for use cases of new asset depreciation and revenue recognition. Customers seems to like the characteristic “waterfall” shape that this approach can provide and visibility into each period’s data/cohort composition.
UX setup: It starts with a user-friendly input layout. The screenshot below illustrates New Asset Spend grid where the user can input dollars spent by month, type of asset, company and department. I will use depreciation example below; revenue would follow nearly identical setup (say, input/upload revenue by channel, line of business, contract terms etc.).
The next module contains user inputs for depreciation term.
Backend mechanics: For each asset we want to know how much to depreciate a month and for how long. This custom waterfall setup takes 3 modules.
Step 1: We calculate the straight-line depreciation as Spend / Term. This is just an additional line item in the input module.
Step 2: Then we need to define for Anaplan the period when to start depreciating.
This is accomplished with the help of custom time module and respective mapping to the regular timescale periods.
The formula in “Custom Month” line item populates the relative number of forecast period in which the spend has occurred.
The time map uses PREVIOUS () + 1 to produce forecast month number and align it with the custom period dimension using FINDITEM.
Step 3: Bring it together into waterfall shape. Match the 1st depreciation period from input module to 1st custom period in calculation module and set the condition to stop. We are aiming the view below.
First line item in this module produces the Boolean checkmark when 1st forecast period of timescale matches the 1st custom period.
Life counter keeps track of how many periods has it been.
‘Depr Amnt’ is only calculated if the count of periods does not exceed useful life term.
The blueprints are attached below. You can take the model to the next level by advancing to the Balance Sheet and calculating capitalized spend and accumulated depreciation.
Please feel free to leave a comment with questions!