Liquidity Planning | From Actual to Plan

[maier_alexander_MO]

Hi all,

we are using Alteryx for aggregation of Liquidity Planning data. At the end of my current workflow I get one output that is my current liquidity (as of end-of-month). The second output is the planned liquidity (sum for one month) for the next 3 months.

The following shows my current output I have to work with - row 1 will always show only the last Actual amount (from end of month, thus from 30/09/2019)

MonthYear	Amount_in_EUR	DataType
2019-09-01	25125511.25	Actual
2019-09-01	-23451.56	Plan
2019-10-01	-11235111	Plan
2019-11-01	352155611.23	Plan
2019-12-01	-2052521	Plan

I would have to do the following two steps:

1. Delete all rows with Plan data that have a date that is the same or older than the Actual date in row 1 (in this case row 2 would have to be eliminated, since this is old data that is still in the input. It could also be more date from e.g. August that would have to be deleted)

After this the output should be as followed - the Plan rows show the planned sum for each month:

MonthYear	Amount_in_EUR	DataType
2019-09-01	25125511.25	Actual
2019-10-01	-11235111	Plan
2019-11-01	352155611.23	Plan
2019-12-01	-2052521	Plan

2. Now I would like to calculate a new column that cumulates all the values row by row. Thus in the first row there would be the original value, row two would show original value + October amount etc.

MonthYear	Amount_in_EUR	Cumulated_in_EUR	DataType
2019-09-01	25125511.25	25125511.25	Actual
2019-10-01	-11235111	13890400.25	Plan
2019-11-01	352155611.23	366046011.48	Plan
2019-12-01	-2052521	...	Plan

I would be really thankful if you could help me with this!

Thank you very much in advance and kind regards,

Alex

[neilgallen · Accepted solution]

This is easily obtained! The early steps establish your latest Actual row, and then remove any plan row before this. I did it this way to account for future changes in the dataset or a table that may not be correctly formatted in sequence.

The later steps are just calculating the running total.

let me know if there are any questions..

[alexcordero · Accepted solution]

Alternative to getting the max date - otherwise pretty much the same methodology.  Would need to adjust if using for more than 1 client at a time or if DataType field values change and your starting point is not in alphabetical order (start vs actual).

[maier_alexander_MO]

Hey  @neilgallen  and @alexcordero :

Thank you so much for your quick help on this, it worked out perfectly and I can now show a graph with the actuals as a line and on the dual axis also the forecasted liquidity! 🙂

Have a great day and thank you again!

Kind regards,

Alex