Calculate Forecast by Month

Hello,

I have attached a sample of INPUT data and the required OUTPUT which I am trying to calculate in Alteryx. Looking at loan data for Jan – Sep 2022. Requirements: for each loan number, calculate the PD_ACTUAL (9 months) and PD_FORECAST (next 12 months). The variables in columns F-G change each quarter however I left them the same for simplicity. Please notice how the formulas change from month to month in calculating forecast. Additional columns can be added for solution, I mostly need the PD actual & forecast. I am stuck and don’t know if a macro is necessary.

I appreciate your help! Let me know if questions!

loan data.xlsx

Preparation

Accepted answers

MatthewO

@bertal34 - I was able to replicate your output with the attached workflow. The bulk of the logic occurs in the Multi-Row Formula tool. I hope this is helpful for you!

Forecast_by_Month.yxzp

All comments

Felipe_Ribeir0

Hi @bertal34

With the attached workflow i was able to make it parcially. The 3 last columns are a pain, but it is possible to do it using the multi row formula with some effort. Maybe you or someone here could finish it

Hope this is usefull, later i am gonna try to make these 3 columns work again if there is no solution.

Calculate Forecast by Month_PARCIAL.yxzp

MatthewO

@bertal34 - I was able to replicate your output with the attached workflow. The bulk of the logic occurs in the Multi-Row Formula tool. I hope this is helpful for you!

Forecast_by_Month.yxzp

bertal34

@MatthewO this looks right on, THANK YOU so much!! Glad this is able to be done using the multi-row and no need to process records individually through a macro.

Quick Links

This months top contributors

AkimasaKajitani 389

mceleavey 388

mbarone 337

Hollingsworth 335

LanisC 335