Creating Debt Ageing report using formula sample 2 page 75 76 77

Sample 2

This report (in a separate sheet) shows the names of suppliers in each debt-age group

	A			B				C				D				E			F			G
1	
2	CUSTOMER	0-30			30-60			60-90			90-120		>120		TOTAL
3	CUSTOMER A	0.00			1.061.054.35	183.487.16		0.00		0.00		1,250,541.51
4	CUSTOMER B	347.467 14		474.091.73		361.508.41		0.00 		0.00		1,183,058.20
5	CUSTOMER C	594.256.75		0.00			456.114 78		0.00		0.00		1,050,371.53
6	CUSTOMER D	260,878.21		0.00			365.834.80		0.00		0.00		626,713.09
7	CUSTOMER E	502,142.54		0.00			541,227.39 		0.00		0.00		1,043,369.93
8	CUSTOMER F	336.624.26		0.00			453.847.28		0.00		0.00		790,471.54
9	CUSTOMER G	848,595.50		0.00			639.004.79		0.00		0.00		1,487,630.29
10	TOTAL		2,803,964.40	1.535,136.08	3,007,055.69	0.00		0.00		7,432,156.17

75

Cells B2 to F2: Here we have the labels to represent each unique group of debt age as available in column DEBT AGE GROUP (in sheet DATA). The spelling of groups must be the same.

Cells A3 to A9: Here we write the labels to represent each unique customer name as available in column ACCOUNT-DEBIT. The spelling must be the same.

Cell B3: We want to get the total of values in column RN-DEBIT (in sheet DATA) where the customer name in cell A3 (in current sheet) matches what is available under the column ACCOUNT-DEBIT (in sheet DATA) and the debt-age group (in the current sheet) matches what is available under column DEBT-AGE GROUP (in sheet DATA). We use this formula:
=SUMPRODUCT((DATA!G5:630000=A3) (DATA PS:P30000-82) (DATAINS: H30000))

(Remember that in the sheet DATA, column G represents ACCOUNT-DEBIT, column P represents DEBT-AGE GROUP and column H represents RM-DEBIT)

We can copy the formula to other cells below it. Be very careful when you do this. Make sure the formula points to the correct cells.

NOTE

You should know how to copy a formula so that a cell address in the formula does not change when copied to another cell. In the above formula, cell address B2 should not change when we copy down the formula. Then, when we copy to the right. cell address A3 should not change.

Cell G3: The formula in this cell sums the values in cells B3 to F3, ie.
=SUM(B3:F3).

Copy down this formula to other cells.

76
Cell B10: The formula in this cell sums the values in cells B3 to B9, i.e.
=SUM(B3:B9).
Copy this formula to other cells on the right.

To get the row of details of each customer in each group, you can use Filtering operation. You can refer to Topic 13 for more information on Filtering.

If you understand how the formula works, you can design the report differently.