Depreciation Calculator - Iterative Macro

[m_tizzie]

I am trying to replicate a depreciation calculator that was previously done in Excel with formulas.  Essentially, I have a list of approximately 100+ assets and I want to calculate the Current Year Expense, YE NBV, and Straight Line Depreciation for each year of those assets' lives.  Due to half year double declining balance depreciation, the calculations are a bit more complicated and are dependent on the prior year being calculated before you can calculate the current year so multi-row formulas were not working for me.  I'm currently trying to create an iterative macro that will perform the calculations one row at a time but I'm having issues getting it to loop.  I am brand new to iterative macros and have been trying to research how to set them up but all the examples I'm seeing are different than what I need.  I would appreciate any help regarding how to correctly set up my iterative macro or ideas of other ways to go about this if I'm missing something. 

Thank you in advance for your help!  

Attached is...

- Input Excel file

Only one asset is currently shown for simplicity.  Year 1 calculations as well as the duplication of rows for the number of years of life were all added in as part of a workflow to set up the data in preparation for the iterative macro loop calculations to be done.

- Alteryx Macro workflow 

This workflow does not include all the necessary calculations yet.  I was just trying to get the loop to work correctly before adding in all the details.

- Screenshot of Depreciation Calculator in Excel

This is just to show what the expected values would be

[Pilsner]

Hello @m_tizzie,

I dont think I have understood your exact problem properly however I have tried to provide some general things to watch our for when using iterative macros.

First and formost, whilst I dont think this applies in your scenario, make sure an itterative macro is actually required. Sometimes people will go to an iterative macro for scenarios where a batch macro would be best suited. Generally speaking, the only times an iterative macro should be required is when you need the output from a previous loop (itteration) to be used in the subsequent iteration. 

Secondly. If you are trying to process one record at a time, I would suggest using a sort tool to get the records in your desired order, outside of the macro. Once the records are in the order you require, put a record ID infront of them:

With this Record ID, inside the macro, you can use a filter tool with the formula [Record ID] = [Engine.IterationNumber] + 1, to force the iterative macro to process your records in the desired order. 

Next, in the image above, you will notice that I have attached my processed records to both the exit output and a union tool. This ensures that both the processed and unprocessed records are looped round each time. Doing this enables you to use the output from the previous iterations in any of the future iterations. However, please be careful as if this is not done with care, it can cause the data to grow exponentially. 

Finally, in the interface designer you need to think carefully about yout output option:

It sounds to me like you are expecting a consistent schema so you may want to select "All iterations will habe the same output schema (Error if different)", however if you are expecting the schema to change you will need to adjust this option accordingly.

I have attached the sample workflow I created below incase it would be helpfull. 

I hope the above all makes sencem please let me know if you have any quesitons.

Regards - Pilsner

[apathetichell]

Hey --- This is not how you do things. You post your excel file with your calcs === not a .png and then we help you figure out the logic. My hunch is part of your issue is Alteryx --- and part of your issue is you don't know the match. How are you calculating straight line here? I don't think you need a macro --- batch or iterative --- at least not for one. you do need to know match and how to use multi-row formula --- and how to build a formula tool to create outlier values.

[PangHC]

no idea on how the calculation work...

sample screenshot of workflow. I will not post my solution.
 
add one more column for [month for first year], where it used in if else for first year and extra year.
the rest is straight forward.

note: change to [month for first year] to 5 to test the accuracy. 

[apathetichell]

@PangHC If I could give out gold starts for the month on community you would win for August. Did you try to back into the straight-line depreciation formula used? I gave up.

[m_tizzie]

@Pilsner  I appreciate the thorough explanation!  This was extremely helpful as I am still learning the basics of macros and this helped explain how to best set them up.  I have updated my workflow to reflect the example that you provided.  I created an Iteration Number column as you did and can see the number going up which I am assuming means the workflow is looping properly.  However, my multi-row calculation doesn't appear to be working and I can't figure out why.  Again, I only have one calculation for now (more are required) but I'm just trying to get things to work and then move on from there.  Is there anything that you can see that I missed that would be causing the workflow to not calculate?  I would expect RecordID2 to be $21,524 (Row1 YE NBV / Row1 Remaining Life * 12).

[apathetichell]

you don't need a macro:

1) generate rows --- model this on your depriciation years --- start on row 0 please.

1) formula tool -- set up the rates for your rows -- this would be 1-(2*1/11) or whatever --- you can put row 1 as an outlier using an if statement. This should be 1-(1/11) --- for year 0 this should be 0.

2) multi-row formula --- create a new fixed decimal called year end value... this should be something life if [_recordID]=0 then [your book value field] else [row-1;your ending book value field]*[your rate field] endif.

3) create  a second multi-row formula --- use it to caputure your delta from [row-1:ending book value]-[ending book value] use ifs to capture the first the first row as zero.

4) isolate your year 7 value --- use a filter tool. this rename your delta as your ending monthly or something 

5) append this back to your main data stream. formula 1)  if record id is greater than or equal to seven--- overwrite your value. in delta. for th last row this should be half your value.

6) attach a running total tool --- and sum up your delta --- this is accrued depreciation.

7) attach a new formuila tool --- use this to overwrite your ending book value for years 7-12 with your beginning bv --- your accrued depreciation. append this from year 0 if you have to.

8) you will need a beginning balance column --- you can create this by shifting the ending balance using the multi-row --- if you don't have it.

9) use your excel formula for straight line depreciation

This may seem hard ---  but it's level easier than an iterative macro.

[PangHC]

@apathetichell hmm i understand it. if not mistaken

The trick is just the NBV is deduct by CY expense instead of Depr.

CY Expense (99% is manual input), it direct deduct the YE NBV.
and Accum Reserve.


@m_tizzie if it manual input,
Option 1: still do it in excel, why change if the template is good. you may just do the combine part.
Option 2: prepare a file for CY expense for respective year and respective asset, then play around the data with join.

another small problem is one problem is how the depr factor is calculate. i cant tie the amount.

[apathetichell]

@PangHC - so two things

!) the straight line is just the formula --- you'll need to compute the opening balance like you did -- -and then you can create the straight line number.

2) the depr factor is just the cy depr/original balance (so for year 2 for example it's just 41,090.91/248,600 

[m_tizzie]

@PangHC Unfortunately, the issue with keeping the current Excel file is that the team has to process hundreds of assets at a time and they are currently inputting one asset at a time to retrieve the depreciation calculations and then combining them all into one spreadsheet.  It's extremely time intensive which is why I am trying to find another solution for them.

I've attached the SAP calculator in Excel format so you can see the formulas behind it.  I apologize for not posting this to start with.  CY Expense is not a manual input - It is reliant on prior year YE NBV and Straight Line depreciation being calculated.  Since each of the columns is dependent on a prior year value to calculate, that's what is giving me a hard time. 

I plan to try some of the suggestions today to see if I can get any closer to a solution.  Thank you!