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!
Solved! Go to Solution.
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.
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 |
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?
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.
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.
Thank you for helping out 🙂 glad to know people here can help with any question 🙂