Creating debt ageing report using formula sample 1 pg 74 75

Creating 'Debt Ageing' Report Using Formulas

We can use a separate sheet to accommodate a Debt Ageing report.

Sample 1

Here is a sample structure of a simple report:

	A	B	C
3   DEBT AGE GROUP		RM				%
4	0-30				2.909.964.40	35.75
5	30-60				1.955.195.68	24.19
6	60-90				3.148.617.09	30:35
7 	90-120				89,296 20		1.10
8	>120				0.00			0.00
9	TOTAL				8.083.072.37	99.99

This sample show the amounts for each debt-age group and its percentage.

In cell A4: Starting from this cell downwards, write each debt age group. Make sure the spelling is the same as that used in column P in sheet Date.

In cell B4: Write the initial formula:
=SUMIF(DATA!P5:P30000,A4,DATA!H5:H30000)

This formula returns the sum (total) of values in range H5:H3000 (in sheet DATA) if the items in range P5:P30000 (in sheet DATA) matches what is stated in cell A4 (in the current sheet). (Remember that range H5:H30000 in sheet DATA contain RM values while range P5-P30000 in sheet DATA contain debt-age groups).

We then copy down this formula to cells below it (until cell B8, in this sample). Be careful when copying down the formula. Make sure the formula points to the correct cells.

74
In cell C4: Write the initial formula: =ROUND(B4/89)*100,2)

This formula calculates the percentage of a debt-age group value compared with the total debt. We then copy down this formula to the cells below it (until cell C8, in this sample). Make sure when copying, the formula points to the correct cell address. You must know how to copy a formula so that a cell address does not change. In this case, when copying down, cell address B9 should not change.

In cell B9: Write this formula:
=SUM(B4:BB)

This formula simply totals the values of each debt-age group. Copy this formula to cell C9. Remember to format all the figures appropriately.

Make sure that the spelling you use in cells A4 to A8 is the same as the spelling you used in column CREDIT-AGE GROUP in sheet DATA.

To get the rows of details for each group, use AutoFilter operation on the data in sheet DATA. You can read more about this in the chapters on Filtering (Topic 13) and on the AutoFilter Primer (Topic 32).