Reconciling Account balance to invoices over a period

BA

Hi Team,

I have some data which I am trying to figure out how much an account balance reconciles to invoices over a period of time for each account. There are 2 calculations I am trying to do:

1. Assign the 'amount to reconcile' (col 3) to the invoice amounts replacing what is there.  If there is a shortfall to replace then what ever is remaining should go there replacing the invoice amount.

2. The remaining balance left if the Account balance is greater than the invoice amount.

Original Data:

Account#	Invoice ID	Amount to Reconcile	26/04/2021	25/07/2021	24/10/2021	23/01/2022	24/04/2022	31/07/2022	30/10/2022
12345	A5678	44.93	3.76
12345	A975	44.93		25.6
12345	B656	44.93						1.2
56789	C68695	125.61			126.78
98755	H864	21.32							25.78
19765	D9594	128.93				66.33
19765	H494850	128.93					1.2
19765	K388343	128.93					20.02
98056	C68695	576.78		456.78
98056	C68695	576.78			235.87

Desired Outcome:

Account#	Invoice ID	Amount to Reconcile	26/04/2021	25/07/2021	24/10/2021	23/01/2022	24/04/2022	31/07/2022	30/10/2022	Remaining balance which did not reconcile ( col B - sum of col C to I)
12345	A5678	44.93	3.76							41.17
12345	A975	44.93		25.6						15.57
12345	B656	44.93						1.2		14.37
56789	C68695	125.61			125.61					0
98755	H864	21.32							21.32	0
19765	D9594	128.93				66.33				62.6
19765	H494850	128.93					1.2			61.4
19765	K388343	128.93					20.02			41.38
98056	C68695	576.78		456.78						120
98056	C68695	576.78			120					0

I have attached this in a spreadsheet if it is easier to play around with (see raw data tab in attached file).

Happy to answer any questions or provide further clarity.

Thanks in advance

Example.xlsx

Gawa

hi @BA 

I believe that I could realize your demand by attached workflow, however if not, please let me know.

Personally it was a kind of fun for me like solving weekly challenge

1189592_gawa.yxzp