Declining balance depreciation method

Hi, I want to calculate depreciation of assets  using declining balance depreciation method on Anaplan.

I went through Anapedia's financial function pages to find function like Excel's VDB, but couldn't find any function applicable for the method.

 

Is there any function that is available for declining balance depreciation method, or is there any other best practice for the method on Anaplan?

 

Best,

Answers

  • Hi @KokiS ,

     

    There is no function for this, but you can "recreate" it yourself via the different available functions. I'd have a look at the PREVIOUS() function.

     

    All the best,

    Maarten.

  • Hi @KokiS 

     

    Please explain the difficulty you're facing by creating a mock up in Excel, and then share the screen shots with as much descriptions as possible.

     

    By bringing up 2 separate topics (1 being finance formula related and 1 being Anaplan formula creation related), the group of users who can help you is smaller.

     

    Thanks,

    LipChean

  • Hi Koki,

     

    To create a depreciation schedule, you will need to create a module with three line items namely 1. opening balance, 2. depreciation ( per month) and 3. Closing balance.

     

    This module needs to be dimensioned by Assets ( list), time ( month ). 

     

    1. Opening balance: Net book value for a given asset as the opening balance plus previous( closing balance)

    2. Depreciation: This will be calculated (Acquisition cost or NBV at the beginning of the period divided by asset life ( useful economic life). 

    3. Closing balance: Opening balance less depreciation.

     

    You will also need a system module to calculate start and end periods as well as to factor in asset disposals.

     

    Let me know if above is of some help. If you need detailed guidance I am happy to help.

     

    Thanks

    N

     

     

  • @KokiS 

     

    See if this helps

     

    Step 1: I have created first module called Community Global Module where I store the assumptions. I have created it for one single asset category  but you can add multiple asset categories as a dimension if you wish to.

     

    Misbah_1-1592204823712.png

    Step 2: Created another module called Community Module - Declining Balance

    Misbah_0-1592205468361.png

    This module will respect the Salvage value of an asset and will not depreciate beyond that - Line 3rd above

    This module will also respect the Final Depreciated Value of an asset and will not cross the Salvage Value.

    Formulae in the Backend

    Amount:  Community Global Module.Amount

    Depreciation without Restrictions: IF NOT First Year THEN PREVIOUS(Asset Amount Until Salvage * Community Global Module.Accelerated Deprecation Percentage) ELSE Community Global Module.Accelerated Deprecation Percentage * Community Global Module.Amount

    Depreciation until Final Value Crosses Salvage: IF Asset Amount without Depreciation Restrictions >= Asset Amount Until Salvage THEN Depreciation without Restrictions ELSE PREVIOUS(Asset Amount without Depreciation Restrictions) - Asset Amount Until Salvage

    First Year: OFFSET(1, -1, 0) = 0. Practically it can not be the first year of a timescale, you can keep it manual but you need this boolean for every asset category to determine the first year of Asset

    Asset Amount Until Salvage: MAX(IF NOT First Year THEN PREVIOUS(Asset Amount Until Salvage) - Depreciation without Restrictions ELSE Amount - Depreciation without Restrictions, Community Global Module.Salvage)

    Asset Amount without Depreciation Restrictions: IF NOT First Year THEN PREVIOUS(Asset Amount Until Salvage) - Depreciation without Restrictions ELSE Amount - Depreciation without Restrictions

     

    Note: You can add Shelf Live as well  in  your calculations to zero out all the future years which are irrelevant for the asset category

     

  • Hi Maarten

     

    Thanks for your quick reply.

    I'll check PREVIOUS() function you mentioned.

     

    Best,

  • Hi LipChean

     

    Thanks for your reply.

    I got some solutions that might work for my problem here.

    So I will try if those solutions work for me. And if they doesn't work, will post further information in detail as you described.

     

    thanks,

  • Hi Naveed

     

    Thanks for your information.

    It helped me a lot.

    currentry l'm trying to apply the method to my model, and might ask some question again.

     

    Thanks,

  • Hi Misbah

     

    Thanks for detailed information.

    l'm trying to apply the method to my model. if the problem is solve, i will let you know.

     

    Thanks,