Creating Creditor ageing report using formula sample 1 pg 85 86

Creating Creditor ageing report using formula

We can create a creditors ageing report either by using formulas or the PivotTable operation.

We can use a separate sheet to accommodate a Creditors Ageing report to be created using formulas.

Sample 1

	A					B					C
1
2	CREDIT AGE GROUP	RM					%
3	0–30				(1,097,766.61)		18.02
4	30-60				(1,243,035.99)		20.40
5	60-90				(2.603,497.82)		42.74
6	90-120				(1.147,774.63)		18.84
7	>120				0.00				0.00
8	TOTAL				(6,092,075,05)		100.00

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

In cell B3, we write an initial formula:

=SUMIF(DATA!$5:$30000,A3, DATA!J5:J30000)

This formula returns the sum (total) of values in range J5:J30000 (in sheet DATA) if the item in range 55:530000 (in sheet DATA) matches what is stated in cell A3 (in the current sheet). (Remember that range J5:J30000 in sheet DATA contain RM values while range $5:$30000 in sheet DATA contain credit-age group).

We then copy this formula to cells below it. Be careful when copying down the formula. Make sure the formula points to the correct cell addresses.

85

In cell C3, we have another initial formula:

=ROUND((B3/B8)*100,2)

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

In cell B8, we have this formula:

=SUM(B3:B7)

This formula simply totals the values of each credit-age group. Copy this formula to cell C8.

Format all the figures appropriately.

Make sure that the spelling you use for items in cells A3 to A7 is the same with the spelling you use in column CREDIT-AGE GROUP in sheet DATA.