monthly depreciation forecast

Hi community,

I'm trying to build depreciation forecast for assets. this is the data in Alteryx format I build so far. I would like to build in monthly depreciation flow so that I can calculate yearly depreciation amounts for different groups. also in any given year I would like to separate new depreciation vs carry over from last year.

Any help will be greatly appreciated

Thank you!

Developer

Developer Tools

Accepted answers

apathetichell

Wasn't sure of the final form you wanted but your data should all be in here for you to summarize as you want... I broke out monthly cumulative depreciation sums by groups and monthly remaining values by group at the end. Hope this helps! and let me know if you are trying to figure out the next step and what you want to get out of the data.

depreciation analysis.yxmd

All comments

apathetichell

post your data as data and I can walk you through this... There are a slew of ways to show depreciation in Alteryx but with a fixed amount - running total is fantastic... You can group and sum the cumulative periodic depreciation and then just subtract that from the original amount to get the ending balance... I'd suggest converting to real dates though in case you need date interactivity- and it helps on sorting. Also - if you have more of a fixed income amortization situation there are finance functions in Alteryx (both with the formula tool and the pre-existing functions) whcih can do some of the work for you.

kseniya_sbitneva

Hi,

Thank you for taking a look at this problem. I attached file with data. Also I did some search on alteryx discussions and was able to find few steps that seems to be heading right direction but would love to learn from you as well. screenshot below with what i have done so far. I'm just learning Alteryx and trying to find different ways of solving .my work might look a bit silly for expert but hoping one day to be better at it

Group	Life in Months	Aquisition Date	Purchase Date	Acquisition Amount	Year	Monthly depreciation
GroupA	60	Jul-19	7/1/2019	25297.91	2019	421.6318
GroupA	60	Aug-19	8/1/2019	184735.1	2019	3078.918
GroupA	60	Dec-19	12/1/2019	526255.3	2019	8770.921
GroupA	60	Jan-20	1/1/2020	281835.2	2020	4697.253
GroupA	60	Jun-20	6/1/2020	4681.5	2020	78.025
GroupA	60	Nov-20	11/1/2020	52363	2020	872.7167
GroupA	60	Jul-21	7/1/2021	291750	2021	4862.5
GroupA	60	Aug-21	8/1/2021	580552	2021	9675.867
GroupB	36	Sep-20	9/1/2020	2723769	2020	75660.25
GroupB	60	Feb-20	2/1/2020	5400407	2020	90006.79
GroupB	60	May-20	5/1/2020	88818.01	2020	1480.3
GroupB	60	Dec-20	12/1/2020	67909.87	2020	1131.831
GroupB	60	Feb-21	2/1/2021	36942.8	2021	615.7133

Depreciation for all groups	total	new	carryover
2019		26,695	26,695
2020		########	########	147,258
2021		########	84,327	########

depreciation by group
GroupA		total	new	carryover
2019		26,695	26,695
2020		205,916	58,659	147,258
2021		292,588	77,554	215,034

apathetichell

No problem! Alteryx is great for these kind of projects and sometimes it's hard to track down sources for ways to go about it... I've posted a few of these kind of projects up here and usually the original posters seem to get distraught because the implementation doesn't require an iterative macro or something.... Just two quick questions -

Would you start depreciation cycle from purchased month or receipt month - and does it start at month 0 or month 1?

apathetichell

depreciation analysis.yxmd

kseniya_sbitneva

Hi, sorry could not reply sooner. Depreciation in my case starts at acquisition date so if asset was acquired in July 2019 (with depreciation starting July 2019) final payment would be Jun 2024.

kseniya_sbitneva

Thank you for helping out 🙂 glad to know people here can help with any question 🙂

m_tizzie

Is there a way to update the Generate Rows tool so rows are only generated up until the next year? For example - Purchase date is June 2024 - I only want new rows through the end of the year (December 2024).

apathetichell

yes.

<Expression_Init>[Purchase Date]</Expression_Init>
<Expression_Cond>datetimediff([RowCount],[Purchase Date],"Months")<=60</Expression_Cond>
<Expression_Loop>datetimeadd([RowCount],1,"months")</Expression_Loop>

is what I see in the XML... swap the cond to look for

datetimeparse(datetimeformat([Purchase Date],'%Y")+"-12-31")=datetimetrim([RowCount],'lastofmonth')

m_tizzie

@apathetichell - Thank you for the quick reply. I am getting an error - "An operator must be between operands (Condition Expression) when replacing the Condition Expression with what you listed.

apathetichell

,'%Y") --- has a typo. it should be ,"%Y") --- you should try to troubleshoot this stuff.

Quick Links

This months top contributors

atcodedog05 19458

Qiu 15866

binu_acs 15708

MarqueeCrew 13708

apathetichell 13703