Declining balance depreciation method

KokiS

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,

MaartenGijbels

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.

LipChean_Soh

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

NB

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

 

 

Misbah

@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.

 

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

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

 

KokiS

Hi Maarten

 

Thanks for your quick reply.

I'll check PREVIOUS() function you mentioned.

 

Best,

KokiS

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,

KokiS

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,

KokiS

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,