Delayed amort of assets at category level

MatthewWilcox · March 20

Hi Team,

Need some help with a bit of a head scratcher. I am working on an asset forecasting module at a category level (due to requirements outside of my control we cannot input the values in at the individual asset level).

The categories are in their own list and I have a simple module that houses their life for depreciation purposes.

The complication comes with the inputting of the values. the user needs to input the additional value into the asset category by year but also a variable for how much to offset the start of the depreciation. so for example software has a useful life of 3 years. I add $30000 of software in FY25 ($10000 a year depreciation) but also want to offset the depreciation by .5 years so FY25 would have $5000, FY26 10000 so on. I have created the input module below showing the exampl

I cannot wrap my head around how the calculation module would go. I know you can use post to push a value into the future but that is only a single output number. below is an excel snip of how the above example would look.

I can accomplish the above in excel, which I could then have it spit out the depreciation values for the user to copy and paste into a depreciation field but am wondering if this can be accomplished completely in anaplan.

Any help would be appreciated.

rob_marshall · March 21

@MatthewWilcox

Ok, see if this works out for you…

I have the same staging modules as you

but I added a line item called Offset Stage with the formula. I will use this as my multiplier.

I created a Fake Time (Year level) that mimics my real time (at the year level)

Now the fun part, the Calc module:

Module is dimensioned by: Time (year level), Assets, and Fake Years

Formulas (start from the bottom up):

Start Month?: ITEM(Time) = SYS Fake Year.Map to Time AND INP Assets.Additions <> 0

Dep Years: IF Start Month? THEN SYS Assets.Useful Life + INP Assets.Offset ELSE IF PREVIOUS(Dep Years) <= 1 THEN 0 ELSE PREVIOUS(Dep Years) - 1

Year Amount: IF Mulltiplier > 0 THEN INP Assets.Additions[LOOKUP: SYS Fake Year.Map to Time] / SYS Assets.Useful Life ELSE PREVIOUS(Year Amount) * Mulltiplier

Multiplier: IF Start Month? THEN INP Assets.Offset Stage ELSE IF PREVIOUS(Mulltiplier) > 0 AND Dep Years > 1 THEN 1 ELSE Dep Years

Data: Year Amount * Mulltiplier…..Also, notice the SUM for the Summary.

For a final of

Hope this helps,

Rob

rob_marshall · March 20

@MatthewWilcox

What are the additional rows in the Excel (rows 3-6)? Additional categories?

MatthewWilcox · March 20

Hi Rob,

Row 2 is the depreciation for the number entered in FY25 30k additions, .5 offset), row 3 is the number entered in FY26 (60k additions, no offset) so the total depreciation for FY25 is 5000 (.5 of the FY25 number), FY 26 is 30k which is 10k from 30k in FY25 and 20k from the 60k in FY26

MatthewWilcox · March 21

Thanks so much Rob thats exactly what I was looking for.

I had gotten about 80% there by creating a module with line items as time but didnt realise i could create a fake years mapped to real years (I will be updating similarly structured modules using versions) but i was running into massive nested ifs for the fractional years.

Your solution is much more elegant

MatthewWilcox · March 24

Hi Rob,

Sorry I encountered an error in the above working. sorry I forgot to mention that the depreciation offset can be greater than one (we can have projects that start incurring costs but have no depreciation for over a year.

MatthewWilcox · March 24

Hi Rob,

I think I solved it. in the asset calculation module I created a new line item offset start month whichi has the formula POST(IF ITEM(Time) = 'BP - SYS09 Time Converstion'.Map to Time AND 'BP - BS Asset Information Time Based Variables'.Additions <> 0 THEN 1 ELSE 0, ROUND('BP - BS Asset Information Time Based Variables'.Depreciation Offset, 0, DOWN))

I updated the start month? line item to Offset Start Month = 1

I updated the offset stage line item to IF Depreciation Offset = 0 THEN 1ELSE IF Depreciation Offset = ROUND(Depreciation Offset, 0, DOWN) THEN 1 ELSE Depreciation Offset - ROUND(Depreciation Offset, 0, DOWN)

and finally the multiplier line item to include a lookup to the time where the value is posted (IF Start Month?THEN 'BP - BS Asset Information Time Based Variables'.Offset Stage[LOOKUP: 'BP - SYS09 Time Converstion'.Map to Time]ELSE IF PREVIOUS(Multiplier) > 0 AND Dep Years > 1 THEN 1 ELSE Dep Years) which appears to be working.

Hopefully these formulas are best practice