Ageing Report based on conditions

[kunal86]

Hi,
I have invoice wise outstanding details where on-account payment is received & ageing Report is calculated based on no of days from transaction date.
I want ageing Report of Outstanding amount based on transaction date but on account receipt should be adjusted against last ageing bracket.
Example - In 1st table ageing is calculated based on transaction date but I want ageing Report of open transaction after adjustment of on account receipt as per 2nd table.
Thanks in advance for providing correct excel formulas.

 

[CA Vijay Gupta]

3. Ageing.xlsx
	B	C	D	E	F	G	H	I	J	K	L
3	Account	Customer Name	Type2	Outstanding	OD	0-7	7-15	15-30	30-45	45-90	>90
4	190175	Global Tradi ng Company	Open	3,33,180.00	3,33,180.00	-		38,180.00	2,57,600.00	37,400.00	-
5			On account	-2,03,740.00	-2,03,740.00	-	-1,00,000.00	-1,00,000.00	-3,740.00	-	-
6		Total		1,29,440.00	1,29,440.00	-	-1,00,000.00	-61,820.00	2,53,860.00	37,400.00	-
7
8	Account	Customer Name	Type2	Outstanding	OD	0-7	7-15	15-30	30-45	45-90	>90
9	190175	Global Trading Company	Open	3,33,180.00	3,33,180.00	-	-	38,180.00	2,57,600.00	37,400.00	-
10			On account	-2,03,740.00	-2,03,740.00	-	-	-	-1,66,340.00	-37,400.00	-
11		Total		1,29,440.00	1,29.440	-	-	38,180.00	91,260.00	-	-
Sheet1

Cell Formulas
Range	Formula
G6:L6,G11:L11	G6	=SUM(G4:G5)
G10:K10	G10	=-MIN(G9,ABS(ABS($F$10)-SUM(ABS(H$10:$M10))))
L10	L10	=-MIN(L9,ABS(ABS($F$10)-SUM(ABS($M$10:M10))))

 

[kunal86]

Th

3. Ageing.xlsx
	B	C	D	E	F	G	H	I	J	K	L
3	Account	Customer Name	Type2	Outstanding	OD	0-7	7-15	15-30	30-45	45-90	>90
4	190175	Global Tradi ng Company	Open	3,33,180.00	3,33,180.00	-		38,180.00	2,57,600.00	37,400.00	-
5			On account	-2,03,740.00	-2,03,740.00	-	-1,00,000.00	-1,00,000.00	-3,740.00	-	-
6		Total		1,29,440.00	1,29,440.00	-	-1,00,000.00	-61,820.00	2,53,860.00	37,400.00	-
7
8	Account	Customer Name	Type2	Outstanding	OD	0-7	7-15	15-30	30-45	45-90	>90
9	190175	Global Trading Company	Open	3,33,180.00	3,33,180.00	-	-	38,180.00	2,57,600.00	37,400.00	-
10			On account	-2,03,740.00	-2,03,740.00	-	-	-	-1,66,340.00	-37,400.00	-
11		Total		1,29,440.00	1,29.440	-	-	38,180.00	91,260.00	-	-
Sheet1

Cell Formulas
Range	Formula
G6:L6,G11:L11	G6	=SUM(G4:G5)
G10:K10	G10	=-MIN(G9,ABS(ABS($F$10)-SUM(ABS(H$10:$M10))))
L10	L10	=-MIN(L9,ABS(ABS($F$10)-SUM(ABS($M$10:M10))))

Thanks for reply.

Actually i have data in multiple line item as per below & need ageing in against each line item but on account receipt should be adjusted old overdue amount of each customer.

Account	Bill.Doc.	Doc. Date	Amt in loc.cur.	Net due dt	Customer Name	Outstanding	OD	0-7	7-15	15-30	30-45	45-90	>90
190175	230089179	02-Aug-23​	37,400.00	12-Aug-23​	Global Trading Company
190175	230089214	08-Aug-23​	2,57,600.00	18-Aug-23​	Global Trading Company
190175	267000412	14-Aug-23​	(3,740.00)	14-Aug-23​	Global Trading Company
190175	230089841	28-Aug-23​	22,484.00	07-Sep-23​	Global Trading Company
190175	1400002370	29-Aug-23​	(1,00,000.00)	29-Aug-23​	Global Trading Company
190175	230089982	31-Aug-23​	15,696.00	10-Sep-23​	Global Trading Company
190175	1400002617	13-Sep-23​	(1,00,000.00)	13-Sep-23​	Global Trading Company
191746	230083045	31-Dec-22​	1,66,674.00	30-Jan-23​	IMPERIAL INTERNATIONAL
191746	230083800	31-Jan-23​	2,37,017.00	02-Mar-23​	IMPERIAL INTERNATIONAL
191746	100019558	28-Feb-23​	(16,741.00)	28-Mar-23​	IMPERIAL INTERNATIONAL
191746	230082700	01-Mar-23​	47,207.98	20-Jan-23​	IMPERIAL INTERNATIONAL
191746	1400005256	18-Mar-23​	(1,50,000.00)	18-Mar-23​	IMPERIAL INTERNATIONAL
191746	230085553	31-Mar-23​	2,64,610.00	30-Apr-23​	IMPERIAL INTERNATIONAL
191746	1400005455	31-Mar-23​	(1,00,000.00)	31-Mar-23​	IMPERIAL INTERNATIONAL
191746	100001439	31-Mar-23​	(36,437.00)	28-Mar-23​	IMPERIAL INTERNATIONAL