Creating Creditor ageing report using formula sample 2 pg 86 87

Sample 2

This report shows the name of suppliers in each credit age group.

	A					B		C				D		E			F		G
11
12	CUSTOMER			0-30	30-60			60-90	90-120		>120	TOTAL
13	TRADE CREDITOR A	0.00	(43,446.00)		0.00	(1,273.70)	0.00 	(44,719.70)
14 	TRADE CREDITOR B	0.00	(2,750.00)		0.00	(563.50)	0.00	(3,313.50)
15	TRADE CREDITOR C	0.00	(43,748.00)		0.00	(3,031.55)	0.00	(46,779.55)
16	TRADE CREDITOR D	0.00	(1,849.19)		0.00	(797.99)	0.00	(2,647 18)
17	TRADE CREDITOR E	0.00	(1,312.32)		0.00	(1,605.32)	0.00	(2,917.64)
18	TRADE CREDITOR F	0.00	0.00			0.00	0.00		0.00	0.00
19	TRADE CREDITOR G	0.00	0.00			0.00	0.00		0.00	0.00
20	TOTAL			0.00	(93,105.51)		0.00	(7,272.06)	0.00	(100,377.57)

In cells B12 to F12:

Here we display the labels to represent each group of debt age as spelt in column CREDIT-AGE GROUP (in sheet DATA). The spelling must be the same.

86

In cells A13 to A19: Here are the supplier names as available in column ACCOUNT-CREDIT (in sheet DATA). The spelling must also be the same.

In cell B13: We want to get the total values in column RM-CREDIT where the supplier name in cell A13 matches what is available under column ACCOUNT CREDIT (in sheet DATA) and the Credit-Age group is the same with what is available under column CREDIT-AGE GROUP. We use this formula:
=SUMPRODUCT((DATA!I5:I30000=A13)*(DATA!S5:S30000=B12)*(DATA!J5:J30000))

Remember that in sheet DATA, column I represents ACCOUNT-CREDIT while column S represents CREDIT-AGE GROUP and column J represents RM-CREDIT

We can copy the formula to other cells. Be very careful when copying the formula to other cells. Make sure the formula points to the correct cells. You must know how to copy a formula so that a cell address does not change. In this case, when copying down, cell address B12 should not change. Then, when we copy to the right, the cell address A13 should not change.

In the cell G13: The formula here sums up the values in cells B13 to F13, ie. =SUM(B13:F13) Copy this formula down to other cells, until cell G19.

In the cell B20: The formula sums up the values in cells B13 to B19, i.e. =SUM(B13:819). Copy this formula to other cells on the right.

If you want to get the details for each customer and/or each group, you can use Data/Filter... operation. Refer to Topic 13 to learn more about filtering data.

NOTE

The above sample report is overly simplified. If you understand how the formula works, you can design the report differently to cater for more creditors' names.

87